My Favorite Blogs

Tuesday, May 25, 2010

Token-based server access validation failed with an infrastructure error - MS SQL Server 2008 on Windows 2008 Server

Issue
Recently I faced login failure issue while logging into a newly built MS SQL Server 2008 instance on Windows Server 2008 R2 server. I’ve ensured that I’m part of the local administrators group in the server even though I’m getting the login failure and SQL didn’t allow me to connect to the instance through SSMS (SQL Server Management Studio).

SQL ErrorLog
2010-05-24 11:27:55.480 Logon Login failed for user 'SQLDBOps\Krishjay'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: ]

Event Log
+ System
- Provider
[ Name] MSSQLSERVER
- EventID 18456
[ Qualifiers] 49152
Level 0
Task 4
Keywords 0x90000000000000
- TimeCreated
[ SystemTime] 2010-05-24T18:15:34.000000000Z
EventRecordID 2166
Channel Application
Computer SQLDBOPs
- Security
[ UserID] S-1-5-21-606947145-1563985344-839528115-120123
- EventData
SQLDBOps\Krishjay
Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors.
[CLIENT: ]

Reason for the issue:
After going through couple of forums and knowledge base articles then I understood that Windows 2008 new Security feature UAC (User Access Control) is blocking me to login to the SQL Instance.

What is User Access Control?
UAC is a new Security feature introduced in Windows Server 2008 (also applies to Windows Server 2008 R2, Windows 7 and Windows Vista). When an administrator logs on to a computer running Windows 2008, the user’s full administrator access token is split into two access tokens: a full administrator access token and a standard user access token. During the logon process, authorization and access control components that identify an administrator are removed, resulting in a standard user access token. The standard user access token is then used to start the desktop, the Explorer.exe process. Because all applications inherit their access control data from the initial launch of the desktop, they all run as a standard user as well.

So the UAC is not passing all my group membership credentials when I log on to the server that’s why I do not have privilege to connect the SQL instance on SSMS. Here if I add my login account explicitly in SQL level then I can connect to SQL without any issues. I mean the Standard user credentials (as per UAC) are not sufficient to connect the SQL instance on Windows 2008 server or you can say that the local admin group membership credentials are not inherited with my account.

Workaround:

To resolve this issue, there are two options, one is open the SSMS with option “Run as Administrator” and the other is “Disable UAC” in the box. Again the answer is depends on the environment and management decision how it needs to be.

Note: Run as Administrator is user level settings (and can be configured for all the users in the box) and Disable UAC is for all users and disabling the server protection from the malwares and viruses.

Run As Administrator Settings

Start --> All Programs-->Microsoft SQL Server-->SQL Server Management Studio -->Right Click --> Properties

The SSMS properties window will pop up and change the settings on the compatibility tab according to your environment.
 

1. Check “Run this program as an administrator” on Compatibility page if you are the only user using SSMS on this box.

2. If this setting required for other admin users on the box then Click on the “Change Settings for all users” button

3. And Check “Run this program as an administrator” for all users.

This will allow opening the SQL Server management studio for all the administrators on the box without any login issues.

Disable UAC:

Go to Command prompt and run the below command to disable the UAC

Reg.exe ADD HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System /v EnableLUA /t REG_DWORD /d 0 /f

Other way of doing the same is Go to Registry Editor

Start --> Run -->Type RegEdit and hit Enter key


In the Registry Editor, Navigate to the below folder

HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System

Right click on System folder and place the mouse pointer on New --> click on "DWORD Value" (New Value will be created under System) -->Rename it as "EnableLUA" and default value will be 0 leave as it is.

Then restart your Computer
Now you will start to get nagging prompts to re-enable UAC, if needed you can disable these prompts.
Click the bubble
At the side, it should say “Change the way Security Center alerts me”, Click that.
Click “Don’t notify me and don’t display the icon”

Now you have disabled UAC but keep in mind that you have disabled some of your protection against spyware and viruses.

Reference:

http://technet.microsoft.com/en-us/library/cc709691(WS.10).aspx
http://technet.microsoft.com/en-us/library/cc731416(WS.10).aspx
http://windows.microsoft.com/en-ZA/windows7/what-is-user-account-control
http://windows.microsoft.com/en-ZA/windows7/turn-user-account-control-on-or-off

9 comments:

  1. Hi, Same thing. UAC!!!
    I had this issue with NetBackup.
    So I had to login with Netbackup user who have MS SQL authoruzation then "Run" NB Client "as Administrator", then just pressed Apply and NB client have logged in to MS SQL.

    ReplyDelete
  2. An easier way to disable UAC is to go to:
    1.) START>>run>> Type msconfig
    2.) Dialog opens select Tools tab
    3.) Look for option Change UAC Settings and select
    4.) Click the launch button
    5.) a new dialog appear where you can slide the setting all the way down.

    ReplyDelete
  3. Thanks.....your solution saved my day.....
    Keep sharing the knowledge....:)

    ReplyDelete
  4. Had the same problem.... rebooted the PC trying to connect and that fixed it

    ReplyDelete
  5. Add the computer account you are connecting from as an administrator to the computer that you are trying to connect to... will resolve the issue without a reboot.

    ReplyDelete
  6. As Alexander indicates above this isn't just something peculiar to SSMS, rather any process that connects to the SQL Server. The question is why is it like this? There are three general requirements that I would assume are common and all three cannot be met simultaneously:

    * I don't want to run with UAC disabled because it is there for a reason.
    * I don't want to force users to run with UAC elevation because then they all have to be local Administrators or at least know the credentials of a local Administrator.
    * I want to manage SQL access based on groups rather than individual Windows accounts so that I only need to make changes once rather than for every user.

    ReplyDelete
    Replies
    1. Apologies - it seems it is only privileges granted due to membership of the BUILTIN\Administrators group that are dependent on UAC elevation. I can create other user groups, assign server roles to these and then members of this group are granted privileges accordingly. I just hadn't logged off and on again for the changes to take effect :\

      Delete
  7. Thank you!!! this resolved my issue... what a frustratingly stupid issue!!! Thanks Microsoft!

    ReplyDelete