Using friendly DNS Names in your TFS environment with Kerberos Authentication Part 1

UPDATED: June 11th, 2012 Added reference to MSA (Managed Service Accounts)

UPDATED: January 14th, 2014 Added link to hotfix for Windows Server 2008 R2 to use MSAs

You’re probably here because you’ve read this excellent post by Ed Blankenship.  If you also want to enable Kerberos Authentication support to this configuration there are a few more steps involved.  This post will only deal with configuring the Kerberos Authentication as it relates to SQL Server, I will cover the TFS and SharePoint configuration in another post.

IMPORTANT

If you are accessing the friendly names on the same computer they refer to you may receive the following error: HTTP Error 401. The requested resources requires user authentication.

An example of this is where I am on the TFS Application Tier and Launch IE and attempt to browse the SQL Server Reporting Services website which is hosted on the local machine.

If this occurs then you probably have not disabled the Loopback Check for this friendly name, please refer to this KB 896861 on the methods to resolve this.

LET’S GET STARTED

To continue I will assume you have friendly names configured for all of your various servers and/or applications.

I’ll step through enabling Kerberos Authentication for the following services in a typical TFS 2010 Enterprise configuration:

• TFS 2010 Application Tier
• SQL Server 2008+
• SharePoint Server 2007/2010 Web Site
• SQL Server 2008+ Reporting Services

SQL Server 2008 and SQL Server 2008 Reporting Services

Here are the service accounts that I will be referring to:

• DOMAIN\_sq8_sqldbserver – this account is used to run the SQL Server Database Engine Service
• DOMAIN\_as8_sqldbserver – this account is used to run the SQL Server Analysis Services Service
• DOMAIN\_rs8_sqlrsserver – this account is used to run the SQL Server Reporting Services Service
• DOMAIN\_ag8_sqldbserver – this account is used to run the SQL Server Agent Service

SPNs (Service Principal Names)

If you want to verify the accounts being used to run your SQL Server Services simply Launch the SQL Server Configuration Manager on the appropriate servers.  It should look something like this screenshot:

We must create the SPNs for the various SQL Server Services service accounts:

• setspn -S MSSQLSvc/sqldbserver:1433 domain\_sq8_sqldbserver
• setspn -S MSSQLSvc/sqldbserver.domain.local:1433 domain\_sq8_sqldbserver
• setspn -S MSOLAPSvc.3/sqldbserver domain\_as8_sqldbserver
• setspn -S MSOLAPSvc.3/sqldbserver.domain.local domain\_as8_sqldbserver
• setspn -S MSOLAPDisco.3/sqldbserver serverHostname
• setspn -S MSOLAPDisco.3/sqldbserver.domain.local serverHostname
• setspn -S http/reports.tfs.domain.local domain\_rs8_sqlrsserver

The first two SPNs above are for the SQL Server Database Engine, the next two SPNs are for SQL Server Analysis Services, the next two are for the SQL Server Browser Service and the last SPN is for the HTTP endpoint for SQL Server Reporting Services.

NOTE: SPN formats have changed starting with SQL Server 2008.  If you are running SQL Server on a different port or using a named instance please reference the link and format your SPN accordingly.  If you are running Analysis Services as a named instance you must also format your SPN to include that information, please refer to the following KB 917409.  If you are using a named instance for Analysis Services or of SQL Server you must also have an SPN for the SQL Server Browser Service, please refer to the following KB 950599.

Configure Active Directory settings

Make sure that all the following conditions are true for the Active Directory directory service settings, you can find the configuration for these settings by launching Active Directory Users and Computers and Right Clicking on the Object (Domain Account or Computer Account) you wish to configure select Properties and then click the tab titled Delegation :

• The Account is sensitive and cannot be delegated setting is not enabled for user accounts that will be delegated.
• The Trust this user for delegation setting is enabled for the domain account of the middle tier that is connecting to SQL Server (Database Service or Analysis Service). For example, if IIS is the middle tier and a domain account is used for the application pool, that application pool domain account must have the Trust this user for delegation setting enabled.
• The Trust this user for delegation setting is enabled for the accounts of all services and COM+ components that are involved in the process.
• The Trust this computer for delegation setting is enabled for all the computers that are involved in the process.

The Active Directory settings I need to configure in this case are as follows:

Launch Active Directory Users and Computers

Find the server that is hosting your TFS Application Tier/Reporting Services and Right Click, select Properties, select the Delegation tab and select the “Trust this computer for delegation to any service (Kerberos only)” radio button and click Apply.

Find the domain account that is running the SQL Server Reporting Services Service and Right Click, select Properties, select the Delegation tab and select the “Trust this user for delegation to any service (Kerberos only)” radio button and click Apply.

WANT A GUI

