Skip to main content

Configuring SSO for the Microsoft SQL Server connector

If you intend to use single sign-on (SSO) to access a Microsoft SQL Server with the ODBC Connector Package's MS SQL Server Connector, you must configure the host server to enable SSO. Single sign-on permits clients to use one Windows authenticated login to access data in shared files.

Warning: If you are configuring SSO for a generic ODBC connection, use the following configuration instructions: Configuring single sign-on (SSO) for the Microsoft SQL (MS SQL) Server.

To configure SSO for the ODBC Connector Package's MS SQL Server Connector, a Windows domain administrator must do the following:

  • Create service accounts in Microsoft Active Directory.
  • Create service principal names (SPN) in Active Directory.
  • Configure delegation for the Qlik Sense services administrator account.
  • Configure the Qlik Sense server for SSO.
  • Configure the MS SQL Server Connector for SSO
  • Configure the MS SQL Server for SSO.
Warning: The same Qlik Sense services administrator account used during the Qlik Sense (central node) installation must be used. If a different account is used, the Qlik Sense services administrator account must own the HTTP service principal. For more information, see User accounts.

Creating service accounts in Microsoft Active Directory

The service accounts created for SSO are used to authenticate connections to the host systems on which the services run. Two service accounts are required:

  • Qlik Sense server where the Qlik ODBC Connector Package is installed.
  • Server where MS SQL Server is running.

Creating service principal names (SPN) in Active Directory

A service principal name (SPN) is a unique identifier of a service instance. SPNs are used during authentication to associate a service instance with a service logon account. This allows a client application to request that a service authenticate an account even if the client does not have the account name. An SPN always includes the name of the host computer on which the service instance is running, so a service instance might register an SPN for each name or alias of its host.

Before the authentication service can use an SPN to authenticate a service, the SPN must be registered on the account object that the service instance uses to log on. A given SPN can be registered on only one account. For Win32 services, a service installer specifies the logon account when an instance of the service is installed. The installer then composes the SPNs and writes them as a property of the account object in Active Directory Domain Services. If the account of a service instance changes, the SPNs must be re-registered under the new account.

When a client connects to a service, it locates an instance of the service, composes an SPN for that instance, connects to the service, and presents the SPN for the service to authenticate.

To set up SSO for MS SQL server, you must create SPNs for the Qlik Sense services administrator account.

Do the following:

  1. Open an elevated command prompt with domain administrator credentials.
  2. Enter the following to create an SPN for the Qlik Sense services administrator:

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

    Note: The <Qlik_Sense_server> must be entered as the fully qualified domain name of the server.
    Note: The <Qlik_Sense_server> is the central node where the Qlik Sense is running.
  3. Enter the following to create an SPN for the MS SQL Server services administrator:

    setspn -A MSSQLSvc/<server_name>:<port> <domain>\<services_administrator>

    Note: The <server_name> must be entered as the fully qualified domain name of the server.
  4. Enter the following commands to verify the result of your SPN setup:
    1. setspn -L <domain>\<Qlik_Sense_services_administrator> to verify the Qlik Sense services administrator.
    2. setspn -L <domain>\<MS_Sql _server_services_administrator> to verify the MS SQL Server services administrator.

Configuring delegation for the Qlik Sense services administrator account

Delegation allows a front-end service to forward client requests to a back-end service so that the back-end service can also impersonate the client. Impersonation is used to check whether a client is authorized to perform a particular action, while delegation is a way of flowing impersonation capabilities, along with the client’s identity, to a back-end service.

To configure SSO for MS SQL Server, you must set up delegation rights to the MS SQL Server service for the Qlik Sense services administrator.

A Windows domain administrator can change the delegation tab on the Qlik Sense services administrator account properties page.

Do the following:

  1. Open the Active Directory Users and Computers management tool with Windows domain administrator credentials.
  2. Right click on your Qlik Sense services administrator account and click Properties.
  3. Go to the Delegation tab, and select Trust this user for delegation to specified services only, then select Use any authentication protocol.
  4. Click Add....
  5. On the Add Services window, click Users or Computers....
  6. On the Select Users or Computers window, enter the domain and user name of the Microsoft SQL Server services administrator and click OK.
  7. On the Add Services window, select the MS SQL Server service and click OK.

You can verify your delegation configuration on the Delegation tab. The MS SQL Server service should now be set as the service to which the Qlik Sense services administrator can present delegation credentials.

Configuring the Qlik Sense server for SSO

To configure the Qlik Sense server for SSO with MS SQL Server, you must:

  • Add the Qlik Sense services administrator to the Administrator group on the Qlik Sense server if it is not already part of that group.
  • Add Qlik Sense services administrator as part of the Act as part of the operating system role in the Local Security Policy.

Do the following:

  1. Log on to the Qlik Sense server as an administrator.
  2. Open Local Security Policy, and go to Security Settings > Local Policies > User Rights Assignment.
  3. Under Policy, right click on Act as part of the operating system and select Properties.
  4. On the Local Security Setting tab, click Add User or Group....
  5. Add the Qlik Sense services administrator account, and click OK.

Configuring the MS SQL Server Connector for SSO

To configure the ODBC Connector Package's MS SQL Server Connector for SSO, you must be logged in as an administrator to the Qlik Sense server where the ODBC Connector Package is installed. If you logged in for the previous configuration steps on the Qlik Sense server, you can simply continue with the following steps:

Do the following:

  1. Open Local Security Policy, and go to Security Settings > Local Policies > User Rights Assignment.
  2. Under Policy, right click on Impersonate a client after authentication and select Properties.
  3. On the Local Security Setting tab, click Add User or Group....
  4. Add the Qlik Sense services administrator account, and click OK.
  5. Restart the Qlik Sense server.

    The server must be restarted for the policy changes to take effect.

Configuring MS SQL Server for SSO

To configure the MS SQL Server for SSO, you must ensure that the MS SQL Server service runs as the MS SQL Server services administrator.

Do the following:

  1. Log on to the MS SQL Server as an administrator.
  2. Open the Sql Server Configuration Manager.
  3. Select SQL Server Services.
  4. Select SQL Server in the right pane and verify that the Log On As column is populated with your MS SQL Server services administrator account.
Note: You must reboot after making changes to remove the SQL self-registration of the SPN under machine account and register the SPN manually on the domain account.