Qlik Sense Enterprise: How to configure SSO for Microsoft SQL Server Connector

[Ayaka_Hanazono]

MS SQL server connector of the Qlik ODBC connector package offers the SSO authentication option using Kerberos. This article covers the overview of the configuration required in Active Directory, Qlik Sense Server, and MS SQL Server. 

Environment

• Qlik Sense Enterprise (Not supported in QlikView/Qlik Sense Desktop/Qlik Cloud)

Follow the instruction: Configuring SSO for the Microsoft SQL Server connector
Visit the page for a detailed explanation.

How to enable Kerberos?

The Kerberos authentication is a default authentication protocol in Windows. To achieve the Kerberos authentication between Qlik Sense Server and MS SQL Server, you need to create Sevice Principal Name (SPN).  The generated SPN  will be authenticated instead of a user/service account.

Creating service accounts in Microsoft Active Directory

• Create 2 new accounts, one for Qlik Sense Server for SSO and another for MS SQL Server (if it's not run by a domain account.)
• This new account for Qlik Sense Server for SSO should be different from the service account for Qlik Sense Services as it needs a license to complete the setup on Hub> Create a Data Connection.

Creating service principal names (SPN) in Active Directory

• Create SPNs for Qlik Sense and MS SQL Server in Active Directory.
• The manual method is described in the help, but with the right permissions, they can be generated automatically.  Automatic SPN Registration

setspn -A HTTP/<Qlik_Sense_server_FQDN>:<port> <domain>\<Qlik_Sense_services_administrator>

setspn -A MSSQLSvc/<MS_SQL_server_name>:<port> <domain>\<MS_SQL_server_services_administrator>

Verify the SPN creation by the following commands:

setspn -L <domain>\<Qlik_Sense_services_administrator> 

setspn -L <domain>\<MS_SQL_server_services_administrator>

Configuring delegation for the Qlik Sense services administrator account

• Once the SPN registration is completed, the delegation tab is created in the AD's User Properties.
• Add the generated SPN by making a search with a domain account name and it's automatically changed to the SPN after the selection.
  
  

Configuring the Qlik Sense server for SSO

• Make sure the Qlik Sense account for SSO is a member of the Administrator group in the Qlik Sense Server.
• Add Act as part of the operating system role in the Local Security Policy.
  
  

Configuring the MS SQL Server Connector for SSO

• Continue from the previous step and add the Impersonate a client after authentication role to the Qlik Sense Server administrator.

Configuring MS SQL Server for SSO

• If MS SQL Server is logged as a local system, change the account to the newly created MS SQL Server administrator previously in the Sql Server Configuration Manager.

 

Create a Microsoft SQL Server connection

• Open the Hub by the Qlik Sense Server account for SSO and open a Create new connection.
• Insert the parameters and enable Single sign-on (Kerberos) to put your Server SPN.
• The SPN format may differ depending on the environment.  SPN Formats
  
  

Test connection should succeed.

Troubleshooting

SPN registration error on MS SQL Server after the service is started.

• The log is generated under C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log
  
  The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/QlikServer2.domain.local ] for the SQL Server service. Windows return code: 0xffffffff, state: 43. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.
• Restart the MS SQL Server service.
• Make sure the permissions are given for the automatic SPN registration.

Verity the Kerberos authentication without using Qlik Sense

• Connecting to the SQL server from another machine (using SQL Server Management Studio)
  
• Run the query and make sure the authentication is Kerberos.
  

  SELECT @@SERVERNAME AS instance,net_transport,auth_scheme
  FROM sys.dm_exec_connections
  WHERE session_id = @@spid

• The result would be like:
  

Test Connection error: String reference not set to an instance of a String. Parameter name: s

• The wrong user to set up a connection. (Non only logging in to the server machine, but the Hub.)
• Make sure the user is the once created for SSO and SPN is generated.

The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.