Вопрос или проблема
На сервере SQL Server 2008 R2 (работающем на Win2003) у нас возникла проблема с входом в систему через доменные группы — доступ к серверу предоставляется, но к определённым базам данных на сервере доступ отсутствует.
Машина SQL Server является членом нашего домена AD, но не является контроллером домена. Мы используем аутентификацию Windows на SQL Server.
Мы создаём входы SQL Server для доменных групп. Настраиваем сопоставления пользователей с базой данных XXX на этом сервере, включаем роли datareader, datawriter и ddladmin. Затем происходит следующее — доменные пользователи, являющиеся членами этих групп, могут подключиться к SQL Server, но не к этой базе данных XXX. Если пользователь “user1” пытается подключиться, он получает ошибку “Не удается открыть базу данных ‘XXX’, запрошенную для входа. Вход не выполнен. Ошибка входа для пользователя ‘domain\user1’.”
Если я вручную добавляю ‘domain\user1’ в базу данных XXX и включаю роли datareader, datawriter, то у user1 нет проблем с подключением.
Любопытно, что user1 может подключаться к двум другим базам данных на этом же сервере — также через доменную группу AD, но другую группу, созданную ранее, и пользователь НЕ настроен индивидуально как пользователь в этих базах данных. Эти базы данных были обе скопированы с более старой установки SQL Server 2005 через отделение-присоединение и работали отлично на SQL-2005, а также работают без проблем на этом сервере SQL-2008 R2. (Они показывают ту же учетную запись пользователя в качестве владельца в свойствах БД, что и проблемная база данных XXX.)
База данных XXX — это новая база данных, созданная на этом сервере R2, и доменная группа AD также новая.
Я могу вручную добавлять пользователей в качестве временного решения, но это не будет работать вечно, и мне нужен намёк на то, что здесь идет не так. Дополнительные данные — служба SQL Server запускается под учетной записью администратора домена.
Я нашёл статью, которая описывает эту точно такую же проблему для SQL server 2005: https://connect.microsoft.com/SQLServer/feedback/details/248615/login-fails-when-user-is-granted-access-via-a-domain-group Но это с 2006 года, в ней нет подсказки по решению, и надо предположить, что это было исправлено.
Если бы мне пришлось угадывать, ваши пользователи не выходят из системы и не входят обратно после добавления в группу, которую вы используете в качестве входа в SQL. Токен доступа пользователя не обновляется с нового членства в группе до тех пор, пока этот пользователь не выйдет из системы и не войдёт снова на свою рабочую станцию. Это объяснило бы, почему старые группы работают, а новая, которую вы тестируете, нет.
Полностью в сторону: запустить SQL Server под учетной записью администратора домена — не лучшая практика. Вам может понадобиться рассмотреть возможность запуска SQL под учетной записью непривилегированного доменного пользователя.
Что касается пользователей, которые могут получить доступ к базам данных, перемещённым через отделение/присоединение — идентификаторы путешествуют с базой данных.
Если бы мне пришлось угадывать, основываясь на поведении, я бы сказал, что SQL испытывает трудности с определением членства в группе или что права доступа к самим базам данных отсутствуют.
Возможно, вам стоит выполнить SELECT * FROM sys.database_principals для получения дополнительной информации о ваших конкретных базах данных и убедиться, что ваша доменная группа там присутствует.
.
Ответ или решение
Проблема с доступом к базе данных в SQL Server при предоставлении доступа через доменную группу может быть вызвана несколькими факторами. Для начала важно понять, что в контексте вашей среды вы используете Windows-аутентификацию на SQL Server 2008 R2, который развернут в домене Active Directory (AD). Ваша серверная машина не является контроллером домена, и вы настраиваете входы в SQL Server для доменных групп. Настройки пользовательских сопоставлений включают роли datareader, datawriter и ddladmin. Однако столкнувшись с проблемой входа в базу данных XXX для пользователей в доменной группе, вы обнаружили, что вручную добавление отдельного пользователя "пользователь1" решает проблему. Давайте рассмотрим теоретические аспекты, примеры и их применение для решения данной проблемы.
Теория
-
Механизм Windows-аутентификации: Когда SQL Server конфигурируется для использования Windows-аутентификации, он полагается на контроллер домена для проверки учетных данных пользователей. Учетные данные пользователя определяются на основе маркера доступа, который генерируется во время входа в систему на рабочей станции.
-
Работа с группами безопасности: Группы домена в AD позволяют администратору упростить управление безопасностью, предоставляя доступ к ресурсам группе пользователей, а не каждому пользователю индивидуально. Однако возможна задержка в обновлении маркера доступа пользователя, когда он добавляется в новую группу безопасности.
-
Сравнение SID: Идентификаторы безопасности (SID) играют ключевую роль в процессе аутентификации. SID сохраняются с базой данных при переносах, таких как detach/attach, и это может привести к различию в поведении между новыми и переносимыми базами данных.
Пример
Рассмотрим ситуацию с базой данных XXX и другими базами данных на сервере. Для базы данных XXX, которая была создана недавно на SQL Server 2008 R2, у пользователей возникают проблемы с доступом через доменные группы. Однако базы данных, которые были перенесены как detach/attach из предыдущей версии SQL Server 2005, не испытывают таких проблем. Это может указывать на то, что с базой данных XXX возникают проблемы с распознаванием членства в группе или установкой необходимого уровня разрешений.
Применение
-
Проверка маркера доступа пользователя: Убедитесь, что пользователи выходят из системы и повторно входят после добавления в новую доменную группу. Это позволит обновить маркер доступа и включить новые членства в группы.
-
Проверка SID: Убедитесь, что SID доменных групп в базе данных XXX корректны. Это можно выяснить, используя запрос
SELECT * FROM sys.database_principalsи проверив присутствие вашей доменной группы. -
Проверка разрешений: Подтвердите, что все необходимые разрешения назначены на уровне базы данных. Неправильная конфигурация разрешений может блокировать доступ, даже если вход выполнен успешно.
-
Аудит и журналирование: Включите аудит и журналирование, чтобы отслеживать попытки входа и исключения. Это может дать подсказки относительно точных причин отказа в доступе.
-
Тестирование учетных записей: Создайте тестовую учетную запись в новой доменной группе и попробуйте подключиться к базе данных XXX. Это может помочь изолировать проблему, связанную с конфигурацией группы или базы данных.
-
Снижение привилегий службы: В среде лучшей безопасности рекомендуется запускать службы SQL Server под учётной записью без привилегий доменного администратора, чтобы минимизировать потенциальные риски.
Подводя итог, проблемы с доступом к базе данных через доменные группы могут возникать из-за нюансов работы аутентификации, различий в SID или неправильных настроек разрешений. Следуя вышеописанным шагам, вы сможете устранить эти проблемы и обеспечить стабильный и безопасный доступ к вашему SQL Server.