How to Secure Data Source Credentials for Power BI Reports

Learn how to secure data source credentials for Power BI reports that use Excel, SQL Server, and SharePoint Online as data sources.

Problem

Power BI is a powerful tool for creating interactive and insightful reports from various data sources. However, to ensure the security and integrity of the data, it is important to follow some best practices and considerations for data source credentials. In this article, we will discuss how to secure data source credentials for Power BI reports that use Excel files from an on-premises file server, databases from on-premises SQL Server, and Excel files from SharePoint Online document library as data sources.

Excel Files from On-Premises File Server

One of the common data sources for Power BI reports is Excel files stored on an on-premises file server. To access these files, Power BI needs to authenticate with the file server using a user account that has read-only access to the folders where the files are located.

Best Practice: Use a Dedicated Domain User Account

A best practice for securing data source credentials for Excel files from an on-premises file server is to use a dedicated domain user account that has read-only access to the folders where the files are located. This way, you can avoid using the user account of your data analyst, which may have more privileges than necessary and pose a security risk.

To create a dedicated domain user account, you need to have access to the Active Directory Domain Services (AD DS) on your network. You can follow these steps to create a new domain user account:

1. Open the Active Directory Users and Computers snap-in on a domain controller or a computer with Remote Server Administration Tools (RSAT) installed.
2. In the console tree, right-click the folder where you want to create the new user account, and then click New > User.
3. In the New Object – User dialog box, type the user name, full name, and password for the new user account, and then click Next.
4. On the next page, you can optionally configure additional properties for the new user account, such as email address, profile path, logon script, etc. Click Next when you are done.
5. On the final page, review the summary of the new user account, and then click Finish.

After creating the new domain user account, you need to grant it read-only access to the folders where the Excel files are located on the file server. You can follow these steps to do so:

1. On the file server, open File Explorer and navigate to the folder where the Excel files are located.
2. Right-click the folder, and then click Properties.
3. On the Properties dialog box, click the Security tab, and then click Edit.
4. On the Permissions for Folder dialog box, click Add.
5. On the Select Users, Computers, Service Accounts, or Groups dialog box, type the name of the new domain user account, and then click Check Names. If the name is valid, it will be underlined. Click OK.
6. On the Permissions for Folder dialog box, select the new domain user account, and then check the Allow box for the Read permission. Click OK.
7. Repeat the steps for any other folders that contain Excel files that you want to use as data sources for Power BI reports.

Consideration: Use a Data Gateway

A consideration for securing data source credentials for Excel files from an on-premises file server is to use a data gateway. A data gateway is a service that allows you to securely connect to on-premises data sources from Power BI. By using a data gateway, you can avoid exposing your data source credentials to the public internet and reduce the risk of unauthorized access.

To use a data gateway, you need to install and configure it on a computer that has access to the on-premises data sources. You can follow these steps to install and configure a data gateway:

1. Download the data gateway installer from the Power BI service.
2. Run the installer and follow the instructions to install the data gateway on a computer that has access to the on-premises data sources.
3. After the installation is complete, sign in to the data gateway with your Power BI account.
4. On the data gateway configuration page, enter a name and a recovery key for the data gateway, and then click Configure.
5. On the Power BI service, go to the Manage gateways page and verify that the data gateway is online and ready to use.

After installing and configuring the data gateway, you need to add the data sources that you want to use for Power BI reports. You can follow these steps to add a data source to the data gateway:

1. On the Power BI service, go to the Manage gateways page and select the data gateway that you want to use.
2. On the data gateway details page, click Add data source.
3. On the Add data source page, select File as the data source type, and then enter the file path of the Excel file that you want to use as a data source. For example, \\fileserver\folder\file.xlsx.
4. Enter the user name and password of the domain user account that has read-only access to the Excel file, and then click Add.

Repeat the steps for any other Excel files that you want to use as data sources for Power BI reports.

Databases from On-Premises SQL Server

Another common data source for Power BI reports is databases from on-premises SQL Server. To access these databases, Power BI needs to authenticate with the SQL Server using a user account that has read-only access to the databases.

Best Practice: Use a SQL Server Authentication User Account

A best practice for securing data source credentials for databases from on-premises SQL Server is to use a SQL Server authentication user account that has read-only access to the databases. This way, you can avoid using the local DB admin accounts, which have more privileges than necessary and pose a security risk.

To create a SQL Server authentication user account, you need to have access to the SQL Server Management Studio (SSMS) on your network. You can follow these steps to create a new SQL Server authentication user account:

1. Open SSMS and connect to the SQL Server instance where the databases are located.
2. In the Object Explorer, expand the Security folder, right-click the Logins folder, and then click New Login.
3. In the Login – New dialog box, type the user name and password for the new user account, and then select SQL Server authentication as the authentication type.
4. On the left pane, click Server Roles, and then uncheck any server roles that are checked by default. The new user account does not need any server roles to access the databases.
5. On the left pane, click User Mapping, and then check the databases that you want the new user account to access. For each database, check the db_datareader role in the Database role membership section. This role grants read-only access to the database.
6. Click OK to create the new user account.

After creating the new SQL Server authentication user account, you can use it to connect to the databases from Power BI.

Consideration: Use a Data Gateway

A consideration for securing data source credentials for databases from on-premises SQL Server is to use a data gateway. As mentioned earlier, a data gateway is a service that allows you to securely connect to on-premises data sources from Power BI. By using a data gateway, you can avoid exposing your data source credentials to the public internet and reduce the risk of unauthorized access.

To use a data gateway, you need to install and configure it on a computer that has access to the on-premises data sources. You can follow the same steps as described in the previous section to install and configure a data gateway.

