KB33411: How to set up Kerberos (Integrated) authentication against Microsoft SQL Server databases for the MicroStrategy Intelligence Server 10.x and higher on Microsoft Windows operating system platforms.

A new feature available with the release of the Strategy Intelligence Server 9.0.1 is the ability to use Kerberos (Integrated) authentication for access to Microsoft SQL Server databases, for users logged into the Intelligence Server using Kerberos authentication. The following technical note describes the steps that users can take to set up Integrated authentication for access to Microsoft SQL Server. Users should note that this feature is only available for the Strategy Intelligence Server running on Windows operating system patforms as the ODBC drivers on Unix/Linux platforms for SQL Server do not support kerberos authentication :
 

• Set up the SQL Server database server for Kerberos Authentication:
  This action is a database side configuration that is outside of Strategy. Users should work with their database administrators to complete this configuration step. Some of the considerations are:
  
1. The account under which the SQL Server service is running should have a SPN of the form 'MSSQLSvc/FQDN:port' registered for the account if one has not been registered already.
2. The SQL Server Instance must use TCP/IP for communication.
• The ODBC DSN used by Strategy to connect to the Warehouse should be set to Windows Authentication Mode.
• To check whether the ODBC DSN and the SQL Server Kerberos setup are correct, the user should login to the Intelligence Server machine and run the DB Query Tool. The user should select the DSN for the Warehouse and click on "Connect" without entering an explicit user name and password. Once the user is connected, they should run the query: 'SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id=@@spid. This query should return the result 'KERBEROS' if the setup is correct, and NTLM if it is falling back to NTLM authentication. The user running the query would need to have the appropriate privileges to query the 'sys' tables:
  

• The user should locate the ODBCConfig.ini file in the "Program Files\MicroStrategy\Common Files\" folder and edit the tags <INTEGRATED_AUTHENTICATION></INTEGRATED_AUTHENTICATION>  section for <DATABASE>SQL SERVER</DATABASE> from "DEFAULT" to "YES" as shown below. Note that both the ODBC20 and ODBC35 sections must be changed, to ensure that all database connections use Integrated Authentication no matter what ODBC API version is used:
  

• The Intelligence Server should be restarted to make this change available, and the user should login to the Intelligence Server using Kerberos (Integrated) authentication and would be able to access the database using the same credentials.

A useful troubleshooting resource for SQL Server protocol related issues and error messages is available at: http://blogs.msdn.com/sql_protocols/archive/2006/12/02/understanding-kerberos-and-ntlm-authentication-in-sql-server-connections.aspx
  
 
 
Third Party Software Installation WARNING
 
The third-party product(s) discussed in this technical note is manufactured by vendors independent of Strategy. Strategy makes no warranty, express, implied or otherwise, regarding this product, including its performance or reliability.
KB33411