How to connect to an instance of SQL Server Desktop Edition or of SQL Server 2005 Express Edition (319930)


The information in this article applies to:

  • Microsoft SQL Server, Desktop Edition 7.0
  • Microsoft SQL Server 2000 Desktop Engine (MSDE)
  • Microsoft SQL Server 2000 64 bit (all editions)
  • Microsoft SQL Server 2005 Express Edition
This article was previously published under Q319930
IMPORTANT: This article contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit 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

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://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_47u6.asp

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:
  1. On the computer that is hosting the instance of MSDE that you are connecting to, open the command prompt window.
  2. 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.
  3. 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'

    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://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_osql_1wxl.asp

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: If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor 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:

HKLM\Software\Microsoft\MSSqlserver\MSSqlServer\LoginMode

If you installed MSDE as a named instance, the LoginMode subkey is located in the following registry subkey:

HKLM\Software\Microsoft\Microsoft SQL Server\Instance Name\MSSQLServer\LoginMode

If you are using SQL Server 2005 Express Edition, the LoginMode registry entry is located in the following registry subkey:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.4\MSSQLServer

To change the value of LoginMode to 2, follow these steps:
  1. In Control Panel, open the Services tool to stop MSSQLSERVER and all other related services (such as SQLSERVERAgent)
  2. To open Registry Editor, click Start, click Run, type regedt32, and then click OK.
  3. 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.4\MSSQLServer

  4. In the right pane, double-click the LoginMode subkey.
  5. 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.
  6. Restart the MSSQLSERVER and the SQLSERVERAgent services for this change to take effect.
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:
  1. Right-click on your computer desktop, click New, and then click Text Document. Rename the file Test.udl.
  2. Double-click the .udl file to open the Data Link Properties dialog box.
  3. On the Provider tab, click Microsoft OLE DB Provider for SQL Server.
  4. On the Connection tab, follow these steps:
    1. Under item 1, select or type the server that you want to connect to.
    2. 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.
    3. Click Test Connection. If the connection is successful, you receive a message that confirms that the test connection succeeded.
back to the top

Troubleshooting

  • 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

back to the top

REFERENCES

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

325022 INFO: MSDE Security and Authentication

For additional information about using Windows Installer if you are customizing setup for SQL Server 2000 Desktop Engine, click the article number below to view the article in the Microsoft Knowledge Base:

227091 Command-Line Switches for the Microsoft Windows Installer Tool

For additional information about changing the default authentication mode for SQL Server 2000 Desktop Engine, click the article number below to view the article in the Microsoft Knowledge Base:

285097 INF: How to Change the Default Login Authentication Mode to SQL While Installing SQL Server 2000 Desktop Engine by Using Windows Installer

For more information about using SQL Server Desktop Engine, see the following Microsoft Web sites:

SQL Server 2000 Desktop Engine (MSDE 2000)
http://www.microsoft.com/sql/techinfo/development/2000/MSDE2000.asp

Integrating MSDE 2000 with your Applications
http://www.microsoft.com/sql/techinfo/development/2000/MSDEintegration.asp

Modification Type:MajorLast Reviewed:12/29/2005
Keywords:kbHOWTOmaster KB319930 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.