If you don’t want to use the command-line (setspn command) to create your SPNs you can use ADSI Edit to update the servicePrincipalName Attribute of the various domain accounts referenced above.

SQL Server Reporting Services Configuration

There is some additional configuration for SQL Server Reporting Services that you must perform by editing the following file:

rsreportserver.config

It is located in the folder where you installed SQL Server Reporting Services which in my case is:

C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer

You must ensure the bolded element exists in the following section:

<Authentication>
<AuthenticationTypes>
<RSWindowsNegotiate/>
<RSWindowsNTLM/>
</AuthenticationTypes>

If a domain account is configured for the Reporting Services service account and an SPN is not configured this configuration might prevent users from accessing the service.

The SQL Server services should all be restarted to take advantage of the SPN configurations we did above, these services are as follows:

• SQL Server Database Engine
• SQL Server Analysis Services
• SQL Server Reporting Services

The first two should be located on the database server and the last one should be located on the TFS Application Tier.

On the TFS Application Tier you must create a SQL Alias, to do this I recommend you execute SQL Server Configuration Manager to configure your Alias.

This step is required so that when a SQL connection is made to your friendly name Kerberos Authentication will be used.

Verify Kerberos is being used by SQL

You can test whether you have everything configured correctly at this point by launching SQL Server Management Studio on the Application Tier and connecting to the database server and executing the following query.  The steps are as follows:

1. Launch SSMS and Connect to your database server using the ALIAS
2. Execute the following Query

select auth_scheme from sys.dm_exec_connections where session_id=@@spid

This will show you the authentication scheme being used on the current connection (your SSMS connection), this will verify your Alias is configured correctly.

If you want to see the authentication scheme used by all the connections just edit the query slightly removing the where clause and including the client address:

select auth_scheme, client_net_address from sys.dm_exec_connections

Verify Kerberos is being used by SSRS website

From a client computer (not directly on the TFS Application Tier) navigate to the SSRS friendly URL you have configured.  Note you may have add the URL to the “Local intranet” zone.

Now connect to the server hosting the SQL Server Reporting Services website and take a look at the Event Viewer specifically looking at the Security Logs for Event ID 4624 it should look something like this and you will see Kerberos was used:

Log Name:      Security
Source:        Microsoft-Windows-Security-Auditing
Date:          5/9/2012 10:32:05 AM
Event ID:      4624
Task Category: Logon
Level:         Information
Keywords:      Audit Success
User:          N/A
Computer:      TFS.domain.local
Description:
An account was successfully logged on.

Subject:
Security ID:        NULL SID
Account Name:        –
Account Domain:        –
Logon ID:        0x0

Logon Type:            3

New Logon:
Security ID:        domain\user
Account Name:        user
Account Domain:        DOMAIN
Logon ID:        0x587dbeb
Logon GUID:        {47214756-1eed-ac0c-0bb8-4dc8eca9c6de}

Process Information:
Process ID:        0x0
Process Name:        –

Network Information:
Workstation Name:
Source Network Address:    –
Source Port:        –

Detailed Authentication Information:
Logon Process:        Kerberos
Authentication Package:    Kerberos
Transited Services:    –
Package Name (NTLM only):    –
Key Length:        0

This event is generated when a logon session is created. It is generated on the computer that was accessed.

If you have any questions on the post please feel free to leave a comment.

Managed Service Accounts

A Managed Service Account (MSA) is a domain account  created and managed by the Domain Controller.  An MSA has the rights to register an SPN (Service Principal Name) with Active Directory and you are not required to manage the password either!

Requirements

• Requires Active Directory
• Extend Active Directory Schema to Windows Server 2008 R2
• The HOST using the MSA must be running Windows Server 2008 R2
• PowerShell, AD PowerShell (part of RSAT) and .NET 3.5

Steps (on DC)

• Open Powershell Command Prompt (as Administrator)
• import-module activedirectory
• New-ADServiceAccount -name MSAname -Enabled $true
• Add-ADComputerServiceAccount -Identity ServerName -serviceaccount MSAname

NOTE: Make sure you keep it under 15 characters or you will have issues on the HOST trying to install it, specifically: Install-ADServiceAccount : Cannot install service account. Error Message: ‘Unknown error (0xc0000017)’.

Steps (on HOST) using the MSA

• Add Feature “Active Directory module for Windows PowerShell”
• Install-ADServiceAccount -Identity MSAname
• Now you can use the MSA using this notation: domain\MSAname$ (leave the password fields blank)
• Windows 2008 R2 RTM/SP1 require the following hotfix to be applied.

NOTE:  The account installing the MSA needs to be a local administrator and permissions to modify the MSA in Active Directory.  If the installer account is a member of Domain Administrators it will work, if not you will need to delegate modify permissions to the service account’s AD object.