Connect Power BI Desktop to Oracle Database through LDAP/JDBC Connection Strings

Lightweight Directory Application Protocol (LDAP) Connection in SQL Developer

There are serveral software clients can be used to connect to Oracle Database, Oracel SQL Developer might be the most popular one.LDAP is the most secure and popular way for Enterprise to manage user access to Oracle Databases. The LDAP servers information can be specified in both JDBC URL and LDAP types of connection.

Custom JDBC URL

jdbc:oracle:thin:@ldap://[ldapservername:ldapport]/[DBservicename],[DomainContext]

LDAP Connection

The picture below shows the LDAP Connection with the same connection information from the JDBC URL above. The the fields of LDAP Server, Context, and DB Service should have the same values as ldapservername:ldapport, DomainContext, and DBservicename from the Custom JDBC URL above.

  • Create a new connection with a new Name
  • Fill in your Username and/or Password
  • Select LDAP in the Connection Type drop-down list
  • Put the ldapservername:ldapport from the above custom JDBC URL into the LDAP Server field
  • The Context should be loaded autoamatically, if not, you can copy DomainContext from the JDBC
  • Click the Load button to load the DBservicename in the DB Service, if it cannot load, you can copy and paste the DBservicename from JDBC URL.
  • Test the connection, if succeffuly, click the Save button.
[username]@//[servername]:[serverport]/[servicename]

Install and Configure ODAC

Step 1: Uninstall any existing version of ODAC

If you have installed the Xcopy version, to uninstall all ODAC products, execute the following command in the Oracel Home directory:

uninstall.bat all odac

Step 2: Check Power BI Desktop Version

You can find the version information from Power BI Desktop -> Help -> About. The following is a screenshot of 64-bit version.

Step 3: Download ODAC Installation File

The installation file can be downloaded from ODAC download page. In my case, I have downloaded ODAC122011_x64.zip from the 64-bit ODAC 12.2c Release 1 (12.2.0.1.1) for Windows x64 section.

Step 4: Install ODAC

Unzip the downloaded file and double-click the setup.exe in the unzipped folder to start the installation process:

  • Choose your preferred language
  • Choose Use Existing Windows User. In my case, my username is corp\<EMP_ID> and password is my LDAP password.
  • Use the default Oracle base and Software location.
  • Use the default Available Product Components.
  • It is recommended to configure ODP.NET for machine-wide level
  • It is O.K. to leave the DB Connection Configuration fields empty, because we are going to use the LDAP configurations. You can also fill them out with the corresponding information from the section LDAP Connection in SQL Developer as shown in the picture below.
  • If you get error for pre-requisite check on certain items, you can just uncheck the boxes and proceed.
  • The remaining installation steps are pretty straight forward. The installation will take couple of minutes.

Step 5: Configure Connection

  • After installation, navigate to installation directory (Oracle home), which is the Software location in the step of Specify Installation Location above. Then go into Network > Admin.
  • Create two files of ldap.ora and sqlnet.ora, if they are not generated automatically.
  • The content of fileldap.ora should be similar to the following:
DIRECTORY_SERVERS = (oid.myinfo.domain.com:3060)
DIRECTORY_SERVER_TYPE =oid
DEFAULT_ADMIN_CONTEXT="cn=OracleContext, dc=DomainContext"
  • File sqlnet.ora should have the follwing content in it:
NAMES.DIRECTORY_PATH= (LDAP, TNSNAMES, EZCONNECT)
  • If you updated the DB Connection Configuration fields during the installation, you will find the file tnsnames.ora should have the following content.
FDW =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = [servername])(PORT = [serverport]))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = [DBservicename])
)
)

Connect to Oracle Database in Power BI Desktop

With the above connection details information, you can take the following steps to conncet to the Oracle Database in Power BI Desktop:

  • From the Home tab, select Get Data
  • From the Get Data window that appears, select More (if necessary), select Database > Oracle database, and then select Connect
  • Put the [DBservicename] in the Server box. If the OID is created with only cn=OracleContext, then you need to append a dot at the end of service name. For my case, my service name is ABC_DEFG and the OID is created only context of cn=OracleContext, I need to specify the server as ABC_DEFG..
  • If asked, use the Database credentials to connect to the database.

References

Power BI Official Document: https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-connect-oracle-database#installing-the-oracle-client

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Lei Feng

Lei Feng

Big Data, Google Cloud Platform, Machine Learning, Operations Research