Edit

Configure Power BI Report Server catalog databases for SQL Server on Linux

  • Article

Applies to: SQL Server 2019 (15.x) and later - Linux ✅ SQL Server Reporting Services (2019 and later)

This article explains how to install and configure the Power BI Report Server (PBIRS) catalog database for SQL Server on Linux.

Prerequisites

In this article, the examples use the domain CORPNET.CONTOSO.COM, and the following configuration.

Configure machines

Machine Operating system Details
Windows domain controller Windows Server 2019 or Windows Server 2022
Report development and deployment (WIN19) Windows Server 2019, running Visual Studio 2019 - Report development and deployment

- File share services to serve as a repository for demand driven or scheduled report output
SQL Server Reporting Services (WIN22) Windows Server 2022, running a supported version of Power BI Report Server (PBIRS) 1
Developer machine Windows 11 client, running SQL Server Management Studio (SSMS)
SQL Server 2019 (rhel8test) Red Hat Enterprise Linux (RHEL) 8.x Server, running SQL Server 2019 (15.x) with the latest CU

Configure accounts

Account name Details
CORPNET\cluadmin Global user account. Local Administrator account on all Windows servers except for the domain controller.
CORPNET\pbirsservice PBIRS service account
CORPNET\linuxservice SQL Server service account (created just for the SQL Server on Linux environment)
CORPNET\reportuser Global user account used to simulate a normal user of PBIRS

Separate servers and separate accounts are used in this example scenario to ensure that Kerberos delegation is functioning properly (that is, double-hop scenarios are being handled).

SQL Server on Linux configuration

Before proceeding with the configuration (or reconfiguration) of PBIRS to use SQL Server on Linux as the backend to host the Report Server catalog databases, ensure that the SQL Server on Linux instance has been joined to the domain.

You can install and configure adutil, and join the domain, following the instructions in Tutorial: Use adutil to configure Active Directory authentication with SQL Server on Linux.

Note

For information about specific packages on RHEL 8, see Connecting RHEL systems directly to AD using SSSD.

SQL Server service principal names (SPNs)

Prior to installing and configuring PBIRS, you must configure the required SPNs on the CORPNET domain. A user with Domain Admin privileges can be used in this case, but any user with enough permissions to create SPNs is sufficient. After the SPN creation, the accounts need to be configured to use Kerberos constrained delegation.

Here are the minimum required SPNs for this scenario:

  • Using an Administrative command prompt, create the SPN for the SQL Server on Linux service account. This instance is using the default port of 1433:

    setspn -S MSSQLSvc/rhel8test:1433 CORPNET\linuxservice
setspn -S MSSQLSvc/rhel8test.CORPNET.CONTOSO.COM:1433 CORPNET\linuxservice

  • The next two SPNs are for the Power BI Report Server service account.

    setspn -S HTTP/WIN22.CORPNET.CONTOSO.COM CORPNET\pbirsservice
setspn -S HTTP/WIN22 CORPNET\pbirsservice

To handle the Kerberos requirements for forwarding Kerberos tickets, when operating within a constrained delegation implementation, we configure delegation using Microsoft's extension to the MIT Kerberos standard, as specified in RFC 4120, and use the Service for User to Proxy (S4U2proxy). This mechanism allows the PBIRS service and SQL Server service to obtain service tickets to other specified services on behalf of a user.

For example, when the reportuser authenticates with the PBIRS server's web interface to view a report, the report executes and has to access data from a data source like a SQL Server table. The SQL Server service must obtain the reportuser Kerberos service ticket, which was granted during the authentication process to the PBIRS server. The S4U2proxy extension provides the necessary protocol transition in order to pass the required credentials without having to forward the user's TGT (ticket granting ticket) or the user's session key.

In order to achieve this, the PBIRS service account (pbirsservice in this example) and the SQL Server service account (linuxservice in this example) need to be granted the Trusted To Authenticate for Delegation right in the domain. There are multiple ways to grant this right (that is, ADSI Edit, Computer and Users UI, etc.). We use an elevated PowerShell command in this example:

  • Get the SQL Server service account and set it to allow delegation. This step enables not only Kerberos delegation, but the S4U2proxy (for protocol transition) delegation on the account. The final two cmdlets apply the delegation authority to specific resources in the domain, the SPNs for the SQL Server instance.

    Get-ADUser -Identity linuxservice | Set-ADAccountControl -TrustedToAuthForDelegation $True
