Аутентификация windows ms sql server

Поддерживаемые методы авторизации

Имеется два различных метода авторизации для подключения к SQL Server: Windows и SQL Server.

Для авторизации Windows требуется, чтобы пользователь сначала авторизовался в Windows со своим логином и паролем. После этого он может подключиться к SQL Server, используя авторизацию Windows. То есть при условии, что их учетной записи Windows был предоставлен доступ к SQL Server через логин (подробнее о логинах ниже). Авторизация Windows тесно связана с безопасностью Windows и называется интегрированной безопасностью (Integrated Security). Авторизация Windows прекрасно работает, когда лицо является частью домена Windows.

Но бывают случаи, когда люди не могут подключиться к Windows; это имеет место при авторизации SQL. Авторизация SQL является менее безопасной, чем авторизация Windows. Для подключения к SQL Server с помощью авторизации SQL, пользователь должен указать логин и пароль при подключении. Пароль логина при авторизации SQL хранится в базе данных master. Т.к. пароль хранится в базе данных, его легче взломать. Поскольку можно сделать бэкап базы с последующим восстановлением, этот способ авторизации менее безопасен, чем при использовании авторизации Windows.

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

Установка SQL Server с поддержкой различных режимов авторизации

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

Рис.1 Выбор режима авторизации

Авторизация Windows выбирается по умолчанию (красная стрелка на рис.1). Если вам требуется поддержка авторизации как Windows, так и SQL Server, вам следует выбрать вариант “Mixed Mode”. При этом становится доступным установка пароля аккаунта SA, и вам потребуется задать пароль SA. При выборе только авторизации Windows, аккаунт SA недоступен. Чтобы защитить учетную запись SA при использовании смешанного режима, вы можете отключить ее после включения.

Как определить, какие методы авторизации поддерживаются

Вы можете проверить установленный метод авторизации несколькими способами. Один из способов — использовать SQL Server Management Studio (SSMS). Для этого выполните щелчок правой кнопкой на имени экземпляра и выберите команду Properties (свойства). В моем случае окно свойств показано на рис.2.

Рис.2 Определение режима авторизации

На рис.2 показывается, что мой экземпляр поддерживает смешанный режим авторизации (красная стрелка).

Другой способ — это использовать код T-SQL. На листинге ниже представлен код для вывода режима авторизации.

SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly')   

          WHEN 1 THEN 'Windows Authentication Only'   

          WHEN 0 THEN 'Windows and SQL Server Authentication'  

       END as [Authentication Mode];

Листинг 1: отображение режима авторизации

Изменение методов авторизации после установки SQL Server

Вы можете захотеть изменить установки авторизации для экземпляра SQL Server. Вы могли использовать настройки по умолчанию при установке для поддержки авторизации Windows, а затем приобрели программу, которая может подключаться к серверу только при использовании авторизации SQL Server. Или вы захотели сделать ваш экземпляр более безопасным, удалив поддержку авторизации SQL Server. Опции авторизации можно легко изменить, используя страницу свойств в SSMS, показанную на рис.2.

Если бы я захотел изменить поддержку авторизации только на Windows, все, что мне потребовалось бы сделать, это щелкнуть на кнопке “Windows authentication mode”, а затем на кнопке ОК для сохранения изменений. После изменения этого свойства, необходимо перезапустить экземпляр, чтобы изменения вступили в силу.

Логины SQL Server

Для подключения к SQL Server вы должны иметь доступ к серверу. Доступ гарантируется посредством логина. Логин также называют участником безопасности (security principal), он хранится в базе данных master. Есть одно исключение — это доступ к автономной базе данных. Пользователи автономных баз данных напрямую подключаются к базе данных без необходимости иметь логин в базе данных master. Автономные базы данных — это тема для последующих статей.

Имеется три типа логинов, которые хранятся в базе данных master: пользователь Windows, группа Windows и SQL. Давайте рассмотрим каждый из этих трех типов логинов.

Логин пользователя Windows предоставляет доступ отдельному пользователю Windows. При создании логина этого типа не требуется задавать пароль. Этот тип логина требует, чтобы пользователь сначала прошел валидацию, подключившись к домену Windows. Пароль хранится в домене Windows.

Логин SQL Server подобен логину Windows в том, что он предоставляет доступ к SQL Server для отдельного пользователя, но отличается тем, что пароль логина SQL хранится в базе данных master. Следовательно, при создании логина SQL Server требуется указывать пароль, а также некоторые другие опции, как показано на рис.3.

Рис.3 Настройка логина при авторизации SQL Server

На рис.3 показано, что для входа в SQL Server может быть применена политика паролей Windows и истечения срока действия, а также может потребовать от пользователя изменить пароль при первом входе в систему. Microsoft добавила эти новые возможности в SQL Server 2005. Для поддержки этих новых возможностей в приложениях может использоваться API NetValidatePasswordPolicy.

Последний тип логина, логин группы Windows, подобен логину Windows с незначительными отличиями. Логин группы Windows обеспечивает доступ к экземпляру SQL Server каждому логину Windows, который является членом группы. Группы Windows являются хорошим способом предоставить доступ множеству логинов Windows при наличии только одного логина SQL Server. Используя группу Windows, доступ к экземпляру SQL Server может регулироваться добавлением или удалением членов группы. Использование групп Windows помогает минимизировать усилия по обеспечению безопасности и решению проблем безопасности, связанных с логинами.

Внизу скриншота на рис.3 вы видите настройку для логина “Default Database” (база данных по умолчанию). При создании логина базой данных по умолчанию является база данных master. Вы можете поменять эту настройку на любую базу данных на сервере. Лучший вариант — установить по умолчанию базу данных, которую пользователь будет использовать при подключении к SQL Server.

Логины Windows считаются более безопасными из-за способа, каким сохраняется пароль для логина. Пароль для логина Windows сохраняется при использовании настоящего шифрования. В то время как пароль для логина SQL не шифруется, а хэшируется. Поэтому пароль SQL легче взломать. Для установки логинов и паролей Windows требуется администратор доменов, а для логинов SQL администраторы базы данных заводят логины и пароли. Использование админов доменов для управления паролями логинов обеспечивает еще один слой безопасности, обычно называемый разделением обязанностей. Разделение обязанностей по созданию и управлению логинами Windows от управления базами данных и доступа к ним обеспечивает дополнительный контроль безопасности по предоставлению доступа к данным, хранящимся на SQL Server.

Создание логина для SQL Server позволяет пользователям подключаться к серверу. Но один лишь логин не предоставляет пользователю доступ к каким-либо данным в различных базах данных на сервере. Чтобы логин мог читать и записывать данные в базу, он должен иметь доступ к тем или иным базам данных. Если требуется, для логина может быть установлен доступ к нескольким базам данных экземпляра.

Пользователи базы данных

Пользователь базы данных — это не то же самое, что и логин. Логин предоставляет пользователю или приложению возможность подключаться к экземпляру SQL Server, в то время как пользователь базы данных дает пользователю права на доступ к базе данных. В каждой базе данных, к которой логину требуется доступ, требуется определить пользователя; исключение составляет логин с правами системного администратора. Если логин имеет права сисадмина, он имеет доступ ко всем базам данных без необходимости связывать его с пользователем базы данных. Эта связь между логином и пользователем базы данных называется мэппингом пользователей. Мэппинг пользователя для логина может быть создан во время создания логина или позже для уже установленных логинов.

Создание пользователя базы данных при создании нового логина

Чтобы показать обеспечение мэппинга пользователя при создании нового логина, я создам новый логин SQL Server с именем “Red-Gate”. На скриншоте (рис.4) показано окно “Login – new”, где я определяю новый логин. Чтобы вывести это окно, я разворачиваю вкладку “Security” в дереве объектов моего экземпляра, а затем выполняю щелчок правой кнопкой на строке «Logins» и выбираю пункт “New Login…” из выпадающего списка.

Рис.4 Создание логина Red-Gate

На рис.4 я ввожу «Red-Gate» в качестве имени логина и пароль этого логина SQL в соответствующих полях диалога. Для предоставления доступа этому новому логину я выполняю щелчок на пункте “User Mapping” в левой панели. После этого откроется окно, показанное на рис.5.

Рис.5 Окно мэппинга пользователя

В красном прямоугольнике выводится список баз данных, с которыми можно связать мой новый логин. Для мэппинга логина “Red-Gate” с базой данных “AdventureWorks2019” мне нужно просто щелкнуть на флажке «Map» рядом с базой данных AdventureWorks2019. Теперь я получу то, что показано на скриншоте (рис.6).

Рис.6 Мэппинг логина с базой данных

После установки флажка Map имя “Red-Gate” автоматически заносится в столбец «User» для базы данных AdventureWorks2019. В интерфейсе автоматически генерируется имя пользователя базы данных, совпадающее с логином. Имена пользователей базы данных не обязательно должны совпадать с логинами. Если вы хотите использовать другое имя, просто наберите желаемое имя вместо предложенного (в моем случае “Red-Gate”). Мэппинг логина с пользователями базы данных обеспечивает только доступ к базе данных, но не предоставляет прав на чтение или обновление данных в базе. В следующих статьях я буду обсуждать предоставление доступа к объектам базы данных на чтение/запись.

Предположим я хочу связать мой новый логин “Red-Gate” и с другими пользовательскими базами данных. В этом случае мне нужно просто проставить флажки рядом с требуемыми базами данных. В данном примере я осуществляю мэппинг логина “Red-Gate” только с базой данных AdventureWorks2019. Для завершения процедуры мэппинга моего логина “Red-Gate” с пользователем базы данных “Red-Gate” нужно щелкнуть кнопку «ОК».

Создание нового пользователя базы данных и связывание его с существующим логином

