Error during enabling CDC on an Azure SQL DB- ‘Msg 22830, Level 16, State 1, Line 274’

Posted by

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.

Issue

An issue was noticed recently while enabling CDC on an azure SQL DB that is restored from a copy of another DB. The command that was executed was 'EXEC sys.sp_cdc_enable_db'. You may receive an error as shown below:

Tanayankar_Chakraborty_0-1669306300001.png

 

 

Error

In addition to the error above- here is the error text : MESSAGE

'Msg 22830, Level 16, State 1, Line 274

Could not update the metadata that indicates database db is enabled for Change Data Capture. The failure occurred when executing the command 'SetCDCTracked(Value = 1)'. The error returned was 15517: 'Cannot execute as the database principal because the principal ''dbo'' does not exist, this type of principal cannot be impersonated, or you do not have permission.'. Use the action and error to determine the cause of the failure and resubmit the request.'

 

Workaround/Fix

If you create a database in Azure SQL Database as a Microsoft Azure Active Directory (Azure AD) user and enable change data capture (CDC) on it, a SQL user (for example, even sysadmin role) won't be able to disable/make changes to CDC artifacts. However, another Azure AD user will be able to enable/disable CDC on the same database.

 

Similarly, if you create an Azure SQL Database as a SQL user, enabling/disabling change data capture as an Azure AD user won't work.

Also Enabling CDC will fail if you create a database in Azure SQL Database as a Microsoft Azure Active Directory (Azure AD) user and don't enable CDC, then restore the database and enable CDC on the restored database.

To resolve this issue, execute this:

  • Login as Azure AD admin of the server
  • Run ALTER AUTHORIZATION command on the database:

 

  • ALTER AUTHORIZATION ON DATABASE::[<db_name_on_which_failure_occurred>] to [<aad_admin_login_name>];
  • exec sp_cdc_enable_db

 

Please note that while trying this, the person logging in with his Azure AD login can bump up his own permissions as well.

 

References

What is change data capture (CDC)? - SQL Server | Microsoft Learn

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.