Always Encrypted with JDBC

[lushoumei]

(1)when we use the following two jars:
[com.microsoft.sqlserver-mssql-jdbc-6.1.0.jre8 and com.microsoft.azure-azure-keyvault-1.0.0]
it occurs "Failed to decrypt a column encryption key. Invalid key store provider name: AZURE_KEY_VAULT."
(2) Then we changed [com.microsoft.azure-azure-keyvault-1.0.0] to [com.microsoft.azure-azure-keyvault-0.9.7]
it occurs "Could not open JDBC Connection for transaction; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: SQL Server instance in use does not support column encryption. "
(3)Finally, we changed [com.microsoft.sqlserver-mssql-jdbc-6.1.0.jre8] to [com.microsoft.sqlserver-mssql-jdbc-6.1.7.jre8-preview], it occurs the same problem as the (1).
(4)The java code we use is:

ExecutorService service = Executors.newFixedThreadPool(10);
		SQLServerKeyVaultAuthenticationCallback authenticationCallback = new SQLServerKeyVaultAuthenticationCallback() {  
		       @Override  
		    public String getAccessToken(String authority, String resource, String scope) {  
		    	   logger.debug("getAccessToken start: authority="+authority+" resource="+resource+" scope="+scope);   
		        AuthenticationResult result = null;  
		        try{  
		                AuthenticationContext context = new AuthenticationContext(authority, false, service);  
		            ClientCredential cred = new ClientCredential(clientID, secret);  
		  
		            Future<AuthenticationResult> future = context.acquireToken(resource, cred, null);  
		            result = future.get();  
		        }  
		        catch(Exception e){  
		        	logger.error("setAzureKeyVaultProvider error:"+e.getStackTrace());
		        }  
		        return result.getAccessToken();  
		    }  
		};  
		
		try {
			SQLServerColumnEncryptionAzureKeyVaultProvider akvProvider = new SQLServerColumnEncryptionAzureKeyVaultProvider(authenticationCallback, service);
		} catch (SQLServerException e) {
			logger.error("setAzureKeyVaultProvider error:"+e.getStackTrace());
		} 

So,which version should we use to solve the problem.

[ajlam]

Hi @lushoumei, thanks for filing this issue. For point #2 - can you please confirm you are using SQL Server 2016 and up?

We'll follow up with the rest of the issues shortly.

[lushoumei]

Hi Andrea Lam, Yes, we use SQL Server 2017. Best regards From: Andrea Lam [mailto:[email protected]] Sent: Wednesday, May 24, 2017 1:41 AM To: Microsoft/mssql-jdbc Cc: Lu, Shoumei; Mention Subject: Re: [Microsoft/mssql-jdbc] Always Encrypted with JDBC (#303) Hi @lushoumei<https://github.com/lushoumei>, thanks for filing this issue. For point #2<#2> - can you please confirm you are using SQL Server 2016 and up? We'll follow up with the rest of the issues shortly. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub<#303 (comment)>, or mute the thread<https://github.com/notifications/unsubscribe-auth/AbdkTZsZvapvMiytkmaSwUAFCa0kEUXJks5r8xovgaJpZM4Nf-zL>.

[lushoumei]

Sorry, we use Azure SQL Database.

[Suraiya-Hameed]

@lushoumei Always Encrypted is supported by Microsoft SQL Azure V12 and above. Can you confirm the Azure version in use with T-SQL SELECT @@VERSION ?

[v-fmeng]

Hi @ajlam & @v-suhame,
I'm the support engineer from Azure SQL database team, I'm currently working on this issue with lushoumei.
For your information, I've verified the Azure Key Vault store provider works fine with the JDBC driver when connecting to the Azure SQL databases.
The error lushoumei received mostly likely is caused by incorrect configurations, we will let you know if we find out the root cause.
But it would be nice if you can share the possible cause of the error.

[xiangyushawn]

Hello @lushoumei @v-fmeng I'm wondering some information of the client machine that the program is running on. Is it a VM on Azure? If the client machie is a VM on Azure cloud, please make sure to use version 6.1.2 or above. We fixed one issue related to using ColumnEncryption on Azure VM #65 . We just realized Point 2 (with driver 6.1.0) could be related to this issue.

[xiangyushawn]

I guess I found the reason. the client code needs to register the key store by calling: SQLServerConnection.registerColumnEncryptionKeyStoreProviders()

To get rid of the error: please use AKV 0.9.7 (and JDBC 6.1.2 or above if on Azure VM), and add the following code after creating SQLServerColumnEncryptionAzureKeyVaultProvider object:

Map<String, SQLServerColumnEncryptionKeyStoreProvider> map1 = new HashMap<String, SQLServerColumnEncryptionKeyStoreProvider>();
map1.put(akvProvider.getName(), akvProvider);
SQLServerConnection.registerColumnEncryptionKeyStoreProviders(map1);

[lushoumei]

The client machine is a VM on Azure cloud and the version is right. We did use AKV 0.9.7 and JDBC 6.1.7.jre8-preview, and we also added the code you offered, but when we tried, it also occurred [Invalid key store provider name: AZURE_KEY_VAULT."]

[xiangyushawn]

what version of OS does the VM have? Could you please also copy the full error stack?

I modified the code you provided a little bit and it is working for me now, could you please run the following code and give us the full error stack?

Please make sure to use key vault after SQLServerConnection.registerColumnEncryptionKeyStoreProviders(map1);.

Thank you.

        ExecutorService service = Executors.newFixedThreadPool(10);
        SQLServerKeyVaultAuthenticationCallback authenticationCallback = new SQLServerKeyVaultAuthenticationCallback() {
            @Override
            public String getAccessToken(String authority,
                    String resource,
                    String scope) {
                AuthenticationResult result = null;
                try {
                    AuthenticationContext context = new AuthenticationContext(authority, false, service);
                    ClientCredential cred = new ClientCredential(applicationClientID, applicationKey);

                    Future<AuthenticationResult> future = context.acquireToken(resource, cred, null);
                    result = future.get();
                }
                catch (Exception e) {
                    e.printStackTrace();
                }
                return result.getAccessToken();
            }
        };

        SQLServerColumnEncryptionAzureKeyVaultProvider akvProvider = new SQLServerColumnEncryptionAzureKeyVaultProvider(authenticationCallback,
                service);

        Map<String, SQLServerColumnEncryptionKeyStoreProvider> map1 = new HashMap<String, SQLServerColumnEncryptionKeyStoreProvider>();
        map1.put(akvProvider.getName(), akvProvider);
        SQLServerConnection.registerColumnEncryptionKeyStoreProviders(map1);

[v-fmeng]

Hi all,
As an update, we've solved this issue for lushoumei.
It turns out the cause is as @v-xiangs suggested, the registerColumnEncryptionKeyStoreProviders() method hasn't been called correctly.
Thanks for your help.

[xiangyushawn]

Thank you for your reply. We are closing this issue now :)

[kartiksubr93]

Hi there, I'm facing the same issue but I use MS SQL Certificate Store. Can you please suggest if the same should work for me as well ?