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

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: If the exception is caught by a try-catch block, you receive the following error message:

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.

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

This behavior is by design.

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.
   Back to the top

   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.

For more information about connecting to Microsoft Desktop Engine, click the following article number to view the article in the Microsoft Knowledge Base: 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: For more information, click the following article number to view the article in the Microsoft Knowledge Base: For more information, visit the following Microsoft Web sites: