ADF Oracle connection deprecated version 1.0

Question

ADF Oracle connection deprecated version 1.0

Colinn Calaguas 70

We have an Oracle connection that's been using a host, port and connection type "Oracle SID". This has been existing for years now but we receive a notification that this will be deprecated soon. It is using version 1 as the documentation says we need to move it to version 2. Upon changing, there's no option for Port and connection type "Oracle SID". The only option for auth type is "Basic" in 2.0, so how can we connect using SID?

How to replicate this existing connection we have of version 1.0 to version 2.0?

This is the current connection we have.

Oracle Connection

This is what happens if we switch to version 2.0

Oracle Connection 2

Answer accepted by question author

0 additional answers

Your answer

Answer 1

Chandra Boorla 15,455 Microsoft External Staff Moderator

Hi Colinn Calaguas

Thank you for your question.

You are right - the Oracle linked service Version 1.0, which supports Host, Port, SID, and Connection Type, is being deprecated, and Microsoft recommends migrating to Version 2.0.

In Version 2.0, the UI no longer provides separate fields for Port, SID, or Connection Type. Instead, it uses a single Server field and expects a full connection string. However, you can still connect using SID by manually crafting a TNS-style connection string.

Here's how you can replicate your existing v1.0 SID-based connection in v2.0:

In the Server field (under version 2.0), use the following format:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<your_host>)(PORT=<your_port>))(CONNECT_DATA=(SID=<your_sid>)))

You can continue using Azure Key Vault for storing and retrieving the password as before - no changes are needed there.

Example - If your current v1.0 values are:

Host: 10.1.2.3
Port: 1521
SID: orcl

Then your Server string in v2.0 should be:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.2.3)(PORT=1521))(CONNECT_DATA=(SID=orcl)))

Paste this entire value into the Server field under v2.0.

User's image

For more details, please refer: Copy data from and to Oracle by using Azure Data Factory or Azure Synapse Analytics

I hope this information helps. Please do let us know if you have any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

As your feedback is valuable and can assist others in the community facing similar issues.

Thank you.

Colinn Calaguas 70 Reputation points

2025-07-23T13:38:14.9866667+00:00

This works indeed! Thank you for the detailed explanation.
Colinn Calaguas 70 Reputation points

2025-07-25T02:56:59.5333333+00:00
I have a follow up question. How about if we were to add properties? I've checked the documentation and this Oracle connection needs to use a Service Name. It looks like this

Now I created this TNS

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST= 10.1.2.3)(PORT= 1521))(CONNECT_DATA=(SERVICE_NAME= orcl)))

How can I add the encryptionmethod on this setup?

Not sure what option to select here.
Chandra Boorla 15,455 Reputation points Microsoft External Staff Moderator

2025-07-25T03:34:44.3033333+00:00

Colinn Calaguas

Great follow-up!

You're absolutely right - when using the Oracle linked service (version 2.0), whether you're connecting using a Service Name (via host/port) or a TNS connection string, you can still configure advanced settings like encryptionmethod.

You don’t need to include encryption parameters in the TNS string itself. The correct approach is to use the "Additional connection properties" section in the linked service UI, and Azure Data Factory will handle the connection string formatting under the hood.

If you've already added encryptionmethod = 1 there, you're on the right track - that’s exactly how it’s intended to be configured, and it’s supported as per Microsoft’s documentation.

If you're still experiencing issues after applying this property, feel free to share the error message or behavior you are seeing

I hope this information helps.
Colinn Calaguas 70 Reputation points

2025-07-25T12:18:19.1666667+00:00
Thanks for the answer. I'm not sure what you mean.

In version 1.0, the "Additional connection properties" are freeform text that's why I was able to add encryptionmethod = 1 like shown in the first pic. But whenever I switch to version 2.0 it became a dropdown with options listed below:

Encryption client Encryption types client Crypto checksum client Crypto checksum types client Initial LOB fetch size Fetch size Statement cache size Initialization string Enable bulk load Support V1 data types Fetch TSWTZ as timestamp

based on these selections I'm not sure what to select if I want to add the encryptionmethod. Really appreciate you looking into this.
Chandra Boorla 15,455 Reputation points Microsoft External Staff Moderator

2025-07-25T13:15:20.05+00:00
@Colinn Calaguas

Thank you for the clarification - that makes perfect sense, and I appreciate you pointing it out.

You're right that in version 2.0, the "Additional connection properties" field only allows selecting from a predefined dropdown list, and unlike in version 1.0 and even you can’t manually type in properties like encryptionmethod.

Microsoft has introduced a solution for this scenario.

ADF now supports a new advanced configuration called connectionPropertiesInString, which allows you to define all supported connection properties in JSON format, store that JSON as a secret in Azure Key Vault (AKV), and then reference it in your linked service. This lets you bypass the UI limitations and pass properties that aren’t in the dropdown.

How it works:

