Just a quick one today. TL;DR: Remove yourself from some unnecessary AD groups.
If you've ever received the message "login failed for 'nt authority\anonymous logon' while working with SQL you know how frustrating it can be. I'm going to throw a troubleshooting tip that doesn't get much attention into the bag of tricks you might want to try.
Kerberos is Broken! /s
You will quickly discover via google - if you didn't already know - that this is most likely a kerberos double-hop issue. You're issuing a request from your client machine to some middleman server, which is then forwarding that request on to a destination service and trying to send your credentials along with the request. In my experience the most typical cases by far are two:
- Opening a web page on your workstation. The web server then issues requests against a database. The web application is set up to delegate client credentials.
- From SSMS on your workstation, issuing a query against SQL Server A which also invokes the use of a linked server to SQL Server B. The security tab of the linked server is set for connections to "be made using the logn's current security context"
The Usual Suspects
The typical (good) advice you will find when you enconter this problem is to check a list of things that looks something like this:
- Make sure the SQL Server has SPN's registered. If you have set SQL to run using a domain account you need to do this manually. You should create at least two, and possibly four SPN's as follows
| MSSQLSvc/machine_name:port domain\service_account_name | (eg, MSSQLSvc/SQLServerA:35312 MyDomain\MySQLServiceAccount) |
| MSSQLSvc/FQDN:port domain\service_account_name | (eg MSSQLSvc/SQLServerA.MyDomain.com:35312 MyDomain\MySQLServiceAccount) |
| MSSQLSvc/machine_name:instance_name domain\account_name | (eg: MSSQLSvc/SQLServerA:MyNamedInstance MyDomain\MySQLServiceAccount) |
| MSSQLSvc/FQDN:instance_name domain\account_name | (eg: MSSQLSvc/SQLServerA.MyDomain.com:MyNamedInstance MyDomain\MySQLServiceAccount) |
- Make sure that the SQL Service account is trusted for delegation (Active Directory Users and Computers > find > SQL Service account > delegation tab)
- Make sure that the client account is not marked as sensitive (Active Directory Users and Computers > find > client account > Account tab > Account Options checkbox "Account is sensitive and cannot be delegated"). If you're down to this step you're running out of options, because this is almost never the problem
- At the command prompt on the client machine, run "klist purge" to refresh your tickets. Again, we're starting to grasp at straws here
- If you have multiple domain controllers, wait around 15 minutes before trying again to make sure all of the required information is replicated to all of the DC's. This step seems to exist just to annoy you, right?
- In SSMS, make direct connections to the involved SQL server(s), and select * from sys.dm_exec_connections where session_id = @@spid. Look at this auth_scheme column. It should say Kerberos, not NTLM.
- You do all of the above, and things still aren't working. Throw your hands up in despair!
But wait, I have one more thing for you to try!
If you're in a position where you have the authority to create linked servers, set up active directory accounts, and particularly if you can create SPN's (which requires domain admin membership), then you might be someone who is a member of a LOT of AD groups.
Kerberos needs to know about all the groups you're in, and will try to stuff all of this information into your ticket. But tickets have a maximum payload size, so your ticket isn't valid.
I offer two solutions
- Remove yourself from some AD groups. This is usually an easy solution, because you tend to accumulate "trash" groups over time - groups that you needed temporarily, or groups that have become superfluous over time. AD admins are pretty good at adding users to groups as needed, but removing them when they're no longer needed is a bit more problematic!
- If you actually need to be in all of your groups, try this hotfix from Microsoft.
