Connect Power BI Desktop to Oracle Database using Azure Active Directory Single Sign On Tokens
I previously blogged about using ODP.NET Authentication using Azure Active Directory to Enable Single Sign-On. That feature provides generic single sign-on (SSO) access between Oracle database and Microsoft Entra ID, also known as Azure Active Directory (AD). Applications can sign on once to Azure AD, then have their credentials recognized and authenticated with Oracle database. Oracle and Microsoft have now extended this SSO capability between Oracle database and Azure AD to include Power BI Desktop as well.
With the generous help of my Oracle colleague, Alan Williams, we have created a couple of videos and this blog post demonstrating how to setup Azure AD SSO with Oracle Database and Power BI Desktop.
After the videos, this blog post continues with more details about Oracle database support for Azure AD and Power BI Desktop, setup steps, and future directions.
Microsoft Power BI users frequently authenticate with Azure Active Directory and want to use their Azure AD single sign-on credentials to access their Oracle data sources seamlessly. Previously, Power BI users either had to access the Oracle database using the database local username and password or had to migrate data from the Oracle database to a different data store if security requirements demanded centralized access management.
With the latest Power BI Desktop release, users can now employ their Azure AD SSO credentials to access Power BI and Oracle Database 19c (version 19.20 or higher), whether the database is an Oracle Cloud Infrastructure (OCI) database service, Oracle Autonomous Database (ADB), or on-premises.
Oracle Database 21c doesn’t include Azure AD SSO support. Oracle Database 23c does.
Security is improved with centrally managed users. Azure AD tokens are used instead of password credentials. Database administrator ease of use is enhanced since data can remain in Oracle database, eliminating potential data migration steps. Users also benefit as their SSO access to their source database eliminates having to remember and continuously rotate their database password credentials.
Let me walk you through how to set up your Oracle database and Power BI Desktop with Azure AD SSO and what Oracle and Microsoft have planned in this area for the future.
Configure the Oracle Database for Azure AD
To configure the Oracle database for Azure AD SSO token access, run two ALTER SYSTEM commands for on-premises or 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"}';
Note: The values for IDENTITY_PROVIDER_CONFIG, such as 111–111–111, are dummy values when using Microsoft Power BI access tokens. Any value can be placed there.
The first ALTER SYSTEM command sets the external identity provider as Azure AD. The second ALTER SYSTEM command is required, and the database will need to be registered with Azure AD app registration to generate these values so the database can accept Azure AD OAuth 2.0 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.
If you are using ADB-Serverless, you run this script instead:
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;
Note: 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 Power BI User to the Database
The Power BI Azure AD user must be authorized to the database. The following commands show how to create this user and grant the necessary permission.
CREATE USER peter_user IDENTIFIED GLOBALLY AS 'AZURE_USER=peter.user@example.com';
GRANT CREATE SESSION TO peter_user;
All privileges and roles required by the user must be granted to the database schema/user.
Connect Power BI to Oracle Database using Azure Active Directory
Now that the Azure AD user is authorized to connect to the Oracle Database, they can log in using those credentials through Power BI Desktop. 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, such as Excel and SQL Server Reporting Services, as well as Power BI.
Once OCMT setup completes, 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” to 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, such as in the graphic below.
If the sign-in is successful, Power BI Desktop indicates the user is now signed in. Click the “Connect” button to then connect to the Oracle database, such as what you see in the graphic below.
That’s all that is needed to sign in to Power BI Desktop and connect to the Oracle database via Azure AD.
Future Directions
In 2024, Oracle and Microsoft plan to expand Azure AD SSO to the cloud-based Power BI service and Oracle database.
Further along, we will migrate how Power BI and Power Query accesses Oracle database from unmanaged ODP.NET to managed ODP.NET to make the install and setup process even simpler, lighter, and smoother.
Read more about the Power BI and the Azure AD multicloud capabilities with the Oracle Database in the Database Security Guide for 19c and Oracle Database 23c Free. For more detailed step-by-step instructions, read the Power BI Desktop and Oracle Database connectivity tutorial.
