SQL Server oddities

So its Saturday night and where I should be out having fun, I am instead sitting in a training room in Wellington New Zealand, configuring a lab for a course I am running on Monday.

Each student lab setup is two virtual machines. The first being a fairly stock standard AD domain controller and the second being a SQL/SharePoint 2010 box. Someone else set up the machines, and I came in to make some changes for the labs next week. But as soon as I fired up the first student VM’s I hit a snag. I loaded SQL Server Management Studio, only to find that I was unable to connect to it as the domain administrator, despite being fairly certain that the account had been granted the sysadmin role in SQL.

Checking the event logs, showed an error that I had not seen before. “Token-based server access validation failed with an infrastructure error”. hmmm

Log Name: Application

Source: MSSQLSERVER

Date: 31/07/2010 6:45:37 p.m.

Event ID: 18456

Task Category: Logon

Level: Information

Keywords: Classic,Audit Failure

User: TRAINSBYDAVE\administrator

Computer: SP01.trainsbydave.com

Description:

Login failed for user ‘TRAINSBYDAVE\administrator’. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors.

[CLIENT: <local machine>]

Event Xml:

<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">

<System>

<Provider Name="MSSQLSERVER" />

<EventID Qualifiers="49152">18456</EventID>

<Level>0</Level>

<Task>4</Task>

<Keywords>0x90000000000000</Keywords>

<TimeCreated SystemTime="2010-07-31T06:45:37.000000000Z" />

<EventRecordID>8281</EventRecordID>

<Channel>Application</Channel>

<Computer>SP01.trainsbydave.com</Computer>

<Security UserID="S-1-5-21-3713613819-1395520312-4192346095-500" />

</System>

<EventData>

<Data>TRAINSBYDAVE\administrator</Data>

<Data> Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors.</Data>

<Data> [CLIENT: &lt;local machine&gt;]</Data>

As it happened, whoever set it up had SQL Server in mixed mode authentication, so I was able to sign in as the sa account and have a poke around. All things considered, it should have worked. The user account in question was definitely in the logins and set with sysadmin server rights as shown below.

Uncle google showed a few people with the error but not as many as I expected to see since half the world gets nailed on authentication issues. I also took the event log suggestion and looked for a previous error. A big nope on that suggestion. In fact in all respects, everything looked sweet. The machine was valid on the domain and I was able to perform any other administrative task.

Finally, I removed the TRAINSBYDAVE\administrator account from the list of Logins in SQL Server. It gave the unsurprising whinge about orphaned database users, but luckily for AD accounts when you re-add the same account back it is smart enough to re-establish the link.

As soon as I re-added the account, all was good again. If I was actually interested enough I’d delve into why this happened, but not tonight – I have another 6 student machines to configure 🙂

Goodnight!