Users of the Microsoft Power BI data visualization tool frequently also use Microsoft Entra ID (MSEI), previously called Azure Active Directory (AD), and want to use their MSEI Single Sign-On credentials to access data sources seamlessly. Previously, Power BI users either had to access the Oracle Database using the DB local username and password or had to migrate data from the Oracle Database to a different database if the security teams demanded centralized access management. 

With the release of the latest Power BI Desktop and service updates, MSEI users can now use their MSEI Single Sign-On (SSO) to access Power BI and Oracle Databases (Oracle Database 19c Release 20, released July 2023; Oracle Database 23ai, or Oracle Autonomous Database) whether the database is an Oracle Cloud Infrastructure (OCI) database service or on-premises.  Security is improved since the users are centrally managed and Azure AD tokens are used instead of password credentials. Ease of use for DBAs is improved since data can remain in the Oracle Database and not have to be migrated. Users also benefit since they can use their SSO to access their source database and not have to remember and continuously rotate their database password credentials.

Configuring the Oracle Database

The Oracle Database configuration is two ALTER SYSTEM commands (DBMS_CLOUD_ADMIN command for ADB-Serverless)

For all databases EXCEPT ADB-Serverless, you run these two commands:

ALTER SYSTEM SET IDENTITY_PROVIDER_TYPE=AZURE_AD SCOPE=BOTH;

ALTER SYSTEM SET identity_provider_config=’{“application_id_uri”: 111-111-111, “tenant_id”: “111-111-111”, “app_id”:”111-111-111”}’;

(The values for identity_provider_config such as 111-111-111 are dummy values when using Microsoft Power BI access tokens and any value can be put there)

The first ALTER SYSTEM command sets the external identity provider as Microsoft Entra ID. The second ALTER SYSTEM command is required, and the database must be registered with MSEI app registration to generate these values so it can accept Azure AD OAuth2 database access tokens. With Power BI Azure AD tokens, this command is still required, but the database isn’t required to be registered with Azure AD, and the values are dummy values.

For ADB-Serverless, you run this command:

BEGIN
  DBMS_CLOUD_ADMIN.ENABLE_EXTERNAL_AUTHENTICATION(
      type   =>’AZURE_AD’,
      params => JSON_OBJECT(‘tenant_id’ VALUE ‘111-111-111’,
                            ‘application_id’ VALUE ‘111-111-111’,
                            ‘application_id_uri’ VALUE ‘111-111-111’),
      force => TRUE
  );
END;

(The values in the JSON object can be anything such as the ‘111-111-111’ used in this example when working with Power BI access tokens)

Authorize user

The Power BI Azure AD user must be authorized to the database

CREATE USER peter_fitch IDENTIFIED GLOBALLY AS ‘AZURE_USER=peter.fitch@example.com’;

GRANT CREATE SESSION TO peter_fitch;

All privileges and roles required by the user must be granted to the database schema/user.

Now that we have set up the database, let’s configure the client side. We will first discuss Power BI Desktop and then Power BI service connectivity.

Connect Power BI Desktop to Oracle Database using Microsoft Entra ID

Now that the MSEI user is authorized to connect to the Oracle Database, it can log in using those credentials through Power BI. On the Power BI Desktop machine, the user should install Oracle Client for Microsoft Tools (OCMT) and configure the client to connect to their Oracle database.

OCMT is a free install. The OCMT web page has tutorials to guide connecting with various Microsoft tools.

Once done, the user should open Power BI Desktop. They should then select the “Get Data” option from the menu followed by selecting “Oracle Database” as their data source. To use their Azure AD credentials, they choose to connect with “Microsoft Account” and start the sign-in process. Power BI opens a new window that connects to Azure AD and requests the user to enter their account credentials.

If successful, Power BI Desktop indicates the user is now signed in. Click the “Connect” button to then connect to the Oracle Database.

That’s all that is needed to sign in to Power BI Desktop and connect to the Oracle database via MSEI.

You can also log in similarly with the Power BI service and MSEI with the Oracle Database. Let’s discuss how.

Connect Power BI Service to Oracle Database using Microsoft Entra ID

MSEI users can log in using the same credentials through the Power BI service as well. Setting up connectivity to Oracle Database using Power BI service shares a lot of similarities with the Power BI Desktop setup.

Since Power BI service is a SaaS app, it requires a separate machine that hosts the Microsoft On-premises Data Gateway that connects to your Oracle database or ADB. The gateway hosts OCMT to facilitate the data access.

Follow these tutorial steps to install OCMT and set up the gateway. Be sure to use a gateway installation download from May 2024 or later. This version contains support for Oracle Database and MSEI SSO.

The linked tutorial demonstrates how to connect using your Oracle database username and password. To use MSEI instead, you will perform a few different steps.

Enable Microsoft Entra Single Sign-on for Data Gateway

By default, MSEI SSO is not enabled for Power BI service tenancies. To enable it, go to the Power BI service Admin Portal from your web browser. Under Tenant Settings, toggle the Microsoft Entra Single Sign-on for Data Gateway option to “Enabled”. Click the “Apply” button.

Typically, users with the tenant administrator Azure role can make this tenancy change.

This privilege allows you to use MSEI SSO with Oracle database and ADB.

Create Microsoft Entra ID Single Sign-on Connection

Let’s walk you through how to connect Power BI service with Oracle database. On the Power BI service page, click the Settings icon in the upper right side of the browser page, then click on “Manage Connections and Gateways”

.

Click “New” on the menu on the upper left part of the web page to start creating the connection. A “New Connection” dialog appears in which you will configure the Oracle connection via the data gateway.

Choose “On-premises” for the connection type. This applies even for cloud databases, such as Oracle Autonomous Database.

For the Gateway Cluster Name drop down, choose the name you gave your on-premises data gateway installation.

For Connection Name, provide a name to easily identify the connection.

For Connection Type, choose “Oracle”.

For Server, enter the net service name in your tnsnames.ora file that matches the database or ADB instance you wish to connect to. You can alternatively provide the entire connect descriptor or an Easy Connect Plus entry.

For Authentication, select OAuth 2.0 for the Authentication Method. Then, click the Edit Credentials link and log in to your MSEI account.

Under “Single Sign-on”, check the box for “Use SSO via Azure AD for DirectQuery queries”.

Finally, click the “Create” button to create the connection in your Power BI service account.

You can now proceed with creating a data flow to the Oracle Database as shown in the install OCMT and Power BI Service tutorial. Since we’ve just created the data source entry, let’s use that connection.

When you create a data flow, you will need to select the Oracle data source connection to use. Navigate to the Connect to Data Source dialog. For Server, enter the same identifier you used when creating the connection in the previous step. In our example, used the net service name entry, “ADB”.

For the Connection drop-down, choose the connection you just created.

Click the Next button.

Congratulations! You should now be connected to the Oracle database or ADB via MSEI SSO.

What’s next?

Read more about the Power BI and the MSEI multicloud capabilities with the Oracle Database in the Database Security Guide for 19c and the recently released Oracle Database 23ai. For more detailed step-by-step instructions to set up Power BI connectivity with Oracle Database, read the following tutorials and watch the following videos:

• Power BI Desktop: Connect to Oracle Database
• Power BI Service: Connect to Oracle Database
• Configure the Oracle Database for Power BI (video)
• Configure Power BI Desktop (video)

The following Microsoft Power BI documentation is available:

• https://learn.microsoft.com/en-us/power-bi/