Incomplete driver for Azure SQL Database prevents using Always Encrypted

Completed
Avatar
Jccm Webster
Created

When I follow instructions for setting JDBC to access Azure SQL Database with some columns protected by Always Encrypted, I run into an issue where my guess is not all java code necessary is available to the driver.  It will probably quickly become obvious that I'm relatively inexperienced in the java environment, so may not be using terms quite right.

MS instructions for JDBC setup: Use Always Encrypted with the JDBC driver - JDBC Driver for SQL Server | Microsoft Learn

MSSQL JDBC Keyvaults with Always Encrypted: Key Vault authentication with Managed Identities · microsoft/mssql-jdbc Wiki · GitHub

Here's what I'm doing:

  1. Create a new Data Source using Azure SQL Server as the Driver -- jumping to the driver shows the following versions: SQL Server ver 12.2.0, MSAL4J ver 1.13.7
  2. Set server host, User, and Database values -- I am using Azure Active Directory interactive authentication
  3. Note that connecting at this point jumps to a browser for authenticating, and I can successfully get into the database with the driver connection properties for Always Encrypted turned off -- this means I see the cyphertext of encrypted columns, but demonstrates I have connectivity.
  4. Now we start altering the data source properties to enable Always Encrypted:
  5. Under Advanced tab, set the following: columnEncryptionSetting = Enabled, keyStorePrincipalId = <client ID -- known working>, keyStoreSecret= <client key -- known working> -- the best reference is the git link I provided, showing how to configure specifically for what I'm trying to do as of driver version 8.3 -- I see there are still fields in the DataGrip UI which this article calls deprecated -- I've tried both ways, and run into the same issue below...
  6. In the Advanced tab, the keyStoreAuthentication field only allows "JavaKeyStorePassword" but we need a different value -- jump back to the General tab, and add the following to the end of the url:  "keyStoreAuthentication=KeyVaultClientSecret" -- note that not all values are allowed -- there is something verifying this value and "KeyVaultClientSecret" is an allowed value, and even shows back on the field in the Advanced tab.  Odd.
  7. Click OK to save changes, open a new console, execute a select statement -- get error "Driver class 'com.azure.core.credential.TokenCredential' not found" with an option to Change driver class
  8. I have tried to get the jars for Azure Core SDK for Java, and dropped the jar next to the other driver files and in the Driver page added a custom JAR.  This actually got the error message to change, but I seemingly started running into dependencies of Azure SDK being missing, so I started retrieving those jars and adding them, but was only able to get past about 5 missing classes before not knowing where to retrieve the missing classes.

It feels safe to say that in general the default Azure SQL Database driver I've downloaded automatically from DataGrip is incomplete for this attempted usage.  Has anyone else been able to get past this?  At this point, I'm kind of over my head, and wondering if anyone else has any suggestions on how to proceed?

Votes

0

Share

10 comments
Avatar
Yaroslav Bedrov
Created

Hello,

Have you tried to install Microsoft Azure Toolkit for IntelliJ?

0
Avatar
Jccm Webster
Created

Hi, Yaroslav, thank you for the suggestion.  It got me closer, but now I'm running into a subsequent problem.  First, I was unable to load that plugin to current DataGrip because the plugin is older.  I downloaded the latest "compatible" DataGrip, version 2022.2.5.  Now I can load the plugin, but the plugin gives an error (on the Plugins settings page): "Requires plugin 'org.jetbrains.idea.maven' to be installed".  I am unable to find that plugin.

However, I noticed that the toolkit ZIP had among other things a lib folder which included a lot of slightly older versions of packages I touched when was manually adding jars, so I went back to that approach and using the lib folder for guidance, got a set of jars which lets the "Test Connection" succeed:

  •  azure-core-1.36.0
  • reactive-streams-1.0.4
  • reactor-core-3.5.3
  • jackson-datatype-jsr310-2.13.4
  • azure-core-http-netty-1.13.0
  • netty-resolver-4.1.89.Final
  • netty-common-4.1.89.Final
  • reactor-netty-core-1.1.3
  • reactor-netty-http-1.1.3
  • At this point, it couldn't find io.netty.channel.EventLoopGroup, and I couldn't find a specific package here, so I just pulled in ALL remaining 19 netty jars from the zip's lib folder, which allowed for the Test Connection to succeed -- perhaps not all 19 are needed

