You may not be able to connect to a SQL Server that is running on a Windows Server 2003 computer by using Windows authentication (840219)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions), when used with:
    • the operating system: Microsoft Windows Server 2003

SYMPTOMS

After you log on to your computer that is running Microsoft Windows Server 2003 by using a Windows user account that has local administrator credentials on your computer, if you try to connect to an instance of Microsoft SQL Server that is running on the computer by using Windows Authentication, the connection may not be successful.

If you use the osql command-line utility to connect to the instance of SQL Server, you may receive the following error message:

Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
If you use SQL Query Analyzer to connect to the instance of SQL Server, you may receive the following error message:

Unable to connect to server NameOfTheInstanceOfSQLServer

Server: Msg 18452, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
This problem occurs when all the following conditions are true:
  • The instance of SQL Server uses the TCP/IP server network library for client connections.
  • A Windows user account that is different from the current Windows logon account is set as the service account for the MSSQLServer service that corresponds to your instance of SQL Server.

CAUSE

This problem can occur because the Windows user account that is used to connect to the instance of SQL Server does not have sufficient permissions on your computer. This article discusses one possible cause of this message. The workaround that is discussed in this article will only help if you experience this specific scenario.

RESOLUTION

To resolve the problem, you must change the user rights settings on your computer to give sufficient permissions to the Windows user account that is used to connect to the instance of SQL Server. To do this, follow these steps.

Note If the Windows user name that is used to connect to the instance of SQL Server is a domain user name and is not a local user name on your computer, you must change the user rights settings by using the Active Directory Users and Computers Microsoft Management Console (MMC) on the computer that acts as the domain controller.
  1. Click Start, point to Administrative Tools, and then click Local Security Policy.
  2. In the left pane of the Local Security Settings window, expand Local Policies, and then click User Rights Assignment.
  3. In the right pane, modify the settings of the policies to add the users who are mentioned in the following table. To do this, under the Policy column header, double-click the corresponding policy, click Add User or Group, and then add a user or group to the policy.
    User rightPolicySecurity setting
    SeImpersonatePrivilegeImpersonate a client after authentication Administrators, SERVICE
    SeLockMemoryPrivilege Lock pages in memoryWindows User, Administrators
    SeBatchLogonRight Log on as a batch jobWindows User
    SeAssignPrimaryTokenPrivilegeReplace a process level tokenWindows User
    SeEnableDelegationPrivilege Enable computer and user accounts to be trusted for delegationAdministrators
    SeChangeNotifyPrivilegeBypass traverse checkingWindows User
    SeServiceLogonRightLog on as a serviceWindows User, Administrators
    Note "Windows User" indicates the Windows user name that is used to connect to the instance of SQL Server. Before you provide these user rights to the Windows user, make sure that the Windows user is eligible for these user rights on the computer that is running the instance of SQL Server.
  4. Close the Local Security Settings window.
  5. Restart the instance of SQL Server.
Additionally, if the user rights settings on your computer are defined by using a security template, you must make sure that the specified user rights are modified accordingly in the corresponding security template.

WORKAROUND

To work around this problem, use one of the following methods:
  • Connect to the instance of SQL Server by using SQL Server Authentication.
  • Configure the instance of SQL Server to use the Named Pipes server network library for the client connections, and then connect to the instance of SQL Server by using Windows Authentication.

MORE INFORMATION

Steps to reproduce the behavior

  1. Log on as an administrator on the computer that is running the instance of SQL Server.
  2. Create a new Windows user that is named User1 on your computer, and then make sure that the User1 Windows user account has local administrator credentials on your computer.
  3. Click Start, point to Settings, and then click Control Panel.
  4. In Control Panel, double-click Administrative Tools.
  5. In the Administrative Tools window, double-click Local Security Policy.
  6. In the left pane of the Local Security Settings window, expand Local Policies, and then click User Rights Assignment.
  7. In the right pane, modify the setting of the policies to add the corresponding users who are mentioned in the following table:
    User rightPolicySecurity setting
    SeImpersonatePrivilegeImpersonate a client after authentication None
    SeLockMemoryPrivilegeLock pages in memoryNone
    SeServiceLogonRightLog on as a serviceAdministrators
    SeBackupPrivilegeBack up files and directoriesUser1, Administrators
    SeDebugPrivilegeDebug programsAdministrators
    SeIncreaseBasePriorityPrivilegeIncrease scheduling priorityUser1, Administrators group
    SeSecurityPrivilegeManage auditing and security logAdministrators
    SeRestorePrivilegeRestore files and directoriesUser1, Administrators
    SeIncreaseQuotaAdjust memory quotas for a processUser1, Administrators
  8. Close the Local Security Settings window.
  9. Make sure that the instance of SQL Server is configured to use the TCP/IP server network library for the client connections, and then restart your instance of SQL Server.
  10. Make sure that the current Windows logon account is set as the service account for your instance of SQL Server.
  11. Restart your instance of SQL Server.
  12. Log off, and then log on to the computer by using the Windows user User1 account.
  13. At a command prompt, type the following command to connect to the instance of SQL Server:

    osql -S NameOfTheInstanceOfSQLServer -E

    You receive the error message that is mentioned in the "Symptoms" section.
Modification Type:MinorLast Reviewed:12/12/2005
Keywords:kbtshoot kbconnectivity kbAuthentication kberrmsg kbUser kbConfig kbSecurity kbprb KB840219 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.