Иногда, когда логин уже существует, требуется предоставить ему доступ к тем или иным базам данных. Предположим, что теперь я хочу установить доступ моему логину Red-Gate к базе данных с именем MyDatabase. Чтобы предоставить логину Red-Gate доступ к еще одной базе данных, у меня есть несколько вариантов. Одним из них может быть просто модификация мэппинга пользователя путем изменения свойств логина. Это подобно тому, как я только что показал, добавляя мэппинг пользователя при создании логина Red-Gate.

Другой вариант — это добавление нового пользователя в базу данных MyDatabase, а затем связывание этого нового пользователя базы данных с логином Red-Gate. Чтобы создать нового пользователя в базе данных MyDatabase, нужно сначала развернуть базу данных, щелкнуть правой кнопкой на пункте “Security”, переместить указатель на пункт «New», а затем щелкнуть на пункте «User…», как показано на рис.7.

Рис.7 Диалог ввода нового пользователя базы данных

При щелчке на пункте меню «User…» откроется окно, показанное на рис.8.

Рис.8 Добавление нового пользователя базы данных

Чтобы предоставить логину Red-Gate доступ к MyDatabase, нужно заполнить форму на рис.8. Сначала рассмотрим пункт “User Type” (тип пользователя). Значением по умолчанию для этого поля является “SQL User with Login” (пользователь SQL с логином). Имеется четыре других типа: SQL user without login (пользователь SQL без логина), User mapped to a certificate (пользователь, связанный с сертификатом), User mapped to an asymmetric key (пользователь, связанный с асимметричным ключом) и пользователи Window. Поскольку я создаю пользователя, который будет связан с логином SQL, я использую значение по умолчанию. Затем я ввожу имя создаваемого пользователя базы данных. Это может быть любое имя, но я предпочитаю использовать имена, совпадающие с соответствующими логинами. Поэтому я введу «Red Gate» в поле «User name». Затем я свяжу нового пользователя с логином. Для этого я могу либо набрать «Red Gate» для логина, либо использовать кнопку «…» для навигации по списку существующих логинов и выбрать нужный.

Последнее, что требуется, это определить схему по умолчанию для этого логина. Имя схемы ассоциируется с коллекцией объектов базы данных, владельцем которых является пользователь базы данных. По умолчанию каждая база данных имеет схему с именем «dbo», владельцем которой является учетная запись пользователя «dbo». При задании нового пользователя базы данных не обязательно указывать схему. Если схема не задана, будет использоваться схема по умолчанию «dbo». Я оставлю обсуждение различных аспектов схем для другой статьи. Когда я создаю нового пользователя базы данных Red-Gate, я оставляю пустым поле схемы по умолчанию и позволяю процессу создания нового пользователя автоматически установить схему по умолчанию в «dbo».

После создания нового пользователя я могу проверить его существование в базе данных, развернув ветку «User» в папке «Security» браузера объектов. Вы также можете создать нового пользователя базы данных и связать его с логином с помощью скрипта. В листинге 2 приводится пример использования T-SQL для создания того же пользователя, которого я только что создал визуальными средствами.

USE [MyDatabase]

GO

CREATE USER [Red-Gate] FOR LOGIN [Red-Gate]

GO

Листинг 2: Создание пользователя базы данных Red-Gate с помощью T-SQL

Методы авторизации SQL Server, логины и пользователи базы данных

Для подключения к SQL Server человеку или процессу необходимо авторизоваться. Имеется два различных метода авторизации на SQL Server: Windows и SQL Server. Метод Windows более безопасен и рекомендуется для подключении к SQL Server. Каждое авторизованное подключение к SQL Server получает доступ к экземпляру посредством логина. Логины определяются на уровне сервера. Сами по себе логины не обеспечивают доступ к данным на SQL Server. Для этого необходимо связать логин с пользователем базы данных. Методы авторизации, логины и пользователи базы данных обеспечивают основы безопасности SQL Server.

В Microsoft SQL Server, исторически сложилось 2 возможных типа аутентификации:

• внутренняя аутентификация средствами SQL 
• аутентификация Windows.

Режим проверки Windows, является основным, современным и рекомендованным к использованию типом аутентификации, а аутентификация средствами SQL Server оставлена преимущественно для совместимости с legacy системами, либо специальными задачами. Она включется только, если выбрать смешанный режим проверки подлиности (он разрешает оба типа аутентификации SQL Server и Windows). Проверку подлиности Windows отключить нельзя.

• При включении смешанного режима, основной встроенной административной учетной записью, является SA, она обладает максимальными полномочиями на SQL сервере
• Если смешанный режим не включался, то УЗ SA также будет создана, но отключена по умолчанию.

УЗ SA рекомендуется включать, только если это требуется для работы ПО, и ее пароль должен быть максимально безопасным. Хоть сейчас в целом нет необходимости использовать встроенную аутентификацию SQL, но еще встречается в инструкциях к third-party software, по прежнему использовать логин sa  для подключения и управления СУБД.

