Skip to main content Skip to complementary content

Configuring single sign-on (SSO) for Microsoft SQL (MS SQL) Server

If your database files access data from MS SQL Server, you can configure the host server to enable SSO. ODBC data source single sign-on permits clients to use one Windows authenticated login to access data in shared files.

To configure SSO for MS SQL Server, a Windows domain administrator must do the following:

  • 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 for SSO
Warning noteThe 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 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. A SPN always includes the name of the host computer on which the service instance is running, so a service instance might register a SPN for each name or alias of its host.

Before the authentication service can use a 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. Log on as a domain administrator.
  2. Open an elevated command prompt.
  3. Enter the following to create a SPN for the Qlik Sense services administrator:

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

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

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

    Information noteThe <server_name> must be entered as the fully qualified domain name of the server.
  5. 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. Log on as a Windows domain administrator.
  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's 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 MS SQL Server

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.
Information noteYou 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.

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!