Microsoft KB Archive/319930

= How to connect to an instance of SQL Server Desktop Edition or of SQL Server 2005 Express Edition =

Article ID: 319930

Article Last Modified on 5/18/2007

-

APPLIES TO


 * 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

-



This article was previously published under Q319930



Important This article contains information about how to modify the registry. Make sure that you back up the registry before you modify it. Make sure that you know how to restore the registry if a problem occurs. For more information about how to back up, restore, and modify the registry, click the following article number to view the article in the Microsoft Knowledge Base:

256986 Description of the Microsoft Windows registry

IN THIS TASK
SUMMARY Requirements Connect to MSDE with Windows NT authentication Connect to MSDE with SQL Server authentication
 * Enable Mixed Mode authentication during installation
 * Enable Mixed Mode authentication after installation

Verify connectivity Troubleshooting REFERENCES



SUMMARY
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.

The default authentication mode for MSDE is Windows Authentication. MSDE installs with a built-in system administrator (SA) user account. However, because SQL Server Authentication is disabled by default, you cannot access the built-in account after a typical installation.

For more information about authentication modes in MSDE, see the following MSDN Web site:

Authentication Modes

http://msdn2.microsoft.com/en-us/library/aa905171(SQL.80).aspx

back to the top

Requirements
The 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)

This article assumes that you are familiar with the following topics:
 * Database terminology
 * Transact-SQL

back to the top

Connect to MSDE with Windows NT authentication
MSDE 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.

NOTE: Windows Authentication Mode is not available when an instance of MSDE is running on Microsoft Windows 98 or Microsoft Windows Millennium Edition (Me).

back to the top

Connect to MSDE with SQL Server authentication
When 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.

Because SQL Server Authentication is disabled under the default installation of MSDE, either you must configure the MSDE installation to enable SQL Server Authentication (Mixed Mode), or you must manually enable Mixed Mode after the installation has completed. Microsoft recommends that you enable Mixed Mode authentication during installation because the post-installation process requires that you manually edit the Windows Registry.

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 sa

This 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  with the new password.

sp_password null,, 'sa'

go

Notice that you receive the following message, which indicates that your password was changed successfully:

Password changed.



For more information about how to use the OSQL utility, see the following MSDN Web site:

OSQL Utility

http://msdn2.microsoft.com/en-us/library/aa214012(SQL.80).aspx

back to the top

Enable Mixed Mode authentication during installation
During installation, you can change the authentication mode that MSDE uses by running the installation with the following command parameter: SECURITYMODE=SQL 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 installation
Warning Serious problems might occur if you modify the registry incorrectly by using Registry Editor or by using another method. These problems might require that you reinstall the operating system. Microsoft cannot guarantee that these problems can be solved. Modify the registry at your own risk. By 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:

To change the value of LoginMode to 2, follow these steps:  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.</li> Locate one of the following subkeys (depending on whether you installed MSDE as the default MSDE instance or as a named instance):

</li> In the right pane, double-click the LoginMode subkey.</li> 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.</li> Restart the MSSQLSERVER and the SQLSERVERAgent services for this change to take effect.</li></ol>

back to the top

Verify connectivity
Follow 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 .</li> Double-click the .udl file to open the Data Link Properties dialog box.</li> On the Provider tab, click Microsoft OLE DB Provider for SQL Server.</li> On the Connection tab, follow these steps:  Under item 1, select or type the server that you want to connect to.</li> Under item 2, select one of the following options: <ul> Use Windows NT Integrated security

Select this option if you are using Windows Authentication.</li> 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.</li></ul> </li> Click Test Connection. If the connection is successful, you receive a message that confirms that the test connection succeeded.</li></ol> </li></ol>

back to the top

Troubleshooting
<ul> 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 &quot;running.&quot;</li> <li>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.</li> <li>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  -U sa

</li></ul>

back to the top

<div class="references_section">