Encrypt data on transit - Oracle OCI to Azure Synapse -
Hello -
We are implementing a solution like this:
- Extract data from Oracle OCI database use Azure Synapse Analytics
- Store Data to Data lake gen 2
- We have created linked service in Synapse to connect to oracle OCI db
We have been asked how to make sure that data is encrypted between Oracle OCI and Synapse.
We have suggested that to create oracle wallet, create a new port on on oracle side 2484. This is done
Oracle team send me a ca certificate with this extension .cert but I don't know where to place that on Synapse side.
The ask is how can make sure we use SSL/TLS to make sure we have a secure encrypted when we transferring data from oracle to datalake using Synapse pipelines.
Please can you help!
Thanks
Xhev
Azure Synapse Analytics
-
phemanth 10,330 Reputation points • Microsoft Vendor
2024-09-20T13:22:43.8766667+00:00 Thanks for reaching out to Microsoft Q&A
To ensure secure encrypted data transfer between Oracle OCI and Azure Synapse using SSL/TLS, follow these steps:
Oracle Wallet Configuration:
You’ve already created the Oracle wallet and set up the port 2484, which is great.
CA Certificate on Synapse:
The CA certificate you received needs to be placed in a location where Synapse can access it. Typically, this involves configuring the linked service in Synapse to use the certificate for SSL/TLS.
Configure Linked Service in Synapse:
In Azure Synapse, go to the Linked Services section.
Edit the linked service that connects to your Oracle OCI database.
In the Advanced section, you should find options to specify SSL/TLS settings.
Upload the CA certificate (.cert file) here.
Verify SSL/TLS Connection:
Ensure that the connection string in your linked service includes parameters to enforce SSL/TLS. For example, you might need to add
ssl=true
and specify the path to the CA certificate.Test the Connection:
After configuring the linked service, test the connection to ensure that the data transfer is encrypted.
For detailed steps and best practices, you can refer to the Microsoft documentation on data encryption best practices.
Hope this helps. Do let us know if you any further queries.
-
Xhevahir Mehalla 25 Reputation points
2024-09-20T15:20:40.0033333+00:00 Hi
Thanks for your input.
However I need to know I cannot find this option on advanced settings: " In the Advanced section, you should find options to specify SSL/TLS settings."
Where do I upload the certificate.
This is what I see when I check on the current linked service.
-
phemanth 10,330 Reputation points • Microsoft Vendor
2024-09-24T17:03:01.3733333+00:00 Thanks for sharing the screenshot!
To upload the CA certificate and ensure SSL/TLS encryption, you can follow these steps:
Azure Key Vault:
- Store your CA certificate in Azure Key Vault. This is a secure way to manage your certificates.
- If you haven’t already, create a Key Vault and upload your certificate there. You can follow the instructions here to upload a certificate to Azure Key Vault.
Linked Service Configuration:
- In your Azure Synapse workspace, go to the linked service configuration for Oracle.
- Instead of directly uploading the certificate in the linked service, you can reference the certificate stored in Azure Key Vault.
- Use the following JSON configuration in the advanced settings to reference the certificate from Key Vault:
{ "type": "Microsoft.Synapse/workspaces/linkedservices", "properties": { "type": "Oracle", "typeProperties": { "connectionString": "your_connection_string", "encryptedCredential": "your_encrypted_credential", "ssl": { "caCert": { "type": "AzureKeyVault", "keyVaultUrl": "https://your-keyvault-name.vault.azure.net/", "secretName": "your-certificate-name" } } } } }
- After updating the linked service configuration, test the connection to ensure that the SSL/TLS settings are correctly applied.
If you need more detailed steps or run into any issues, feel free to ask!
-
Xhevahir Mehalla 25 Reputation points
2024-09-25T10:06:49.0633333+00:00 Thanks, that's useful but I still can't figure out what to fill these two lines
"connectionString": "your_connection_string", "encryptedCredential": "your_encrypted_credential",
Do I build the ConnectionString ? how do I do that?
I am using this link as well but even this cannot get working:
This is proving difficult to work out.
Thanks
-
phemanth 10,330 Reputation points • Microsoft Vendor
2024-09-26T13:06:23.64+00:00 I understand that configuring the connection string and encrypted credentials can be tricky. Let’s break it down:
Building the Connection String
For Oracle, the connection string typically includes the host, port, service name, user ID, and password. Here’s a basic template:
Host=<your_host>;Port=<your_port>;Service Name=<your_service_name>;User Id=<your_user_id>;Password=<your_password>;EncryptionMethod=1;TrustStore=<path_to_your_certificate>
Example
If your Oracle database details are as follows:
- Host:
oracle.example.com
- Port:
2484
- Service Name:
orcl
- User ID:
your_user
- Password:
your_password
- Path to CA Certificate:
data://-----BEGIN CERTIFICATE-----<certificate_content>-----END CERTIFICATE-----
Your connection string would look like this:
Host=oracle.example.com;Port=2484;Service Name=orcl;User Id=your_user;Password=your_password;EncryptionMethod=1;TrustStore=data://-----BEGIN CERTIFICATE-----<certificate_content>-----END CERTIFICATE-----
Encrypted Credential
For the
encryptedCredential
, you need to securely store your credentials. Azure Synapse allows you to use Azure Key Vault for this purpose. Here’s how you can do it:Store Credentials in Azure Key Vault:
- Store your Oracle database credentials (user ID and password) in Azure Key Vault.
Reference the Key Vault in Synapse:
- In your linked service configuration, reference the stored credentials from Azure Key Vault.
Example JSON Configuration
{ "type": "Microsoft.Synapse/workspaces/linkedservices", "properties": { "type": "Oracle", "typeProperties": { "connectionString": "Host=oracle.example.com;Port=2484;Service Name=orcl;User Id=your_user;Password=your_password;EncryptionMethod=1;TrustStore=data://-----BEGIN CERTIFICATE-----<certificate_content>-----END CERTIFICATE-----", "encryptedCredential": { "type": "AzureKeyVault", "keyVaultUrl": "https://your-keyvault-name.vault.azure.net", "secretName": "your_secret_name" }, "ssl": { "caCert": { "type": "AzureKeyVault", "keyVaultUrl": "https://your-keyvault-name.vault.azure.net", "secretName": "your_certificate_name" } } } } }
You can refer to the Microsoft documentation for more details on configuring the linked service properties1.
- Host:
-
phemanth 10,330 Reputation points • Microsoft Vendor
2024-09-27T13:54:38.1333333+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-28T21:12:20.0833333+00:00 Hi again,
We have tried this option using the same steps but we still have the same error message:
We did this:
- Create a secret for just the server_ca.cert certificate
- Create a new secret to store the OracleConnectionString holding these info (Host=hostname;Port=2484;ServiceName=sname;User Id=user;Password=password;EncryptionMethod=1;TrustStore=@Microsoft.KeyVault(SecretUri=https://kv.vault.azure.net/secrets/OracleCertificateString);
- Create a Link Service for Oracle using the KeyVault from the step 2.
The error message is the same : cannot load the trust store.
We created tnsanmes.ora on VM where Self-hosted is installed. Installed the Oracle client . When I run sqlplus then I can manage to connect to the oracle database.
How come when I try from Synapse we cannot see the certificate.
We run out of options what else to change.
Please can you help!
Thanks
Sign in to comment