Преимущества аутентификации Windows

1. По сравнению  с аутентификацией SQL — она более безопасна, тк не передается логин и пароль, а используется встроенные механизмы безопасности Windows, токены или сертификаты
2. Централизованное управление (создание\изменение\блокирование) учетными записями на уровне windows машины или инфраструктуры AD
3. Удобство пользователя: локально подключение осуществляется через сессию Windows

Преимущества аутентификации SQL сервер

1. Обеспечение поддержки устаревших систем
2. Возможность минимизировать взаимодействие с внешними системами. Например, при предоставлении доступа только УЗ SQL, можно ограничить перечень УЗ, которым позвонено получать доступ к данным, причем централизованное упраление УЗ не позволит к ним подключиться просто сменив пароль на уровне AD
3. Возможность в рамках одной сессии настроит разные процессы с разными правами доступа.

Как изменить тип аутентификации в Microsoft SQL Server 2019

Самый простой способ изменения типа аутентификации SQL, это использование графического интерфейса SQL Server Management Studio (SSMS). 

1. Запустить SSMS, и указать имя целевого SQL сервера
2. Подключиться и выбрать свойства сервера (Properties)
3. Перейти на закладку Security и выбрать необходимый режим проверки подлинности.
4. Нажать ОК
5. в случае, если режим аутентификации менялся, то для применения настроек, необходимо выполнить перезапуск службы SQL Server или перезагрузить сервер целиком.

В этой статье мы рассмотрим средства SQL Server для обеспечения безопасности и лучшие практики, связанные с настройкой и обеспечением безопасности в этой СУБД.

Для начала вспомним базовые концепции безопасности SQL Server. MSSQL управляет доступом к объектам через аутентификацию и авторизацию.

• Аутентификация — это процесс входа в SQL Server, когда пользователь отправляет свои данные на сервер. Аутентификация устанавливает личность пользователя, который проходит аутентификацию;
• Авторизация — это процесс определения того, к каким защищаемым объектам может обращаться пользователь, и какие операции разрешены для этих ресурсов.

Многие объекты SQL Server имеют свои разрешения, которые могут наследоваться от вышестоящего объекта. Разрешения могут быть предоставлены отдельному пользователю, группе или роли.

Аутентификация в SQL Server

Аккаунт SQL Server можно разделить на 2 части: Имя входа и Пользователь.

• Имя входа – это глобальный логин для всего экземпляра SQL Server. С помощью него вы проходите процесс аутентификации;
• Пользователь – это участник базы данных, привязанный к определенному Имени Входа.

Например, ваше имя входа на сервер может быть domain\username, а пользователь в базе данных, привязанный к этому имени входа может называться domain_databaseUser. Практически всегда имя входа и пользователь в базе данных совпадают по названию, но нужно иметь в виду что они могут и различаться, иметь разные имена.

SQL Server поддерживает 2 режима аутентификации:

• Аутентификация Windows (Windows Authentication) – аутентификация осуществляется с помощью системы безопасности Windows. Пользователям, которые уже аутентифицированы в Windows и имеют права на SQL Server не нужно предоставлять дополнительные учетные данные.
• Смешанный режим аутентификации (Mixed Mode Authentication) – в этом режиме помимо аутентификации Windows поддерживается аутентификация самого SQL Server через логин и пароль.

Microsoft рекомендует использовать аутентификацию Windows, если есть такая возможность. Для аутентификации посредством логина и пароля, данные (логин и пароль) передаются по сети, хоть и в зашифрованном виде. При Windows аутентификации по сети передаётся серия зашифрованных сообщений, в которых не участвует пароль пользователя.

Но некоторые приложения, особенно старые, не поддерживают аутентификацию Windows, поэтому при установке режима аутентификации стоит учитывать какие приложения будут подключаться к серверу.

SQL Server поддерживает три типа Login Name (имен входа):

• Локальная учетная запись пользователя Windows или учетная запись домена/доверенного домена.
• Группа Windows. Предоставление доступа локальной группе Windows или группе из AD домена. Позволяет предоставить доступ ко всем пользователям, которые являются членами группы.
• Логин SQL Server (SQL Server authentication). SQL Server хранит имя пользователя и хэш пароля в базе данных master, используя методы внутренней аутентификации для проверки входа в систему.

SQL Server автоматически интегрируется с Active Directory. Если вы хотите раздать права доменной учетной записи, вам нужно использовать NetBios имя домена и логин учетной записи. Например для пользователя username в домене domain.local будет верным “domain\username”.

Авторизация в SQL Server

Для авторизации SQL Server использует безопасность на основе ролей, которая позволяет назначать разрешения для роли или группы Windows/домена, а не отдельным пользователям. В SQL Server есть встроенные роли сервера и баз данных, у которых есть предопределенный набор разрешений.

В SQL Server есть 3 уровня безопасности, их можно представить, как иерархию от высшего к низшему:

