You can use your Microsoft Entra ID (was Azure AD) SSO credentials to access an Oracle Database from Microsoft Excel and other tools, when using the Oracle Database ODBC driver. The following example shows you how you can access an Oracle Database from Microsoft Excel.

• Configure the database for Entra ID integration.

  See Also:

  Authenticating and Authorizing Microsoft Entra ID (Azure AD) Users for Oracle Databases in Oracle Database Security Guide for more information about Entra ID integration with Oracle Database.

• Get a valid OAuth 2 token for your database before you start your configuration and put the token into the location specified by the TOKEN_LOCATION parameter in your connect string.

  Note:

  The token is only valid for about an hour. You may need to request a new token if the time taken to complete the configurations exceeds an hour.

  See Also:

  Local Naming Parameters in the tnsnames.ora File in Oracle Database Net Services Reference for more information about the TOKEN_LOCATION parameter.

1. Navigate to https://www.oracle.com/database/technologies/instant-client/microsoft-windows-32-downloads.html or https://www.oracle.com/database/technologies/instant-client/winx64-64-downloads.html
2. Download the Instant Client Basic Package.
3. Download the ODBC package.
4. Unzip the Instant Client folder (instantclient_xx_yy) to the desired location. For example: C:\Oracle\SQLPlus\.
5. Unzip the ODBC package and put the contents of the zip file: instantclient_xx_yy into the Instant Client folder (files include odbc_install.exe, odbc_license, and odbc_readme).
6. Run odbc_install.

   This registers the ODBC driver with the ODBC Data Sources GUI.

1. Set the TNS_ADMIN environment variable to point to your tnsnames.ora folder.
2. Add the path to your ODBC driver in the PATH environment variable.

   • The name of the ODBC driver is odbcad32.exe – for both the 32-bit and 64-bit drivers.

   • Paradoxically, on a 64-bit Windows Operating System (OS), the 32-bit odbcad32.exe is installed in C:\Windows\sysWOW64, and the 64-bit odbcad32.exe is installed in C:\Windows\system32.

   • If you are on a 64-bit Windows OS, put C:\Windows\system32 near the beginning of the path – and definitely in front of sysWOW64 to make sure it sees the 64-bit version first.

3. Add the connect string for the Oracle Database configured for the Entra ID in tnsnames.ora.
   The following is an example:

   azuredb = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)
   (host=adb.us-ashburn-1.oraclecloud.com))(connect_data=(service_name=xxx123_azuredb_high.adb.oraclecloud.com))
   (security=(ssl_server_dn_match=yes)(TOKEN_AUTH=OAUTH)(TOKEN_LOCATION="C:\USERS\PETERFI\Oracle\azuredb\token")))

   See Also:

   Authenticating and Authorizing Microsoft Entra ID (Azure AD) Users for Oracle Databases in Oracle Database Security Guide for more information about Entra ID integration with Oracle Database.

1. You need to get an OAuth 2 token (if not already done) at this point because you need the token to test the new Data Source Name (DSN) that you will create in the ODBC Data Source Administrator GUI in the next section.

1. Open ODBC Data Source Administrator for the correct bit (32-bit or 64-bit).

   You can open ODBC Data Source Administrator from the command line or you can use search or open.

2. Ensure that the Drivers tab displays the Oracle Database ODBC driver that you just installed.

   Figure 4-1 ODBC Data Source Administrator (32-bit)

   
3. Open the User DSN tab.
4. Click Add.
5. In the Create New Data Source dialog box, select the appropriate Oracle Database ODBC driver.

   Figure 4-2 Create New Data Source

   
6. Select Finish.
   The Oracle ODBC Driver Configuration dialog box is displayed.
7. In the Oracle ODBC Driver Configuration dialog box, do the following:

   Figure 4-3 Oracle ODBC Driver Configuration

   
   1. In Data Source Name, fill in a data source name.
   2. In TNS Service Name, select the database for the TNS service name.

      The selected name should reflect what is in the tnsnames.ora file.

   3. Leave User ID blank.
   4. Click the Test Connection button.

      You should see a connection successful message (make sure you still have a valid token).

      The token that you put into TOKEN_LOCATION is sent to the database. If you are authorized to access the database, then you get a connection successful message as follows:

      Figure 4-4 Oracle ODBC Driver Configuration - Connection Successful Message

      
   5. Click OK to close the message.
8. In the Oracle ODBC Driver Configuration dialog box, click OK.

   In the ODBC Data Source Administrator dialog box, in the User Data Sources list, you can see your new user data source displayed.

   Figure 4-5 ODBC Data Source Administrator (32-bit)

   

1. Open a new Excel sheet.
2. Select the Data tab.
3. On the Data tab, in the Get & Transform Data section, click Existing Connections.
4. On the Existing Connections dialog box, click Browse for More.
5. On the Select Data Source dialog box, click New Source.
6. On the Data Connection Wizard dialog box, select ODBC DSN from the data source type list, and click Next.

   You should see your new DSN.

7. On the Data Connection Wizard - Connect to ODBC Data Source dialog box, select the new DSN and click Next.

   Figure 4-6 Data Connection Wizard - Connect to ODBC Data Source

   

   When you click Next, Excel accesses the database using your token.

   Note:

   Ensure that your token is still valid.

   The Data Connection Wizard - Select Database and Table dialog box is displayed.

   Figure 4-7 Data Connection Wizard - Select Database and Table

   
8. In the Data Connection Wizard - Select Database and Table dialog box, select the database that contains the data you want to import to Excel, and click Next.
9. Fill out the next form with file name and description, and other details as needed, and click Finish.
10. From the data import form, select the required data to import.

    You can now see the data in the Excel sheet.

    Figure 4-8 Excel Sheet with Imported Data

    

    This concludes the setup of the Oracle Database ODBC driver in Microsoft Excel.