SQL 2005 SPN (SSAS/SSRS/SQL)


What is SPN?


An SPN is a unique identifier for a service on a network that uses Kerberos authentication. It consists of a service class, a host name, and a port. On a network that uses Kerberos authentication, an SPN for the server must be registered under either a built-in computer account (such as NetworkService or LocalSystem) or user account. SPNs are registered for built-in accounts automatically. However, when you run a service under a domain user account, you must manually register the SPN for the account you want to use.


To create an SPN, you can use the SetSPN command line utility.


Note: You must be a member of the Domain Administrators group to run the Setspn command.


SQL 2005 Database Services  SPN


You must be a domain administrator to run the utility on the domain controller


1)If you are using SQL Server failover clustering, run the following SETSPN command:


setspn -A MSSQLSvc/<FQDN> <SQL_Service_Account>


For example, if MySQLServer.MyDomain.com is running under the domain user account SQLSVC, where MySQLServer.MyDomain.com is the name of the instance of SQL Server 2005 that is clustered, run the following command:


setspn -A MSSQLSvc/MySQLServer.MyDomain.com SQLSVC


2) For both clustered and non-clustered computers that are running SQL Server, run the following SETSPN command to register an SPN for the port that the computer that is running SQL Server is using:


setspn -A MSSQLSvc/<FQDN>:<Port> <SQL_Service_Account>


For example, if MySQLServer.MyDomain.com is running under the domain user account SQLSVC on port 1433, run the following SETSPN command:


setspn -A MSSQLSvc/MySQLServer.MyDomain.com:1433 SQLSVC


SQL 2005 Analysis Services SPN


If the instance of Analysis Services is clustered, use the Analysis Services virtual name as the fully qualified domain name (FQDN).


1) To create the SPN for the Analysis Services server that is running under a domain account, run the following commands at a command prompt:


Setspn.exe -A MSOLAPSvc.3/Fully_Qualified_domainName OLAP_Service_Startup_Account


Note Fully_Qualified_domainName is a placeholder for the FQDN.


Setspn.exe -A MSOLAPSvc.3/serverHostName OLAP_Service_Startup_Account


2) To create the SPN for the Analysis Services server that is running under the LocalSystem account, run the following commands at a command prompt:


Setspn.exe -A MSOLAPSvc.3/Fully_Qualified_domainName serverHostName


Setspn.exe -A MSOLAPSvc.3/serverHostName serverHostName


3) For named instance in SQL Server 2005 Analysis Services


MSOLAPSvc.3/serverHostName.Fully_Qualified_domainName:instanceName


MSOLAPSvc.3/serverHostName:instanceName


SQL 2005 Reporting Services SPN


If you are deploying Reporting Services in a network that uses the Kerberos protocol for mutual authentication, you must create a Service Principal Name (SPN) for the Report Server service if you configure it to run as a domain user account.


Install Reporting Services and configure the Report Server service to run as a domain user account. Note that users will not be able to connect to the report server until you complete the following steps.


Log on to the domain controller as domain administrator.


Open a Command Prompt window.


Copy the following command, replacing placeholder values with actual values that are valid for your network:


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


The values that you specify for <computername>, <domainname>, and <port> identify the unique network address of the computer. If you only have one domain and are using port 80, you can omit <domainname> and <port> from your command line. <domain-user-account> is the user account under which the SPN is registered (This is the same user under which Reporting Services Application Pool is running)


Additional Information:


HTTP is the service class. The Report Server Web service runs in HTTP.SYS. A by-product of creating an SPN for HTTP is that all Web applications on the same computer that run in HTTP.SYS (including applications hosted in IIS) will be granted tickets based on the domain user account. If those services run under a different account, the authentication requests will fail. To avoid this problem, be sure to configure all HTTP applications to run under the same account, or consider creating host headers for each application and then creating separate SPNs for each host header.


 

Comments (2)

  1. BI_Knowhow says:

    Configuring Datapump in IIS 7 SSAS (64 Bit) and Oracle Client Issues Issue while Saving BLG File related

  2. JasonH says:

    There is a bit of an internet myth that MSOLAPSvc.4 is the right SPN for OLAP 2008, but this is incorrect. MSOLAPSvc.3 is applicable for SQL 2005, SQL 2008, and SQL 2008 R2 Analysis Services. No need to upgrade the v.3 SPN currently, since it is the same for all 3 versions of OLAP mentioned.

Skip to main content