locked
Setting SPN's for Analysis Services RRS feed

  • Question

  • HI,

    I want to setup Kerberos on our SQL Server Analysis Services 2005.

    We have a named instance BLAH01 listening on port nnnn.

    To connect to this instance currently we must use:

    FullyQualifiedDomainName:nnnn

    We can't use FullyQualifiedDomainName\BLAH01 because firewall to port sq browser service listens on is not open so it can't do redirect.

    I have been reading this article: http://support.microsoft.com/kb/917409

    and it says:

    "Unlike with the SQL Server engine, you cannot specify a port after the colon. You must use the actual instance name for all functionality to work correctly."

    Can anyone advise the correct steps to take?

    Do I:

    1. Create browser service spns, Olap service spns and open firewall rule to browser service.

    MSOLAPSvc.3/serverHostName.Fully_Qualified_domainName:instanceName
    MSOLAPSvc.3/serverHostName:instanceName
    MSOLAPDisco.3/serverHostName.Fully_Qualified_domainName:instanceName MSOLAPDisco.3/serverHostName:instanceName

    2. Create Olap service spns without :instancename

    MSOLAPSvc.3/serverHostName.Fully_Qualified_domainName
    MSOLAPSvc.3/serverHostName

    3. Create Olap Service spns with :instancename

    MSOLAPSvc.3/serverHostName.Fully_Qualified_domainName:instanceName
    MSOLAPSvc.3/serverHostName:instanceName

    4. Something else

    Oh and Configure the active directory setting for the accounts involved, it's all so easy!

    Thanks.

    Friday, September 30, 2011 2:47 AM

Answers

  • Option 3 looks good but you need to have SPN for Browser Services in case of named instance.

    SPNs for named instance - SSAS
    MSOLAPSvc.3/serverHostName.Fully_Qualified_domainName:instanceName
    MSOLAPSvc.3/serverHostName:instanceNameMSOLAPDisco.3/serverHostName.Fully_Qualified_domainName:instanceName

    For Browser Services - Excerpt from KB which I published long time back.
    http://support.microsoft.com/kb/950599

    This behavior occurs only when the connection string contains the SSPI=Kerberos parameter. In this case, the connection is forced to use Kerberos authentication, and the SPN for the SQL Server Browser service must be configured.

    If the connection string does not contain the SSPI=Kerberos parameter, Kerberos authentication is typically used. The connection to the SQL Server Browser service uses NTLM and the NT_ANONYMOUS account instead. In this case, the connection to the SQL Server Browser service is successful. The SQL Server Browser service determines the correct port. Then, the actual database connection uses Kerberos authentication to provide the true authentication.

    Setspn.exe -a MSOLAPDisco.3/serverHostName.Fully_Qualified_domainName Browser_Service_Startup_Account

    Setspn.exe -a MSOLAPDisco.3/serverHostName Browser_Service_Startup_Account

    Startup Account if Services are running on AD Account, if services are running on local system then you need to specify ServeHost Name.

    Another Relevant post releated to Port:

    http://blogs.msdn.com/b/karang/archive/2009/09/05/sql-server-analysis-services-port-sql-2005-2008.aspx

    Even if you use HTTP you need to specify the instance name in the msmdpump.ini
    <ServerName>localhost\named_instance</ServerName>
    So Browser Service will come in picture.

    Configure AS HTTP on Windows 2003
    http://technet.microsoft.com/en-us/library/cc917711.aspx

    Configure As HTTP on WIndows 2008
    http://msdn.microsoft.com/en-us/library/gg492140.aspx

    Regards
    Karan Gulati
    DS - Escalation Services
    blogs.msdn.com/karang

     

    Tuesday, October 4, 2011 9:39 AM
  • Option 3 is the correct one. We have got many installations (but with firewall opened for SQL browser) all of which work with this option.
    Friday, September 30, 2011 2:05 PM
  • Hi mssql$joey,

    Typically, you have to start SQL browser service and open its firewall rule, then follow option 3 you provided. Besides this, try to configure http access for your analysis services by following article, then setup the spn for the http address, i am not sure it works. If it is ok, that will avoid to use SQL Broswer services.

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

    Regards,

    Jerry

    TechNet Subscriber Supportin forum
    If you have any feedback on our support, please contact  tnmff@microsoft.com

    Tuesday, October 4, 2011 5:58 AM
  • You need SPN for browser service only in a case if connection string contains the SSPI=Kerberos Parameter. In that case the connection is forced to use Kerberos Authentication and SPN for SQL Server Browser must be configured. Below mentioned KB which we published talks about it in details: http://support.microsoft.com/kb/950599 Feel free to contact me if you need further clarifications. Karan Gulati DS-Escalation Services. http://blogs.msdn.com/karang
    Thursday, October 27, 2011 7:37 AM

