SQL Server oddities

Send to Kindle

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.

clip_image002

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.

clip_image004

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.

clip_image006

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!

clip_image008

 Digg  Facebook  StumbleUpon  Technorati  Deli.cio.us  Slashdot  Twitter  Sphinn  Mixx  Google  DZone 

No Tags

Send to Kindle
Bookmark the permalink.

4 Responses to SQL Server oddities

  1. Heidi says:

    omigod.. thankyou for this. I have spent a week troubleshooting various SharePoint issues, and this particular one has taken me a couple of days until I found your post.

  2. We encountered this error while implementing this week and your post helped us solve it.
    Thank you!

  3. Pingback: Login failed: Token-based server access validation failed with an infrastructure error | The SQL Server Diaries

  4. Pingback: Login failed for user ‘xxx’. Reason: Token-based server access validation failed with an infrastructure error | LouieBao.Net

Leave a Reply

Your email address will not be published. Required fields are marked *