If you have every come across double hop issue in sql server and spend more than 2 days banging your head against a wall to find the right solution then this article might help you . This article will not talk in detail about Kerberos authentication, there are some real good articles that talk in detail which you can find at the end of this article.
Issue: You are using SSMS on your desktop connected to ServerA and trying to query database on ServerB using linked server which is set to run under windows credentials then you will notice below error message:
‘Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’. (.Net SqlClient Data Provider)’.
If you are using sql accounts for linked servers then this article might not be very helpful, i would strongly recommend using windows credentials so that you can track who has access and also be easy pass for info security team :).
Basically you will have to enable Kerberos authentication across ServerA and ServerB, to do this you will have to tweak SPN (Service Principal Name). If you have met below requirements then it is just a 2 step process to make this work.
- Account under which sql services are running should be same domain account across all the servers and should be local admin on the box.
- TCP/IP should be enabled on all the servers that would be talking to each other.
- SQL instances should have static ports, Kerberos Configuration manager will give warning for dynamic ports .
If above 3 requirements are met then you need to :
i) Download the tool ‘Microsoft® Kerberos Configuration Manager for SQL Server®’ from here . Before you tweak anything please download this tool and have it run against the servers that you want to connect. It is very likely SPN’s are already set up properly, search of word SPN in sql log , if SPN’s are set properly you should see something like ‘The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/ServerA.domain ] for the SQL Server service.’ . This tool will also fix any bad SPN’s .
ii) In active directory, right click properties of the account under which sql services are running. Click on Delegation and select second option ‘Trust this user for delegation to any service (Kerberos only).’ You could select the 3rd option also to be more specific and add individual server and services.
Once above two steps are completed you should be successfully able to use linked servers using AD authentication. Keep in mind every AD is different it can take anywhere between 15 mins to 2 hours to replicate your changes across different AD’s, you will have to wait until changes are replicate across all AD’s. If you are on a mission to have this implemented in production like me i would strongly recommend to test this in lower environment or even on Microsoft Virtual labs so that you don’t have to mess around with your AD, if you are using MVA this module will provide you 3 VM hosts and 1 DC, OS and SQL Server are 2012 but same steps apply to all OS versions.
Questions and comments are welcomed .
i) http://www.seangallardy.com/2014/05/using-kerberos-with-sql-server-part-1-double-hop/ .