Lesson Learned #352: Could not update the metadata that indicates database enabling CDC.

[Guest Jose_Manuel_Jurado]

Today, we got a error message while trying to enable cdc for a database using the sqladmin user. Our customer got the error message: Msg 22830, Level 16, State 1, Procedure sys.sp_cdc_enable_db_internal, Line 283 [batch Start Line 0]

Could not update the metadata that indicates database XYZ is enabled for Change Data Capture. The failure occurred when executing the command 'SetCDCTracked(Value = 1)'. The error returned was 33171: 'Only active directory users can impersonate other active directory users.'. Use the action and error to determine the cause of the failure and resubmit the request.

 

 

 

This issue is related due to CDC does not work on natively on restored database, as you could see in this URL: What is change data capture (CDC)? - SQL Server | Microsoft Learn

 

 

 

Enabling CDC fails on restored Azure SQL DB created with Microsoft Azure Active Directory (Azure AD)

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, follow these steps:

 

 

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

 

ALTER AUTHORIZATION ON DATABASE::[<restored_db_name>] TO [<azuread_admin_login_name>];

EXEC sys.sp_cdc_enable_db

 

 

 

In another hand, you could receive an error message: Msg 22830, Level 16, State 1, Procedure sys.sp_cdc_enable_db_internal, Line 283 [batch Start Line 0] Could not update the metadata that indicates database XYZf 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.

 

 

 

In this case the suggestion is to execute the following TSQL command at database level:

 

 

 

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

exec sp_cdc_enable_db

 

 

 

Enjoy!

 

Continue reading...