All replies

  • Option 3 is the correct one. We have got many installations (but with firewall opened for SQL browser) all of which work with this option.
    Friday, September 30, 2011 2:05 PM
  • Hi mssql$joey,

    Typically, you have to start SQL browser service and open its firewall rule, then follow option 3 you provided. Besides this, try to configure http access for your analysis services by following article, then setup the spn for the http address, i am not sure it works. If it is ok, that will avoid to use SQL Broswer services.

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

    Regards,

    Jerry

    TechNet Subscriber Supportin forum
    If you have any feedback on our support, please contact  tnmff@microsoft.com

    Tuesday, October 4, 2011 5:58 AM
  • Option 3 looks good but you need to have SPN for Browser Services in case of named instance.

    SPNs for named instance - SSAS
    MSOLAPSvc.3/serverHostName.Fully_Qualified_domainName:instanceName
    MSOLAPSvc.3/serverHostName:instanceNameMSOLAPDisco.3/serverHostName.Fully_Qualified_domainName:instanceName

    For Browser Services - Excerpt from KB which I published long time back.
    http://support.microsoft.com/kb/950599

    This behavior occurs only when the connection string contains the SSPI=Kerberos parameter. In this case, the connection is forced to use Kerberos authentication, and the SPN for the SQL Server Browser service must be configured.

    If the connection string does not contain the SSPI=Kerberos parameter, Kerberos authentication is typically used. The connection to the SQL Server Browser service uses NTLM and the NT_ANONYMOUS account instead. In this case, the connection to the SQL Server Browser service is successful. The SQL Server Browser service determines the correct port. Then, the actual database connection uses Kerberos authentication to provide the true authentication.

    Setspn.exe -a MSOLAPDisco.3/serverHostName.Fully_Qualified_domainName Browser_Service_Startup_Account

    Setspn.exe -a MSOLAPDisco.3/serverHostName Browser_Service_Startup_Account

    Startup Account if Services are running on AD Account, if services are running on local system then you need to specify ServeHost Name.

    Another Relevant post releated to Port:

    http://blogs.msdn.com/b/karang/archive/2009/09/05/sql-server-analysis-services-port-sql-2005-2008.aspx

    Even if you use HTTP you need to specify the instance name in the msmdpump.ini
    <ServerName>localhost\named_instance</ServerName>
    So Browser Service will come in picture.

    Configure AS HTTP on Windows 2003
    http://technet.microsoft.com/en-us/library/cc917711.aspx

    Configure As HTTP on WIndows 2008
    http://msdn.microsoft.com/en-us/library/gg492140.aspx

    Regards
    Karan Gulati
    DS - Escalation Services
    blogs.msdn.com/karang

     

    Tuesday, October 4, 2011 9:39 AM
  • Hi Karan,

    we have a named instance BUT we never connect via browser service, servername\instancename.

    We always connect via servername:port.

    Would we still need spn for browser service in this case?

    Cheers.

    And thanks everyone for posting.

    Thursday, October 27, 2011 12:34 AM
  • You need SPN for browser service only in a case if connection string contains the SSPI=Kerberos Parameter. In that case the connection is forced to use Kerberos Authentication and SPN for SQL Server Browser must be configured. Below mentioned KB which we published talks about it in details: http://support.microsoft.com/kb/950599 Feel free to contact me if you need further clarifications. Karan Gulati DS-Escalation Services. http://blogs.msdn.com/karang
    Thursday, October 27, 2011 7:37 AM