SQL Login Failed: Token-based server access validation failed with an infrastructure error

Something I'd forgotten when I was setting up a new SQL server, was how the UAC (User Account Controls) permissions affect connections to SQL when the windows account in question is part of a group.

I've only experienced this with sysadmin accounts, but the problem can occur in applications or even Management Studio, where users who should have full sysadmin access find they cannot connect at all.

The only clue is that the SQL log contains the lines:

Source: Logon
Message: Error: 18456, Severity: 14, State: 11.

and

Source: Logon
Message: Login failed for user 'DOMAIN\grumpydba'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 1.2.3.4]

Issue is that SQL doesn't seem to apply sysadmin access rights to users / accounts that only exist in SQL groups.

Two quick fixes:

1. If you are opening SQL Management Studio, right click and select "Run as administrator".  This should give you the access you expect.

2. Add the account explicitly as a SQL login, and grant sysadmin.  (Use this if for a system account) This will give the account the relevant permissions and able to connect.

Upgrade to SQL 2005 fails

Recently I had a problem upgrading a couple of SQL 2000 instances to SQL 2005 running exactly the same process as I had done many times before.  Early in the update, I kept getting the error message:

UpgradeAdvisor: returned -1.

Error message:
 

Alternatively sometimes it shows:

SQL BPA command line has stopped working

Turns out this is caused by the inbuilt upgrade advisor that runs within the upgrade setup, and fails if .Net 3.5 SP1 has been applied to the server.

The fix is simply to create a new directory and copy a single file (BPAClient.dll) within the setup bootstrap folder structure. 

Either just search for the file name BPAClient.dll, or navigate to the BPA folder (probably C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\BPA) - the BPAClient.dll file should be in the "bin" directory in here.

Within the BPA directory, create a new directory called "BPAClient" - and into this directory copy the BPAClient.dll - now run the SQL 2005 setup to upgrade your instance and it should work.

UPDATE: Alternatively, just copy the BPAClient.dll file from the ...\BPA\bin folder into the ...\BPA directory - this also fixes the problem