Create a JSON object with your connection details and any supported JDBC properties (e.g. oracle.net.encryption_client, oracle.net.crypto_checksum_client, etc.).

Example:

{ "host": "your-oracle-host", "port": 1521, "username": "yourUsername", "password": "yourPassword", "authenticationType": "Basic", "oracle.net.encryption_client": "required", "oracle.net.crypto_checksum_client": "required" }

Store this JSON as a secret in Azure Key Vault.

In your Oracle linked service (v2.0), set the connectionPropertiesInString field to point to that AKV secret using this format:

{ "connectionPropertiesInString": { "type": "AzureKeyVaultSecret", "store": { "referenceName": "YourKeyVaultLinkedService", "type": "LinkedServiceReference" }, "secretName": "YourSecretName" } }

You’ll now be able to use advanced properties that are not exposed in the v2.0 UI, including those required for encryption or custom security settings.

Why this matters for your case:

Since encryptionmethod is not supported directly in v2.0, and the dropdown is limited, this approach allows you to set equivalent modern Oracle properties like:

oracle.net.encryption_client

oracle.net.encryption_types_client

oracle.net.crypto_checksum_client

These are the recommended replacements for encryptionmethod and offer the same (or better) security behavior.

I hope this information helps.
Colinn Calaguas 70 Reputation points

2025-07-26T01:11:28.74+00:00

Let me discuss this with my team first. If I get it correctly we need to create another Azure Key Vault that contains these properties? Cause we have an existing Azure Key Vault that's been used in different connections. If we were to just edit that existing key, wouldn't that affect all the connections using the Azure Key Vault?
Colinn Calaguas 70 Reputation points

2025-07-31T05:19:36.89+00:00
Hi Good day,

We tried several approach but we are still receiving errors. So for this one we didn't include the encryption method.

We used a TNS string as:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST= 10.1.2.3)(PORT= 1521))(CONNECT_DATA=(SERVICE_NAME= orcl)))

and we received this error when we tried to Test the connection.

For our next approach we used the JSON like this

and this one

But both of these have the same error

Are we missing some steps? or did we map the JSON correcly by using the Advanced section for Oracle connection? Like in my last 2 screenshots?
Chandra Boorla 15,455 Reputation points Microsoft External Staff Moderator

2025-08-01T11:48:42.4766667+00:00
Colinn Calaguas

Apologies for any confusion caused by my earlier response.

Thank you again for sharing the details and screenshots of your recent attempts, they’ve been very helpful in understanding the full context.

You're absolutely right to highlight the connectionPropertiesInString approach, Microsoft has introduced this capability to help bypass UI limitations in certain V2 connectors like PostgreSQL, MySQL, and Snowflake. However, Oracle V2.0 does not officially support this configuration as of now, which explains the errors you’re seeing during testing.

Summary of your attempts

Approach 1 – TNS String

The error you received: ORA-50201: Failed to connect to server or failed to parse connect string …suggests that Oracle V2.0 does not accept the TNS string in the Server field. In the current version of the connector, the recommended method is to use the individual fields for:

Host

Port

Connection Type = Oracle Service Name

Service Name

ADF will automatically construct the correct connection string internally, so full TNS strings are not needed and not supported in this context.

Approach 2 & 3 – JSON via advanced > Specify dynamic contents

The error: ErrorCode=11402, The value of the property 'connectionString' is invalid …indicates that Oracle V2.0 does not support overriding connection properties via "connectionString" or "connectionPropertiesInString" using the JSON editor. This approach works only with certain V2 connectors, but not currently with Oracle.

Recommended action - To proceed successfully with Oracle V2.0:

Use the standard UI configuration:

Host

Port

Service Name

Authentication Type = Basic

Password (via Key Vault or inline)

For encryption or checksum settings, use only the predefined options in the “Additional connection properties” dropdown (e.g. encryption client, crypto checksum client, etc.)

Avoid injecting unsupported JSON properties like encryptionMethod or connectionPropertiesInString, as they are not yet compatible with Oracle V2.0 and will result in validation errors.

Feedback

Appreciate if you could share the feedback on our feedback channel. Which would be open for the user community to upvote & comment on. This allows our product teams to effectively prioritize your request against our existing feature backlog and gives insight into the potential impact of implementing the suggested feature.

I hope this information helps.
Colinn Calaguas 70 Reputation points

2025-08-05T09:59:44.7166667+00:00
Thank you for explaining the errors. What do you mean about using Standard UI Configuration? We cannot fill-up Host, Port and Service name fields while on Version 2.0. This is possible on version 1.0 but not on 2.0.

Are you talking about TNS where there's a host, port and service name?

Like this one?

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST= 10.1.2.3)(PORT= 1521))(CONNECT_DATA=(SERVICE_NAME= orcl)))

Share via

ADF Oracle connection deprecated version 1.0

0 additional answers

Your answer