How to connect to an instance of SQL Server Desktop Edition or of SQL Server 2005 Express Edition
This article was previously published under Q319930
IN THIS TASK
This step-by-step article describes how to establish a connection to an instance of Microsoft SQL Server Desktop Engine (MSDE) or of Microsoft SQL Server 2005 Express Edition.
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:
Authentication Modesback to the top
RequirementsThe following list outlines the recommended hardware, software, network infrastructure, and service packs that are required:
- 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 with Windows NT authenticationMSDE achieves logon security integration with Windows NT 4.0, Windows 2000, or Windows XP by using the security attributes of a network user to control logon access. A user's network security attributes are established at network logon and are validated by a Windows domain controller. When a network user tries to connect, MSDE uses Windows-based facilities to determine the validated network user name. To successfully connect to and administer MSDE under Windows Authentication, one of the following conditions must be met:
- 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.
back to the top
Connect to MSDE with SQL Server authenticationWhen a user connects with a specified logon name and password from a non-trusted connection, MSDE performs the authentication by checking if a SQL Server logon account has been set up with a password that matches the password that the user specifies. If MSDE does not have a logon account set, authentication fails.
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:
Enable Mixed Mode authentication during installationDuring installation, you can change the authentication mode that MSDE uses by running the installation with the following command parameter:
This command parameter causes MSDE to install with Mixed Mode authentication. With this authentication mode, you can connect to MSDE by using Windows Authentication or SQL Server Authentication.
back to the top
Enable Mixed Mode authentication after installationImportant 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:
322756 How to back up and restore the registry in WindowsBy default, the value of the LoginMode Windows registry subkey is set to 1 for Windows Authentication. To enable Mixed Mode authentication after installation, you must change this value to 2.
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:
If you installed MSDE as a named instance, the LoginMode subkey is located in the following registry subkey:
If you are using SQL Server 2005 Express Edition, the LoginMode registry entry is located in the following registry subkey:
HKLM\Software\Microsoft\Microsoft SQL Server\<Instance Name>\MSSQLServer\LoginMode
To change the value of LoginMode to 2, follow these steps:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer
- 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.
Verify connectivityFollow these steps (which are not specific to a technology) to make sure that you can connect properly to an instance of MSDE:
- 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
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
325022 MSDE security and authenticationFor more information about using Windows Installer if you are customizing setup for SQL Server 2000 Desktop Engine, click the following article number to view the article in the Microsoft Knowledge Base:
227091 Command-line switches for the Microsoft Windows Installer toolFor more information about changing the default authentication mode for SQL Server 2000 Desktop Engine, click the following article number to view the article in the Microsoft Knowledge Base:
285097 How to change the default login authentication mode to SQL while installing SQL Server 2000 Desktop Engine by using Windows InstallerFor more information about using SQL Server Desktop Engine, see the following Microsoft Web sites:
SQL Server 2000 Desktop Engine (MSDE 2000)
Integrating MSDE 2000 with your Applications
Integrating MSDE 2000 with your Applications
Article ID: 319930 - Last Review: 05/18/2007 15:54:37 - Revision: 4.5
Microsoft SQL Server 7.0 Desktop Edition, Microsoft SQL Server 2000 Desktop Engine (Windows), Microsoft SQL Server 2000 64-bit Edition, Microsoft SQL Server 2005 Express Edition
- kbhowtomaster KB319930