How to retrieve the encrypted column as decrypted through ReadyAPI?
I need to verify some data in a column encrypted database. The database is using clientId and clientSecret to authentication to AKV.
To decrypt the database, I used Always Encrypted with the JDBC driver connection
Connection string
"jdbc:sqlserver://<server>:<port>;columnEncryptionSetting=Enabled;keyStoreAuthentication=KeyVaultClientSecret;keyStorePrincipalId=<clientId>;keyStoreSecret=<clientSecret>"
Database driver: Microsoft JDBC "mssql-jdbc-9.2.1.jre11.jar"
ReadyAPI version: 3.8.0
The first connection try was connected successfully. The second connection try failed and popped up an error "Failed to get a connection for the specified properties; com.microsoft.sqlserver.jdbc.SQLServerException: Key store providers cannot be set more than once."
In the first connection, if I run the query to try retrieving encrypted column, it will be hanging there; but if I tried retriving non-encrypted column, it worked and could return the column data.
Did anyone experience this situation?
- Hey RoseYang,
Ive got no experience with column level encrypted databases, however i suggest using the same approach to diagnose the problem and the solution which i always use when setting up a new db connection in readyapi/soapui.
You need to remember that readyapi/soapui isnt really doing any of the db connection work....its all done by the driver and the connection parameters youre specifying in the connection string/url.
With this in mind, try and create a db connection in any other db interrogation tool (e.g. TOAD, DBVisualiser, etc.) using the jdbc connection string and the relevant driver. This will prove whether this issue is a readyapi/soapui issue (which i doubt) or whether you might need to alter your connection string parameters slightly or even alter the driver you need (which is highly likely)
Have you checked that the driver is the correct version for the instance of db youre connecting to?
As i state above, ive used this approach whenever setting up a new db connection and it helps cos it isolates out readyapi/soapui from the problem allowing you to focus on gettimg the driver/connection string parameters sorted which is where the issue is most likely to be.
Oh yeah, im assuming youve already done this but just in case, confirm the driver files you need (sqlserver depending on connection type needs >1 file), then confirm the version of jre/jdk you need to run the drivers. You also need to ensure if your readyapi/soapui is 32bit, then you use 32bit jre/jdk. If readyapi/soapui is 64bit then you need 64bit jre/jdk.
Ta
Rich Hi richie,
Thank you so much for your help! I have checked everything you mentioned. They were all good.
The main issue was caused by missing jar files. When some jar files were missing, it did not show in ReadyAPI logs, so it was just hanging there. However, if I run readyapi.bat from command line, it will show the logs there.
I add jar files one by one based on the prompt in logs, then it worked.