• Уровень сервера – на этом уровне можно раздать права на базы данных, учетные записи, роли сервера и группы доступности;
• Уровень базы данных включают в себя схемы, пользователи базы данных, роли базы данных и полнотекстовые каталоги;
• Уровень схемы включают такие объекты, как таблицы, представления, функции и хранимые процедуры.

Встроенные роли сервера

Роль	Описание
sysadmin	Участник роли имеет полные права ко всем ресурсам SQL Server.
serveradmin	Участники роли могут изменять параметры конфигурации на уровне сервера и выключать сервер.
securityadmin	Участники роли управляют логинами и их свойствами. Они могут предоставлять права доступа GRANT, DENY и REVOKE на уровне сервера и на уровне базы данных, если имеют к ней доступ. securityadmin мало чем отличается от роли sysadmin, потому что участники этой роли потенциально могут получить доступ ко всем ресурсам SQL Server.
processadmin	Участники роли могут завершать процессы, запущенные в SQL Server.
setupadmin	Участники роли могут добавлять и удалять связанные серверы с помощью TSQL.
bulkadmin	Участники роли могут запускать BULK INSERT операции.
diskadmin	Участники роли могут управлять устройствами резервного копирования. На практике эта роль практически не применяется.
dbcreator	Участники роли могут создавать, изменять, удалять и восстанавливать базы данных.
public	Каждый логин SQL Server находится в этой роли. Изменить членство public нельзя. Когда у пользователя нет разрешения для объекта, к которому он получает доступ, пользователь наследует разрешения public роли для этого объекта.

Схема ролей SQL Server:

На практике использования серверных ролей не особо распространено, потому что часто пользователю нужен уникальный набор разрешений. Исключением могут быть роль sysadmin для системных администраторов и роль public.

Встроенные роли базы данных

Роль	Описание
db_owner	Участники роли могут выполнять все действия по настройке и обслуживанию базы данных, включая удаление.
db_securityadmin	Участники роли могут менять членство других ролей. Участники этой группы потенциально могут увеличить свои права до db_owner, поэтому стоит считать эту роль эквивалентной db_owner.
db_accessadmin	Участники роли могут управлять доступом к базе данных для существующих на сервере логинов.
db_backupoperator	Участники роли могут выполнять резервное копирование базы данных.
db_ddladmin	Участники роли могут выполнять любую DDL команду в базе данных.
db_datawriter	Участники роли могут создавать/изменять/удалять данные во всех пользовательских таблицах в базе данных.
db_datareader	Участники роли могут считывать данные со всех пользовательских таблиц.
db_denydatawriter
db_denydatareader	Участникам роли запрещен доступ к пользовательским таблицам базы данных.

Так же стоит отдельно выделить специальные роли в базе данных msdb.

db_ssisadmin db_ssisoperator db_ssisltduser	Участники этих ролей могут администрировать и использовать SSIS (SQL Server Integration Services).
dc_admin dc_operator dc_proxy	Участники этих ролей могут администрировать и использовать сборщик данных.
PolicyAdministratorRole	Участники этой роли имеют полный доступ к политикам SQL Server
ServerGroupAdministratorRole ServerGroupReaderRole	Участники этих ролей имеют полный доступ к зарегистрированным группам серверов.
SQLAgentUserRole SQLAgentReaderRole SQLAgentOperatorRole	Участники этих ролей имеют полный доступ заданиям агента SQL Server

Заметка: имейте в виду, что участники ролей dc_ssisadmin и dc_admin могут повысить свои права до уровня sysadmin.

Схема по встроенным ролям баз данных в SQL Server:

Роли приложений

Роль приложения – это объект базы данных (такой же, как и обычная роль базы данных), который позволяет с помощью аутентификации через пароль менять контекст безопасности в базе данных. В отличие от ролей баз данных, роли приложений по умолчанию находятся в неактивном состоянии и активируются, когда приложение выполняет процедуру sp_setapprole и вводит соответствующий пароль.

В отличие от обычных ролей, роли приложений практически никогда не используются. Как исключение, их применение можно найти в multi-layer приложениях.

Фильтрация данных в SQL Server

Фильтрация данных в SQL Server через хранимые процедур/представления/функции можно отнести к реализации принципу наименьших привилегий, так как вы предоставляете доступ не ко всем данным в таблице, а лишь к некоторой их части.

Например, можно предоставить пользователю права только на SELECT из представления и запретить прямой доступ к таблицам, которые используются в представлении. Таким образом вы предоставите доступ только к части данных из таблицы, задав фильтр where в представлении.

Фильтрация данных через Row-Level Security

Безопасность на уровне строк или Row-Level Security (RLS) позволяет фильтровать данные таблицы для разных пользователей по настраиваемому фильтру. Это осуществляется через SECURITY POLICY в T-SQL

На данном скриншоте политика настраивается таким образом, что пользователь Sales1 будет видеть строки таблицы, в которых значение столбца Sales равняется имени пользователя (Sales1), а пользователь Manager будет видеть все строки.

