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
Date: 31/07/2010 6:45:37 p.m.
Event ID: 18456
Task Category: Logon
Keywords: Classic,Audit Failure
Login failed for user ‘TRAINSBYDAVE\administrator’. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors.
[CLIENT: <local machine>]
<Provider Name="MSSQLSERVER" />
<TimeCreated SystemTime="2010-07-31T06:45:37.000000000Z" />
<Security UserID="S-1-5-21-3713613819-1395520312-4192346095-500" />
<Data> Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors.</Data>
<Data> [CLIENT: <local machine>]</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 🙂