Как исправить ошибку аутентификации пользователя sa и сбои подключения в заданиях SQL Server Agent

При настройке автоматического резервного копирования в SQL Server Management Studio (SSMS) администраторы часто сталкиваются с ситуацией, когда служба SQL Server Agent запущена, задания созданы, но при их выполнении возникают ошибки доступа. Наиболее типичные симптомы — это ошибка с кодом 18456 (Login failed for user 'sa') и ошибка выполнения пакета DTExec с кодом 0x80131501, указывающая на невозможность подключения к серверу (local). В этой статье мы подробно разберем, как проанализировать эти ошибки и привести систему в рабочее состояние.

Анализ причины возникновения ошибки 18456 (Login failed for user 'sa')

Первым делом нам необходимо понять, почему сервер отклоняет попытку входа пользователя sa, даже если вы уверены в правильности пароля. Ошибка 18456 в журнале SQL Server является универсальной, но она всегда указывает на проблему безопасности. Рассмотрим наиболее вероятную причину: режим аутентификации сервера.

Если SQL Server настроен в режиме «Windows Authentication mode», любые попытки подключения через SQL-аутентификацию (включая пользователя sa) будут немедленно отклонены. Проанализируем, как это проверить и изменить. Проделаем следующие шаги:

1. Подключимся к экземпляру SQL Server через SSMS, используя Windows-авторизацию.
2. Нажмем правой кнопкой мыши на корень сервера в Object Explorer и выберем Properties (Свойства).
3. Перейдем на страницу Security (Безопасность).
4. Убедимся, что в разделе «Server authentication» выбран пункт SQL Server and Windows Authentication mode (Смешанный режим).
5. Если был выбран только Windows-режим, изменим его и нажмем OK. Важно: после этого изменения требуется перезагрузка службы SQL Server.

Для проверки текущего режима аутентификации с помощью скрипта, мы можем использовать следующий код:

SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly')   
WHEN 1 THEN 'Windows Authentication Mode'   
WHEN 0 THEN 'SQL Server and Windows Authentication Mode'   
END as [Authentication_Mode];

Проблема использования псевдонима (local) в планах обслуживания

Во втором сообщении форума упоминается ошибка Не удалось подключиться к серверу (local). Это специфическая проблема планов обслуживания (Maintenance Plans) в SQL Server 2016. Внутренние механизмы пакетов SSIS, которые лежат в основе планов обслуживания, не всегда корректно разрешают имя (local), когда запуск происходит от имени виртуальной учетной записи службы NT SERVICE\SQLSERVERAGENT.

Рассмотрим, как это исправить. Вместо использования системного псевдонима в настройках соединения плана обслуживания, нам следует указать явное имя сервера. Выполним следующие действия:

1. Откроем раздел Management и перейдем в Maintenance Plans.
2. Откроем нужный план обслуживания в режиме конструктора.
3. Нажмем на кнопку Manage Connections (Управление подключениями) на верхней панели.
4. В поле «Server name» заменим (local) на точку . или, что более надежно, на Hostname (имя вашего компьютера/сервера).

Настройка прав учетной записи SQL Server Agent

Выясним причину, по которой задание может падать, даже если оно запускается под учетной записью NT SERVICE\SQLSERVERAGENT. По умолчанию эта учетная запись должна иметь права на выполнение задач агента, но для операций резервного копирования и управления базами данных ей часто требуются расширенные полномочия на уровне сервера (роль sysadmin).

Проанализируем ситуацию: если владельцем (Owner) задания указан sa, но само задание запускается в контексте службы агента, возникает конфликт прав. Нам необходимо убедиться, что логин службы агента добавлен в систему и имеет нужные роли. Выполним следующий SQL-запрос для проверки и назначения прав:

-- Добавление учетной записи службы агента в список логинов сервера
IF NOT EXISTS (SELECT name FROM sys.server_principals WHERE name = 'NT SERVICE\SQLSERVERAGENT')
BEGIN
    CREATE LOGIN [NT SERVICE\SQLSERVERAGENT] FROM WINDOWS WITH DEFAULT_DATABASE=[master];
END

-- Назначение роли sysadmin для полного доступа к операциям бэкапа
ALTER SERVER ROLE [sysadmin] ADD MEMBER [NT SERVICE\SQLSERVERAGENT];

Проверка прав доступа к каталогам резервного копирования

Часто проблема кроется не в SQL Server, а в правах файловой системы Windows. В сообщении №11 утверждается, что «доступ открыт для всех», однако для службы SQL Agent этого недостаточно, если речь идет о сетевых ресурсах.

Разберем подробнее: если вы делаете бэкап на сетевой диск или UNC-путь (например, \\BackupServer\Share), виртуальная учетная запись NT SERVICE\SQLSERVERAGENT не сможет получить к нему доступ, так как она является локальной для текущего сервера. В этом случае мы рекомендуем:

• Либо изменить учетную запись запуска службы SQL Server Agent на доменную учетную запись, у которой есть права Full Control на целевую папку.
• Либо создавать бэкапы на локальный диск, а затем перемещать их скриптом.

Для локальных папок обязательно проверьте вкладку Security в свойствах папки. Там должна быть явно добавлена учетная запись NT SERVICE\SQLSERVERAGENT (или SQLServerMSASUser$...) с правами на запись.

Работа с журналом заданий для точной диагностики

Если общие ошибки не дают полной картины, проанализируем историю выполнения конкретного шага задания. Как правильно отметил участник обсуждения в сообщении №8, нужно смотреть специализированный лог задания. Проделаем это вместе:

1. В SSMS развернем узел SQL Server Agent -> Jobs.
2. Нажмем правой кнопкой мыши на проблемном задании и выберем View History (Просмотр журнала).
3. В открывшемся окне выделим строку с ошибкой и в нижней части окна внимательно изучим текст сообщения. Часто там указан конкретный код ошибки файловой системы или подробности отказа в доступе.

Использование 32-битного режима выполнения

В редких случаях, связанных с версией SQL Server 2016 (13.0.1601.5), компоненты выполнения пакетов могут конфликтовать с разрядностью системы при использовании специфических провайдеров данных. Рассмотрим, как переключить режим выполнения шага задания:

1. Откроем Properties задания и перейдем на вкладку Steps.
2. Выберем шаг выполнения бэкапа и нажмем Edit.
3. Перейдем на вкладку Execution options.
4. Попробуем установить флаг Use 32 bit runtime, если остальные методы не помогли. Это заставит агент использовать 32-битную версию DTExec, что иногда решает проблемы с подключением через старые драйверы.

Подводя итог, можно сказать, что решение проблем с Агентом SQL всегда требует комплексного подхода: проверки режима аутентификации сервера, уточнения прав учетной записи службы и корректного указания имен серверов в планах обслуживания. Мы надеемся, что эти шаги помогут вам успешно настроить автоматизацию резервного копирования ваших баз данных.