Set-ADUser -Identity linuxservice -Add @{'msDS-AllowedToDelegateTo'=@('MSSQLSvc/rhel8test.CORPNET.CONTOSO.COM:1433')}
Set-ADUser -Identity linuxservice -Add @{'msDS-AllowedToDelegateTo'=@('MSSQLSvc/rhel8test:1433')}

  • Get the Power BI Report Server service account and set it to allow delegation. This step enables not only Kerberos delegation, but the S4U2proxy (for protocol transition) delegation on the account. The final two cmdlets apply the delegation authority to specific resources in the domain, the SPNs for the SQL Server and PBIRS server.

    Get-ADUser -Identity pbirsservice | Set-ADAccountControl -TrustedToAuthForDelegation $True
Set-ADUser -Identity pbirsservice -Add @{'msDS-AllowedToDelegateTo'=@('MSSQLSvc/rhel8test.CORPNET.CONTOSO.COM:1433')}
Set-ADUser -Identity pbirsservice -Add @{'msDS-AllowedToDelegateTo'=@('MSSQLSvc/rhel8test:1433')}
Set-ADUser -Identity pbirsservice -Add @{'msDS-AllowedToDelegateTo'=@('HTTP/Win22.CORPNET.CONTOSO.COM')}
Set-ADUser -Identity pbirsservice -Add @{'msDS-AllowedToDelegateTo'=@('HTTP/Win22')}

Power BI Report Server (PBIRS)

PBIRS should be installed in configuration only mode.

Immediately after installing PBIRS, you must configure it to support Kerberos authentication. PBIRS by default only supports NTLM authentication. During the installation process, you need to update one of the PBIRS configuration files before completing the PBIRS configuration process, either in the UI, or via the command line. If you use an existing PBIRS installation, you still need to perform the edits, and the PBIRS service must be restarted to take effect. The configuration file is the rsreportserver.config. It is in path where PBIRS was installed. For example, on a default installation of PBIRS, the file is in the following location:

C:\Program Files\Microsoft SQL Server Reporting Services\SSRS\ReportServer

This XML file can be edited in any text editor. Remember to make a copy of the file before editing. Once you have opened the file, search for the AuthenticationTypes tag within the XML document, and add the RSWindowsNegotiate and RSWindowsKerberos attributes ahead of the RSWindowsNTLM attribute. For example:

<Authentication>
<AuthenticationTypes>
    <RSWindowsNegotiate/>
    <RSWindowsKerberos/>
    <RSWindowsNTLM/>
</AuthenticationTypes>

This step is required, because SQL Server on Linux only supports SQL and Kerberos authentication.

Note

We only need to include the RSWindowsKerberos attribute, but using RSWindowsNegotiate is helpful if standardizing PBIRS configuration files across a fleet of servers that support a mixture of Windows and Linux SQL Server instances is desired.

PBIRS UI configuration

Once the PBIRS service has been restarted after the configuration file edits have been completed, you can proceed with the remaining PBIRS configuration options such as setting the domain based service account and connecting to the remote SQL Server on Linux instance.

The PBIRS service account should appear within the SQL Server instance with appropriate permissions. You can check the permissions in SQL Server Management Studio (SSMS). In Object Explorer, navigate to Security > Logins, right-click on the CORPNET\pbirsservice account, and select Properties. The permissions are visible on the User Mapping page.

Finally, we can add the reportuser as a login on the SQL Server for testing purposes. In this case, we took the easy button and added the user to the db_datareader role within two user databases: AdventureWorks and AdventureWorksDW.

After reports have been deployed

If you need to set up report subscriptions after reports are deployed, it is a good practice to configure embedded credentials in the PBIRS data sources. All credential options work properly, except for the use of embedded credentials configured with the impersonate the user viewing the report option. This step fails when using Windows credentials, because of a limitation within the SQL Server on Linux implementation that makes impersonation more difficult.

Related content