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

Monday, May 24, 2010

MS SQL Server 2008 Administration Exam - Free from BrainBench

BrainBench announced the release of their new MS SQL Server 2008 Administration test. This certification test is available for free on their site for a limited time as they gather data to monitor its performance.

 
Be among the first to take this test and measure your level of expertise with the new content. These tests are developed as pre-employment assessments where MS SQL Server 2008 Administration is used significantly in the candidate’s job role. Once again, when helping with this test validation, completion of the test offers free certification

To take the test, go to http://www.brainbench.com/xml/bb/common/testcenter/taketest.xml?testId=2831 You may have to register if it is your first time taking IT tests with Brainbench.

Take some time and explore our entire library of free tests. Forward this message and challenge your colleagues to top your score!


Good luck!

Monday, May 3, 2010

How to create Policies using PowerShell scripts

Introduction

As you know that SQL Server 2008 supports Windows PowerShell and is a powerful scripting shell that helps administrators to automate and built robust server administration scripts. In my previous articles I’ve written about Using SQL Server 2008 PBM, how to monitor Free Space of data file(s) and How to Evaluate Policies on Multiple Instances using PowerShell scripts. In this article I’m going to walk you through how to create policies using PowerShell script.
In SQL 2008, policies can be created in two ways. One is using SSMS (SQL Server Management Studio) wizard and second is using PowerShell script. In my scenario I’ve been requested to create this script to deploy/create policies as a POST installation step. Suppose if you need to use this script for unattended SQL installations then make sure that this script called only after a complete SQL installation done on a server, because you need a SQL policy store to create your policies.Read More