After installing and configuring the data gateway, you need to add the data sources that you want to use for Power BI reports. You can follow these steps to add a data source to the data gateway:

1. On the Power BI service, go to the Manage gateways page and select the data gateway that you want to use.
2. On the data gateway details page, click Add data source.
3. On the Add data source page, select SQL Server as the data source type, and then enter the server name and the database name of the database that you want to use as a data source. For example, sqlserver\instance\database.
4. Select SQL Server authentication as the authentication method, and then enter the user name and password of the SQL Server authentication user account that has read-only access to the database, and then click Add.

Repeat the steps for any other databases that you want to use as data sources for Power BI reports.

Excel Files from SharePoint Online Document Library

A third common data source for Power BI reports is Excel files from SharePoint Online document library. To access these files, Power BI needs to authenticate with the SharePoint Online using a user account that has read-only access to the document library.

Best Practice: Use a Dedicated SharePoint Online User Account

A best practice for securing data source credentials for Excel files from SharePoint Online document library is to use a dedicated SharePoint Online user account that has read-only access to the document library. This way, you can avoid using the user account of your Power BI developer, which may have more privileges than necessary and pose a security risk.

To create a dedicated SharePoint Online user account, you need to have access to the Microsoft 365 admin center on your network. You can follow these steps to create a new SharePoint Online user account:

1. Open the Microsoft 365 admin center and sign in with your admin account.
2. In the left navigation pane, click Users > Active users.
3. On the Active users page, click Add a user.
4. On the Add a user page, enter the user name, display name, and password for the new user account, and then click Next.
5. On the next page, select the SharePoint Online license for the new user account, and then click Next.
6. On the next page, you can optionally configure additional settings for the new user account, such as email address, contact information, roles, etc. Click Next when you are done.
7. On the final page, review the summary of the new user account, and then click Finish.

After creating the new SharePoint Online user account, you need to grant it read-only access to the document library where the Excel files are located. You can follow these steps to do so:

1. On the SharePoint Online site, go to the document library where the Excel files are located.
2. On the document library page, click the Settings icon, and then click Library settings.
3. On the Library settings page, click Permissions for this document library.
4. On the Permissions page, click Stop Inheriting Permissions.
5. On the confirmation dialog box, click OK.
6. On the Permissions page, click Grant Permissions.
7. On the Share dialog box, type the email address of the new SharePoint Online user account, and then select Can view as the permission level. Click Share.

Repeat the steps for any other document libraries that contain Excel files that you want to use as data sources for Power BI reports.

Consideration: Use OAuth2 Authentication

A consideration for securing data source credentials for Excel files from SharePoint Online document library is to use OAuth2 authentication. OAuth2 is a protocol that allows Power BI to access SharePoint Online data sources without storing or exposing the user credentials. By using OAuth2 authentication, you can avoid entering and saving the user credentials in Power BI, and reduce the risk of unauthorized access.

To use OAuth2 authentication, you need to sign in to Power BI with the same account that has access to the SharePoint Online data sources. You can follow these steps to sign in to Power BI with OAuth2 authentication:

1. On the Power BI service, click Sign in on the top right corner of the page.
2. On the Sign in page, enter the email address and password of the account that has access to the SharePoint Online data sources, and then click Sign in.
3. On the consent dialog box, click Accept to allow Power BI to access your SharePoint Online data sources.

After signing in to Power BI with OAuth2 authentication, you can connect to the SharePoint Online data sources without entering the user credentials again.

Frequently Asked Questions (FAQs)

Question: What are the benefits of securing data source credentials for Power BI reports?

Answer: Securing data source credentials for Power BI reports can help you:

• Protect your data from unauthorized access and manipulation
• Comply with the data security and privacy policies and regulations
• Reduce the maintenance and troubleshooting efforts for data source connections
• Improve the performance and reliability of data refresh and query operations

Question: How can I change the data source credentials for Power BI reports?

Answer: You can change the data source credentials for Power BI reports by following these steps:

1. On the Power BI service, go to the Datasets page and select the dataset that you want to change the data source credentials for.
2. On the dataset details page, click the Settings icon, and then click Data source credentials.
3. On the Data source credentials page, select the data source that you want to change the credentials for, and then click Edit credentials.
4. On the Edit credentials dialog box, enter the new credentials for the data source, and then click Sign in.

Question: How can I test the data source credentials for Power BI reports?

Answer: You can test the data source credentials for Power BI reports by following these steps:

1. On the Power BI service, go to the Datasets page and select the dataset that you want to test the data source credentials for.
2. On the dataset details page, click the Refresh icon, and then click Refresh now.
3. On the Refresh history page, check the status and details of the refresh operation. If the refresh is successful, it means the data source credentials are valid and working. If the refresh fails, it means the data source credentials are invalid or not working, and you need to change or fix them.

Summary

In this article, we have discussed how to secure data source credentials for Power BI reports that use Excel files from an on-premises file server, databases from on-premises SQL Server, and Excel files from SharePoint Online document library as data sources. We have covered the best practices and considerations for each data source type, and provided the steps to create and use dedicated user accounts, data gateways, and OAuth2 authentication. By following these guidelines, you can ensure the security and integrity of your data and Power BI reports.

Disclaimer: This article is for informational purposes only and does not constitute professional advice. The author and the publisher are not liable for any errors or omissions, or for any consequences arising from the use of the information in this article. The user is responsible for verifying the accuracy and validity of the information in this article, and for complying with the applicable laws and regulations regarding data security and privacy. The user is also responsible for backing up the data and Power BI reports before making any changes to the data source credentials. The user should consult a qualified professional before taking any action based on the information in this article.