This allows me to connect to the database and run queries which do not touch encrypted columns.  Unfortunately, when I attempt to access an encrypted column, in the result cell instead of a value I see the following error:

-- <failed to load>
-- java.lang.NoClassDefFoundError: com/azure/security/keyvault/keys/cryptography/models/KeyWrapAlgorithm
--     at com.microsoft.sqlserver.jdbc.SQLServerColumnEncryptionAzureKeyVaultProvider.validateEncryptionAlgorithm(SQLServerColumnEncryptionAzureKeyVaultProvider.java:616)
--     at com.microsoft.sqlserver.jdbc.SQLServerColumnEncryptionAzureKeyVaultProvider.decryptColumnEncryptionKey(SQLServerColumnEncryptionAzureKeyVaultProvider.java:338)
--     at com.microsoft.sqlserver.jdbc.SQLServerSymmetricKeyCache.getKey(SQLServerSymmetricKeyCache.java:101)
--     at com.microsoft.sqlserver.jdbc.SQLServerSecurityUtility.decryptSymmetricKey(SQLServerSecurityUtility.java:236)
--     at com.microsoft.sqlserver.jdbc.SQLServerSecurityUtility.decryptWithKey(SQLServerSecurityUtility.java:282)
--     at com.microsoft.sqlserver.jdbc.ServerDTVImpl.getValue(dtv.java:3710)
--     at com.microsoft.sqlserver.jdbc.DTV.getValue(dtv.java:248)
--     at com.microsoft.sqlserver.jdbc.Column.getValue(Column.java:190)
--     at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getValue(SQLServerResultSet.java:2105)
--     at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getValue(SQLServerResultSet.java:2091)
--     at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getString(SQLServerResultSet.java:2568)
--     in JdbcHelperImpl.getObject(JdbcHelperImpl.java:307)
-- Caused by: java.lang.ClassNotFoundException: com.azure.security.keyvault.keys.cryptography.models.KeyWrapAlgorithm
--     ... 32 more

I then saw two additional jars from the zip lib which I pulled in:

  • azure-security-keyvault-keys-4.5.3
  • azure-security-keyvault-secrets-4.5.3

Unfortunately, the addition of these did not help, and I remain with the problem of seeing the above java exception in the result grid instead of the plaintext values.

0
Avatar
vasily chernov
Created

To many dependencies are required.
I tried the following pom.xml:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>Azure-Always-Encrypted</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>11</maven.compiler.source>
        <maven.compiler.target>11</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

    <dependencies>
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>mssql-jdbc</artifactId>
            <version>12.2.0.jre8</version>
        </dependency>
        <dependency>
            <groupId>com.microsoft.azure</groupId>
            <artifactId>msal4j</artifactId>
            <version>1.13.7</version>
        </dependency>
        <dependency>
            <groupId>com.azure</groupId>
            <artifactId>azure-security-keyvault-keys</artifactId>
            <version>4.6.0</version>
        </dependency>
    <dependency>
            <groupId>com.azure</groupId>
            <artifactId>azure-security-keyvault-secrets</artifactId>
            <version>4.6.0</version>
        </dependency>
    </dependencies>
</project>