Схемы в SQL Server

У некоторых объектов SQL Server (таблицы, процедуры, представления, функции) есть схема. Схемы можно представить, как контейнеры для различных объектов (или пространство имён/namespace, если вы знакомы с программированием).

Например, если у пользователя есть права на select из схемы, то пользователь так же может делать select со всех объектов этой схемы. То есть объекты, принадлежащие схеме, наследуют её разрешения. Когда пользователи создают объекты в схеме, объекты принадлежат владельцу схемы, а не пользователю. Разрешения не наследуются от схемы пользователями. Т.е. у пользователей со схемой dbo по умолчанию, нет разрешений которые предоставлены этой схеме – они должны быть явно указаны.

Главное отличие схем от ролей в том, что разрешения на схемы могут быть предоставлены ролям. Например, у роли testrole могут быть разрешения select со схемы schema1 и разрешения на select/update на схеме schema2. Объект может принадлежать всего одной схеме, но права на него могут быть у нескольких ролей.

Встроенные схемы

В SQL Server есть встроенные системные схемы:

• dbo
• guest
• sys
• INFORMATION_SCHEMA

Схема dbo является схемой по умолчанию для новых баз данных, а пользователь dbo является владельцем схемы dbo. По умолчанию, новые пользователи в базе данных имеют схему dbo в качестве схемы по умолчанию. Другие встроенные схемы нужны для системных объектов SQL Server.

Шифрование данных средствами SQL Server

SQL Server может шифровать данные, процедуры и соединения с сервером. Шифрование возможно с использованием сертификата, асимметричного или симметричного ключа. В SQL Server используется иерархичная модель шифрования, то есть каждый слой иерархии шифрует слой под ним. Поддерживаются все известные и популярные алгоритмы шифрования. Для реализации алгоритмов шифрования используется Windows Crypto API.

Самыми распространенными типами шифрования являются TDE (Прозрачное шифрование данных) и Always Encrypted.

Прозрачное шифрование данных

Прозрачное шифрование данных или Transparent Data Encryption шифрует всю базу целиком. При краже физического носителя или .mdf/.ldf файла, злоумышленник не сможет получить доступ к информации в базе данных.

Диаграмма, для того чтобы представить весь процесс

Базовое шифрование базы данных через T-SQL:

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
go
CREATE CERTIFICATE ServerCert WITH SUBJECT = 'DEK Certificate';
go
USE AdventureWorks2012;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE ServerCert;
GO
ALTER DATABASE AdventureWorks2012
SET ENCRYPTION ON;
GO

Always Encrypted

Эта технология позволяет хранить шифрованные данные в SQL Server без передачи ключей шифрования самому SQL Server. Always Encrypted так же как и TDE шифрует данные в базе данных, но не на уровне базы, а на уровне столбца.

Для шифрования Always Encrypted использует 2 ключа:

• Column Encryption Key (CEK)
• Column Master Key (CMK)

Все процессы шифрования и дешифрования данных происходят на клиенте, в базе данных хранятся только зашифрованное значение ключа шифрования (CEK).

Always Encrypted так же позволяет ограничить доступ к данным даже для DBA, таким образом давая возможность не беспокоиться о том, что администратор получит доступ к данным, к которым не должен.

Когда стоит использовать шифрование SQL Server?

Шифрование данных это одна из важных мер обеспечения безопасности, но шифрование может быть требовательно к ресурсам сервера и иногда может быть лишним.

Если пользователи обращаются к данным через общедоступную сеть, то для обеспечения безопасности может потребоваться шифрование, но если данные передаются по защищенной интрасети или ВПН, то необходимости в шифровании данных нет. Так же стоит рассмотреть возможность шифрования данных, если есть угроза кражи физического носителя с базами данных.

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

Использование Group Managed Service Accounts для SQL Server

Групповые управляемые учетные записи службы или gMSA – это специальная учетная запись, которая автоматически управляется Active Directory. gMSA это развитие технологии MSA, так как MSA было невозможно использовать в кластерных сценариях.

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

Имейте в виду, что версия Windows Server для работы с gMSA должна быть не ниже 2012.

Оценка уязвимостей SQL Server через SSMS

В SQL Server Management Studio есть функция оценки уязвимостей для базы данных.

Выберите базу данных -> Tasks -> Vulnerability Assessment -> Scan For Vulnerabilities.

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

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

Аудит активности в SQL Server

SQL Server предоставляет возможность вести аудит любой пользовательской активности в экземпляре сервера.

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

Рассмотрим базовую настройку аудита:

В SSMS, во вкладке Security -> Audits создайте новый аудит.

Затем, для аудита нужно создать Спецификацию (Audit Specification), для указания событий, которые будут отслеживаться.

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

Общие рекомендации по безопасности SQL Server

Всегда следуйте принципу наименьших привилегий. В том числе настройте аккаунт службы SQL Server с помощью gMSA. Ни в коем случае не используйте доменный аккаунт с привилегиями администратора домена.

