How to fix "Certificate with thumbprint null not found in certificate store null" when trying to get plaintext from encrypted columns-sql server?

Question

I have a remote SQLserver (2017 ) version, with a database Test_database, which has 1 encrypted column, encrypted using "Always Encrypted Certificate".

How to decrypt the values in this column using JDBC?

I tried exporting the certificate in .pfx format and manually adding it to the certmgr.msc in my system under 'PERSONAL' folder. The process works fine. But, if I try to do this programmatically, am getting the error. This is where I need, some guidance and help.

String connectionUrl = "jdbc:sqlserver://172.24.114.121:1433;database=Test_database;user=sqlSampleLogin;password=myPass;trustServerCertificate=true;encrypt=true;columnEncryptionSetting=Enabled;"
        +"keyStoreAuthentication=JavaKeyStorePassword"
        +";keyStoreLocation="+pfxCertificateLocation
        +";keyStoreSecret==myPass;";

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
connection = DriverManager.getConnection(connectionUrl);
PreparedStatement selectStatement = connection.prepareStatement("Select * from Table_1");
ResultSet results = selectStatement.executeQuery();
while (results.next()) {
    String name = results.getString("name");
    String sName = results.getString("sAMAccountName"); //Error in this line, since its encrypted
    System.out.println(name +"\t"+sName+"\t");
}

Expected Results: Decrypted values of the column "sAMAccountName"
Actual Results: Certificate with thumbprint null not found in certificate store null in certificate location null (SQLServerException)

How to fix "Certificate with thumbprint null not found in certificate store null" when trying to get plaintext from encrypted columns-sql server?

0 Answers0