Устранение ошибок включения CDC — Часть 1

Автор: Brass Contributor, Troubleshooting CDC enabling failure - Part 1

Недавно мой заказчик столкнулся с двумя интересными проблемами, связанными с CDC, и сегодня я хотел бы поделиться процессом их диагностики и первопричинами.

Проблема:

В выходные заказчик попытался перенести некоторые изменения на производственный сервер. При попытке включить CDC на производственном сервере сначала возникла ошибка безопасности:

#1: Ошибка безопасности:

Msg 22832, Level 16, State 1, Procedure sp_cdc_enable_table_internal, Line 622

Could not update the metadata that indicates table [dbo].[Table_Name] is enabled for 

Change Data Capture. The failure occurred when executing the command 

'[sys].[sp_cdc_add_job] @job_type = N'capture''. 

The error returned was 916: 'The server principal "S-1-9-3-1293060401-1231192665-3834407059-1208013724." 

is not able to access the database "msdb" under the current security context.'. 

Use the action and error to determine the cause of the failure and resubmit the request

Заказчик быстро нашёл обходной путь для этой проблемы, создав два задания CDC вручную:

EXEC [sys].[sp_cdc_add_job] @job_type = N'capture';
EXEC [sys].[sp_cdc_add_job] @job_type = N'cleanup';

После создания этих двух заданий вручную они столкнулись со второй проблемой:

#2: Допустимость неопределённых значений (Nullable column):

Msg 22832, Level 16, State 1, Procedure sp_cdc_enable_table_internal, Line 622

Could not update the metadata that indicates table [dbo].[Table_Name] is enabled for 

Change Data Capture. The failure occurred when executing the command 

'insert into [cdc].[change_tables]'. The error returned was 515: 

'Cannot insert the value NULL into column 'has_drop_pending', table 'LLCProduction.cdc.change_tables'; 

column does not allow nulls. INSERT fails.'. 

Use the action and error to determine the cause of the failure and resubmit the request.

Эту проблему также решили обходным путём, изменив параметр базы данных ANSI_NULL_DEFAULT на значение ON:

ALTER DATABASE DB_Name SET ANSI_NULL_DEFAULT ON;

Вопросы без ответа:

Хотя заказчик в итоге включил CDC, вопросы остались: почему возникли эти проблемы? Как администраторы баз данных могут избежать их в будущем?

Диагностика:

Мы проверили несколько аспектов на производственном сервере:

1. У администратора БД есть привилегии sysadmin.
2. Согласно этой статье, учётные записи служб SQL Server и SQL Server Agent не являются NETWORK SERVICE.

Мы попытались воспроизвести эти проблемы в тестовой среде с той же версией сборки (10.0.4064), и там CDC включился успешно.

Похоже, мы зашли в тупик. Я решил изучить процесс создания CDC, особенно процесс создания заданий агента, и вот что я обнаружил:

Хранимая процедура sys.sp_cdc_enable_table вызывает sys.sp_cdc_enable_table_internal, которая затем вызывает несколько других хранимых процедур. Во время этих вызовов контекст безопасности переключается дважды.

Ниже приведён псевдокод этого процесса:

"sys.sp_cdc_enable_db"
	Create user cdc and cdc system objects
"sys.sp_cdc_enable_table"
	Execute as User = cdc
	Exec sys.sp_cdc_enable_internal
  	Exec sys.sp_cdc_add_job
  	Exec sys.sp_cdc_add_job_internal (execute as dbo)
  	Create msdb.dbo.cdc_jobs table & create jobs

Ошибка возникла в sys.sp_cdc_add_job, а сообщение об ошибке — «не удаётся получить доступ к базе данных "msdb"». Доступ к msdb осуществляется в sys.sp_cdc_add_job_internal, и эта хранимая процедура выполняется от имени пользователя dbo в пользовательской базе данных. Вопрос сводится к тому, почему пользователь dbo в пользовательской базе данных не имеет доступа к базе данных msdb?

При изучении msdb на производственном сервере мой друг, администратор БД Фрэнк Фан, указал, что пользователь guest в msdb отключён.

На других экземплярах, где CDC включался успешно, пользователь guest был включён! Чтобы убедиться, что отключённый пользователь guest в msdb может быть причиной сбоя CDC, я быстро провёл следующий тест в своём экземпляре SQL 2016 SP1 и успешно воспроизвёл ту же проблему:

#1: Отключение пользователя guest в msdb путём отзыва разрешения CONNECT:

REVOKE CONNECT
        TO guest
GO

#2: Создание тестовой базы данных и попытка включить CDC:

CREATE DATABASE cdcdb
GO

USE cdcdb
GO

CREATE TABLE t1 (
        id INT PRIMARY KEY
        ,msg NVARCHAR(20)
        )
GO

SET ANSI_NULL_DFLT_OFF OFF
GO

EXEC sys.sp_cdc_enable_db
GO

EXEC sys.sp_cdc_enable_table @source_schema = N'dbo'
        ,@source_name = N't1'
        ,@role_name = N'cdc_Admin';
GO

CDC не включился и выдал то же сообщение об ошибке:

Msg 22832, Level 16, State 1, Procedure sp_cdc_enable_table_internal, Line 639 [Batch Start Line 27]
Could not update the metadata that indicates table [dbo].[t1] is enabled for Change Data Capture.
The failure occurred when executing the command '[sys].[sp_cdc_add_job] @job_type = N'capture''.
The error returned was 916: 'The server principal "S-1-9-3-330084520-1159009536-4224874419-612529348"
is not able to access the database "msdb" under the current security context.'. Use the action and
error to determine the cause of the failure and resubmit the request.

Чтобы исправить эту ошибку, мы включили пользователя guest в msdb, предоставив ему разрешение CONNECT. После этого sys.sp_cdc_enable_table выполнилась успешно.

Что мы узнали: Оставляйте пользователя guest включённым в базах данных master, msdb и tempdb. См. рекомендацию Microsoft.