Unable to access oracle database from a port using SSL Certificate from Synapse
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
Azure Key Vault
Azure Synapse Analytics
-
phemanth 10,330 Reputation points • Microsoft Vendor
2024-09-25T09:46:24.1533333+00:00 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
andsqlnet.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
orOracle 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.
- Ensure that the
-
phemanth 10,330 Reputation points • Microsoft Vendor
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 10,330 Reputation points • Microsoft Vendor
2024-09-26T12:59:21.52+00:00 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
andYes
for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members. -
phemanth 10,330 Reputation points • Microsoft Vendor
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 10,330 Reputation points • Microsoft Vendor
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.
Sign in to comment