Cannot login to SQL Server 2008 R2 with Windows authentication
- by Ian Boyd
When i try to connect to SQL Server (2008 R2) using Windows authentication:
i cannot:
Checking the Windows Application event log, i find the error:
Login failed for user 'AVATOPIA\ian'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: ]
Log Name: Application
Source: MSSQLSERVER
Event ID: 18456
Level: Information
User: AVATOPIA\ian
OpCode:
Task Category: Logon
i can login to the computer itself using Windows authentication. i can log into SQL Server using the local Windows Administrator account.
We can connect to 8 other SQL Servers on the domain using Windows Authentication. Just this one, whitch is the only one that is 2008 R2 is failing. So i assume it's a bug with *2008 R2.
Note: i cannot logon locally, or remotely, using Windows authentication. i can login locally and remotely using SQL Server Authentication.
Update
Note: It's not limited to SQL Server Management Studio, standalone applications that connect using Windows authentication:
fail:
Note: It's not a client problem, as we can connect fine to other (non-SQL Server 2008 R2 machines):
i'm sure there's a technote or knowledge base article describing why SQL Server 2008 R2 is broken by default, but i can't find it.
Update 2
Matt figure out the change that Microsoft made so that SQL Server 2008 R2 is broken by default:
Administrators are no longer administrators
All that remains is to figure out how to make Administrators administrators.
One of these days i'm going to start a list of changes around Microsoft's "broken by default" initiative.
Steps to reproduce the problem
How do i add a group to the sysadmin fixed server role? Here's the steps i try, that don't work:
Click Add:
Click Object Types:
Ensure that you have no ability to add groups:
and click OK.
Under Enter the object names to select, enter Administrators:
Click Check Names, and ensure that you are not allowed to add groups:
and click Cancel.
Click Browse..., and ensure that you have no ability to add groups:
You should now still not have added any group to the sysadmin role.
Additional information
SQL Server Management Studio is being run as an administrator:
SQL Server is set to use Windows Authentication:
tried while logged into SQL with both sa and the only other sysadmin domain account (screenshot can be supplied for those who don't believe)
