Encrypt data on transit - Oracle OCI to Azure Synapse -

@Xhevahir Mehalla

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.

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.

@Xhevahir Mehalla

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!

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:

https://learn.microsoft.com/en-us/azure/data-factory/connector-oracle?tabs=data-factory#linked-service-properties

This is proving difficult to work out.

Thanks

@Xhevahir Mehalla

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.