Note The same concepts and discussions about MSDE in this article also apply to SQL Server 2005 Express Edition.
MSDE uses two authentication modes:
- Windows Authentication Mode (Windows Authentication)
In Windows Authentication Mode, a user can connect through a Microsoft Windows NT 4.0, a Microsoft Windows 2000, or a Microsoft Windows XP user account.
- Mixed Mode (Windows Authentication and SQL Server Authentication)
In Mixed Mode, users can use either Windows Authentication or SQL Server Authentication to connect to an instance of MSDE. Users who connect through a Windows NT 4.0, a Windows 2000, or a Windows XP user account can use trusted connections in either Windows Authentication Mode or Mixed Mode.
For more information about authentication modes in MSDE, see the following MSDN Web site:
- Microsoft Windows XP Professional, Microsoft Windows 2000 Professional, Microsoft Windows 2000 Server, Microsoft Windows 2000 Advanced Server, or Microsoft Windows NT 4.0 Server
- Microsoft MSDE 1.0 or Microsoft SQL Server 2000 Desktop Engine (MSDE 2000)
- Database terminology
- Connect to MSDE from the local computer (by using Windows Domains), and log on to Windows under an administrator account.
- Connect to MSDE remotely if the Windows user account has been added to the Administrator group on the remote system that is hosting MSDE.
Under some circumstances, you may be required to use SQL Server Authentication. The following list outlines some of the circumstances in which you must use SQL Server Authentication:
- MSDE is running on Windows 98. Because Windows Authentication Mode is not supported on Windows 98, MSDE uses Mixed Mode authentication when it is running on Windows 98 (but supports only SQL Server Authentication).
- You connect to MSDE over a network that is not using Windows domains. In this case, there is no domain controller that can validate your Windows account.
- Your Windows account is not, or cannot be, added to the system that is hosting the instance of MSDE that you are trying to connect to. However, you do have an account that you can use SQL Server Authentication to connect with.
WARNING: The built-in SA user account is installed without a password. This enables you to connect to MSDE initially. However, if you enable SQL Server Authentication, you must create a password for this account immediately. To create a password for the built-in SA account, follow these steps to use OSQL, which is a command-line utility that is installed with MSDE:
- On the computer that is hosting the instance of MSDE that you are connecting to, open the command prompt window.
- Type the following command, and then press ENTER:osql -U saThis connects you to the local, default instance of MSDE by using the SA account.
- Type the following commands on separate lines, and then press ENTER:
NOTE: Make sure that you replace 'mynewpassword' with the new password.sp_password null, 'mynewpassword', 'sa'Notice that you receive the following message, which indicates that your password was changed successfully:
Important This section, method, or task contains steps that tell you how to modify the registry. However, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs. For more information about how to back up and restore the registry, click the following article number to view the article in the Microsoft Knowledge Base:
The location of the LoginMode subkey depends on whether you installed MSDE as the default MSDE instance or as a named instance. If you installed MSDE as the default instance, the LoginMode subkey is located in the following registry subkey:
- In Control Panel, open the Services tool to stop MSSQLSERVER and all other related services (such as SQLSERVERAgent)
- To open Registry Editor, click Start, click Run, type regedt32, and then click OK.
- Locate one of the following subkeys (depending on whether you installed MSDE as the default MSDE instance or as a named instance):HKEY_LOCAL_MACHINE\Software\Microsoft\MSSqlserver\MSSqlServer\
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\<Instance Name>\MSSQLServer\
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer
- In the right pane, double-click the LoginMode subkey.
- In the DWORD Editor dialog box, set the value of this subkey to 2, make sure that the Hex option is selected, and then click OK.
- Restart the MSSQLSERVER and the SQLSERVERAgent services for this change to take effect.
- Right-click on your computer desktop, click New, and then click Text Document. Rename the file Test.udl.
- Double-click the .udl file to open the Data Link Properties dialog box.
- On the Provider tab, click Microsoft OLE DB Provider for SQL Server.
- On the Connection tab, follow these steps:
- Under item 1, select or type the server that you want to connect to.
- Under item 2, select one of the following options:
- Use Windows NT Integrated security
Select this option if you are using Windows Authentication.
- Use a specific user name and password
Select this option if you are using SQL Server Authentication. If you select this option, you must type the user name and the password.
- Use Windows NT Integrated security
- Click Test Connection. If the connection is successful, you receive a message that confirms that the test connection succeeded.
- If the SQL Server service is not currently running, your test connection fails. To verify that the SQL Server service is running, click the MSSQLServer icon in the notification area, and then make sure that the status is displayed as "running."
- When you try to connect to MSDE by using the built-in SA account, your connection may fail if someone has already created a password for that account.
- If you cannot run the OSQL utility on the computer that is hosting MSDE, run the OSQL utility from another computer, and then use the -U command-line switch to specify the server in the connection command. For example:osql -s servername -U sa
Integrating MSDE 2000 with your Applications
Article ID: 319930 - Last Review: Mar 23, 2009 - Revision: 1