Shawn Meyers (@1dizzygoose), Principal Architect
Recently I have been dealing with a lot of SPN and SQL Server double hop issues. I was really starting to get good at all of the syntax needed to make this work, but then I found a tool (almost an easy button) from Microsoft.
This tool came out five years ago, so how come this isn’t the first result when Googling SPN issues? At the time of this writing it isn’t. Hopefully many of you will find this tool helpful, and this blog will make it easier for others to find it. I wonder how many people have been banging their head against the wall trying to solve these sorts of issues when they could solve them with just a couple of clicks? So if you want solve those SSPI errors, or are trying to setup double hop authentication, read on.
First, what are Service Principle Names (SPNs) and double hop issues? In simple terms, a SPN is a unique identifier for a Windows service and a service account running that service. SPNs are used for Kerberos authentication. Double hop issues are when you have a client connect to one SQL Server and that server needs to pull data from another SQL Server. The first server uses Windows Authentication credentials on the second server and the connection to the first SQL Server is made using Kerberos authentication. However, if Kerberos fails to work, it will default to NTLM and a NTLM connection cannot be passed to a different server and the connection will receive an error, usually ‘failed anonymous login’. Many organizations get around not fixing SPNs by using a set of SQL Server credentials between the two servers, but this means all users using this connection have the same level of security, which typically isn’t a good idea. You also see this a lot with linked servers and SSRS. This blog is meant to be a very brief overview with the idea that you can Google to learn more. In addition, post shows how to solve these issues, but does not explain the nature of the issue itself.
The tool is called the Kerberos Configuration Manager, and you can run it from anywhere (including your laptop) and connect to your SQL Servers. Download the 64 bit version, since if you are still running a 32 bit OS I’m not sure why – the 2000’s are long over. After you download it, run the .exe to install it. Keep in mind, that it is not a full application, and doesn’t show up as a new application in many operating systems., For example, in Windows 10 it doesn’t always show up in the application search, so just browse to the c:\program files\Microsoft\Kerberos Configuration Manager directory and run the executable. You will want to run it as an administrator, as you will want the extra permissions.
The image below shows what the opening screen looks like. Notice the text in red, which indicates that SSRS in SharePoint mode, and people running SQL Server 2005 and above are out of luck – this is yet another reason to upgrade beyond not being supported.
What can you find and fix with this tool? I am glad you asked, as you can fix SPN issues easily. Plus, it looks at delegation, and delegation is one of those areas that people may not know about when it comes to double hop authentication.
On the tool bar, select Connect and you will see the screen below. If you are running it locally, just click connect. If you are connecting to a remote server, enter in the server name (in most cases you don’t need to enter in the username and password). If you are connecting to a remote server on a different subnet, you will usually need the username and password of a user with admin rights to the server and SQL Server. I have found a few servers that I cannot connect to remotely, and have had to run the tool locally, which I run from a network share so you don’t have to install it. I haven’t had time to figure out the reason why yet.
Once the tool queries the AD, it will come back with a screen with all sorts of information. There are three tabs, and the first tab is labeled System, which offers background information for you. But the SPN tab is where the real fun is.
My example below shows that the SPN for the SQL Server is fine, but the AG Listener SPNs are not correct. But look at the far right, and there is a button to generate a script to fix the issue, or a button to just fix it outright. The fix it button works great if you have established enough permissions in the Active Directory to create a SPN, but for most DBAs this is not the case. So you can generate a script, which also works wonders and you can take that script and provide it to the AD admins at your organization and after they run the script, you are set.
The Delegation tab below shows whether the service account for SQL Server is configured for delegation. You need to enable delegation in order to allow the service account to pass credentials for other accounts along to the next server. However, in the example below delegation is not set. So, if I wanted to use this server for double hop authentication, I would need to enable delegation on the service account.
The screen shot below shows the SPN for SSRS is set correctly, which is important when using double hop security for your reports. This tool does a good job of looking at all of these things, but make sure you run it against all of the SQL Server involved in the double hop process.
When you select ‘generate the script’ it creates a .cmd file, which you can just run. I typically open it up and select only the key part of the script that I care about. For example, in the script below, which is a sample generated, the only line I really need is the highlighted one.
SetSPN -s "MSSQLSvc/credit.meyers.local:1433" "MEYERS\SQLService"
This is the SetSPN syntax needed to create the SPN correctly:
@echo off :: This script is generated by the Microsoft(c) SQL Server(c) Kerberos Configuration Manager tool. :: The file is intended to be run in domain "meyers.local" echo Changing SPN configuration may affect other services running on the same machine. By selecting to continue, the following action(s) will be performed: echo. echo Add SPN "MSSQLSvc/credit.meyers.local:1433" to account "MEYERS\SQLService" :Prompt set /p answer=Are you sure you want to continue? (Y/N): if %answer% == Y goto Yes if %answer% == y goto Yes if %answer% == N goto No if %answer% == n goto No cls echo Unknown input goto Prompt :No exit :Yes SetSPN -s "MSSQLSvc/credit.meyers.local:1433" "MEYERS\SQLService" set /p answer=Press any key to continue... @echo on
To set delegation you need to go into AD, using Active Directory Users and Computers. Make sure Advanced Features are being shown, or the delegation tab will not show up. On the delegation tab, you set the account for delegation. I selected the Kerberos only option, but in a truly secure environment, I would list out all the services that are allowed to use it.
To recap, the Kerberos Configuration Manager is a simple way to solve your SPN issues. Whether it is a SSPI handshake error, or a double hop issue with a linked server or SSRS; this tool will query the AD and list out what needs to be done to resolve the issue. This includes finding duplicates, discovering that SPN is set to the wrong service account, or if one just missing. So no more spending a bunch of time trying to get the syntax correct and have it still not work, because you didn’t realize you created a duplicate, etc.
