Logon error 18456

Недавно решил навести порядок в журнале подопечного SQL сервера. Большое количество записей состояло из ошибки 18456 — ошибка авторизации, причем в качестве источника стояла local system — что немного вводило в ступор. Вроде как кроме инстанса SQL сервера ничего больше на сервере не крутится.  В итоге выяснилось, что это был Report Server, которому была дадены необходимые права и журнал сервера стал горрраздо читабельнее )). В ходе писоков нашел мануал, котороый в принципе один мог бы почомчь разбораться в ситуации, если бы попался первым )http://www.eraofdata.com/blog/2009/01/. За это его перевод:

Определение источника ошибки авторизации SQL Server (18456 ошибка)

Всем нам периодически приходится определять источник ошибок авторизации  (login failures), и, заметив рост количества тем на форумах с просьбой о помощи в это вопросе, я решил начать новый год с поиска быстрого способа точного определения этих ошибок.   Я порыскал в интернете,в предположении, что эта тема была много раз обсосана и закрыта на других форумах или блогах, но с удивлением обнаружил, что хотя и есть много статей о том, что такое «ошибка авторизации» и что означают все коды, возвращаемые в сообщениях ошибки, я не смог найти пошагового описания того, что должен сделать DBA для определения откуда пришел запрос на авторизацию приведший к ошибке. Эта запись — моя попытка исключить этот пробел и показать как выделить процесс, вызывающий эту проблему.

Технология крайне не зависима от версий,  так что это не критично в каком пакете Вы это будете делать, но я предполагаю, что вы знаете как использовать SQL Server Profiler для трассировки запросов.

Ошибка входа 18456

Ошибка входа вызывает ошибку 18456 и сопровождается соответствующей записью в журнале SQL сервера (SQL Server 2000 не отображает IP адрес):

Severity в логе указывает на серьезность ошибки. Severity: 14 означает, что ошибка может быть исправлена пользователем, что вполне логично для ошибок авторизации.
Следующее число указывает на номер состяния. Большинство ошибок имеют связанный с ними  номер  состояния , который обозначает дополнительную информацию обычно уникальную для каждой ошибки. Для ошибки авторизации состояние 8, показанное в приведенном выше примере, указывает что был использован некорректный пароль.

Следовательно, номер состояния предоставляет неоцинимую информацию  о причине ошибки авторизации и зачастую его достаточно для определения причины возникновения ошибки 18456.

В приведенной ниже таблице приведены расшифровки некоторых значений:

Следующий пункт информации — логин (SQL сервера или Windows), сгененрировавший ошибку, затем IP адрес хоста, скоторго была предпринята попытка авторизации, который предоставляет полезную информацию для перекрестного поиска, для подтверждения что мы исследуем верный хост для выделения ошибки авторизации.

Определение ошибки авторизации.

Если информации, содержащейся в журнале ошибок не достаточно для определения источника ошибки, то следующей шаг это запустить трассировку SQL сервера для получения большего количества информации.
Наикратчайший путь для определения ошибок авторизации лежит через трассироку с помощью SQL Server Profiler (SSP).

Если Вы используете SQL Server 2005 или выше т у Вас до сих пор включен трассировщик по умолчанию (который включен по умолчанию в установке из коробки), тогда вам не нужна начинать новую трассировку. Проверьте вместо этого мою запись The SQL Server default trace.

Если у вас более ранняя версия SQL Serverm или же трассировка по умолчанию выключена (или же у вас проблемы с английским) — читайте дальше. (Нничего страшного не произойдет, если запустите еще один поток трассировки).

Запустите SSP, и, используя или ваш любимы шаблон трасировки, или же создав новый (File > Templates > New Templates…), убедитесь что выбраны следующие колонки:

Эти столбцы можно найти в диалоговом окне Trace Properties в закладке Events Selection. Если они не видны, поставте флажок «Show all columns». Обратите внимание, что столбе SPID выбран по умолчанию и не может быть убран.

В столбце Events выберите поле Audit Login в группе Security Audit. Так как мы заинтересованы только в ошибках авторизации, то это будет единственным выбранным событием, и мы будем уверенны что своим вмешательством мы создадаим минимальную нагрузку на систему. В «боевых» системах не желательно (ИМХО) запускать гафическую трассировку SSP на сервере, всегда желательно использовать трассировку на стороне сервера.

Нижеследующий рисунок показываает завршенный шаблон трассировки:

Это может выглядеть несколько уныло, но мы заинтересованы только в поиске конкретной ошибки.

