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

by | Nov 24, 2022 | Technology | 0 comments

This article is contributed. See the original author and article here.

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

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.

Submit a Comment