Authentication in SQL Server
SQL Server supports two authentication modes, Windows authentication mode and mixed mode.
Windows authentication is the default, and is often referred to as integrated security because this SQL Server security model is tightly integrated with Windows. Specific Windows user and group accounts are trusted to log in to SQL Server. Windows users who have already been authenticated do not have to present additional credentials.
Mixed mode supports authentication both by Windows and by SQL Server. User name and password pairs are maintained within SQL Server.
We recommend using Windows authentication wherever possible. Windows authentication uses a series of encrypted messages to authenticate users in SQL Server. When SQL Server logins are used, SQL Server login names and encrypted passwords are passed across the network, which makes them less secure.
With Windows authentication, users are already logged onto Windows and do not have to log on separately to SQL Server. The following SqlConnection.ConnectionString specifies Windows authentication without requiring users to provide a user name or password.
Logins are distinct from database users. You must map logins or Windows groups to database users or roles in a separate operation. You then grant permissions to users or roles to access database objects.
Authentication Scenarios
Windows authentication is usually the best choice in the following situations:
There is a domain controller.
The application and the database are on the same computer.
You are using an instance of SQL Server Express or LocalDB.
SQL Server logins are often used in the following situations:
If you have a workgroup.
Users connect from different, non-trusted domains.
Internet applications, such as ASP.NET.
Specifying Windows authentication does not disable SQL Server logins. Use the ALTER LOGIN DISABLE Transact-SQL statement to disable highly-privileged SQL Server logins.
Login Types
SQL Server supports three types of logins:
A local Windows user account or trusted domain account. SQL Server relies on Windows to authenticate the Windows user accounts.
Windows group. Granting access to a Windows group grants access to all Windows user logins that are members of the group.
SQL Server login. SQL Server stores both the username and a hash of the password in the master database, by using internal authentication methods to verify login attempts.
SQL Server provides logins created from certificates or asymmetric keys that are used only for code signing. They cannot be used to connect to SQL Server.
Mixed Mode Authentication
If you must use mixed mode authentication, you must create SQL Server logins, which are stored in SQL Server. You then have to supply the SQL Server user name and password at run time.
SQL Server installs with a SQL Server login named sa (an abbreviation of «system administrator»). Assign a strong password to the sa login and do not use the sa login in your application. The sa login maps to the sysadmin fixed server role, which has irrevocable administrative credentials on the whole server. There are no limits to the potential damage if an attacker gains access as a system administrator. All members of the Windows BUILTIN\Administrators group (the local administrator’s group) are members of the sysadmin role by default, but can be removed from that role.
SQL Server provides Windows password policy mechanisms for SQL Server logins. Password complexity policies are designed to deter brute force attacks by increasing the number of possible passwords. SQL Server can apply the same complexity and expiration policies to passwords used inside SQL Server.
Concatenating connection strings from user input can leave you vulnerable to a connection string injection attack. Use the SqlConnectionStringBuilder to create syntactically valid connection strings at run time. For more information, see Connection String Builders.
External Resources
For more information, see the following resources.
Изменение режима проверки подлинности сервера Change server authentication mode
Применимо к: Applies to: SQL Server SQL Server (все поддерживаемые версии) SQL Server SQL Server (all supported versions) Применимо к: Applies to: SQL Server SQL Server (все поддерживаемые версии) SQL Server SQL Server (all supported versions)
В этом разделе описывается, как изменить режим проверки подлинности сервера в SQL Server 2019 (15.x) SQL Server 2019 (15.x) с помощью среды SQL Server Management Studio SQL Server Management Studio или Transact-SQL Transact-SQL . This topic describes how to change the server authentication mode in SQL Server 2019 (15.x) SQL Server 2019 (15.x) by using SQL Server Management Studio SQL Server Management Studio or Transact-SQL Transact-SQL . В процессе установки компонент Компонент SQL Server Database Engine SQL Server Database Engine настраивается на использование режима проверки подлинности Windows или режима проверки подлинности SQL Server и Windows. During installation, Компонент SQL Server Database Engine SQL Server Database Engine is set to either Windows Authentication mode or SQL Server and Windows Authentication mode. После установки вы можете изменить режим проверки подлинности в любое время. After installation, you can change the authentication mode at any time.
Если во время установки был выбран Режим проверки подлинности Windows , то имя входа sa отключено, а пароль присваивается программой установки. If Windows Authentication mode is selected during installation, the sa login is disabled and a password is assigned by setup. Если впоследствии изменить режим проверки подлинности на проверку подлинности SQL Server и Windows, то имя входа sa останется отключенным. If you later change authentication mode to SQL Server and Windows Authentication mode, the sa login remains disabled. Чтобы можно было пользоваться именем входа sa, включите его и присвойте ему новый пароль с помощью инструкции ALTER LOGIN. To use the sa login, use the ALTER LOGIN statement to enable the sa login and assign a new password. Имя входа sa может подключаться к серверу только с использованием проверки подлинности SQL Server SQL Server . The sa login can only connect to the server by using SQL Server SQL Server Authentication.
Перед началом Before you begin
Учетная запись sa — хорошо известная учетная запись SQL Server SQL Server и часто становится мишенью злоумышленников. The sa account is a well known SQL Server SQL Server account and it is often targeted by malicious users. Не включайте учетную запись sa, если это не требуется для работы приложения. Do not enable the sa account unless your application requires it. Для имени входа sa очень важно использовать надежный пароль. It is important that you use a strong password for the sa login.
Изменение режима проверки подлинности с помощью SSMS Change authentication mode with SSMS
В обозревателе объектов среды SQL Server Management Studio SQL Server Management Studio щелкните правой кнопкой мыши сервер и выберите пункт Свойства. In SQL Server Management Studio SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.
На странице Безопасность , в разделе Серверная проверка подлинностивыберите новый режим проверки подлинности сервера, а затем нажмите кнопку ОК. On the Security page, under Server authentication, select the new server authentication mode, and then click OK.
В диалоговом окне среды SQL Server Management Studio SQL Server Management Studio нажмите кнопку ОК , чтобы подтвердить необходимость перезапуска SQL Server SQL Server . In the SQL Server Management Studio SQL Server Management Studio dialog box, click OK to acknowledge the requirement to restart SQL Server SQL Server .
В обозревателе объектов щелкните правой кнопкой мыши сервер и выберите пункт Перезапустить. In Object Explorer, right-click your server, and then click Restart. Если работает агент SQL Server SQL Server , он тоже должен быть перезапущен. If SQL Server SQL Server Agent is running, it must also be restarted.
Включение имени входа sa Enable sa login
Имя входа sa можно включить с помощью SSMS или T-SQL. You can enable the sa login with SSMS or T-SQL.
использование SSMS; Use SSMS
В обозревателе объектов разверните узел Безопасность, разверните «Имена входа», щелкните правой кнопкой мыши имя входа saи выберите Свойства. In Object Explorer, expand Security, expand Logins, right-click sa, and then click Properties.
На вкладке Общие, возможно, придется создать и подтвердить пароль для имени входа sa. On the General page, you might have to create and confirm a password for the sa login.
На странице Состояние в разделе Имя входа щелкните Включитьи нажмите кнопку ОК. On the Status page, in the Login section, click Enabled, and then click OK.
Использование Transact-SQL Using Transact-SQL
В следующем примере включается имя входа sa и устанавливается новый пароль. The following example enables the sa login and sets a new password. Замените надежным паролем. Replace with a strong password before you run it.
Изменение режима проверки подлинности (T-SQL) Change authentication mode (T-SQL)
В следующем примере проверка подлинности сервера переключается со смешанного режима (Windows + SQL) на Windows. The following example changes Server Authentication from mixed mode (Windows + SQL) to Windows only.
В следующем примере для изменения реестра сервера используется расширенная хранимая процедура. The following example uses an extended stored procedure to modify the server registry. При неправильном изменении реестра могут возникнуть серьезные проблемы. Serious problems might occur if you modify the registry incorrectly. В результате может потребоваться переустановка операционной системы. These problems might require you to reinstall the operating system. Корпорация Майкрософт не гарантирует, что эти проблемы можно устранить. Microsoft cannot guarantee that these problems can be resolved. Ответственность за изменение реестра лежит на пользователе. Modify the registry at your own risk.
Для изменения режима аутентификации необходимы разрешения системного администратора или сервера контроля The permissions required to change the authentication mode are sysadmin or Control Server
Основные средства обеспечения безопасности в 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.
Схема ролей 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 |
Схема по встроенным ролям баз данных в 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 или Аккаунт с Наименьшими Правами). Этот принцип является важной частью безопасности сервера и данных.
Для пользователей с административными правами рекомендуется выдавать разрешения только на те операции, которые им будет необходимы. Встроенные серверные роли стоит использовать только тогда, когда набор их разрешений полностью совпадает с задачами пользователя.
Предоставление прав ролям, а не пользователям
Когда пользователей становится много, управлять их разрешениями становится сложнее, и также становится сложнее не допустить ошибки в предоставлении прав.
Рекомендуется предоставлять разрешения ролям, а пользователей добавлять в роли. Таким образом вы добьетесь большей прозрачности, так как все пользователи той или иной роли будут иметь одинаковые права. Добавить или удалить пользователей из роли проще, чем воссоздать отдельные наборы разрешений для отдельных пользователей. Роли могут быть вложенными, но так делать не рекомендуется, из-за меньшей прозрачности и потенциального ухудшения производительности (если вложенных ролей станет слишком много).
Можно предоставить права пользователю на схему. В этом случае пользователи сразу смогут работать с вновь созданными объектами в этой схеме, в отличии от ролей, когда при создании нового объекта, роли нужно будет раздать на него права.