Microsoft KB Archive/321698

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

Article ID: 321698

Article Last Modified on 4/28/2007

-

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

-



This article was previously published under Q321698



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 &quot;References&quot; section of this article.

Steps to reproduce the behavior
 Install a fresh, default installation of MSDE 2000. Do not use any command-line switches to modify the default authentication mode. Start Microsoft Visual Studio .NET.</li> On the File menu, point to New, and then click Project.</li> Click Visual Basic Projects under Project Types, click Windows Application under Templates, type AuthenticationTest in the Name box, and then click OK.</li>  Add the following statement to the top of Form1.vb: Imports System.Data.SqlClient </li>  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(&quot;server=yourserver;uid=sa;database=master&quot;) conn.Open If (conn.State = ConnectionState.Open) Then MessageBox.Show(&quot;Connection opened successfully&quot;) End If   conn.Close

Catch ex As SqlException MessageBox.Show(ex.Message) End Try </li> Modify the connection string of the SqlConnection object to connect to your instance of MSDE 2000.</li>  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
</li> Use one of the methods that is listed in the &quot;Resolution&quot; section earlier in this article to enable Mixed Mode Authentication. Mixed Mode Authentication enables both Windows Authentication and SQL Server Authentication.</li> 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: <ol style="list-style-type: lower-alpha;"> On the computer that is hosting the instance of MSDE 2000 that you are connecting to, open the command prompt window.</li> 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.</li> Type the following commands on separate lines, and then press ENTER:

NOTE: Make sure that you replace ' ' with the new password.

sp_password null, ' ', 'sa'

go

Notice that you receive the following message, which indicates that your password was changed successfully:

<pre class="fixed_text">Password changed.

</li></ol> </li>  Add your new password to the connection string. Locate the following line of code in Form1.vb: Dim conn As New SqlConnection(&quot;server=yourserver;uid=sa;database=master&quot;) Add your password to the connection string as follows, where &quot;yournewpassword&quot; is the password that you created in the previous step: Dim conn As New SqlConnection(&quot;server=yourserver;uid=sa;pwd=yournewpassword;database=master&quot;) </li> Press F5 to compile and to execute the code. A dialog box appears, which states that the connection to SQL Server succeeded.</li></ol>

<div class="references_section">