Принцип наименьших привилегий

Когда вы заводите новых пользователей, рекомендуется использовать принцип LUA (Least-privileged User Account или Аккаунт с Наименьшими Правами). Этот принцип является важной частью безопасности сервера и данных.

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

Предоставление прав ролям, а не пользователям

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

Рекомендуется предоставлять разрешения ролям, а пользователей добавлять в роли. Таким образом вы добьетесь большей прозрачности, так как все пользователи той или иной роли будут иметь одинаковые права. Добавить или удалить пользователей из роли проще, чем воссоздать отдельные наборы разрешений для отдельных пользователей. Роли могут быть вложенными, но так делать не рекомендуется, из-за меньшей прозрачности и потенциального ухудшения производительности (если вложенных ролей станет слишком много).

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

Аутентификация Windows — один из важнейших способов обеспечения безопасности MS SQL Server, который позволяет установить соединение с базой данных с использованием аккаунта Windows пользователя. Данный механизм аутентификации обеспечивает высокий уровень безопасности и снижает риск несанкционированного доступа к данным.

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

Для настройки аутентификации Windows в MS SQL Server необходимо выполнить несколько шагов. Во-первых, необходимо установить режим аутентификации Windows в SQL Server Management Studio. Затем следует настроить подключение клиента, указав в нем требуемые параметры для аутентификации Windows. Наконец, необходимо проверить, что пользователь имеет соответствующие разрешения на подключение к базе данных.

Важно отметить, что при использовании аутентификации Windows все учетные записи пользователей, которым предоставляется доступ к базе данных, должны быть аккаунтами Windows. Это означает, что отключена возможность использования SQL Server Authentication и аутентификации по логину и паролю.

Аутентификация Windows является надежным и удобным способом обеспечения безопасности MS SQL Server. Ее использование позволяет значительно снизить риск несанкционированного доступа к данным и защитить базу данных от внешних угроз.

Что такое аутентификация Windows?

Windows предлагает два основных метода аутентификации: аутентификацию на основе пароля и аутентификацию по сертификатам. Аутентификация на основе пароля требует от пользователей ввода уникального логина и пароля для входа в систему. Аутентификация по сертификатам использует электронные сертификаты для проверки подлинности пользователей.

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

Аутентификация Windows обеспечивает удобство и безопасность для пользователей и администраторов системы, так как пользователи могут использовать свои существующие учетные записи Windows без необходимости запоминать дополнительные пароли. Кроме того, она позволяет предоставлять доступ к ресурсам на базе различных уровней разрешений и управлять правами доступа.

Основные принципы

Основные принципы аутентификации Windows включают:

• Интеграция с учетными записями Windows: Аутентификация Windows использует учетные записи Windows для проверки подлинности пользователей базы данных. Пользователи могут использовать свои учетные данные Windows для входа в SQL Server без необходимости создавать отдельные учетные записи.
• Безопасность: Аутентификация Windows обеспечивает повышенный уровень безопасности, так как пользователи входят в базу данных с использованием своих проверенных учетных данных Windows. Это позволяет контролировать доступ к базе данных с помощью групповой политики Windows.
• Упрощенная администрация: Аутентификация Windows позволяет упростить администрирование базы данных, так как нет необходимости в управлении отдельными учетными записями SQL Server. Все управление пользователями и их правами возлагается на учетные записи Windows.
• Централизованное управление: Аутентификация Windows обеспечивает централизованное управление учетными данными пользователей. Администраторы могут управлять учетными записями Windows в Active Directory, влияя на доступ пользователей к базе данных.

Основные принципы аутентификации Windows для MS SQL Server позволяют создавать безопасные и удобные среды для работы с базой данных, используя проверенные учетные данные Windows пользователей.

Как работает аутентификация Windows для MS SQL Server?

Аутентификация Windows для MS SQL Server предоставляет механизмы для проверки подлинности пользователей базы данных с использованием учетных записей Windows. Это означает, что пользователи могут использовать свои учетные записи Windows для получения доступа к MS SQL Server.

При использовании аутентификации Windows, процесс проверки подлинности происходит следующим образом:

1. Пользователь пытается подключиться к базе данных MS SQL Server, указывая свои учетные данные Windows.
2. Сервер SQL проверяет правильность предоставленной информации и сверяет ее с информацией в Active Directory.
3. Если информация совпадает, пользователь получает доступ к серверу SQL, и ему предоставляются права, соответствующие его роли в базе данных.

Преимущества аутентификации Windows для MS SQL Server:

• Удобство использования: Пользователям необходимо запоминать только свои учетные данные Windows, вместо отдельных учетных записей SQL Server.
• Централизованное управление: Учетные записи пользователей хранятся в Active Directory, что обеспечивает централизованное управление пользователями и правами доступа.
• Безопасность: Аутентификация Windows позволяет использовать все механизмы безопасности, реализованные в Windows, такие как сложные политики паролей и двухфакторная аутентификация.

