Sql-server – Cannot login to SQL Server 2008 R2 with Windows authentication

sql serversql-server-2008-r2

When i try to connect to SQL Server (2008 R2) using Windows authentication:

enter image description here

i cannot:

enter image description here

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:

enter image description here

fail:

enter image description here

Note: It's not a client problem, as we can connect fine to other (non-SQL Server 2008 R2 machines):

enter image description here

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

enter image description here

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:

    enter image description here

  2. Click Object Types:

    enter image description here

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

    enter image description here

    and click OK.

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

    enter image description here

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

    enter image description here

    and click Cancel.

  6. Click Browse…, and ensure that you have no ability to add groups:

    enter image description here

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:

    enter image description here

  • SQL Server is set to use Windows Authentication:

    enter image description here

  • 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)

Best Answer

As Matt has stated the Windows login you are using has not been setup and you will need to add it with a login that is a member of the sysadmin fixed server role.

You do not need to be a member of the sysadmin fixed server role to simply connect to your SQL Server 2008 R2 instance.

BTW, during the R2 install, one is able to specify login(s) that need to be added to the sysadmin fixed server role.

UPDATE - How to enable the adding of groups in SQL Server security How to add a Windows group in SQL Server

After including groups, you can find them (if they exist) using the Check Names in the Select User or Group pop-up.

Related Topic