You cannot connect to MSDE 2000 by using ADO.NET with SQL Server Authentication

Article translations Article translations
Article ID: 321698 - View products that this article applies to.
This article was previously published under Q321698
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

On This Page

SYMPTOMS

If you try to open a SqlConnection object to a new installation of Microsoft SQL Server 2000 Data Engine (MSDE 2000) under the following conditions, a SqlException exception may by thrown:
  • You are using SQL Server Authentication.
  • You are using the built-in SA account with the default blank password.
If the exception is not caught in a try-catch block, you receive the following error message:
An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll

Additional information: System error.
If the exception is caught by a try-catch block, you receive the following error message:
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection.

CAUSE

The default authentication mode for MSDE 2000 is Windows Authentication. MSDE 2000 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.

RESOLUTION

To enable SQL Authentication, use one of the following methods:
  • Use the following command-line switch to enable SQL Authentication during installation:
    SECURITYMODE=SQL
  • Edit the registry after installation.
For more information about how to do either method, 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 Installer
Note When you change the default login authentication mode from Windows NT authentication to SQL authentication, we recommend that you change the sa password to a strong password.

For more information about how to change the SQL Server sa password, click the following article number to view the article in the Microsoft Knowledge Base:
322336 How to verify and change the system administrator password in MSDE or SQL Server 2005 Express Edition

STATUS

This behavior is by design.

MORE INFORMATION

If Microsoft SQL Server is installed on the same network as MSDE 2000, and if your network is using an NT Domain, you may be able to use the SQL Server Enterprise Manager utility to connect to your instance of MSDE 2000 and to enable Mixed Mode Authentication.

By disabling the built-in user account with a blank password, MSDE 2000 is kept more secure after a default installation. If you enable SQL Server Authentication, it is crucial to the security of your system that you immediately create a password for the SA account. To do this, you can use the sp_password stored procedure in the osql command-line utility that is installed with MSDE 2000. For more information, see the "References" section of this article.

Steps to reproduce the behavior

  1. Install a fresh, default installation of MSDE 2000. Do not use any command-line switches to modify the default authentication mode.
  2. Start Microsoft Visual Studio .NET.
  3. On the File menu, point to New, and then click Project.
  4. Click Visual Basic Projects under Project Types, click Windows Application under Templates, type AuthenticationTest in the Name box, and then click OK.
  5. Add the following statement to the top of Form1.vb:
    Imports System.Data.SqlClient
    					
  6. Double-click Form1 to create a Form1_Load event handler, and then add the following code to the handler:
    Try
        Dim conn As New SqlConnection("server=yourserver;uid=sa;database=master")
        conn.Open()
        If (conn.State = ConnectionState.Open) Then
            MessageBox.Show("Connection opened successfully")
        End If
        conn.Close()
    
    
    Catch ex As SqlException
        MessageBox.Show(ex.Message)
    End Try
    					
  7. Modify the connection string of the SqlConnection object to connect to your instance of MSDE 2000.
  8. Press F5 to compile and to execute the code. Notice that you receive the following error message:
    Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection.

    Resolution

  9. Use one of the methods that is listed in the "Resolution" section earlier in this article to enable Mixed Mode Authentication. Mixed Mode Authentication enables both Windows Authentication and SQL Server Authentication.
  10. Because the built-in SA user account is installed without a password, you must create a password for this account immediately when you enable SQL Server Authentication. To create a password for the built-in SA account, follow these steps:
    1. On the computer that is hosting the instance of MSDE 2000 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 2000 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.
      							
  11. Add your new password to the connection string. Locate the following line of code in Form1.vb:
    Dim conn As New SqlConnection("server=yourserver;uid=sa;database=master")
    						
    Add your password to the connection string as follows, where "yournewpassword" is the password that you created in the previous step:
    Dim conn As New SqlConnection("server=yourserver;uid=sa;pwd=yournewpassword;database=master")
    					
  12. Press F5 to compile and to execute the code. A dialog box appears, which states that the connection to SQL Server succeeded.

REFERENCES

For more information about connecting to Microsoft Desktop Engine, click the following article number to view the article in the Microsoft Knowledge Base:
319930 How to connect to an instance of SQL Server Desktop Edition or of SQL Server 2005 Express Edition
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 Command-line switches for the Microsoft Windows Installer Tool
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
325022 MSDE security and authentication
For more information, visit the following Microsoft Web sites:
SQL Server Enterprise Manager
http://msdn2.microsoft.com/en-us/library/aa174466.aspx

How to set up Mixed Mode security
http://msdn2.microsoft.com/en-us/library/aa215470.aspx

sp_password
http://msdn2.microsoft.com/en-us/library/aa238870.aspx

Using the osql utility
http://msdn2.microsoft.com/en-us/library/aa213090.aspx

SQL Server 2000 Desktop Engine (MSDE 2000)
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

Properties

Article ID: 321698 - Last Review: February 27, 2014 - Revision: 3.8
APPLIES TO
  • Microsoft ADO.NET 2.0
  • Microsoft ADO.NET 1.0
  • Microsoft Visual Studio .NET 2002 Professional Edition
  • Microsoft Visual Studio .NET 2002 Enterprise Architect
  • Microsoft Visual Studio .NET 2002 Enterprise Developer
  • Microsoft Visual Studio .NET 2002 Academic Edition
  • Microsoft Data Engine 1.0
  • Microsoft SQL Server 2000 Desktop Engine (Windows)
  • Microsoft ASP.NET 1.1
  • Microsoft Visual Studio .NET 2003 Academic Edition
  • Microsoft Visual Studio .NET 2003 Enterprise Architect
  • Microsoft Visual Studio .NET 2003 Enterprise Developer
  • Microsoft Visual Studio .NET 2003 Professional Edition
  • Microsoft Visual Studio 2005 Professional Edition
Keywords: 
kbnosurvey kbarchive kbprb kbsqlclient kbsystemdata KB321698

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com