Утром в службу поддержки обратился один из разработчиков корпоративного приложения. Он не мог сделать копию с базы данных MS SQL Server, и просил выяснить причину ошибки.Первое с чего стоит начать — проверить ошибку на воспроизводимость.
Попробуем снять копию командой:
BACKUP DATABASE [SDB] TO DISK=N'\\FS1\Backup\sdb_full.bak' WITH COPY_ONLY
Действительно, попытка снять копию заканчивалась ошибкой:
Что может вызвать такую проблему?
SQL Server запускается от встроенной учетной записи «Network Service»
На всякий случай проверяем разрешение имени сервера FS1 по короткому имени и по FQDN. Оба имени разрешаются и, что важно, указывают на один и тот же сервер. Открываем сетевую папку, проверяем разрешения на NTFS и Share Permissions. Все в порядке, учетная запись сервера SQL1 имеет разрешение на запись.
Может быть проблемы с NTLM, Kerberos? Попробуем снять резервную копию, используя FQDN сервера.
BACKUP DATABASE [SDB] TO DISK=N'\\FS1.contoso.test\Backup\sdb_full.bak' WITH COPY_ONLY
Интересно. С использованием FQDN резервная копия успешно создалась. Что это значит? Разве что ситуация стала еще более запутанной.
SQL Server перезапускать в рабочее время нельзя. Оставаться в ночь не хотелось бы.
Когда ничего непонятно, лучший друг администратора — Wireshark или Microsoft Network Monitor. Если снять хороший дамп, то можно или разобраться, или уж запутаться так запутаться.
Ставить на ответственный сервер Microsoft Network Monitor теоретически безопасное мероприятие, но жизнь так часто вносит коррективы в самые безопасные начинания.
Перезагружаться нельзя, ставить монитор нежелательно. Тогда воспользуемся службой Windows Event Tracing.
Включили трассировку:
netsh trace start persistent=yes capture=yes tracefile=c:\temp\trace.etl
Повторили команду резервного копирования несколько раз:
BACKUP DATABASE [SDB] TO DISK=N'\\FS1\Backup\sdb_full.bak' WITH COPY_ONLY
BACKUP DATABASE [SDB] TO DISK=N'\\FS1\Backup\sdb_full.bak' WITH COPY_ONLY
BACKUP DATABASE [SDB] TO DISK=N'\\FS1\Backup\sdb_full.bak' WITH COPY_ONLY
Остановили трассировку:
netsh trace stop
Открываем файл в Microsoft Network Monitor на рабочей станции администратора:
Каждый раз при попытке снятия копии появляется событие KDC_ERR_PREAUTH_REQUIRED с загадочным пользователем DBAdmin. Это не учетная запись сотрудника, администратора, под ней не запускается SQL Server.
KDC_ERR_PREAUTH_REQUIRED означает, что учетные данные неверны.
Но резервное копирование выполняется в контексте службы «MS SQL Server», а она запущена под «Network Service». Причем здесь DBAdmin?
В Windows есть «Диспетчер учетных данных», он же «Credentials Manager», позволяющий сохранять учетные данные для различных сетевых ресурсов. Его можно вызвать командой «control userpasswords2» или «netplwiz»:
Давайте проверим, нет ли в контексте учетной записи компьютера «SQL1\Network Service» сохраненных альтернативных учетных данных для сервера FS1.
Для того, чтобы запустить процесс от имени другого пользователя, воспользуемся psexec.
Если запустить psexec с ключем "-s", мы попадем в контекст «Local System». Не подойдет.
Для того, чтобы попасть в контекст «Network Service» запускаем утилиту со следующими ключами:
psExec.exe -i -u “nt authority\network service” cmd.exe
Проверим, повторяется ли в контексте «Network Service» ошибка Access Denied при обращении к серверу FS1:
Ошибка воспроизводится.
Проверим сохраненные учетные данные. Запустить «control userpasswords2» без колдовства с Explorer не получится. Да и не надо, для работы с «Credentials Manager» из командной строки есть утилита cmdkey.exe.
Для того, чтобы вывести сохраненные учетные данные выполним команду:
cmdkey /list
Никаких сохраненных учетных данных не обнаружено. Еще интереснее.
Итак, что мы знаем на текущий момент:
- В контексте учетной записи компьютера «SQL1\Network Service» при обращении по протоколу SMB к серверу FS1 возвращается ошибка Access Denied
- При обращении к серверу по FQDN FS1.contoso.test ошибка не возвращается
- Обращение к серверу FS1 происходит с использованием учетной записи DBAdmin, которая нигде в явном виде не используется
- В контексте «SQL1\Network Service» в Credentials Manager учетные данные не сохранялись
Подождите, а ведь учетные данные можно сохранить не только в Credentials Manager, но и в памяти службы «Lanman Workstation».
Если подключить диск с параметром /savecred, то учетные данные сохранятся в Credentials Manager:
net use \\FS1\Backup /persistent:yes /savecred
Если опустить параметр /savecred, то учетные данные сохранятся в памяти службы до перезагрузки
net use \\FS1\Backup /persistent:yes /user:DBAdmin
Проверим нет ли у нас сохраненных подключений:
net use
Есть! Теперь понятно, почему при обращении к FS1 возвращалась ошибка, а к FS1.contoso.test — нет.
Удалим сохраненные подключения:
net use * /delete
Проверяем резервное копирование:
Проблема решена.
А в чем же было дело? Причина ошибки весьма нетривиальна. Внутри корпоративного приложения от имени SQL Server был подключен сетевой диск под пользователем DBAdmin, который из-за ошибки в приложении не был в дальнейшем отключен. Спустя некоторое время у пользователя DBAdmin, вероятно сменился пароль, или сервер был перезагружен. И вот он, загадочный Access denied!
Какие для себя можно сделать выводы?
- Когда вы выполняете резервное копирование SQL Server, обращение к сетевым ресурсам производится от имени учетной записи службы SQL Server, а не от пользователя, запустившего команду BACKUP DATABASE. Следует помнить об этом, настраивая разрешения.
- Всегда снимайте дополнительные полные резервные копии с ключом WITH COPY_ONLY. SQL Server помечает страницы данных, измененные после полного резервного копирования, и в дифференциальную копию попадают только измененные страницы. Логично, что после каждого полного резервного копирования, состояние страниц очищается. Ключ позволяет не очищать отметку страниц, и последовательность не будет нарушена.
- В случае ошибки «Access denied» не лишним будет проверить, повторяется ли ошибка и по имени узла, по FQDN, по IP адресу.
- Вы можете попасть в контекст безопасности нужной учетной записи, запустив psexec с ключем -U.
- Для вывода учетных данных из службы хранения ключей используется утилита cmdkey.
- Для вывода сохраненных подключенных сетевых подключений используйте команду net use.
Спасибо за внимание.
Спасибо ildarz, sabin за содержательные комментарии, устранение неточностей и подсказки.
Комментарии (36)
ildarz
28.07.2015 12:46Может быть проблемы с NTLM? Это странно. Гораздо чаще проблемы встречаются с Kerberos. Как проверить авторизацию используя NTLM и Kerberos? Для NTLM используем короткое имя или IP-адрес, а для Kerberos — FQDN.
Нет. По короткому имени в домене точно так же по умолчанию используется Kerberos, что вы, собственно, и увидели в дальнейшем при трассировке. NTLM используется при обращении по IP.
То есть вы установили, что поведение бэкапов разное при обращении по разным именам, но способ аутентификации тут ни при чем.
KDC_ERR_PREAUTH_REQUIRED означает, что учетные данные неверны.
Не означает. Это вообще не ошибка, а стандартное поведение по умолчанию (требование выполнить пре-аутентификацию Kerberos).
А в списке выводов не вижу «не дизайнить приложения идиотским способом, подразумевающим запоминание (хотя бы и временное) чьих-то учетных данных в контексте безопасности служебных учеток». :)
P.S. MS Message analyzer (наследник MS Network Monitor) позволяет подключаться к удаленной машине.
gotch Автор
28.07.2015 12:52Да, пожалуй вы правы по всем пунктам. Сделаем творческую правку сейчас.
Дизайн приложений. Тут вот что сделано, то сделано. Настоящий, хороший, опытный разработчик обязательно должен уметь вызвать глубокое недоумение у администраторов :)
MS MA я видел только на стадии беты, он произвел неоднозначное впечатление. Спасибо за наводку, попробуем его.
aml
28.07.2015 13:41Поставил плюс за мастерство. Но блин, люди, двадцать первый век на дворе, а вы ещё пользуетесь системами, в которых даже логов нормальных нет.
gotch Автор
28.07.2015 13:55+1Есть в ваших словах правда. Логов-то там сколько угодно. Можно посмотреть в журнал безопасности трех контроллеров домена (в каждом журнале 1 000 000 событий), файлового сервера, SQL сервера. Я попробовал, но это оказалось просто неподъемной задачей. Там точить-непереточить набор скриптов, которые выберут то, что нужно.
074909
21.08.2015 15:28Зря Вы эвентлоги не собираете на отдельный сервер (без доступа к нему никому и из штатных админов, дабы избежать даже случайной порчи или утери).
А подобная описанной в статье задача настолько популярна, что издавна существуют шустренькие маленькие костыльчики:
Account Lockout Status (LockoutStatus.exe) is a combination command-line and graphical tool that displays lockout information about a particular user account.
www.microsoft.com/en-us/download/details.aspx?id=15201
(инсталляция не требуется, можно просто распаковать пакет и пользоваться).
Внутри несколько утилит, в том числе умеющая быстренько-многопоточно выбрать нужное событие с нужным эвентайди и с вхождением нужного текста в описание (к примеру, имя хоста или юзера) со всех контроллеров:
EventCombMT.exe — Данная утилита поможет собирать определенные события из системных журналов указанных компьютеров в одно центральное хранилище. К набору утилит добавлен Help-файл, подробно описывающий работу с программой EventCombMT.exe
По сути — это просто гуй для посроения wmi запросов, их выполнения и сохранения результатов.
ildarz
28.07.2015 14:11+3системами, в которых даже логов нормальных нет
Не расскажете поподробнее, какими такими системами, и каких именно логов в них нет применительно к данному конкретному случаю?
miga
28.07.2015 14:46+2Полагаю, автор комментария намекает на то, что администраторам
нормальныхиных операционных систем и соответсвующих СУБД несколько забавно читать про подобные проблемы и то, как они решаются.ildarz
28.07.2015 15:11+1«Хотелось бы всё же услышать начальника транспортного цеха» (с). Но вы, если хотите, можете выступить за него и тоже попробовать ответить на мой вопрос. А там, глядишь, увидим, кому будет забавно. :)
realscorp
29.07.2015 10:06Ох, как здорово, наверное, жить в мире, где есть такие операционные системы, в которых никогда-никогда не возникает ошибок со сложными причинами, для определения которых нужно использовать те или иные инструменты и методы анализа событий!
Жаль, что в нашем мрачном мире такого еще нет :(
Расскажите только, вы из далекого будущего или из далекого прошлого?miga
29.07.2015 10:34Вы действительно не понимаете, насколько дико описанная проблема выглядит для людей, не вовлеченных в инфраструктуру Windows?
Давайте взглянем со стороны, в чем суть проблемы (по-крайней мере, как это понял я): некое приложение (отдельное, сторонее приложение, отдельный процесс) замонтировало сетевой диск от имени MSSQL, забыло его отмонтировать, в результате чего сам MSSQL, как отдельное приложение, в отдельном процессе вдруг получил Access Denied при обращении к тому же ресурсу. Для меня такая связность независимых компонентов выглядит просто чудовищно, просто в голове не укладывается, как в такой среде, где взмах крыла бабочки в одном месте вызывает ураган в другом, вообще можно работать.ildarz
29.07.2015 11:50Чего я действительно не понимаю, так это того, зачем давать какие-то комментарии в области, в которой не разбираетесь. Суть проблемы вы описали неверно. На мой вопрос, с которого началась эта ветка комментариев, ответить тоже не можете или не хотите. Но виновата почему-то винда. :)
P.S. Если коротко, описанная проблема заключается в том, что в контексте безопасности учетной записи А было из-за человеческого раздолбайства сделано сетевое подключение, используя учетную запись Б. Невозможная ситуация в других ОС, да. :Dmiga
29.07.2015 12:11Какое счастье, что у меня нет необходимости разбираться в этой области :) Ваше описание проблемы, кстати, звучит для меня так же чудовищно.
ildarz
29.07.2015 12:43+1man mount.cifs
Будете дальше клоунаду устраивать?miga
29.07.2015 13:10Послушайте, ну мне совершенно неинтересно, как работает самба, керберос или пес его знает что еще.
Все, что я вижу — это ужасную связанность всего со всем, котороая, как показал нам автор топика, выстрелила в совершенно неожиданном месте. Наверное, это плата за какие-то удобства, благодаря которым есть много людей, которые используют эту экосистему. Это, наверное, действительно удобно, когда базу можно забэкапить на удаленный сервер одним SQL-запросом, в моем мире для этого бы пришлось писать какой-нибудь скрипт, раскладывать ключи для авторизации и т.д. — но, по-крайней мере, тут нет подводных камней, кроме тех, что ты сам себе разложил.ildarz
29.07.2015 16:40Допустим, у вас в продакшене не MSSQL на винде, а вовсе даже оракл на линуксе, а добрый разработчик так спроектировал приложение, что из Оракла выполняются системные вызовы с использованием su, и в результате что-то идет не так — кто виноват? Оракл и линукс, да?
miga
29.07.2015 17:01В конкретном случае виноват, очевидно, тот, кто так настроил capabilities ораклу — неявным автомагическим образом ничего не делается, и это именно то, о чем я говорю — в линуксе гораздо меньше всяких неявных штук, которые могут внезапно выплыть боком.
ildarz
30.07.2015 10:50О. То есть когда оракл на линуксе — виноваты люди. А когда ровно такой же случай (да-да, оно не «само», это надо было специально постараться), но MS SQL на винде — внезапно, винда. В принципе, тут больше добавить нечего. :)
miga
30.07.2015 12:04Вы чувствуете разницу между «человек почитал man capabilities и настроил запуск через setcap кривой программы, которая все сломала» и между «человек написал программу, которая не так сходила в сетевой ресурс, в результате чего взорвалась СУБД»?
ildarz
30.07.2015 13:57> xp_cmdshell 'net use'
Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see «Surface Area Configuration» in SQL Server Books Online.
Я всё верно понял — если заглянул в man capabilities, настроил и сломал — то сам виноват, а если заглянул в SQL Server Books Online, настроил и сломал — это система виновата?miga
30.07.2015 14:03Внутри корпоративного приложения от имени SQL Server был подключен сетевой диск под пользователем DBAdmin, который из-за ошибки в приложении не был в дальнейшем отключен
Как думаете, ошибку допустили сознательно, либо она была вызвана неявными телодвижениями системы при подключении сетевого диска?gotch Автор
30.07.2015 14:49Думаю, что разработчики тестировали некий функционал на бета-версии приложения. Вероятно это была часть функционала, связанная с формированием локальной реплики полной БД для offline клиентов.
ildarz
30.07.2015 17:22Со стороны системы никаких «неявных телодвижений» тут нет — сделано ровно то, о чем систему явным образом попросили (если вы так и не дали себе труд заглянуть в man mount.cifs, ближайшим аналогом, пожалуй, будет создание директории через su и забывание про то, что надо бы на неё потом права правильные выставить). Что творилось в мозгах у дававшего команду — вопрос не ко мне.
realscorp
29.07.2015 16:17Давайте взглянем со стороны, в чем суть проблемы
Не совсем так, как вы описали. Некое приложение, которому по недосмотру дали права на выполнение команд от имени MSSQL в контексте пользователя network service, нелепым образом изменило настройки подключения к определенному сетевому ресурсу.
Корневые причины: кривое приложение, неверно выданные права.
В других ОС кривое приложение в контексте, например, root разве ничего не сможет напортачить в системе?miga
29.07.2015 16:53Я, кажется, понял, как можно объяснить. В других ОС меньше набор абстракций, над которыми можно что-то делать, скуднее системный API, поэтому, несмотря на то, что напортачить с соответствующими привилегиями можно одинаково эпично, сделать это случайно — гораздо сложнее. Меньше неявных действий под капотом — больше контроля, меньше неожиданностей.
qw1
01.08.2015 18:50Аналогичная ситуация на линуксе: ставится огромное кривое enterprise-приложение, которое компания купила за 100500 миллионов. В процессе установки оказывается, что ораклу нужны su-права. Индусы-разработчики не могут объяснить, зачем нужен root, «так исторически сложилось, а теперь рут убирать нельзя, т.к. всё с ним тестировалось». Попытки админа объяснить, что такое в продакшен ставить нельзя, встречают аргумент — «50 контор как-то работает, если у тебя не получается, мы найдём другого админа».
И что делать? Ставить, давать права, а потом так же разбираться, если индусский код под рутом косячит.
Sabin
28.07.2015 15:35В Windows есть «Диспетчер учетных данных», он же «Credentials Manager», позволяющий сохранять учетные данные для различных сетевых ресурсов. Его можно вызвать командой «control userpasswords2»
В «простых» версиях windows эта команда заменена на «netplwiz», выполняющую ровно ту же функцию. Скорее всего, в новых серверных версиях так же нужно вводить именно её.
yosemity
28.07.2015 19:56Использую только пути через DFS, везде, где это возможно. Подключение сетевых дисков давно оставил только для удобства юзеров, их использование для сервисных учетных записей — есть зло. К слову и для юзеров сетевые диски ведут на DFS-линки. Сам давно забыл где какая шара лежит, миграция проходит с легкостью. Иногда случаются сложности при работе с линуховыми шарами, если DFS-ссылка ведет на линух. Этим грешит, например Veeam Backup & Replication. Хотя его репозиторий может быть подключен по ssh, так что терпимо.
gotch Автор
29.07.2015 09:30Мы тоже в 99% случаев используем DFS. Но корень DFS ведь тоже необходимо подключить?
DFS хорош, правда до тех пор пока, например, один из трех серверов, несущих доменный корень не сходит с ума. Поймать его на этом и перезапустить — маленький квест.realscorp
29.07.2015 10:11DFS хорош, правда до тех пор пока, например, один из трех серверов, несущих доменный корень не сходит с ума. Поймать его на этом и перезапустить — маленький квест.
А можно поподробнее? Не сталкивался, интересно.gotch Автор
29.07.2015 12:55У клиентов, использующих «сошедший с ума» сервер, появляется неожиданный Access denied при подключении корня.
Интересно, что ошибка проявляется соответственно у каждого третьего, а причин такой ошибки в нашей сети может быть несколько, поэтому сразу не понятно, что проблема в DFS.
Плюс необходимо на клиента скопировать dfsutil, при помощи которой можно понять, какой именно сервер «заболел», введя dfsutil cache referral.
yosemity
29.07.2015 12:48В смысле подключить корень? Корней может быть несколько с разной степенью вложенности. В случае с доменом просто прописываем путь к домену и нужному линку.
И тоже не сталкивался что что-то сходит с ума. Наоборот, прямо на ходу отрубал основную папку, клиенты (Win7+) прыгают на резервный не замечая.gotch Автор
29.07.2015 12:57Жалко дампы Network Monitor стер, где эту ошибку хорошо видно. После замены несущих серверов с 2008R2 на 2012R2 ошибка не повторялось, свежих улик нет.
realscorp
29.07.2015 10:11Спасибо за статью. Узнал из нее, что у майкрософта есть собственный инструмент анализа трафика.
lostpassword
Прикольно, прям как у Руссиновича.))
gotch Автор
До Руссиновича нам далековато. Я не могу с таким упоением рассказывать про коалесцирующие таймеры и кеш-когерентные шины. :)