with mvn dependency:copy-dependencies and got the following list of jar files:

  1. accessors-smart-2.4.8.jar
  2. accessors-smart-2.4.9.jar
  3. asm-9.1.jar
  4. asm-9.3.jar
  5. azure-core-1.37.0.jar
  6. azure-core-http-netty-1.13.1.jar
  7. azure-security-keyvault-keys-4.6.0.jar
  8. azure-security-keyvault-secrets-4.6.0.jar
  9. content-type-2.2.jar
  10. jackson-annotations-2.13.4.jar
  11. jackson-core-2.13.4.jar
  12. jackson-databind-2.13.4.2.jar
  13. jackson-datatype-jsr310-2.13.5.jar
  14. jcip-annotations-1.0-1.jar
  15. json-smart-2.4.8.jar
  16. json-smart-2.4.10.jar
  17. lang-tag-1.6.jar
  18. lang-tag-1.7.jar
  19. msal4j-1.13.7.jar
  20. msal4j-1.13.8.jar
  21. mssql-jdbc-12.2.0.jre8.jar
  22. netty-buffer-4.1.89.Final.jar
  23. netty-codec-4.1.89.Final.jar
  24. netty-codec-dns-4.1.89.Final.jar
  25. netty-codec-http2-4.1.89.Final.jar
  26. netty-codec-http-4.1.89.Final.jar
  27. netty-codec-socks-4.1.89.Final.jar
  28. netty-common-4.1.89.Final.jar
  29. netty-handler-4.1.89.Final.jar
  30. netty-handler-proxy-4.1.89.Final.jar
  31. netty-resolver-4.1.89.Final.jar
  32. netty-resolver-dns-4.1.89.Final.jar
  33. netty-resolver-dns-classes-macos-4.1.89.Final.jar
  34. netty-resolver-dns-native-macos-4.1.89.Final-osx-x86_64.jar
  35. netty-tcnative-boringssl-static-2.0.56.Final.jar
  36. netty-tcnative-boringssl-static-2.0.56.Final-linux-aarch_64.jar
  37. netty-tcnative-boringssl-static-2.0.56.Final-linux-x86_64.jar
  38. netty-tcnative-boringssl-static-2.0.56.Final-osx-aarch_64.jar
  39. netty-tcnative-boringssl-static-2.0.56.Final-osx-x86_64.jar
  40. netty-tcnative-boringssl-static-2.0.56.Final-windows-x86_64.jar
  41. netty-tcnative-classes-2.0.56.Final.jar
  42. netty-transport-4.1.89.Final.jar
  43. netty-transport-classes-epoll-4.1.89.Final.jar
  44. netty-transport-classes-kqueue-4.1.89.Final.jar
  45. netty-transport-native-epoll-4.1.89.Final-linux-x86_64.jar
  46. netty-transport-native-kqueue-4.1.89.Final-osx-x86_64.jar
  47. netty-transport-native-unix-common-4.1.89.Final.jar
  48. nimbus-jose-jwt-9.22.jar
  49. nimbus-jose-jwt-9.30.2.jar
  50. oauth2-oidc-sdk-9.35.jar
  51. oauth2-oidc-sdk-10.7.1.jar
  52. reactive-streams-1.0.4.jar
  53. reactor-core-3.4.27.jar
  54. reactor-netty-core-1.0.28.jar
  55. reactor-netty-http-1.0.28.jar
  56. slf4j-api-1.7.36.jar

 

So, make sure you've got all dependencies in class path.

0
Avatar
vasily chernov
Created

Also, it's a good idea ask Microsoft to provide sample JAVA app or at least pom.xml for all required libs

0
Avatar
Caspar Due
Created

I was able to make it work using the pom.xml here: https://github.com/microsoft/mssql-jdbc/blob/main/pom.xml

1
Avatar
Amite15
Created

Caspar Due could you please give more details for people who are unfamiliar with how maven/java works?

0
Avatar
vasily chernov
Created

I'll take a look at the provided pom.xml and try to update dependencies accordingly. 
Caspar Due , thank you for update.

0
Avatar
vasily chernov
Created

Amite15 , Caspar Due has added additional jars that were mentioned in pom.xml. 

0
Avatar
Amite15
Created

I eventually found a way to get all the required jars from a pom file (I also already forgot what the command was, but I should have it saved somewhere 🙂). I still had a problem authenticating with the key vault, I am not sure what authentication method I should use. When using SSMS for example, there is interactive authentication involved, but I don't think that would be possible to achieve with DataGrip.

0
Avatar
vasily chernov
Created

Amite15 ,

Command is not important.  It's enough to get jar list. 

By the way, if interactive authentication is required, the driver does support it. 

0

Please sign in to leave a comment.