How does MS SQL Server handle windows logins?
To understand how this works it helps to understand how SQL handles Windows Logins. Basically SQL Server only handles the authentication of a SQL Login, NOT a windows login. Windows login authentication is passed off to the Operating System via the SSPI. If the SSPI says the login is good it allows it, if it says it's bad it doesn't. Simple so far you might think, well yes it is, the issue comes when you want to specify Kerberos rather than NTLM authentication.
Why Kerberos over NTLM Authentication?
There are quite a few reasons why it is better to use Kerberos rather than NTLM authentication.
NTLM is not necessarily a bad authentication method, but it does have issues that Kerberos doesn't:
- It assumes trustworthiness of the server connecting to it.
- It requires more traffic than Kerberos so performance is not as good.
- NTLM is vulnerable to replay attacks, because it does not include a timestamp with the transaction, Kerberos on the other hand does, and if the timestamp is outside the time range (Default 5 mins) then Kerberos will reject the network traffic.
- NTLM cannot perform multiple hops.
An SPN is needed for Kerberos authentication as it provides the client connecting to the SQL service with certain information:
- Type of service (In this case SQL Server MSSQLSvc).
- The Name of the server.
- The Port.
- The service account running the service.
MS SQL Service Account
As we all know it is good practice to use a domain account to run your SQL Server Service (MSSQLSvc). The problem with this is that if your SQL Service is not running as either the Servers System Account or a Domain Administrator, SQL Server cannot register its Service Principle Name (SPN) when the service is started, and you will see an error in the SQL log that looks something like this:
The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
There is however a way of setting the SPN Manually with a Domain Administrator account using the setspn command.
Using the setspn command
Please note you will need to run the Add and Delete setspn command with a Domain Administrator Account.
The setspn command has various switches, but the ones we are interested in are:
setspn -l <Account>
This will list the SPNs that are registered for the account specified, this can either be a User or Computer Account.
setspn -a <Service>/<SQL Server Name>:<Port> <Account>
In this case the <Service> would always be MSSQLSvc as we are setting the SPN for SQL Server. The -a switch will ADD the SPN.
setspn -d <Service>/<SQL Server Name>:<Port> <Account>
The -d switch will DELETE the SPN.
Examples
Listing the SPNs assigned to a server.
SETSPN -L MyWebServer
Listing the SPNs for the SQL Server Service Account.
SETSPN -L MyDomain\SQLServerServiceAccount
Adding SPNs for the SQL Server Service Account
Default instance:
SETSPN -A MSSQLSvc/MyDatabaseServer.MyDomain MyDomain\SQLServerServiceAccount
SETSPN -A MSSQLSvc/MyDatabaseServer MyDomain\SQLServerServiceAccount
SETSPN -A MSSQLSvc/MyDatabaseServer.MyDomain:1433 MyDomain\SQLServerServiceAccount
SETSPN -A MSSQLSvc/MyDatabaseServer:1433 MyDomain\SQLServerServiceAccount
As you can see we are adding 4 SPNs here this is because you need to remember to set an SPN for both the NETBIOS and FDQN.
With a Default instance of SQL you do not really need to specify the port number. This is because the default instance should be using port 1433 (Unless it's been changed) so a port is not required. However it doesn't hurt to run the commands with the ports added.
Named instance:
It is important to remember that if you want to use Kerberos authentication on a Named instance of SQL that is not using the Computer System Account or a Domain Administrator Account then for obvious reasons you will need to statically assign a specific port so you can set an SPN.
I say this is obvious but this is because if you leave the SQL browser Service to Dynamically assign a port your SPN would not work unless by pure chance the SQL Browser Service assigns the port which you have manually assigned the SPN.
So once you have statically assigned the listening port for your Named instance of SQL you can set up the required SPNs. In this example I have used port 4444.
SETSPN -A MSSQLSvc/MyDatabaseServer.MyDomain:4444 MyDomain\SQLServerServiceAccount
SETSPN -A MSSQLSvc/MyDatabaseServer:4444 MyDomain\SQLServerServiceAccount
Checking whether you are connecting using NTLM or Kerberos
To check which method of authentication you are using you can run the following in Query Analyser:
This will give you a table that shows all connections to the server with various useful info.
