Use Kerberos for single sign-on (SSO) from Power BI to on-premises data sources

Is this page helpful?

Use Kerberos constrained delegation to enable seamless single sign-on (SSO) connectivity. Enabling SSO makes it easy for Power BI reports and dashboards to refresh data from on-premises sources.

Supported data sources

We currently support the following data sources:

  • SQL Server
  • SAP HANA
  • SAP BW
  • Teradata
  • Spark
  • Impala

We also support SAP HANA with Security Assertion Markup Language (SAML).

SAP HANA

To enable SSO for SAP HANA, follow these steps first:

For more information about setting up SSO for SAP HANA by using Kerberos, see Single Sign-on Using Kerberos in the SAP HANA Security Guide. Also see the links from that page, particularly SAP Note 1837331 – HOWTO HANA DBSSO Kerberos/Active Directory.

Prepare for Kerberos constrained delegation

You must configure several items for Kerberos constrained delegation to work properly, including Service Principal Names (SPN) and delegation settings on service accounts.

Prerequisite 1: Install and configure the Microsoft on-premises data gateway

This release of the on-premises data gateway supports an in-place upgrade, as well as settings take-over of existing gateways.

Prerequisite 2: Run the gateway Windows service as a domain account

In a standard installation, the gateway runs as a machine-local service account (specifically, NT Service\PBIEgwService).

Screenshot of service account

To enable Kerberos constrained delegation, the gateway must run as a domain account, unless your Azure Active Directory (Azure AD) instance is already synchronized with your local Active Directory instance (using Azure AD DirSync/Connect). To switch to a domain account, see Switch the gateway to a domain account later in this article.

Note

If Azure AD Connect is configured, and user accounts are synchronized, the gateway service doesn't need to perform local Azure AD lookups at runtime. You can use the local service SID (instead of requiring a domain account) for the gateway service. The Kerberos constrained delegation configuration steps outlined in this article are the same as that configuration. They are simply applied to the gateway's computer object in Azure AD, instead of the domain account.

Prerequisite 3: Have domain admin rights to configure SPNs (SetSPN) and Kerberos constrained delegation settings

It's not recommended for a domain administrator to temporarily or permanently allow rights to someone else to configure SPNs and Kerberos delegation without requiring domain admin rights. In the following section, we cover the recommended configuration steps in more detail.

Configure Kerberos constrained delegation for the gateway and data source

As a domain administrator, configure an SPN for the gateway service domain account, and configure delegation settings on the gateway service domain account.

Configure an SPN for the gateway service account

First, determine whether an SPN was already created for the domain account used as the gateway service account:

  1. As a domain administrator, open Active Directory Users and Computers.

  2. Right-click on the domain, select Find, and enter the account name of the gateway service account.

  3. In the search result, right-click the gateway service account, and select Properties.

  4. If the Delegation tab is visible on the Properties dialog box, then an SPN was already created. You can jump ahead to configuring delegation settings.

    If there is no Delegation tab on the Properties dialog box, you can manually create an SPN on that account. This adds the Delegation tab. Use the setspn tool that comes with Windows (you need domain admin rights to create the SPN).

    For example, imagine the gateway service account is “PBIEgwTest\GatewaySvc”, and the machine name with the gateway service running is called Machine1. To set the SPN for the gateway service account for that machine in this example, run the following command:

    Image of set SPN command

    With that step completed, we can move on to configuring delegation settings.

Configure delegation settings on the gateway service account

The second configuration requirement is the delegation settings on the gateway service account. There are multiple tools you can use to perform these steps. Here, we'll use Active Directory Users and Computers, which is a Microsoft Management Console (MMC) snap-in to administer and publish information in the directory. It's available on domain controllers by default. You can also enable it through Windows Feature configuration on other machines.

We need to configure Kerberos constrained delegation with protocol transiting. With constrained delegation, you must be explicit about which services you want to delegate to. For example, only SQL Server or your SAP HANA server accepts delegation calls from the gateway service account.

This section assumes you have already configured SPNs for your underlying data sources (such as SQL Server, SAP HANA, Teradata, and Spark). To learn how to configure those data source server SPNs, refer to technical documentation for the respective database server. You can also see the What SPN does your app require? blog post.

