Unable to access oracle database from a port using SSL Certificate from Synapse

Question

Unable to access oracle database from a port using SSL Certificate from Synapse

Xhevahir Mehalla 25

Hello -

I am struggling to get something working.

Here's the setup:

Use synapse to connect to Oracle OCI database - using port 1521 no encryption and is working fine
Oracle dba creating a new port 2484 which is encrypted
Azure team received the server_ca.cert file from Oracle team
We placing the about file on the VM where are running self-hosted Integration RunTime under c:\app\wallet\server_ca.cert file.
I am trying different ways to create a service link but I cannot make it work.
I use the Key vault to create the connection strings like this: Host=dbname;Port=2484;ServiceName=servicename;User Id=user;Password=user;EncryptionMethod=1;TrustStore=C:\app\wallet;
I tried to create the service link and I get unable to access the TrustStore.
I even to trust to add the ca on the connection string like this: Host=dbname;Port=2484;ServiceName=servicename;User Id=user;Password=user;EncryptionMethod=1;TrustStore=data:// -----BEGIN CERTIFICATE-----certificate-----END CERTIFICATE----- but gives me userName and password error message which is not true.

I can provide you the script if that helps.

Not sure what am I doing wrong.

Please can you help me with this.

Thanks

Xhev

phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-09-25T09:46:24.1533333+00:00
@Xhevahir Mehalla

Thanks for reaching out to Microsoft Q&A.:

please check the below steps

Ensure that the server_ca.cert file is correctly placed in the specified directory (C:\app\wallet\).

Check the permissions of the wallet files to ensure they are accessible by the Integration Runtime.

Make sure your connection string is correctly formatted. It should include the path to the trust store and the correct encryption method. For example:
Host=dbname;Port=2484;ServiceName=servicename;User Id=user;Password=user;EncryptionMethod=1;TrustStore=C:\app\wallet\server_ca.cert;

Verify the listener.ora and sqlnet.ora files on the Oracle server to ensure they are configured to use SSL/TLS on port 2484.

Ensure that the SSL version and cipher suites are compatible between the client and server.

Enable SQLNet tracing on the Oracle server and client to capture detailed logs. This can help identify where the connection is failing.

Use a tool like sqlplus or Oracle SQL Developer to test the connection independently of Synapse. This can help isolate whether the issue is with the database configuration or the Synapse setup.

Ensure that there are no firewalls or network security groups blocking traffic on port 2484.

If these steps don’t resolve the issue, please share any error messages or logs you have. This additional information can help diagnose the problem more accurately.

1: Configuration of TCP/IP with SSL and TLS for Database Connections 2: How To Investigate And Troubleshoot SSL/TLS Issues on the Database And …

3: Troubleshoot common connection errors for your RDS for Oracle instance

Hope this helps. Do let us know if you any further queries.
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-09-26T12:44:07.6733333+00:00

@Xhevahir Mehalla We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Xhevahir Mehalla 25 Reputation points

2024-09-26T12:52:27.2966667+00:00

I have used a different version of this.

I created the Oracele Connection String as Secret in the key . I added the CA certificate on the Connection string as ---- begin certificate ----- end certificate and seem to work ok.

Not sure which one is the best way to follow but at least I found one way how to do it.

Thanks for your help and your suggestions.

Thanks

Xhev
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-09-26T12:59:40.18+00:00

@Xhevahir Mehalla Glad to know your issue has been resolved. Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others "I'll repost your solution in case you'd like to accept the answer.
Xhevahir Mehalla 25 Reputation points

2024-09-26T15:48:46.6966667+00:00

Hi , I think I spoke too early.

Although the connection shows that is created and connected BUT when I tried to extract data from Oracle I get the error message like this:

{ "errorCode": "2200", "message": "Failure happened on 'Source' side. ErrorCode=InvalidParameter,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The value of the property '' is invalid: 'Connection string exceeds maximum allowed length of 1024.'.,Source=,''Type=System.ArgumentException,Message=Connection string exceeds maximum allowed length of 1024.,Source=System.Data,'", "failureType": "UserError", "target": "Dynamic_Query_And_ExtractCSV", "details": [] }

