Microsoft KB Archive/822502

= &quot;Login failed for user&quot; error message when a user who does not have administrative credentials tries to gain access to an MSDE instance =

Article ID: 822502

Article Last Modified on 5/2/2006

-

APPLIES TO


 * Microsoft Visual Studio 2005 Standard Edition
 * Microsoft Visual Studio 2005 Professional Edition
 * Microsoft Visual Studio .NET 2003 Enterprise Architect
 * Microsoft Visual Studio .NET 2003 Enterprise Developer
 * Microsoft Visual Studio .NET 2003 Academic Edition
 * 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 SQL Server 2000 Desktop Engine (Windows)

-



SYMPTOMS
If you install a Microsoft SQL Server 2000 Desktop Engine (MSDE) instance by using a Microsoft Windows Administrator user account, a user who does not have administrative credentials may not be able to gain access to the MSDE instance through Microsoft Visual Studio 2005 or Microsoft Visual Studio .NET Server Explorer.

When you try to gain access to the MSDE instance through Server Explorer, the SQL Server Login dialog box appears. If you select the Use Windows NT Integrated Security option in this dialog box to log on to the MSDE instance, you receive the following error message:

Login failed for user ' \ '.

Note In this error message example,  and   are placeholders for the domain name and the user name of the Windows user account that you are using when you try to gain access to the MSDE instance.



WORKAROUND
To work around this problem, you can give logon rights for the MSDE instance to a Windows user account that does not have administrative credentials. You can then add this Windows user account to the System Administrators Group to give access rights for the user databases to this user account. To do this, follow these steps:  Log on to the computer that is running the MSDE instance of Visual Studio 2005 or Visual Studio .NET. Log on as Administrator or as any other Windows user who has administrative credentials. Start Visual Studio 2005 or Visual Studio .NET. On the File menu, point to New, and then click Project.

The New Project dialog box appears. Under Project Types, click Visual Basic Projects.

Note In Visual Studio 2005, click Visual Basic in Project Types. Under Templates, click Windows Application, and then click OK. By default, Form1.vb is created. On View menu, click Code.  Paste the following code as the first line in Form1.vb: Imports System.Data.SqlClient </li>  Paste the following code in the class definition of Form1 in Form1.vb: Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load ' Grant logon rights. RunProc(&quot;<Computer Name>&quot;, &quot;master&quot;, &quot;sp_grantlogin&quot;, &quot;@loginame&quot;, &quot;<Domain Name>\<User Name>&quot;)

' Add the user account to the System Administrators Group. RunProc(&quot;<Computer Name>&quot;, &quot;master&quot;, &quot;sp_addsrvrolemember&quot;, &quot;@rolename&quot;, &quot;sysadmin&quot;, &quot;@loginame&quot;, _ &quot;<Domain Name>\<User Name>&quot;) End Sub

Private Sub RunProc(ByVal HostName As String, ByVal DbName As String, ByVal CmdName As String, ByVal Param1Name _ As String, ByVal Param1Value As Object, Optional ByVal Param2Name As String = vbNullString, Optional ByVal Param2Value _ As Object = Nothing) Dim cn As New SqlConnection(&quot;Data Source=&quot; & HostName & &quot;\vsdotnet;Database=&quot; & DbName & _ &quot;; Integrated Security=True&quot;) Dim cmd As New SqlCommand(CmdName, cn) Dim param1 As New SqlParameter(Param1Name, Param1Value) Dim param2 As New SqlParameter(Param2Name, Param2Value)

' Open the connection. cn.Open

' Set CommandType to StoredProcedure. cmd.CommandType = CommandType.StoredProcedure

' Add parameters. cmd.Parameters.Add(param1) If Param2Name <> vbNullString Then cmd.Parameters.Add(param2) End If

' Run the command. cmd.ExecuteNonQuery

' Close the connection. cn.Close End Sub Note Replace <Computer Name> in the code with the name of the computer that is running the MSDE instance. Replace <Domain Name> and <User Name> with the domain name and the user name of the Windows user account that you want to give access rights for the user databases to. </li> On Debug menu, click Start to run the project.</li></ol>

<div class="status_section">

STATUS
This behavior is by design.

<div class="moreinformation_section">

MORE INFORMATION
To gain access to an MSDE instance from Server Explorer, you can do one of the following:
 * Provide Microsoft SQL Server user credentials.
 * Use the Use Windows NT Integrated Security option to provide the Windows user credentials.

When a MSDE instance is installed, SQL Server users are not automatically created for the instance. Therefore, you can only use the Use Windows NT Integrated Security option to log on to the MSDE instance from Visual Studio .NET.

Steps to Reproduce the Behavior

 * 1) Log on to your computer by using the Administrator account or any other user account that has administrative credentials.
 * 2) Install Visual Studio 2005 or Visual Studio .NET with MSDE from the Visual Studio 2005 or Visual Studio .NET Installation CD-ROM.
 * 3) Complete the installation process for the MSDE instance.
 * 4) Log off from your computer.
 * 5) Log on to your computer by using a Windows user account that does not have administrative credentials.
 * 6) Start Visual Studio 2005 or Visual Studio .NET.
 * 7) On the View menu, click Server Explorer.
 * 8) In Server Explorer, expand Servers, and then expand the   node where   is the name of your computer.
 * 9) Expand SQL Servers, and then double-click  \VSDOTNET where   is the name of the server computer where the databases are located.
 * 10) In the SQL Server Login dialog box, click to select the Use Windows NT Integrated Security check box.
 * 11) Click OK.

<div class="references_section">