Why does CDC table function fail silently when sent from ADF but not SSMS?

Question

Why does CDC table function fail silently when sent from ADF but not SSMS?

Allen, Phil 0

I have an ADF Lookup Activity query calling a CDC table function on an on-prem SQL server. I'm trying to call sys.fn_cdc_get_net_changes_my_table. Initially, when we called this function from ADF, we received a single row with all NULL values as a response, even if the LSN inputs passed in were invalid. The exact same function call, from the same user account, worked as expected when sent from SSMS.

We determined by trial and error that adding the cdc_admin role to the user allowed the query to work from ADF.

Two questions:

Why is this role required to call the table function from ADF, but not from SSMS? Is this intended behavior or a bug?
If there's a permissions issue, why doesn't the table function fail or return an error, rather than send an incorrect response? This also feels like a bug.

1 answer

Your answer

Answer 1

Erland Sommarskog 128.3K MVP Volunteer Moderator

If it works from SSMS and not from ADF that is because the login you use from SSMS has the required permissions, in difference to the login from ADF. The topic for cdc.fn_cdc_get_net_changes says this in the permission section:

Requires membership in the sysadmin fixed server role or db_owner fixed database role. For all other users, requires SELECT permission on all captured columns in the source table and, if a gating role for the capture instance was defined, membership in that database role. When the caller does not have permission to view the source data, the function returns a row with NULL values for all the columns.

I would guess your SSMS user has the SELECT permissions needed (or is an admin).

Allen, Phil 0 Reputation points

2025-09-05T13:10:57.3466667+00:00

Thanks, Erland, for the explanation. However, as I stated in the original problem statement, I use the same system user account for both ADF and SSMS. I've tested this with a brand new user account and verified the behavior.

The cdc_admin role is not needed to run the command successfully via SSMS, but is required from ADF.

And while this behavior (return a NULL row instead of returning an error) may be intended behavior, it still feels like a bug. If I don't have permissions to run a Powershell command, for example, I receive an error. Not an invalid response.
Erland Sommarskog 128.3K Reputation points MVP Volunteer Moderator

2025-09-05T14:00:41.6066667+00:00
Let me first address the lack of an explicit error message. They are constrained by the fact that they are running from a function, where many commands are not available, for instance RAISERROR and THROW. Thus, raising an explicit error is not possible. They could do some trick like:

SELECT @int = 'Permission denied on table "xyz".'

But the message that comes back looks strange for the untrained eye. (Although, I seem to recall that when I played with CDC many years ago and I got an error that was a bit incomprehensible. When I looked into the code I saw that they purposely provoked an error, and I realised that this was the best thing they could do.)

As for the permissions as such, you will need to dig deeper. I don't use ADF myself, but I believe that g´to get ADF to talk on SQL Server on-prem, you need a bridging service which is the one ADF talks to and this local services is what logs into SQL Server. Which credential is this service using?

Also, what sort of user are you using? An SQL login? An Entra principal of some sort?

Troubleshooting steps you could take is to dump the output of the function SYSTEM_USER and origeinal_login)() somewhere, as well as the contents of the views sys.user_token and sys.login_token to see which security tokens you have.

Share via

Why does CDC table function fail silently when sent from ADF but not SSMS?

1 answer

Your answer