Для настройки аутентификации Windows для MS SQL Server необходимо убедиться, что режим аутентификации установлен в «Mixed Mode», где доступны и аутентификация Windows, и аутентификация SQL Server. Затем необходимо добавить учетные записи пользователей Windows в базу данных и предоставить им необходимые права доступа.

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

Настройка аутентификации Windows

Для настройки аутентификации Windows в MS SQL Server необходимо выполнить следующие шаги:

1. Убедитесь, что сервер SQL установлен и доступен.
2. Запустите инструмент управления SQL Server Configuration Manager.
3. В меню слева выберите «SQL Server Services» и найдите ваш экземпляр SQL Server.
4. Щелкните правой кнопкой мыши на экземпляре и выберите «Properties».
5. Перейдите на вкладку «Security» и выберите «Windows Authentication».
6. Нажмите «OK» для сохранения изменений и перезапустите службу SQL Server для применения настроек.
7. Настройте доступ для пользователей Windows в SQL Server Management Studio.
8. Откройте SQL Server Management Studio и подключитесь к вашему экземпляру SQL Server.
9. Выберите базу данных, к которой хотите предоставить доступ.
10. Щелкните правой кнопкой мыши на базе данных, выберите «Properties» и перейдите на вкладку «Security».
11. В разделе «Users or roles» добавьте нужных пользователей Windows и настройте им права доступа.
12. Нажмите «OK» для сохранения изменений.

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

Как настроить аутентификацию Windows в MS SQL Server?

Чтобы настроить аутентификацию Windows в MS SQL Server, следуйте следующим шагам:

1. Откройте SQL Server Management Studio и подключитесь к экземпляру SQL Server.
2. Нажмите правой кнопкой мыши на экземпляр SQL Server и выберите «Свойства».
3. В окне «Свойства сервера» выберите вкладку «Безопасность».
4. В разделе «Режим сервера» выберите «Аутентификация Windows».
5. Нажмите кнопку «OK», чтобы сохранить изменения и закрыть окно «Свойства сервера».

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

Важно отметить, что для успешной аутентификации Windows в MS SQL Server, у пользователей должны быть учетные записи Windows и разрешения на подключение к базе данных SQL Server.

Настройка аутентификации Windows в MS SQL Server является эффективным способом повышения безопасности базы данных и защиты от несанкционированного доступа.

Преимущества аутентификации Windows в MS SQL Server:

Преимущество	Описание
Улучшенная безопасность	Аутентификация Windows позволяет управлять доступом к базе данных на основе учетных записей Windows, что повышает безопасность данных.
Удобство использования	Пользователи могут использовать свои существующие учетные записи Windows для аутентификации в MS SQL Server, что делает процесс удобным и простым.
Интеграция с существующей инфраструктурой	Аутентификация Windows позволяет интегрировать MS SQL Server в существующую сетевую инфраструктуру, что упрощает управление пользователями и учетными записями.

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

Преимущества аутентификации Windows

1. Интеграция с существующей инфраструктурой Windows:

Аутентификация Windows позволяет использовать существующую инфраструктуру аутентификации Windows, такую как активная директория, для управления доступом к MS SQL Server. Это позволяет упростить процесс аутентификации и управления доступом пользователей.

2. Высокая безопасность:

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

3. Удобное управление доступом:

Аутентификация Windows позволяет упростить управление доступом пользователей к MS SQL Server. Администраторы могут назначать роли и права доступа с помощью средств активной директории, что уменьшает риск ошибок и облегчает процесс управления доступом.

4. Единый пользовательский опыт:

Аутентификация Windows позволяет пользователям использовать одни и те же учетные данные для доступа к различным приложениям и службам, включая MS SQL Server. Это упрощает процесс аутентификации и повышает удобство использования для пользователей.

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

Какие преимущества дает аутентификация Windows для MS SQL Server?

Аутентификация Windows предоставляет ряд преимуществ при использовании MS SQL Server:

Удобство и простота использования	Аутентификация Windows позволяет пользователям входить в систему базы данных без необходимости вводить отдельные учетные данные. Вместо этого, она использует информацию о входе в систему Windows, что делает процесс аутентификации более удобным и простым.
Улучшенная безопасность	Аутентификация Windows гарантирует безопасность, поскольку базируется на доверии к операционной системе Windows. Это позволяет контролировать доступ пользователей к базе данных на основе учетной записи Windows и групп безопасности. Кроме того, аутентификация Windows поддерживает механизмы шифрования, что обеспечивает защиту конфиденциальности данных.
Единое управление учетными записями	Использование аутентификации Windows позволяет централизованно управлять учетными записями пользователей в системе Windows. Это означает, что администраторам необходимо поддерживать только одну базу данных пользователей, что упрощает администрирование и обновление учетных данных.

В итоге, аутентификация Windows для MS SQL Server является удобным, безопасным и эффективным методом аутентификации, который предоставляет ряд преимуществ для пользователей и администраторов.