Entra ID Integration For SQLDeveloper

SQLDeveloper has been one of the preferred Integrated Development Environment (IDE) for Oracle developers, database administrators and power users for decades. This powerful tool has been recently supplemented with the ability to centralize authentication, authorization, and user life cycle database management of Oracle database users through cloud native integration with Microsoft Entra ID.  This integration is made possible through the Azure (Entra ID) resource extensions available from the ojdbc-extensions libraries.  The setup of SQLDeveloper for Entra ID integration is straight forward.  Much of what you see in this blog post was a distillation and simplification of the work produced by Ty Stahl from his blog post of the same topic.

Setup SQLDeveloper For Entra ID Integration

1. The first step is to download SQLDeveloper and extract SQLDeveloper into your desired location.  In my lab, this is C:\Program Files\sqldeveloper.

2. The next step is to build ojdbc-extensions with Apache Maven or download them and place them in a subdirectory of <sqldeveloper_base> folder.  For example, I extracted the downloaded extensions into C:\Program Files\sqldeveloper such that the resulting ojdbc-extensions libraries are in the "C:\Program Files\sqldeveloper\sdks\jdbc-azure" folder.

3. Next, start and then stop SQLDeveloper in order to create the SQLDeveloper product.conf configuration file in <home>\AppData\Roaming\sqldeveloper\<version>.  

4. Update the "<home>\AppData\Roaming\sqldeveloper\<version>\product.conf" file with individual AddJavaLibFile reference for each of the ojdbc-extensions jar files.  In my case, here are the following entries added:

AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\accessors-smart-2.5.1.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\asm-9.6.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\azure-core-1.54.1.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\azure-core-http-netty-1.15.7.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\azure-data-appconfiguration-1.7.3.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\azure-identity-1.14.2.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\azure-json-1.3.0.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\azure-security-keyvault-secrets-4.9.1.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\azure-xml-1.1.0.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\content-type-2.3.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\jackson-annotations-2.13.5.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\jackson-core-2.13.5.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\jackson-databind-2.13.5.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\jackson-datatype-jsr310-2.15.2.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\jackson-module-jaxb-annotations-2.12.2.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\jcip-annotations-1.0-1.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\jna-5.13.0.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\jna-platform-5.13.0.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\json-smart-2.5.1.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\lang-tag-1.7.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\msal4j-1.17.2.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\msal4j-persistence-extension-1.3.0.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-buffer-4.1.115.Final.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-codec-4.1.115.Final.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-codec-dns-4.1.112.Final.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-codec-http-4.1.115.Final.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-codec-http2-4.1.115.Final.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-codec-socks-4.1.115.Final.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-common-4.1.115.Final.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-handler-4.1.115.Final.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-handler-proxy-4.1.115.Final.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-resolver-4.1.115.Final.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-resolver-dns-4.1.112.Final.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-resolver-dns-classes-macos-4.1.112.Final.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-resolver-dns-native-macos-4.1.112.Final-osx-x86_64.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-tcnative-boringssl-static-2.0.62.Final.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-tcnative-boringssl-static-2.0.69.Final-linux-aarch_64.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-tcnative-boringssl-static-2.0.69.Final-linux-x86_64.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-tcnative-boringssl-static-2.0.69.Final-osx-aarch_64.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-tcnative-boringssl-static-2.0.69.Final-osx-x86_64.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-tcnative-boringssl-static-2.0.69.Final-windows-x86_64.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-tcnative-classes-2.0.69.Final.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-transport-4.1.115.Final.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-transport-classes-epoll-4.1.115.Final.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-transport-classes-kqueue-4.1.115.Final.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-transport-native-epoll-4.1.115.Final-linux-x86_64.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-transport-native-kqueue-4.1.115.Final-osx-x86_64.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-transport-native-unix-common-4.1.115.Final.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\nimbus-jose-jwt-9.40.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\oauth2-oidc-sdk-11.18.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\ojdbc-provider-azure-1.0.4.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\ojdbc-provider-common-1.0.4.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\ojdbc8-23.4.0.24.05.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\reactive-streams-1.0.4.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\reactor-core-3.4.41.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\reactor-netty-core-1.0.48.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\reactor-netty-http-1.0.48.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\slf4j-api-1.7.36.jar

Use SQLDeveloper With Entra ID Integration

