It’s been a while since my last post (i.e. almost 1 & 1/2 years, ouch!), but I hope to get back into a more regular pace with my updates going forward. My next few posts will be a short series related to Kerberos Authentication, particularly in relation to the SQL Server product family. Kerberos is a critical component needed for some advanced security features and configurations and Microsoft recommends it be enabled in every SQL Server deployment, pending outstanding circumstances. Some people will find it difficult to setup, but those challenges tend to relate to misunderstanding what’s going on and what permissions are required to get everything working. In this post, I’ll discuss what Kerberos is and what is needed within your Windows environment for SQL Server to utilize this authentication protocol.
What is Kerberos?
Some equate the pain of properly configuring Kerberos to a bite from the 3-headed hound from hell, Cerberus, whom the authentication protocol so happens to be named after.
Kerberos is formally defined as a network authentication protocol that works on the basis of ‘tickets’ to allow nodes communicating over a non-secure network to prove their identity to one another in a secure manner. It’s a process where a client authenticates with a server by way of a 3rd party (e.g. the protocol involves the client, the server, and the 3rd party authentication service, hence why Kerberos is named in honor of Cerberus, the three-headed dog).
Here is my attempt to summarize the 11 steps, which occur in 4 phases of the Kerberos authentication handshake:
- User Client-based Login – You log into your local machine using your user credentials.
- Client Authentication – The client’s identity is authenticated with the Kerberos service by sending a one-way hashed version of its credentials. Kerberos returns an authorization token in response.
- Client Service Authorization – The authenticated client requests access to a service which is also associated with Kerberos. The Kerberos service sends back an authorization ticket that can only be decrypted by the service requested by the client along with the service’s identity for the client to confirm during the final validation phase.
- Client Service Request – The client makes a direct request to the service which it now has an authorization ticket for. Both client and server services confirm each others’ identities by way of mutually encrypted messages and access is granted upon successful validation.
If you want to get into the more technical details, I’m going to point you to a great article by Lynn Root, Explain Like I’m 5: Kerberos, which is worth the read if you have the time. Because I understand my description above is pretty awful and even Lynn’s article isn’t the easiest to follow, I’m going to attempt to even further simplify things by way of a metaphor. A round of Kerberos authentication is, in my opinion, quite similar to taking a trip to a Disney amusement park. It’s fun, trust me!
- When you book a trip to Disney, you must create an account on their website. This is effectively an equivalent to logging into your local client.
- While actively logged into their site, you book various items (e.g. hotel, fast pass reservations, meals, etc.) and create your trip itinerary. Now to reserve your selections, you put down a deposit or pay in full using your credit card. This step is equivalent to authenticating who you are with a trusted 3rd party (e.g. the credit card company).
- Disney receives your payment and sends you a MagicBand, which you wear for the duration of your stay. This is your authorization ticket in a quite literal sense.
- You are only able to enter the park after successfully scanning your MagicBand. This way the park knows you are attending during your chosen time and that you have paid (e.g. you are who you say you are); this transaction also implicitly proves you’re at the right park because the MagicBand works (as it doesn’t work anywhere else). This step is the completion of the mutually authenticating component that makes Kerberos so secure.
The key points here are both you and the park now trust that each other is who they say they are without having anyone at the gate check ID (e.g. your username) or take your money (e.g. your password), and no one is able to impersonate you or the amusement park without a significant amount of effort since the authentication was done ahead of time by a trusted 3rd party (in this case the credit card company). The overall transaction is quite secure, and even if you lose your MagicBand you can get another one after a new authenticating transaction is made (e.g. a replacement fee), making it very difficult for someone else to impersonate you.
NTLM: Kerberos’s Less Critical Sibling
Kerberos’s high level of security requires a sacrifice of simplicity to get working, as a number of pieces must be in place beforehand for the protocol to even function. In the event Kerberos isn’t properly configured though, authentication will revert to a less secure Windows authentication protocol, NTLM (NT Lan Manager), rather than fail outright.
NTLM by definition is a challenge-response authentication protocol which uses direct messages between client and server to authenticate a client in a connection oriented environment. Authentication is done using hashed password values stored on the server (or domain controller), however a lack of salting of the hash(es) make them password equivalent. Basically if you can grab the stored hash value from the server, you can impersonate a user without knowing their actual password. Under NTLM, user credentials are only as secure as the server storing them. The process for NTLM authentication is simplified as follows:
- Negotiate Phase – The client will send a message to the service it wants to use requesting access.
- Challenge Phase – The server will send a response back to the client asking for credentials to validate it is authorized to use said service.
- Authentication Phase – The client will respond with a hashed version of it’s credentials, which the service will validate, and upon success access is provided.
This quick rundown is also quite forgettable, so to continue with the theme established in my earlier layman’s example, where Kerberos is a trip to Disney, NTLM is more like a trip to a carnival. Since a carnival often travels across the country, it’s much more cost prohibitive for them to setup equivalent infrastructure to that of Disney (e.g. kiosks, wrist bands, etc). Instead carnivals opt for the age-old cash is king approach. At most carnivals you buy tickets with cash, after which you then give to the ride attendants. Your typical carnival (e.g. NTLM) transaction goes as follows:
- You buy a book of 40 tickets for $20. You negotiate (e.g. pay money) for tickets which are effectively your hashed credentials.
- The attendant at the Ferris Wheel (or whatever ride you prefer) tells you the ride will cost 2 tickets per person. This is the challenge produced by the server.
- You provide 2 tickets per person and go on the ride. You respond with the authorized hashed credentials and authentication succeeds.
This is a far less cumbersome process as it doesn’t require a 3rd party. It’s still somewhat secure as it does not require you provide your raw password (e.g. money) at every challenge (e.g. ride). It allows for quick authentication and in many cases works without issue. However, if someone were to take your tickets without your approval, there is nothing you can do to prevent their unauthorized use. NTLM suffers from this same issue. The tickets are only as secure as you can make their container (e.g. your pocket at the carnival or a server on the domain).
The differences between Kerberos and NTLM authentication methods are subtle, but as they say, the devil is in the details. The key difference is regarding the level of trust NTLM assumes that Kerberos does not. NTLM authentication trusts you are who you say you are blindly, while Kerberos asks a friend to vouch for you. Additionally, in Kerberos, the client makes sure the server is also a trusted entity, where in NTLM no such confirmation of the service is performed.
How am I currently Authenticating?
SQL Server, by nature, doesn’t care what method of authentication is employed, so many database administrators may not even realize there are different types of authentication available. To reiterate, by default in a Windows domain, if a connection is not established via Kerberos, it is established via NTLM instead. To quickly find out what type of authentication is in use for an existing connection you can run the following query (ref KB 909801):
select auth_scheme, net_transport from sys.dm_exec_connections where session_id=@@spid
This will let you know if your current connection (and implicitly any other remote connection) is authenticated via Kerberos or NTLM. If you see NTLM show up, then the environment likely requires additional configuration before Kerberos authentication is utilized.
What is needed for Kerberos Authentication?
Starting with Windows 2000, if your SQL Server deployment is on a Windows Domain, most of the tools to utilize Kerberos authentication are already in place. The Domain Controller already comes with a Key Distribution Center (KDC) and, by default, the Kerberos protocol is the preferred authentication method over NTLM. Client machines (e.g. PCs on the domain) pre-authenticate via Kerberos upon login unless explicitly instructed not to, so generally the only outstanding part in the Kerberos 3-party authentication model that requires configuration is the server a particular client is connecting to.
Client connections need a way to recognize that a server service is a willing participant for Kerberos authentication, and this is the part of the Kerberos authentication protocol that is not turnkey by default. The way clients identify services are Kerberos compatible is by way of a Server Principal Name (SPN). The SPN is used by the client as a reference when it requests authentication from the KDC. Without the SPN, the client and server cannot mutually authenticate one another, as the client would have no way to validate the service it is requesting to use.
In order for a service to register a SPN automatically, the service account running said service must have proper permissions on the domain. These required permissions are granted by default if the service is running as a Machine Account, a Managed Service Account, a Group Managed Service Account, or a Virtual Account. Sadly, most SQL Server deployments use a traditional service account which is generally not granted the required permissions to register a SPN.
The quick fix here is to manually register the SPN for the SQL Server service in question. In order to do this though, the user running the operation must either be within the Domain Admin group, the Enterprise Admin group, or said user must have had the appropriate authority delegated to it (which will be discussed in more detail in my next post). If you’re account (or an account you have access to) does not belong to one of these groups (and no DBA should), bring in a box of treats for your system administrators and have them run the appropriate command on your behalf.
The command in question to run is setspn, and the format needed is dependent upon what protocol(s) the SQL Server service has enabled. Beginning with SQL 2008, both TCP/IP and Named Pipes protocols are supported for Kerberos authentication, so the relevant SPN formats are as follows:
| Syntax | Description |
|---|---|
| MSSQLSvc/fqdn:port | The provider-generated default SPN when TCP is used (e.g. TCP/IP connections). |
| MSSQLSvc/fqdn | The provider-generated default SPN for a default instance when a protocol other than TCP is used (e.g. Named Pipes). |
| MSSQLSvc/fqdn:InstanceName | The provider-generated default SPN for a named instance when a protocol other than TCP is used (e.g. Named Pipes). |
port is a TCP port number.
fqdn is a fully-qualified domain name.
InstanceName is a SQL Server instance name.
To summarize what’s above, if you’re using TCP/IP connectivity exclusively, the only thing you’re concerned with is the port number the SQL Server service is running on. Names are irrelevant. If you are using named pipes for connectivity, you will want to ensure you register the name of the instance (or a lack of a name in the case of the default instance).
Examples for servers utilizing TCP/IP connections:
## This will register a SPN for a default instance setspn -a MSSQLSvc/servernameA.mydomain.com:1433 mydomain\serviceAccount ## This will register a SPN for a named instance listening on port 60203 setspn -a MSSQLSvc/servernameB.mydomain.com:60203 mydomain\serviceAccount
Examples for servers utilizing Named Pipes connections:
## This will register a SPN for a default instance setspn -a MSSQLSvc/servernameC.mydomain.com mydomain\serviceAccount ## This will register a SPN for a named instance, TestX64 setspn -a MSSQLSvc/servernameD.mydomain.com:TESTX64 mydomain\serviceAccount
Of note is my lack of any mention of Kerberos support with the shared memory protocol (which is in stark contrast to this TechNet article). This is because you are only able to connect to a SQL Instance via Shared Memory when connecting from the local machine. Connections from the local machine do not reach out to the domain and therefore do not initiate the Kerberos authentication handshake. All Shared Memory connections will revert to NTLM authentication, so don’t be alarmed if you cannot authenticate via Kerberos using Shared Memory.
Is there any way to make this easier?
There are absolutely ways to make this whole process easier if not practically automated, and in my next blog post I’ll explain how to do just that. Hopefully this post was helpful to better understand Kerberos authentication, SPNs, and how SQL Server can be more secure by taking advantage of both.
Comment if you have questions, and stay tuned for more!
John
John Eisbrener, DBA 