In the following steps, we assume an on-premises environment with two machines: a gateway machine and a database server running SQL Server. For the sake of this example, we'll also assume the following settings and names:

  • Gateway machine name: PBIEgwTestGW
  • Gateway service account: PBIEgwTest\GatewaySvc (account display name: Gateway Connector)
  • SQL Server data source machine name: PBIEgwTestSQL
  • SQL Server data source service account: PBIEgwTest\SQLService

Here's how to configure the delegation settings:

  1. With domain administrator rights, open Active Directory Users and Computers.

  2. Right-click the gateway service account (PBIEgwTest\GatewaySvc), and select Properties.

  3. Select the Delegation tab.

  4. Select Trust this computer for delegation to specified services only > Use any authentication protocol.

  5. Under Services to which this account can present delegated credentials, select Add.

  6. In the new dialog box, select Users or Computers.

  7. Enter the service account for the SQL Server data source (PBIEgwTest\SQLService), and select OK.

  8. Select the SPN that you created for the database server. In our example, the SPN begins with MSSQLSvc. If you added both the FQDN and the NetBIOS SPN for your database service, select both. You might only see one.

  9. Select OK. You should see the SPN in the list now.

    Optionally, you can select Expanded to show both the FQDN and NetBIOS SPN. The dialog box looks similar to the following if you selected Expanded. Select OK.

    Screenshot of Gateway Connector Properties dialog box

Finally, on the machine running the gateway service (PBIEgwTestGW in our example), you must grant the gateway service account the local policy Impersonate a client after authentication. You can perform and verify this with the Local Group Policy Editor (gpedit).

  1. On the gateway machine, run: gpedit.msc.

  2. Go to Local Computer Policy > Computer Configuration > Windows Settings > Security Settings > Local Policies > User Rights Assignment.

    Screenshot of Local Computer Policy folder structure

  3. Under User Rights Assignment, from the list of policies, select Impersonate a client after authentication.

    Screenshot of Impersonate a client policy

    Right-click, and open Properties. Check the list of accounts. It must include the gateway service account (PBIEgwTest\GatewaySvc).

  4. Under User Rights Assignment, from the list of policies, select Act as part of the operating system (SeTcbPrivilege). Ensure that the gateway service account is included in the list of accounts as well.

  5. Restart the On-premises data gateway service process.

If you're using SAP HANA, we recommend following these additional steps, which can yield a small performance improvement.

  1. In the gateway installation directory, find and open this configuration file: Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config.

  2. Find the FullDomainResolutionEnabled property, and change its value to True.

    <setting name=" FullDomainResolutionEnabled " serializeAs="String">
          <value>True</value>
    </setting>
    

Run a Power BI report

After completing all the configuration steps, you can use the Manage Gateway page in Power BI to configure the data source. Then, under Advanced Settings, enable SSO, and publish reports and datasets binding to that data source.

Screenshot of Advanced settings option

This configuration works in most cases. However, with Kerberos there can be different configurations depending on your environment. If the report still won't load, contact your domain administrator to investigate further.

Switch the gateway to a domain account

If necessary, you can switch the gateway from a local service account to run as a domain account, by using the On-premises data gateway user interface. Here's how:

  1. Open the On-premises data gateway configuration tool.

    Screenshot of option to launch gateway desktop app

  2. Select the Sign-in button on the main page, and sign in with your Power BI account.

  3. After sign-in is completed, select the Service Settings tab.

  4. Select Change account to start the guided walk-through.

    Screenshot of On-premises data gateway desktop app, with Change account option highlighted

Configure SAP BW for SSO

Now that you understand how Kerberos works with a gateway, you can configure SSO for your SAP Business Warehouse (SAP BW). The following steps assume you've already prepared for Kerberos constrained delegation, as described earlier in this article.

This guide attempts to be as comprehensive as possible. If you've already completed some of these steps, you can skip them. For example, you might have already created a service user for your SAP BW server and mapped an SPN to it, or you might have already installed the gsskrb5 library.

Set up gsskrb5 on client machines and the SAP BW server

Note

gsskrb5 is no longer actively supported by SAP. For more information, see SAP Note 352295. Also note that gsskrb5 doesn't allow for SSO connections from the data gateway to SAP BW Message Servers. Only connections to SAP BW Application Servers are possible.

