How to Configure Kerberos Authentication for SQL and MOSS

Apart from AX, In my current role, I have been extensively working on MOSS/Sharepoint Administration and Troubleshooting, Configuring Kerberos Authentication for SQL, MOSS Server and SSRS, Planning for Disaster Recovery, Troubleshooting SSRS.


This post is on my experience on setting up Kerberos for our standalone MOSS, SQL and SSRS Servers and troubleshooting IIS for Kerberos.

1. Server Farm Topology

TEST-DEV2 Server: MOSS Server 2007 Farm  (Primary) – Central Administration, IIS

TEST-DEV1 Server:


a.   SQL Server 2008 Database Engine, SSRS 2008 (Sharepoint Integrated Mode)

b. MOSS WFE (Web Front End Part of Server Farm configured on TEST-DEV1)

2. Setup Kerberos for SQL Server (TEST-DEV1)

Before we setup Kerberos for Sharepoint Server, we need to configure SQL for Kerberos

a. Register SPN’s for SQL Server Service (one  with NETBIOS and other with FQDN) as shown below

Setspn -a http/<computer-name>.<domain-name>:<port> <domain-user-account>

Setspn Command line Reference - http://technet.microsoft.com/en-us/library/bb735885.aspx

2. Test  the connectivity on SQL Server is via Kerberos and not NTLM

Logon to SSMS (staying in the same box) and fire the below SQL, if SPN’s are properly setup, then you should see KERBEROS in auth_scheme field.

               

3. Another way to test if Kerberos is being used as Authentication scheme is to bring up Central Administration site and now go back to TEST-DEV1 (the host box where SQL is running). Bring up the Event Viewer (check Security tab)

4. In the Security Log, you should be able to see Success audit record for Logon/Logoff category event

Check the Detailed Authentication information and should be able to see the Login mechanism as Kerberos which confirms our test that MOSS is communicating with SQL via Kerberos

3. Setup Kerberos for MOSS Server (TEST-DEV2)

Register SPN’s for MOSS Server (Service account) in the similar fashion as we did for SQL:

1. Register with NETBIOS name
2. Register with FQDN name as shown below

3. Browse to Central Administration website and confirm by going to the Event Viewer of the hostmachine in Security log, (similar to the steps we performed for SQL) that the authentication package here is Kerberos

TROUBLESHOOTING:

A.

Troubleshooting IIS Authentication HTTP Error 401 after Kerberos is setup for MOSS Server Farm

Note: If you are running IIS 7.0 server in a Web farm the KDC will not know in advance which individual server the request may go to and hence ticket decryption may fail

Bring up IIS Manager, Go to each of your websites and turn off the Kernel Mode Authentication under Windows Authentication Advanced Settings option

OR

Let Kernel mode authentication be enabled and the Application pool's identity be used for Kerberos ticket decryption. The only thing you need to do here is:

1. Run the Application pool under a common custom domain account.

2. Add this attribute "useAppPoolCredentials" in the ApplicationHost.config file.

<system.webServer>

  <security>

     <authentication>

        <windowsAuthentication enabled="true" useKernelMode="true" useAppPoolCredentials="true" />

     </authentication>

  </security>

</system.webServer>

Remember there is no GUI setting for this. You need to modify the ApplicationHost.config file from

<%SystemDrive%>/Windows/System32/inetsrv/config folder on the IIS 7.0 machine.

Note:
Tried both the options but Option A works fine with Option B, there’s this frequent system crash as I reckon it’s changing the applicationhost.config file settings. Need to check this behaviour in Production Server as Option B is more performance friendly and recommended approach

B.

Configuring Kerberos for SSRS (running in Sharepoint Integrated Mode):

1. Register SPN for SSRS Server Instance  (both with NETBIOS and FQDN)

Setspn -a http/<computer-name>.<domain-name>:<port> <domain-user-account>

Reference - http://technet.microsoft.com/en-us/library/cc281382.aspx

2. Open the RsReportServer.config file and locate the <AuthenticationTypes> section. Add <RSWindowsNegotiate/> as the first entry in this section to enable Kerberos

3. Test: Deploy a Report through BIDS to  a Report Center site, http://test-dev2/rc/

Troubleshooting tip: You may run into error rsaccessdenied: The account SHarepoint/System doesn’t have permissions to deploy the report. In order to troubleshoot this error, check if spn’s are properly registered with the sharepoint service account and there’s no duplicity of SPN’s.