Connecting to On-Prem SQL Server through Change Data Capture (ADF) Issue

Question

Connecting to On-Prem SQL Server through Change Data Capture (ADF) Issue

Jason Sherbondy 0

So I have been trying to get CDC working for the past two days but can't seem to get it to connect to my on-premise SQL Server as it seems to have issues using self hosted integration runtime inside the CDC wizard.

I am trying to do incremental loads from my on-premise SQL Server to Azure SQL database so that 5 tables will keep in sync with the production database (OLTP) so that I can build out my OLAP system but can't get the first step.

Could someone please give me some clarification on what is going on here and the best way to keep my tables in sync between these two systems.

Chandra Boorla 15,455 Reputation points Microsoft External Staff Moderator

2025-06-04T23:16:52.3233333+00:00
@Jason Sherbondy

It sounds like you're off to a solid start trying to set up incremental loads using Change Data Capture (CDC) in Azure Data Factory (ADF) but are running into issues with the Self-Hosted Integration Runtime (SHIR). Here are a few key things to check to get the connection working smoothly:

Verify SHIR Setup -

Install SHIR on a machine that can access your on-prem SQL Server.
In ADF, create a linked service using SHIR and click “Test Connection” to ensure it's successful.
Make sure SHIR can access Azure endpoints and your SQL Server without being blocked by firewalls or proxies.

Enable CDC on Your On-Prem SQL Server -

Run the following SQL commands -

-- Enable CDC at the database level EXEC sys.sp_cdc_enable_db; -- Enable CDC on specific tables EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'YourTableName', @role_name = N'cdc_admin', @supports_net_changes = 1;

Ensure the SQL Server Agent is running; it’s required for CDC to function.
Make sure your SQL Server edition supports CDC (Standard or Enterprise edition depending on the version).

Check Table Requirements -

Each table must have a primary key.
The table must have ongoing changes (inserts/updates/deletes) for CDC to track.
Try performing a few test updates after enabling CDC to validate it’s working.

Confirm Network Connectivity -

Enable remote connections in SQL Server:

In SSMS: Server > Properties > Connections > "Allow remote connections to this server"

In SQL Server Configuration Manager:

Go to SQL Server Network Configuration, and ensure TCP/IP is enabled.

Check the port (usually 1433) and ensure it's allowed in your firewall.

Monitor ADF Pipelines -

Use the Monitor tab in ADF to track pipeline runs and errors.
Pay attention to any authentication issues, network failures, or CDC-specific errors in the logs.

Use Workarounds if CDC Wizard Fails -

If the CDC wizard in ADF continues to fail:

Use a watermark column (e.g., LastModifiedDate) for incremental logic.

Alternatively, use cdc.fn_cdc_get_all_changes_<capture_instance>() in a stored procedure.

Build a pipeline using Lookup + Copy or Mapping Data Flows to sync data manually.

If the issue still persists, please let me know what error or behavior you're seeing in the CDC wizard or ADF monitor.

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

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.

Thank you.
Jason Sherbondy 0 Reputation points

2025-06-05T15:47:15.36+00:00

Hi, This might be a longer thread but I wanted to confirm that I can move data from On prem SQL Server to Azure database through a copy activity so everything is able to connect but I guess the main question I want to find out at least for now is it even possible for the CDC (Preview) to connect to on-prem SQL Server 2019 as is creating a connection in the wizard the SHIR option is grayed out.

At least if I know it is possible (I know its a preview version) then it would help me greatly so I can continue to go down this route but if I think that is not possible then I will go down the route of CDC functionality but that might have issues down the road and is much more difficult to setup.
Chandra Boorla 15,455 Reputation points Microsoft External Staff Moderator

2025-06-05T17:56:21.1566667+00:00
@Jason Sherbondy

Thanks for the detailed question, you're absolutely on the right track, and I completely understand the confusion around the CDC (Preview) setup with on-prem SQL Server.

To clarify:

Yes, Azure Data Factory’s Change Data Capture (CDC - Preview) does support on-premises SQL Server 2019 using Self-hosted Integration Runtime (SHIR).

However, the reason you're seeing the SHIR option grayed out in the CDC wizard is due to a known UI limitation, especially when:

You're working in Git mode instead of Live mode.

A SHIR linked service hasn’t been pre-created.

SHIR is not yet installed/registered in the Data Factory

Recommended Steps:

Switch your ADF to Live Mode (CDC wizard currently doesn't fully work in Git mode).
Ensure SHIR is already installed, registered, and running.
Create a linked service manually to your on-prem SQL Server using SHIR.
Then relaunch the CDC wizard, SHIR should now be selectable.

If that still doesn't work, you can fall back to a manual CDC approach using:

Use cdc.fn_cdc_get_all_changes_<capture_instance>() in a stored procedure.
Build pipelines using Lookup + Copy or Mapping Data Flows.
Use watermark columns like LastModifiedDate for incremental sync

Conclusion:

You're not blocked, the functionality is supported, and you're just hitting a preview-time limitation in the UI. Once you switch to Live Mode and ensure SHIR is properly configured, you should be able to proceed.

I hope this information helps.
Jason Sherbondy 0 Reputation points

2025-06-05T20:38:45.7333333+00:00
I want to show you pictures to make sure I troubleshoot this issue correctly. If you could please instruct further as I have been at very long time. Thank you for your patience as we really need this functionality to work. (to keep source table in sync INSERT, UPDATES and DELETES at least once a day)

You're working in Git mode instead of Live mode. ( I believe I switched to Live Mode and still grayed out)

A SHIR linked service hasn’t been pre-created. (Is this not the way it is supposed to be? And FYI I tray the CDC after this and it still didn't work)

SHIR is not yet installed/registered in the Data Factory
Chandra Boorla 15,455 Reputation points Microsoft External Staff Moderator

2025-06-05T21:59:03.7933333+00:00

@Jason Sherbondy

Apologies for the inconvenience.

Please reach out to our support team to gain deeper insights and explore potential solutions. It's highly recommended that you reach out to our support team. Their expertise will be invaluable in suggesting the most appropriate approach.

Thank you.
Jason Sherbondy 0 Reputation points

2025-06-06T12:30:03.7266667+00:00

How would I reach your support team?
Chandra Boorla 15,455 Reputation points Microsoft External Staff Moderator

2025-06-09T20:09:33.45+00:00

@Jason Sherbondy

How to contact Microsoft Azure Support:

Go to the Azure Portal
Click the "Show portal menu" icon in the top-left corner
Select “Help + support”
Click “Create a support request”
Describe your issue by filling the details:

Thank you.
Jason Sherbondy 0 Reputation points

2025-06-16T19:31:44.2066667+00:00

So I tried that and it didnt connect me with any personal but instead connected me a Q and A page which I don't think will solve my issue. How do I get a representative to help me as this issue needs specialized help ? This is an urgent matter. Thank you.
Chandra Boorla 15,455 Reputation points Microsoft External Staff Moderator

2025-06-17T21:29:35.3733333+00:00

@Jason Sherbondy

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.

Also, please try to check your private message, as we’ve shared additional details there that might assist you further.
Jason Sherbondy 0 Reputation points

2025-06-18T12:36:27.9533333+00:00

Hi,

I do not have a solution as of yet but i have been talking with a rep about setting up a time for a conversation to discuss.
Chandra Boorla 15,455 Reputation points Microsoft External Staff Moderator

2025-06-23T18:58:37.2633333+00:00

@Jason Sherbondy

Just checking in to see if the below suggestion helped. 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.

2 answers

Your answer

Jason Sherbondy 0 Reputation points

2025-06-05T15:47:15.36+00:00

Hi, This might be a longer thread but I wanted to confirm that I can move data from On prem SQL Server to Azure database through a copy activity so everything is able to connect but I guess the main question I want to find out at least for now is it even possible for the CDC (Preview) to connect to on-prem SQL Server 2019 as is creating a connection in the wizard the SHIR option is grayed out.

At least if I know it is possible (I know its a preview version) then it would help me greatly so I can continue to go down this route but if I think that is not possible then I will go down the route of CDC functionality but that might have issues down the road and is much more difficult to setup.
Chandra Boorla 15,455 Reputation points Microsoft External Staff Moderator

2025-06-05T17:56:21.1566667+00:00

@Jason Sherbondy

Thanks for the detailed question, you're absolutely on the right track, and I completely understand the confusion around the CDC (Preview) setup with on-prem SQL Server.

To clarify:

Yes, Azure Data Factory’s Change Data Capture (CDC - Preview) does support on-premises SQL Server 2019 using Self-hosted Integration Runtime (SHIR).

However, the reason you're seeing the SHIR option grayed out in the CDC wizard is due to a known UI limitation, especially when:

You're working in Git mode instead of Live mode.

A SHIR linked service hasn’t been pre-created.

SHIR is not yet installed/registered in the Data Factory

Recommended Steps:

Switch your ADF to Live Mode (CDC wizard currently doesn't fully work in Git mode).
Ensure SHIR is already installed, registered, and running.
Create a linked service manually to your on-prem SQL Server using SHIR.
Then relaunch the CDC wizard, SHIR should now be selectable.

If that still doesn't work, you can fall back to a manual CDC approach using:

Use cdc.fn_cdc_get_all_changes_<capture_instance>() in a stored procedure.
Build pipelines using Lookup + Copy or Mapping Data Flows.
Use watermark columns like LastModifiedDate for incremental sync

Conclusion:

You're not blocked, the functionality is supported, and you're just hitting a preview-time limitation in the UI. Once you switch to Live Mode and ensure SHIR is properly configured, you should be able to proceed.

I hope this information helps.
Jason Sherbondy 0 Reputation points

2025-06-05T20:38:45.7333333+00:00

I want to show you pictures to make sure I troubleshoot this issue correctly. If you could please instruct further as I have been at very long time. Thank you for your patience as we really need this functionality to work. (to keep source table in sync INSERT, UPDATES and DELETES at least once a day)

You're working in Git mode instead of Live mode. ( I believe I switched to Live Mode and still grayed out)

A SHIR linked service hasn’t been pre-created. (Is this not the way it is supposed to be? And FYI I tray the CDC after this and it still didn't work)

SHIR is not yet installed/registered in the Data Factory
Chandra Boorla 15,455 Reputation points Microsoft External Staff Moderator

2025-06-05T21:59:03.7933333+00:00

@Jason Sherbondy

Apologies for the inconvenience.

Please reach out to our support team to gain deeper insights and explore potential solutions. It's highly recommended that you reach out to our support team. Their expertise will be invaluable in suggesting the most appropriate approach.

Thank you.
Jason Sherbondy 0 Reputation points

2025-06-06T12:30:03.7266667+00:00

How would I reach your support team?
Chandra Boorla 15,455 Reputation points Microsoft External Staff Moderator

2025-06-09T20:09:33.45+00:00

@Jason Sherbondy

How to contact Microsoft Azure Support:

Go to the Azure Portal
Click the "Show portal menu" icon in the top-left corner
Select “Help + support”
Click “Create a support request”
Describe your issue by filling the details:

Thank you.
Jason Sherbondy 0 Reputation points

2025-06-16T19:31:44.2066667+00:00

So I tried that and it didnt connect me with any personal but instead connected me a Q and A page which I don't think will solve my issue. How do I get a representative to help me as this issue needs specialized help ? This is an urgent matter. Thank you.
Chandra Boorla 15,455 Reputation points Microsoft External Staff Moderator

2025-06-17T21:29:35.3733333+00:00

@Jason Sherbondy

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.

Also, please try to check your private message, as we’ve shared additional details there that might assist you further.
Jason Sherbondy 0 Reputation points

2025-06-18T12:36:27.9533333+00:00

Hi,

I do not have a solution as of yet but i have been talking with a rep about setting up a time for a conversation to discuss.
Chandra Boorla 15,455 Reputation points Microsoft External Staff Moderator

2025-06-23T18:58:37.2633333+00:00

@Jason Sherbondy

Just checking in to see if the below suggestion helped. 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.

Answer 1

Hi @Jason Sherbondy
We have reviewed the scenario offline, and I want to confirm that your understanding is correct - currently, the CDC (Preview) wizard in Azure Data Factory does not support connecting to on-premises SQL Server via Self-hosted Integration Runtime (SHIR). This is a known limitation of the preview version, and there is no supported workaround within the CDC wizard at this time.

The SHIR option being grayed out in the CDC wizard UI is expected behavior when targeting on-prem SQL sources.
Even when SHIR is properly installed and registered, the CDC UI does not expose support for on-prem SQL via SHIR in its current preview state.

You can still achieve CDC-like functionality using the manual CDC pattern, which includes:

Enabling CDC on your SQL Server tables.
Using stored procedures like cdc.fn_cdc_get_all_changes_<capture_instance>().
Designing ADF pipelines using Lookup + Copy activities or Mapping Data Flows.
Optionally, maintaining a high-watermark column (e.g., LastModifiedDate) for incremental loads.

This approach allows you to maintain sync between on-prem SQL and Azure SQL DB even without wizard-based CDC.

We understand this is a key use case, and if support for SHIR in the CDC wizard becomes available in the future, we’ll make sure to follow up.

Hope this helps. Do let us know if you 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.

Answer 2

Jason Sherbondy 0

Thank you for clarifying that the CDC (Preview) function does not work as of yet for my use case.

A couple of follow up items about the approach you suggested using cdc.fn_cdc_get_all_changes_<capture_instance>() as I am vaguely aware of this but how will I scale it for keeping 100+ tables in-sync including INSERT, UPDATE and DETELES.

On a few forums, I also saw people suggest transaction replication from on premise to Azure SQL Database. What do you think of this approach?

Share via

Connecting to On-Prem SQL Server through Change Data Capture (ADF) Issue

2 answers

Your answer