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:

1. Click Add:

   

2. Click Object Types:

   

3. Ensure that you have no ability to add groups:

   

   and click OK.

4. Under Enter the object names to select, enter Administrators:

   

5. Click Check Names, and ensure that you are not allowed to add groups:

   

   and click Cancel.

6. 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)