cancel
Showing results for 
Search instead for 
Did you mean: 

How to retrieve the encrypted column as decrypted through ReadyAPI?

SOLVED
RoseYang
New Contributor

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 driverMicrosoft 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?

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
richie
Community Hero

Re: Can ReadyAPI connect to a column encrypted database and retrieve the encrypted column as decrypt

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
if this helped answer the post, could you please mark it as 'solved'? Also if you consider whether the title of your post is relevant? Perhaps if the post is solved, it might make sense to update the Subject header field of the post to something more descriptive? This will help people when searching for problems. Ta

View solution in original post

RoseYang
New Contributor

Re: Can ReadyAPI connect to a column encrypted database and retrieve the encrypted column as decrypt

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.

 

 

 

View solution in original post

2 REPLIES 2
richie
Community Hero

Re: Can ReadyAPI connect to a column encrypted database and retrieve the encrypted column as decrypt

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
if this helped answer the post, could you please mark it as 'solved'? Also if you consider whether the title of your post is relevant? Perhaps if the post is solved, it might make sense to update the Subject header field of the post to something more descriptive? This will help people when searching for problems. Ta

View solution in original post

RoseYang
New Contributor

Re: Can ReadyAPI connect to a column encrypted database and retrieve the encrypted column as decrypt

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.

 

 

 

View solution in original post

New Here?
Join us and watch the welcome video:
Announcements
Top Kudoed Authors