gsskrb5 must be in use by both the client and server to complete an SSO connection through the gateway. The Common Crypto Library (sapcrypto) isn't currently supported.

  1. Download gsskrb5 - gx64krb5 from SAP Note 2115486 (SAP s-user required). Ensure you have at least version 1.0.11.x of gsskrb5.dll and gx64krb5.dll.

  2. Put the library in a location on your gateway machine that is accessible by your gateway instance (and also by the SAP GUI if you want to test the SSO connection by using SAP Logon).

  3. Put another copy on your SAP BW server machine in a location accessible by the SAP BW server.

  4. On the client and server machines, set the SNC\_LIB and SNC\_LIB\_64 environment variables to point to the locations of gsskrb5.dll and gx64krb5.dll, respectively.

Create a SAP BW service user and enable SNC communication

In addition to the gateway configuration you've already done, there are a few additional steps specific to SAP BW. The Configure delegation settings on the gateway service account section of the documentation assumes you've already configured SPNs for your underlying data sources. To complete this configuration for SAP BW:

  1. On an Active Directory Domain Controller server, create a service user (initially just a plain Active Directory user) for your SAP BW Application Server in your Active Directory environment. Then assign an SPN to it.

    SAP recommends starting the SPN with SAP/, but it should also be possible to use other prefixes, such as HTTP/. What comes after the SAP/ is up to you; one option is to use the SAP BW server's service user's username. For example, if you create BWServiceUser@\<DOMAIN\> as your service user, you can use the SPN SAP/BWServiceUser. One way to set the SPN mapping is the setspn command. For example, to set the SPN on the service user we just created, you would run the following command from a cmd window on a Domain Controller machine: setspn -s SAP/ BWServiceUser DOMAIN\ BWServiceUser. For more information, see the SAP BW documentation.

  2. Give the service user access to your SAP BW Application Server:

    1. On the SAP BW server machine, add the service user to the Local Admin group for your SAP BW server. Open the Computer Management program and double-click the Local Admin group for your server.

      Screenshot of Computer Management program

    2. Double-click the Local Admin group, and select Add to add your service user to the group. Select Check Names to ensure you've entered the name correctly. Select OK.

  3. Set the SAP BW server's service user as the user who starts the SAP BW server service on the SAP BW server machine.

    1. Open Run, and enter "Services.msc". Look for the service corresponding to your SAP BW Application Server instance. Right-click it, and select Properties.

      Screenshot of Services, with Properties highlighted

    2. Switch to the Log on tab, and change the user to your SAP BW service user. Enter the user's password, and select OK.

  4. Sign in to your server in SAP Logon, and set the following profile parameters by using the RZ10 transaction:

    1. Set the snc/identity/as profile parameter to p:<the SAP BW service user you've created>, such as p:BWServiceUser@MYDOMAIN.COM. Note the p: that precedes the service user's UPN. It's not p:CN= like when Common Crypto Lib is used as the SNC library.

    2. Set the snc/gssapi_lib profile parameter to <path to gsskrb5.dll/gx64krb5.dll on the server machine (the library you'll use depends on OS bitness)>. Remember to put the library in a location the SAP BW Application Server can access.

    3. Also set the following additional profile parameters, changing the values as required to fit your needs. Note that the last five options enable clients to connect to the SAP BW server by using SAP Logon, without having SNC configured.

      Setting Value
      snc/data_protection/max 3
      snc/data_protection/min 1
      snc/data_protection/use 9
      snc/accept_insecure_cpic 1
      snc/accept_insecure_gui 1
      snc/accept_insecure_r3int_rfc 1
      snc/accept_insecure_rfc 1
      snc/permit_insecure_start 1
    4. Set the property snc/enable to 1.

  5. After setting these profile parameters, open the SAP Management Console on the server machine, and restart the SAP BW instance. If the server won't start, confirm that you've set the profile parameters correctly. For more on profile parameter settings, see the SAP documentation. You can also consult the troubleshooting information later in this section if you encounter problems.

Map a SAP BW user to an Active Directory user

Map an Active Directory user to an SAP BW Application Server user, and test the SSO connection in SAP Logon.

  1. Sign in to your SAP BW server by using SAP Logon. Run transaction SU01.

  2. For User, enter the SAP BW user you want to enable SSO connections for (in the previous screenshot, we're setting permissions for BIUSER). Select the Edit icon (the image of a pen) near the top left of the SAP Logon window.

    Screenshot of SAP BW User maintenance screen

  3. Select the SNC tab. In the SNC name input box, enter p:<your Active Directory user>@<your domain>. Note the mandatory p: that must precede the Active Directory user's UPN. The Active Directory user you specify should belong to the person or organization for whom you want to enable SSO access to the SAP BW Application Server. For example, if you want to enable SSO access for the user testuser@TESTDOMAIN.COM, enter p:testuser@TESTDOMAIN.COM.

    Screenshot of SAP BW Maintain users screen

  4. Select the Save icon (the image of a floppy disk) near the top left of the screen.

Test sign-in by using SSO

Verify that you can sign in to the server. Use the SAP Logon through SSO as the Active Directory user for whom you've just enabled SSO access.

  1. As the Active Directory user you just enabled SSO access for, sign in to a machine on which SAP Logon is installed. Launch SAP Logon, and create a new connection.

  2. In the Create New System Entry screen, select User Specified System > Next.

    Screenshot of Create New System Entry screen

  3. Fill in the appropriate details on the next screen, including the application server, instance number, and system ID. Then select Finish.

  4. Right-click the new connection and select Properties. Select the Network tab. In the SNC Name text box, enter p:<the SAP BW service user's UPN>, such as p:BWServiceUser@MYDOMAIN.COM. Then select OK.

    Screenshot of System Entry Properties screen

  5. Double-click the connection you just created to attempt an SSO connection to your SAP BW server. If this connection succeeds, proceed to the next step. Otherwise, review the earlier steps in this document to make sure they've been completed correctly, or review the troubleshooting section below. Note that if you can't connect to the SAP BW server via SSO in this context, you won't be able to connect to the SAP BW server using SSO in the gateway context.

Troubleshoot installation and connections

If you encounter any problems, follow these steps to troubleshoot the gsskrb5 installation and SSO connections from the SAP Logon.

  • Viewing the server logs (…work\dev_w0 on the server machine) can be helpful in troubleshooting any errors you encounter in completing the gsskrb5 setup steps. This is particularly true if the SAP BW server won't start after the profile parameters have been changed.

  • If you're unable to start the SAP BW service due to a logon failure, you might have provided the wrong password when setting the SAP BW "start-as" user. Verify the password by logging in to a machine in your Active Directory environment as the SAP BW service user.

  • If you get errors about SQL credentials preventing the server from starting, verify that you've granted the service user access to the SAP BW database.

  • You might get the following message: "(GSS-API) specified target is unknown or unreachable." This usually means you have the wrong SNC name specified. Make sure to use "p:" only, not "p:CN=" or anything else in the client application, other than the service user's UPN.

  • You might get the following message: "(GSS-API) An invalid name was supplied." Make sure "p:" is in the value of the server's SNC identity profile parameter.

  • You might get the following message: "(SNC error) the specified module could not be found." This is usually caused by putting the gsskrb5.dll/gx64krb5.dll somewhere that requires elevated privileges (administrator rights) to access.

Add registry entries to the gateway machine

Add required registry entries to the registry of the machine that the gateway is installed on. Here are the commands to run:

  1. REG ADD HKLM\SOFTWARE\Wow6432Node\SAP\gsskrb5 /v ForceIniCredOK /t REG_DWORD /d 1 /f

  2. REG ADD HKLM\SOFTWARE\SAP\gsskrb5 /v ForceIniCredOK /t REG_DWORD /d 1 /f

Set configuration parameters on the gateway machine

There are two options for setting configuration parameters, depending on whether you have Azure AD Connect configured so that users can sign in to the Power BI service as an Azure AD user.

If you have Azure AD Connect configured, follow these steps.

  1. Open the main gateway configuration file, Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll. By default, this file is stored at C:\Program Files\On-premises data gateway.

  2. Ensure the FullDomainResolutionEnabled property is set to True, and SapHanaSsoRemoveDomainEnabled is set to False.

  3. Save the configuration file.

  4. From the Services tab of Task Manager, right-click the gateway service and select Restart.

    Screenshot of Task Manager Services tab

If you don't have Azure AD Connect configured, follow these steps for every Power BI service user you want to map to an Azure AD user. These steps manually link a Power BI service user to an Active Directory user with permission to sign in to SAP BW.

  1. Open the main gateway configuration file, Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll. By default, this file is stored at C:\Program Files\On-premises data gateway.

  2. Set the ADUserNameLookupProperty to msDS-cloudExtensionAttribute1, and the ADUserNameReplacementProperty to SAMAccountName. Save the configuration file.

  3. From the Services tab of Task Manager, right-click the gateway service and select Restart.

    Screenshot of Task Manager Services tab

  4. Set the msDS-cloudExtensionAttribute1 property of the Active Directory user. This is the user you mapped to a SAP BW user. Set the property to the Power BI service user for whom you want to enable Kerberos SSO. One way to set the msDS-cloudExtensionAttribute1 property is by using the Active Directory Users and Computers MMC snap-in. (You can also use other methods.)

    1. Sign in to a Domain Controller machine as an administrator user.

    2. Open the Users folder in the snap-in window, and double-click the Active Directory user you mapped to a SAP BW user.

    3. Select the Attribute Editor tab.

      If you don't see this tab, you'll need to search for directions on how to enable it, or use another method to set the property. Select one of the attributes and then the M key to go to the Active Directory properties that start with the letter m. Locate the msDS-cloudExtensionAttribute1 property, and double-click it. Set the value to the username you use to sign in to the Power BI Service, in the form YourUser@YourDomain.

    4. Select OK.

      Screenshot of String Attribute Editor dialog box

    5. Select Apply. Verify that the correct value has been set in the Value column.

Add a new SAP BW Application Server data source to the Power BI service

Add the SAP BW data source to your gateway by following the instructions earlier in this article on running a report.

  1. In the data source configuration window, enter the Application Server's Hostname, System Number, and client ID, as you would to sign in to your SAP BW server from Power BI Desktop. For the Authentication Method, select Windows.

  2. In the SNC Partner Name field, enter p: <the SPN you mapped to your SAP BW service user>. For example, if the SPN is SAP/BWServiceUser@MYDOMAIN.COM, you should enter p:SAP/BWServiceUser@MYDOMAIN.COM in the SNC Partner Name field.

  3. For the SNC Library, select SNC_LIB or SNC_LIB_64.

  4. The Username and Password should be the username and password of an Active Directory user with permission to sign in to the SAP BW server with SSO. In other words, these should belong to an Active Directory user who has been mapped to a SAP BW user through the SU01 transaction. These credentials are only used if the Use SSO via Kerberos for DirectQuery queries box is not checked.

  5. Select the Use SSO via Kerberos for DirectQuery queries box, and select Apply. If the test connection is not successful, verify that the previous setup and configuration steps were completed correctly.

    The gateway always uses the typed-in credentials to establish a test connection to the server, and to do scheduled refreshes of import-based reports. The gateway only attempts to establish an SSO connection if the Use SSO via Kerberos for DirectQuery queries is selected, and the user is accessing a direct query-based report or dataset.

Test your setup

To test your setup, publish a DirectQuery report from Power BI Desktop to the Power BI service. Make sure you’re signed in to the Power BI service as either an Azure AD user, or a user you’ve mapped to the msDS-cloudExtensionAttribute1 property of an Azure AD user. If the setup has been completed successfully, you should be able to create a report from the published dataset in the Power BI service. You should also be able to pull data through visuals in the report.

Troubleshoot gateway connectivity issues

  1. Check the gateway logs. Open the gateway configuration application, and select Diagnostics > Export logs. The most recent errors are at the bottom of any log files you examine.

    Screenshot of On-premises data gateway application, with Diagnostics highlighted

  2. Turn on SAP BW tracing, and review the generated log files. There are several different types of SAP BW tracing available. Consult the SAP documentation for more information.

Errors from an insufficient Kerberos configuration

If the underlying database server and gateway are not configured properly for Kerberos constrained delegation, you might receive the following error message about failing to load data:

Screenshot of error message

The technical details associated with the error message (DM_GWPipeline_Gateway_ServerUnreachable) might look like the following:

Screenshot of error message technical details

The result is that the gateway can't impersonate the originating user properly, and the database connection attempt failed.

Next steps

For more information about the On-premises data gateway and DirectQuery, check out the following resources: