Incomplete driver for Azure SQL Database prevents using Always Encrypted

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

Hello,

Have you tried to install Microsoft Azure Toolkit for IntelliJ?

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.

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.

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

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

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

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

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.

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. 

vasily chernov What I mean regarding interactive authentication is that authentication against the key vault is interactive. With SSMS for example, I only tick the “always encrypted” option, and then when I query an encrypted column I get an interactive dialog to authenticate against the key vault. I assume this requires extra development on DataGrip side to allow that. Interactive auth against the DB itself works fine. 

I managed to get Always Encrypted working with a key stored in Azure Key Vault. After adding the required jar files, I used an App Registration client ID and secret to access the key vault. I didn't see any other option to work with jdbc and a key stored in key vault.

My issue now is that the secret is saved as plain text on my machine, since all connection properties are saved as plain text in .idea folder. It gets even worse because I have a git repository where I save my workspace. What would be a good way (if any) to avoid saving the secret as plain text?

Amite15 , could you share jar files list? Have you specified your secret in JDBC URL? 

I can make them accessible with Azure SQL Database JDBC driver out of the box.