Eventstream - SQL Server CDC on VM DB Error

[ricauduro]

I´m facing an issue to create a CDC connection to a SQL Server on VM, similar to this one Solved: Re: SQL Server on VM DB Error - Microsoft Fabric Community
Property 'username's value '' is invalid Artifact ID: fa6efc37-ce95-4e4c-9ebb-af6622246cf1 Action: createEventStreamSource Type: SqlServerOnVmDbCdc
But the connection is Basic and have username and password defined. Also, I`m using this connection to ingested data using pipelines

[v-sathmakuri]

Hi @ricauduro ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you!!

[ricauduro · Accepted solution]

Hi @v-sathmakuri . I´ve performed some tests but without success. The DBA is reviewing the permissions you mentioned but as he told me before, they were already ok. 

We are connectiong using SQL authentication and I can ingest data from other tables, not CDC, using the same connection without any issues. 
Thank you for all your support and I let you know soon about new tests.

[v-sathmakuri]

Hi @ricauduro ,

Could you please let me know if you have resolved this issue? If so, kindly mark the helpful reply and accept it as the solution. This will assist other community members in resolving similar issues more quickly.

Thank you!!

[v-sathmakuri]

Hi @ricauduro ,

Could you please let me know if you have resolved this issue? If so, kindly mark the helpful reply and accept it as the solution. This will assist other community members in resolving similar issues more quickly.

Thank you!!

[ricauduro]

Hi @v-sathmakuri . Thank you for your reply. We had a long weekend in Brazil... Friday and Monday were holidays. I'll test your approach today and let you know.

[v-sathmakuri]

Hi @ricauduro ,

Thank you for reaching out to Microsoft Fabric Community.

Even when using Basic Authentication, you should verify the connection through the Fabric portal. To do this, navigate to the Manage connections pane, locate the SQL Server connection, and click Edit to confirm or update authentication details.

CDC connections don’t always support Windows Authentication or integrated authentication by default. It’s essential to use SQL Authentication and ensure that the SQL user has access to the target database. The user must also have CDC-related permissions such as SELECT and VIEW DATABASE STATE to facilitate data capture.

Additionally, the SQL user needs the appropriate permissions to access specific CDC system tables and functions. This includes read access to cdc.change_tables, cdc.lsn_time_mapping, and the ability to execute fn_cdc_get_all_changes_* functions, which are crucial for retrieving change data.

There are scenarios where a connection might appear valid in the interface but is not properly registered for CDC. In such cases, try deleting and re-creating the connection directly through the Eventstream source setup. Restarting the Fabric runtime or workspace can help refresh the environment. 

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you!!