Once SQLDeveloper has been setup for Entra ID integration, the next step is to add Entra ID enabled Oracle databases to the configuration and use them.

1. Open SQLDeveloper

2. Add an Entra ID enabled database by clicking on the green plus symbol in the upper lefthand corner of the SQLDeveloper app and fill out the fields as follows:

Name: <datbase_name>
Database Type: Oracle
Authentication Type: OS
Connection Type: TNS
Network Alias: <TNS_Alias_Name>

Here is a sample screenshot from my lab environment:

3. Once the database has been added, double-click on the database in the "Connections" panel to initiate the authentication process. In my case, PDB1.

4. A web browser window will open to the Microsoft login.  You select the desired account or add a new one.  If you don’t already have an authenticated session, it will prompt you for your password followed by entering in the code from the Microsoft authenticator application. 

5. Once successfully authenticated, the web browser window will indicate that authentication is complete and that you can close the web browser window.

At this point, the structure of database in SQLDeveloper will be revealed in the Connections panel in accordance with the permissions granted to your user.

Troubleshooting

For your reference, here is a sample tnsname.ora entry to illustrate the requisite requirements for an Entra ID integrated connection string.

PDB1 = 
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCPS)(HOST=hrdb.dbauthdemo.com)(PORT=2484))
    (SECURITY=
      (SSL_SERVER_DN_MATCH=TRUE)
      (WALLET_LOCATION=SYSTEM)
      (TOKEN_AUTH=AZURE_INTERACTIVE)
      (TENANT_ID=7f4c6e3e-a1e0-43fe-14c5-c2f051a0a3a1)
      (CLIENT_ID=e5124a85-ac3e-14a4-f2ca-1ad635cf781a)
      (AZURE_DB_APP_ID_URI=https://dbauthdemo.com/16736175-ca41-8f33-af0d-4616ade17621)
    )
    (CONNECT_DATA=
      (SERVER=DEDICATED)
      (SERVICE_NAME=pdb1)
    )
  )

The key differences to note from a normal service description are the requirement for encrypted (e.g. TLS based TCPS) connection and Entra ID configuration.

The TLS encryption is required between the database client and server to securely pass the access token between the client and server. These settings are highlighted in blue.

The Entra ID configuration is highlighted in red where:

• TOKEN_AUTH specifies the SAML authentication flow, which is interactive in this case
• TENANT_ID specifies the ID of the Azure/Entra ID tenancy
• CLIENT_ID specifies the registered Entra ID web application for the database client
• AZURE_DB_APP_ID_URI specifies the URI of the registered Entra ID web application of the database server

Note that WALLET_LOCATION is set to SYSTEM. This means that the database server's TLS certificate is signed by a public certificate authority for which the operating system already contains a copy of the certificate authorities certificate chain in the operating system trust store.  Therefore, a local wallet file containing the certificate authority's certificate chain or a self-signed certificate from the database server.

If the database server's certificate is self-signed or signed by a private certificate authority, then you will instead need to specify the path to the wallet that includes this information.  Here is an example of what the WALLET_LOCATION would look like in the TNS entry if you had to specify a path to the wallet folder:

      (WALLET_LOCATION=C:\Users\DBClient\Desktop\tls)

Also, in addition to specifying that the TLS certificate subject or one of the subject alternative name entries (SAN) must match the HOST value of the service description (e.g. SSL_SERVER_DN_MATCH=TRUE), you can also specify the full DN of the certificate with the following where the value of SSL_SERVER_CERT_DN highlighed in red would need to exactly match the subject of the server's certificate:

      (SSL_SERVER_CERT_DN="CN=hrdb.dbauthdemo.com")

If you are not certain what the value of the server's certificate is, you can determine it from a UNIX/Linux system with the openssl tool. Here is a sample invocation for your reference:

$ echo | openssl s_client -connect hrdb.dbauthdemo.com:2484 2>&1 | openssl x509 -noout -text | egrep -i "DNS|subject"
        Subject: CN=hrdb.dbauthdemo.com
        Subject Public Key Info:
            X509v3 Subject Alternative Name: 
                DNS:hrdb.dbauthdemo.com, DNS:www.hrdb.dbauthdemo.com
            X509v3 Subject Key Identifier:

I hope you found this post helpful and informative.

Blessings!

Brad