When i try to connect to SQL Server (2008 R2) using Windows authentication:
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
- 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.
Note: It's not limited to SQL Server Management Studio, standalone applications that connect using Windows authentication:
Note: It's not a client problem, as we can connect fine to other (non-SQL Server 2008 R2 machines):
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 Object Types:
Ensure that you have no ability to add groups:
and click OK.
Under Enter the object names to select, enter
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.
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
saand the only other sysadmin domain account (screenshot can be supplied for those who don't believe)