To quote Maria Von Trapp, “Let’s start at the very beginning, a very good place to start.” In case there is anyone who doesn’t know, SharePoint heavily relies on SQL. In fact, a SharePoint farm, isn’t really the servers, its the configuration database that lives in SharePoint. The two are so closely related, it is no surprise that in order to utilize Kerberos for SharePoint, you have to set up Kerberos for SQL first.
Now, if the SQL service account is a domain admin account when SQL is first configured, then Kerberos is set up for you, which saves you a lot of trouble, but you will still want to verify that it is configured.
Here is an easy way to verify the SPNs and delegation for SQL:
1. On a machine connected to the domain, open ADSIEdit. In Windows Server 2008, on a domain controller, this is now available by clicking Start>Administrative Tools>ADSIEdit. On other machines, you may have to install the utility and access it by running ADSIEdit.msc.
2. Once ADSIEdit is open, expand the tree down to where you see the service account SQL is utilizing to run.
3. Right click on that account and click Properties.
4. Scroll down through the properties until you see servicePrincipalName. Double click the property.
Here you should see at least 2 entries they should be in this format: MSSQLSvc/servername.domain.com and MSSQLSvc/Servername.domain.com:1433. You may see more entries if other SQL servers are utilizing this same account as the service account.
If the entries are there, you will need to verify that you can connect using Kerberos. Open SQL Server Management Studio on a separate machine and connect to the SQL instance you are testing. When you connect, you can then go to the server where SQL is running and you should see an entry in the Windows Security Logs where there is a logon from the account name you are using to connect and the Logon Process will say Kerberos.
If you can see all of this, then you are ready to move on, you can stop reading if you really want to, but I suggest you read on, just so you know how to handle it at that time when you have to manually set the SPNs and delegation. Believe me, that time will come!
So there are a couple of ways to set up Kerberos manually for SQL. An easy way is to use ADSIEdit as we did to check for the SPN and enter the SPNs as shown above, the other is a little bit better way, and that is using SetSPN.
SetSPN is a command line tool, so you will have to open a command prompt. You can also verify the SPNs using this method as well by typing the following: SetSPN –Q servername.domain.com. This will return both entries for SQL as well as the account the SPN is registered to.
To add the SPNs, you will need to type: SetSPN –U –A mssqlsvc/servername.domain.com sqlserviceaccount, be sure you replace servername.domain.com with YOUR server name and sqlserviceaccount with the actual service account you are using. You will need to do this both with and without the port.
Once you have that done, you will need to make sure you set the machine up for delegation. Now, if you are using SQL on a cluster, you will need to set up the cluster name that is used, not the individual nodes of the cluster for the delegation.
To trust the machine for delegation, open up Active Directory Users and Computer (ADUC) and expand the tree until you can see the machine name in the right hand pane. Open the properties for that machine and click on the Delegation tab. Then ensure the radio button is selected for “Trust this computer for delegation to any service (Kerberos only).
Once you have set up the SPN and the delegation, you can test using the same method as suggested above with SQL Server Management Studio.
The next installment in my Kerberos series will be setting up Kerberos for SharePoint. Hope you learned something from this one!