I checked that connection string is 1289 char which is more than 1024.

How do I go about to fix this?

I tried another method by placing the CA certificate on the VM where Self-hosted InteggrationRunTime is installed but that gives me Invalid username or password .

This is has been tested as well on sqlplus and it works but on Synpase does not work.

I am not sure what would be the workaround anymore.

Pls advise!
Xhevahir Mehalla 25 Reputation points

2024-09-26T15:51:20.69+00:00

when I run your suggestion : Host=dbname;Port=2484;ServiceName=servicename;User Id=user;Password=user;EncryptionMethod=1;TrustStore=C:\app\wallet\server_ca.cert;

I get this error message:

ERROR [28000] [Microsoft][ODBC Oracle Wire Protocol driver][Oracle]ORA-01017: invalid username/password; logon denied ERROR [28000] [Microsoft][ODBC Oracle Wire Protocol driver][Oracle]ORA-01017: invalid username/password; logon denied
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-09-27T14:56:58.1366667+00:00
@Xhevahir Mehalla
The error message indicates that your connection string exceeds the maximum allowed length of 1024 characters. Here are a few strategies to address this:

Use Environment Variables:

Store parts of your connection string in environment variables and reference them in your connection setup. This can help reduce the length of the connection string.

Key Vault References:

Use Azure Key Vault to store sensitive parts of your connection string (like the certificate) and reference them in your connection string. This can help keep the connection string within the allowed length.

Simplify Connection String:

Ensure that your connection string only includes necessary parameters. Remove any redundant or unnecessary parts.

Invalid Username/Password Error

The ORA-01017: invalid username/password; logon denied error suggests that there might be an issue with the credentials or how they are being passed. Here are some steps to troubleshoot this:

Double-Check Credentials:

Ensure that the username and password are correct and have the necessary permissions.

Encoding Issues:

Sometimes, special characters in the username or password can cause issues. Ensure that they are properly encoded.

Test Independently:

Since you mentioned that it works with sqlplus, double-check the exact credentials and connection string used there and ensure they match what you’re using in Synapse.

Trust Store Path:

Ensure that the path to the trust store is correct and accessible by the Integration Runtime.

Combining Both Solutions

Given the length of your connection string, you might need to combine both strategies:

Use Azure Key Vault to store the certificate and other sensitive parts. Reference these parts in your connection string to keep it within the allowed length.

Here’s an example of how you might structure your connection string using Key Vault references:

Host=dbname;Port=2484;ServiceName=servicename;User Id=user;Password=user;EncryptionMethod=1;TrustStore=@Microsoft.KeyVault(SecretUri=https://<YourKeyVaultName>.vault.azure.net/secrets/<YourSecretName>);

Next Steps

Simplify and Test:

Simplify your connection string and test each part independently to ensure it works.

Logs and Tracing:

Enable detailed logging and tracing to capture more information about where the connection is failing.

1 answer

Your answer

phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-09-25T09:46:24.1533333+00:00

@Xhevahir Mehalla

Thanks for reaching out to Microsoft Q&A.:

please check the below steps

Ensure that the server_ca.cert file is correctly placed in the specified directory (C:\app\wallet\).

Check the permissions of the wallet files to ensure they are accessible by the Integration Runtime.

Make sure your connection string is correctly formatted. It should include the path to the trust store and the correct encryption method. For example:
Host=dbname;Port=2484;ServiceName=servicename;User Id=user;Password=user;EncryptionMethod=1;TrustStore=C:\app\wallet\server_ca.cert;

Verify the listener.ora and sqlnet.ora files on the Oracle server to ensure they are configured to use SSL/TLS on port 2484.

Ensure that the SSL version and cipher suites are compatible between the client and server.

Enable SQLNet tracing on the Oracle server and client to capture detailed logs. This can help identify where the connection is failing.

Use a tool like sqlplus or Oracle SQL Developer to test the connection independently of Synapse. This can help isolate whether the issue is with the database configuration or the Synapse setup.

Ensure that there are no firewalls or network security groups blocking traffic on port 2484.

If these steps don’t resolve the issue, please share any error messages or logs you have. This additional information can help diagnose the problem more accurately.

1: Configuration of TCP/IP with SSL and TLS for Database Connections 2: How To Investigate And Troubleshoot SSL/TLS Issues on the Database And …

3: Troubleshoot common connection errors for your RDS for Oracle instance

Hope this helps. Do let us know if you any further queries.
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-09-26T12:44:07.6733333+00:00

@Xhevahir Mehalla We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Xhevahir Mehalla 25 Reputation points

2024-09-26T12:52:27.2966667+00:00

I have used a different version of this.

I created the Oracele Connection String as Secret in the key . I added the CA certificate on the Connection string as ---- begin certificate ----- end certificate and seem to work ok.

Not sure which one is the best way to follow but at least I found one way how to do it.

Thanks for your help and your suggestions.

Thanks

Xhev
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-09-26T12:59:40.18+00:00

@Xhevahir Mehalla Glad to know your issue has been resolved. Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others "I'll repost your solution in case you'd like to accept the answer.
Xhevahir Mehalla 25 Reputation points

2024-09-26T15:48:46.6966667+00:00

Hi , I think I spoke too early.

Although the connection shows that is created and connected BUT when I tried to extract data from Oracle I get the error message like this:

{ "errorCode": "2200", "message": "Failure happened on 'Source' side. ErrorCode=InvalidParameter,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The value of the property '' is invalid: 'Connection string exceeds maximum allowed length of 1024.'.,Source=,''Type=System.ArgumentException,Message=Connection string exceeds maximum allowed length of 1024.,Source=System.Data,'", "failureType": "UserError", "target": "Dynamic_Query_And_ExtractCSV", "details": [] }

I checked that connection string is 1289 char which is more than 1024.

How do I go about to fix this?

I tried another method by placing the CA certificate on the VM where Self-hosted InteggrationRunTime is installed but that gives me Invalid username or password .

This is has been tested as well on sqlplus and it works but on Synpase does not work.

I am not sure what would be the workaround anymore.

Pls advise!
Xhevahir Mehalla 25 Reputation points

2024-09-26T15:51:20.69+00:00

when I run your suggestion : Host=dbname;Port=2484;ServiceName=servicename;User Id=user;Password=user;EncryptionMethod=1;TrustStore=C:\app\wallet\server_ca.cert;

I get this error message:

ERROR [28000] [Microsoft][ODBC Oracle Wire Protocol driver][Oracle]ORA-01017: invalid username/password; logon denied ERROR [28000] [Microsoft][ODBC Oracle Wire Protocol driver][Oracle]ORA-01017: invalid username/password; logon denied

Answer 1

@Xhevahir Mehalla

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer .

**Ask:**I am struggling to get something working.

Here's the setup:

Use synapse to connect to Oracle OCI database - using port 1521 no encryption and is working fine
Oracle dba creating a new port 2484 which is encrypted
Azure team received the server_ca.cert file from Oracle team
We placing the about file on the VM where are running self-hosted Integration RunTime under c:\app\wallet\server_ca.cert file.
I am trying different ways to create a service link but I cannot make it work.
I use the Key vault to create the connection strings like this: Host=dbname;Port=2484;ServiceName=servicename;User Id=user;Password=user;EncryptionMethod=1;TrustStore=C:\app\wallet;
I tried to create the service link and I get unable to access the TrustStore.
I even to trust to add the ca on the connection string like this: Host=dbname;Port=2484;ServiceName=servicename;User Id=user;Password=user;EncryptionMethod=1;TrustStore=data:// -----BEGIN CERTIFICATE-----certificate-----END CERTIFICATE----- but gives me userName and password error message which is not true.

I can provide you the script if that helps.

Not sure what am I doing wrong.

Solution:I have used a different version of this.

I created the Oracele Connection String as Secret in the key . I added the CA certificate on the Connection string as ---- begin certificate ----- end certificate and seem to work ok.

Not sure which one is the best way to follow but at least I found one way how to do it.

Thanks for your help and your suggestions.

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.

Share via

Unable to access oracle database from a port using SSL Certificate from Synapse

1 answer

Your answer