United States

Change

All Microsoft Sites

Help and Support

Article ID: 319930 - Last Review: May 18, 2007 - Revision: 4.5

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

View products that this article applies to.

This article was previously published under Q319930

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 (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 (http://msdn2.microsoft.com/en-us/library/ms970651.aspx) .

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 '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://msdn2.microsoft.com/en-us/library/aa214012(SQL.80).aspx (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

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:

322756 (http://support.microsoft.com/kb/322756/ ) How to back up and restore the registry in Windows

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.1\MSSQLServer

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

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.
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:
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 more information, click the following article number to view the article in the Microsoft Knowledge Base:

325022 (http://support.microsoft.com/kb/325022/ ) MSDE security and authentication

For 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 (http://support.microsoft.com/kb/227091/ ) Command-line switches for the Microsoft Windows Installer tool

For 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 (http://support.microsoft.com/kb/285097/ ) 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://msdn2.microsoft.com/en-us/sql/aa336301.aspx (http://msdn2.microsoft.com/en-us/sql/aa336301.aspx)

Integrating MSDE 2000 with your Applications
http://www.microsoft.com/sql/prodinfo/previousversions/msde/msdeintegration.mspx (http://www.microsoft.com/sql/prodinfo/previousversions/msde/msdeintegration.mspx)

Back to the top