SQL Server and Kerberos

As a DBA you’ll be wanting to promote the use of Kerberos authentication rather than NTLM authentication.

 

DBAs don’t have to be concerned with the whole Kerberos chain, rather you’ll primarily need to ensure your instances are able to service Kerberos authentication requests.

 
Let’s get started.

 
Using Sql Server Configuration Manager, look at the Protocols being used by your instance(s).

 
As shown below, this server has 2 instances, a default and a named instance called NI1.

 
Both instances are configured to listen on Shared Memory, Named Pipes and TCP/IP.

image01

image02

Verify which TCP/IP port the instance is listening on.

 
In this example the default instance is listening on TCP port 1433.

image03

The named instance NI1 is listening on TCP port 2433.

 
NOTE: For named instances you’ll want to keep away from using TCP Dynamic Ports as a restart of the SQL Server service may impact your Kerberos authentication.

image04

Next, check which service account the instances are using for the SQL Server service.

 
Below we can see the default instance is using MYDOMAIN\zSQL01D_SS and the named instance NI1 is using MYDOMAIN\zSQL01D_SS1.

image05

Kerberos relies on a Service Principal Name (SPN) to identify the SQL Server instance.

 
You would use the following syntax to verify if these services have a SPN associated.

setspn /L MYDOMAIN\zSQL01D_SS
setspn /L MYDOMAIN\zSQL01D_SS1

NOTE: I’m using a / (forward slash) instead of – (dash) as you may find you run into issues if you copy/paste a setspn command using a dash.

 
As shown, currently the accounts don’t have any SPNs associated so Kerberos authentication cannot be used.

image06

To verify this, I’ve RDPed to another server SQL02D and then connected to the default instance on SQL01D using the TCP/IP protocol and run the following command:

SELECT net_transport,auth_scheme
FROM sys.dm_exec_connections
WHERE session_id = @@spid

TIP: Using tcp: in front of the instance name forces TCP/IP and hence the connection below shows the use of tcp:SQL01D

image07

image08

TIP: Using np: in front of the instance name forces use of Named Pipes and hence the connection below shows the use of np:SQL01D

image09

image10

So, we know Kerberos is not available and we are using NTLM.

 
To add a SPN to the service accounts, you’ll need a Domain Administrator to run the following commands.

 
NOTE: The Domain Administrator, does not need to be on the SQL server as the command simply registers the SPN in AD against the service account.

 
We’ll start with setting the most common TCP/IP protocol SPN.

setspn /S MSSQLSvc/SQL01D.MyDomain.local:1433 MYDOMAIN\zSQL01D_SS
setspn /S MSSQLSvc/SQL01D.MyDomain.local:2433 MYDOMAIN\zSQL01D_SS1

image11

Once the Domain Administrator has run the above commands you can verify that you now have a SPN associated with the service accounts.

setspn /L MYDOMAIN\zSQL01D_SS
setspn /L MYDOMAIN\zSQL01D_SS1

 

NOTE: You don’t have to restart the SQL Server service for the SPN to come into effect.

image012
Now if we retest a TCP connection you should see KERBEROS authentication being used.

SELECT @@SERVERNAME AS instance,net_transport,auth_scheme
FROM sys.dm_exec_connections
WHERE session_id = @@spid

image13

The Named Pipes will still show NTLM.

image14

Now to enabled Kerberos for Named Pipes the Domain Administrator will need to run the following:

setspn /S MSSQLSvc/SQL01D.MyDomain.local MYDOMAIN\zSQL01D_SS
setspn /S MSSQLSvc/SQL01D.MyDomain.local:NI1 MYDOMAIN\zSQL01D_SS1

image15

Once the Domain Administrator has run the above commands you can verify the SPN exist.

setspn /L MYDOMAIN\zSQL01D_SS
setspn /L MYDOMAIN\zSQL01D_SS1

NOTE: Again no restart of the SQL Server service is required for the instance to take advantage of the SPN.

image16
Now when you retry your Named Pipes test you should see Kerberos in use.

image17

So as above, enabling Kerberos authentication is a fairly straight forward process.

 
Finally, if you find that a SPN needs to be removed for some reason i.e. a port change, the setspn command S is simply replaced with a D and again this has to be run by a Domain Administrator e.g.

setspn /D MSSQLSvc/SQL01D.MyDomain.local:2433 MYDOMAIN\zSQL01D_SS1

image18

Leave a Reply