Today I want to tell you about an issue we ran into while working on one of our customers and, while it’s easy to understand when explained, it might be tricky to get done when you don’t look at the whole picture.
Background
When you start learning about SQL Server security, the first thing you read is that SQL Server (on Windows) can use either SQL Server Authentication, Windows Integrated Authentication or both at the same time. Windows Authentication is the preferred way as it’s more secure and robust than SQL Logins.
Connections using Windows authentication over TCP can obtain one of two different Authentication schemes, either NTLM or Kerberos.
In the same way that it is more secure to use Windows Authentication over SQL Server Authentication, Kerberos is more secure than NTLM.
The problem of Kerberos is that is not easy to configure and multiple times results in the well known Anonymous Logon Error, aka Double Hop.
That’s why you will find plenty of IIS and other applications out there, using SQL logins (Impersonation Users), because Windows Authentication can be really frustrating and applications won't be able to connect to SQL Server otherwise.
There are multiple resources in the internet that explain the Double-Hop issue, so that won’t be the scope of this post, but I will show how to correctly configure SPNs to SQL Server Availability Groups, which is the first link in the Kerberos chain.
Setup
The environment I’ll be testing this is a basic configuration of two SQL Servers using Availability Groups:
- MATTAGSQL01\MSSQL2017, listening port 1433
- MATTAGSQL02\MSSQL2017, listening port 1433
- MATTAGRAG_LN, AG listener’s listening port 5100
To allow Kerberos authentication, we need to create Server Principal Names for the SQL Servers and the AG listener for both, the instance name and the listening ports for the service account
Before creating any SPN for the Availability Group, the configuration for the service account is as follows:
And that will allow us to connect to each of the instances using Kerberos:
Note that I'm running this from SQL02, so only SQL01 will use Kerberos as SQL02 uses Shared Memory.
If I run the same for the AG listener, since I didn't create any SPNs, I'm expecting not to be able to use Kerberos yet.
So to get this working, I'm going to create my SPNs, one for the InstanceName and another for the Listener's listening Port, 5100. Once it's done, I have all these:
You can see both versions using fqn and NetBiosName, again for the InstanceName and port. That should get the job done.
Not quite.
We cannot get a Kerberos connection (just yet), applications that use Windows Authentication to connect to the listener most likely will get [NT AUTHORITY\Anonymous Logon] login errors.
As mentioned earlier, there is a ton of resources for this specific errors, which will tell you to configure SPNs and Delegation to make it work, but (forgetting about delegation now) we DO have our SPNs created, why doesn’t it work?
The Problem
At this point it’s clear we have a problem and I intentionally hid it partially, but it might well be overlooked.
If I run the same query, but just bringing the columns I’m interested in, we’ll see it more clearly.
When we try to connect to the listener specifying the instance name, our request will go from the client to the listener which will determine the Primary Replica and then it will connect through the SQL Browser, if running, which in our case it's required to as we're using named instances.
The instance then will respond from the port the instance is listening on, hence we get the Local TCP Port as 1433, where the Listener Listening port is 5100.
We have not created a SPN for the listener using the instance port, remember we did only for the listener’s listening port.
The Fix
Now let’s create the missing SPNs and see if that allow us to get Kerberos connections.
Now that we have the combination Listener:InstancePort (1433) we can try again.
If we open a new query window and if our DNS server has updated (IMPORTANT!!), we can see all connections using TCP use now Kerberos, I also included one connecting to the listener port, so you can see the difference.
And that is the foundation to start using Windows Authentication in your applications that need to connect to a SQL Server Availability Groups Listener.
Conclusion
As you’ve seen, it’s not super complicated to get Kerberos working, but it’s important you understand the different elements that will allow you to use it.
If you have the right SPN configuration and still can't see it, be wary that sometimes DNS changes take time to apply, in my case I cleared the DNS cache and rebooted my domain controller, but my environment is a lab not a production server, so you might have to wait for the changes to be replicated.
You also might be wondering if it wouldn't have been much easier to choose the same ports everywhere from the beginning, the answer is Yes!, but then I couldn't have written this post.
Thanks for reading!
