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

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.