Шаг 1

Сохраните измененный шаблон трассировки и запустите новую трассировку, определив сохраненный шаблон в качестве шаблона для новой трассировки и ждите появления ошибки авторизации. Остановите трассировку после появления ошибки авторизации.

Шаг 2

Столбец Hostname должен содержать запись имени сервера на котором произошел неверный логин, и ClientProcessID должен содержать идентификатор проблемного процесса (PID) (или процессов, если есть несколько связанных процессов).

Шаг 3

Заходим на сервер, на которм произошла ошибка и просматриваем подходящие процессы с помощью диспетчера задач (Task Manager). Для просмотра PID с помощью диспетчера задач, запустите этот диспетчер (Shift+Ctrl+Esc), идем в пунтк View->Select Columns… и выбираем флажок PID (Process Identifier) и нажимаем Ok.

Нажимаем на вкладку Processes для просмотра процессов, запущенных на сервере (убедитесь что стоит флажок Show all processes from all users) и нажмите на заголовок колонки PID, для сортировки по убыванию или возрастанию.

Шаг 4

После того, как Вы определите проесс по его PID, остается всего-лишь вопрос определения где процесс хранит настройки авторизации SQL сервера и проверить их Обычно, это процесс является сервисом, так что это вопрос всего0лишь вопрос запуска оснастки Services через Панель управления, или Start->Run-> services.msc

Так что, счастливой охоты!

Сказка о поломке или что делать, когда проблема не понятна

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

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

Задача простая

1. пишем запрос, показывающий где у нас лежит tempdb:

   use tempdb

   go

   exec sp_helpfile

   go

2. пишем запрос для изменения настроек хранения tempdb:

   use master

   go

   alter database tempdb

   modify file (name = tempdev, filename = 'Новый_Диск:Новый_Каталогtempdb.mdf')

   go

   alter database tempdb

   modify file (name = templog, filename = 'Новый_Диск:Новый_Каталогtemplog.ldf')

   go

3. выполняем его и рестартуем службу SQL Server

После перезапуска службы tempdb создается уже на новом месте. Всё просто и понятно, всего два действия — выполнение запроса для переноса и рестарт службы. Ну а если служба не запустится, то идем в логи сервера и смотрим что помешало — места нет под tempdb, прав не хватает у службы SQL Server или неправильно путь указали для tempdb, всякое бывало в работе.

Приступаем. Запрос выполнили, службу перезапустили, служба перезапущена, проверяем коннект и SSMS зависает на попытке подключится к серверу. Идем смотреть  логи сервера Windows и… ничего. Служба запущена без ошибок, проблем нет. 

Очень сложно исправлять поломку, когда не понимаешь в чём её причина.

В текстовых логах MS SQL были лишь сообщения:

Logon       Login failed for user ‘ADUser’. Reason: Failed to open the explicitly specified database ‘database’. [CLIENT: 10.10.1.10]

Logon       Error: 18456, Severity: 14, State: 38.

По коду ошибки было понятно, что есть проблемы с правами на базы данных, но подключится не получалось даже используя sa.

 Т.к. идеи у нас быстро кончились, в интернете пытались найти причины проблемы с правами простым перебором:

1. Подключили диск для tempdb на раздел, где места с большим запасом. Маловероятно, что tempdb не создался из-за нехватки места и в логе это не указал, но проверить это требовалось. Не помогло.

2. Запустили SQL Server в минимальной конфигурации (с ключом –f). Файлы tempdb создались в варианте по умолчанию, но на доступ к серверу это не повлияло.

3. В минимальной конфигурации подключились используя DAC и попытались изменить ещё раз пути для tempdb. Обнаружили, что настройки из базы данных считываются, но не меняются, выдавая ошибку, что данного файла не существует.

Сложив полученное определили, что причиной проблем является повреждение базы master — из базы не могли получить данные о правах пользователя и tempdb, но в логе это никак не отражалось и понять в чем причина не удавалось. После восстановления базы master:

DBCC CHECKALLOC (‘master’,REPAIR_REBUILD);

CHECKALLOC found 0 allocation errors and 0 consistency errors in database ‘master’.

получили возможность подключится к остальными базам. Решение было неочевидным, т.к. на поломку базы master указывали только косвенные признаки, такие как недоступность информации о правах.

Два простых вывода — лог может подвести и понять в чем неисправность можно только по совокупности косвенных признаков и backup служебных баз может сильно сэкономить время на восстановление работы сервиса.