Проверка подлинности windows sql server

1. Как пользоваться данной инструкцией

2. Сетевая версия

2.1. Требования к настройкам SQL Server для работы ПО «Ветеринарный офис» в сетевом режиме

2.1.1. Включение режима проверки подлинности SQL Server и Windows. Включение пользователя SQL Server ‘SA’ с правами администратора.

2.1.2. Настройка SQL Server для доступа из сети.

2.1.3. Назначение текущему пользователю прав администратора SQL Server.

2.1.4. Установка SQL Server в ручном режиме

2.2. Требования к настройкам ПО «Ветеринарный офис» для работы в сетевом режиме

2.3. Если ничего не помогло

1. Как пользоваться данной инструкцией

Раздел «2. Сетевая версия» содержит вводные общие сведения для настройки сетевого режима работы.
Для выполнения настройки сетевого режима работы ПО «Ветеринарный офис» необходимо ознакомиться с пунктом 2 и последовательно выполнить пункты:
•    2.1. Требования к настройкам SQL Server для работы ПО «Ветеринарный офис» в сетевом режиме.
•    2.2. Требования к настройкам ПО «Ветеринарный офис» для работы в сетевом режиме.
Для выполнения пункта 2.1, потребуется выполнение некоторых из пунктов 2.1.1, 2.1.2, 2.1.3, 2.1.4.

2. Сетевая версия

Программа ветеринарной клиники «Ветеринарный офис» в базовом варианте рассчитана на работу нескольких пользователей. Нет необходимости приобретения дополнительных пакетов, для перехода в многопользовательский режим достаточно произвести некоторые настройки в программе и СУБД SQL Server (включена в инсталлятор), используемой для хранения данных. Основным для настройки сетевого режима является раздел «Требования к настройкам SQL Server для работы ПО «Ветеринарный офис» в сетевом режиме». Все остальные разделы в данном документе являются вспомогательными, либо дополняющими, описывающими конкретные операции из основного раздела. Ниже описана краткая вводная часть.

Прежде всего необходимо понимать, что ветеринарная программа имеет клиент-серверную архитектуру. В качестве сервера используется Microsoft SQL Server. Из этого следует, что для функционирования системы необходим ОДИН сервер и НЕСКОЛЬКО экземпляров программы ветеринарной клиники. Количество экземпляров «Ветеринарного офиса» определяется потребностями конечного потребителя и может колебаться от одного до нескольких десятков.

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

Далее следует определиться с режимом аутентификации пользователей на SQL Server. Существует два режима:

•    Режим проверки подлинности Windows.
•    Режим проверки подлинности SQL Server и Windows.

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

Тем не менее, если у вас возникают проблемы с выполнением миграции в одно пользовательском режиме, что возможно в Windows Vista, рекомендуется отнести текущего пользователя к администраторам SQL Server.

2.1. Требования к настройкам SQL Server для работы ПО «Ветеринарный офис» в сетевом режиме

Для того чтобы «Ветеринарный офис» работал в сетевом режиме, когда несколько пользователей работают с одной БД, необходимо чтобы выполнялись следующие настройки SQL Server:

1.    Разрешены локальные и удаленные соединения (Настройка SQL Server для доступа из сети)
2.    Разрешен протокол TCP/IP (Настройка SQL Server для доступа из сети)
3.    Включен режим проверки подлинности SQL Server и Windows и включен пользователь SQL Server ‘SA’ с правами администратора.

В случае, если на сервере планируется использовать брандмауэр, необходимо разрешить доступ к SQL Server. Для этого необходимо открыть порт 1433. Внимание, для того, чтобы SQL Server ожидал подключения по порту 1433, он должен быть установлен экземпляром по умолчанию. Для этого необходимо выполнить установку SQL Server в ручном режиме. В случае именованных экземпляров SQL Server используются динамические порты и корректно настроить брандмауэр не получится.
Инсталлятор из пакета установки ПО «Ветеринарный офис» устанавливает именованный экземпляр SQL Server и разрешает удаленные и локальные соединения по протоколу TCP/IP. Т.е. первые два требования выполняются инсталлятором.

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

2.1.1. Включение режима проверки подлинности SQL Server и Windows. Включение пользователя SQL Server ‘SA’ с правами администратора.

Данная настройка выполняется только при автоматической установке SQL Server инсталлятором ПО «Ветеринарный офис». Если проводилась ручная установка SQL Server, то проводить настройку в данной части не нужно, либо если требуется дополнительно убедиться, что включен режим проверки подлинности SQL Server и Windows и включен пользователь SQL Server ‘SA’ с правами администратора.

Все проверки проводятся среде SQL Server Management Studio и поэтому необходимо, чтобы данная среда была установлена. Дальнейшая методика предполагает, что SQL Server Management Studio установлена на машине, где установлен SQL Server. Для уменьшения размера инсталлятора, среда SQL Server Management Studio была исключена из программы установки ПО «Ветеринарный офис». Поэтому ее необходимо скачать дополнительно, перейдя по ссылке http://htsl.ru/phocadownload/SQLServer2005_SSMSEE.msi для 32 разрядных систем и http://htsl.ru/phocadownload/SQLServer2005_SSMSEE_x64.msi для 64 разрядных систем.

После установки и запуска откроется окно соединения с SQL Server. Необходимо выбрать режим «Проверка подлинности Windows». Внимание: среду SQL Server Management Studio необходимо запускать с правами администратора.

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

После чего перейти к странице «Безопасность» и установить радио-переключатель «Проверка подлинности SQL Server и Windows» и нажать кнопку «ОК».

Затем перейти в раздел «Безопасность->Имена входа->sa», выбрать свойства логина «SA».

На странице «Общие» установить пароль, для чего заполнить поля «Пароль», «Подтверждение пароля». Например ввести туда значение «123». Переключатель «Требовать использование политики паролей» должен быть снят.

На странице «Состояние» необходимо установить радио-переключатели «Предоставить» и «Включено».

Далее необходимо убедиться, что логин «SA» был включен корректно, для чего выполнить отключение от сервера. Правой кнопкой по серверу и из контекстного меню выбрать пункт «Отключить».

Далее выбрать меню «Файл->Подключить к обозревателю объектов». Выбрать «Проверка подлинности SQL Server». Имя входа указать «SA». В качестве пароля «123» (или свой, который был установлен в вышеописанных шагах)

Нажать кнопку «Соединить». Если подключение прошло успешно, то включение режима проверки подлинности SQL Server и Windows и включение пользователя SQL Server ‘SA’ с правами администратора прошло успешно. В противном случае повторить все шаги с начала данного раздела.

2.1.2. Настройка SQL Server для доступа из сети.

По умолчанию SQL Server после установки настроен на подключение только с локального компьютера. После установки необходимо разрешить подключении из сети. Для этого следует воспользоваться утилитой «SQL Server Surface Area Configuration», которая располагается по пути  «Пуск\Все программы\Microsoft SQL Server 2005\Configuration Tools».

После запуска кликнуть ссылку «Surface Area Configuration for Services and Connections». В открывшемся окне необходимо выбрать пункт «Remote Connections» и установить радио-переключатель «Local and remote connections» и там же установить радио-переключатель «Using TCP/IP only».

Затем следует нажать кнопку «ОК». В открывшемся окне с предупреждением о необходимости перезапуска службы SQL Server так следует нажать кнопку «ОК»

Для вступления измененных настроек в силу необходимо либо перезагрузить компьютер, либо службу SQL Server. Для последнего можно воспользоваться утилитой «SQL Server Configuration Manager», располагающейся по пути «Пуск\Все программы\Microsoft SQL Server 2005\Configuration Tools».

2.1.3. Назначение текущему пользователю прав администратора SQL Server.

 Для этого необходимо воспользоваться утилитой «SQL Server Surface Area Configuration», которая располагается по пути  «Пуск\Все программы\Microsoft SQL Server 2005\Configuration Tools»

После чего кликнуть ссылку «Add New Administrator». Откроется окно назначения административных прав текущему пользователю:

Необходимо кликнуть кнопку «>>», что значит перенести все роли, после чего нажать кнопку «ОК»

2.1.4. Установка SQL Server в ручном режиме

Для установки SQL Server в ручном режиме, необходимо скачать его с нашего сайта, либо с сайта Microsoft. Инсталлятор SQL Server работает как в 32 так и 64 битной версиях Windows. В случае, если необходимо работать со включенным брандмауэром Windows, во время установки необходимо выбрать опцию «Экземпляр по умолчанию (Default instance)».
Для этого в окне «Registration Information» снять переключатель «Hide advanced configuration options».

В окне «Instance Name» установить радио переключатель в положение «Default instance»

Так же рекомендуется установить режим проверки подлинности SQL Server и Windows, для чего установить радио-переключатель в положение «Mixed Mode (Windows Authentication and SQL Server Authentication)». Тут же следует задать пароль администратора SQL Server (пользователь «SA») и в дальнейшем указывать его в настройках ПО «Ветеринарный офис».

Кроме этого рекомендуется предоставить текущему пользователю (если под ним предполагается настраивать SQL Server или работать с программой) административные права SQL Server. Для этого необходимо установить переключатель «Add user to the SQL Server Administrator role».

После установки, необходимо проверить и разрешить доступ к SQL Server из сети.

2.2. Требования к настройкам ПО «Ветеринарный офис» для работы в сетевом режиме

По умолчанию рабочие места ПО «Ветеринарный офис» настроены для подключения к локально установленному SQL Server (SQL Server ‘.’, БД: ‘VetClinic’, Встроенная проверка подлинности: ‘Да’). В случае установки удаленного рабочего места, необходимо настроить подключение к экземпляру SQL Server на сетевом компьютере. Для этого необходимо зайти в меню «Сервис->Настройки».

Далее откроется диалоговое окно настройки конфигурации. В разделе «Подключение» необходимо указать имя хоста удаленного компьютера с установленным SQL Server, для этого ввести значение в поле «SQL Server». Менять название базы данных в подавляющем большинстве случаев не требуется, рекомендуется оставить значение «VetClicnic». Поскольку в данной статье описывается режим проверки подлинности (аутентификации) пользователей SQL Server и Windows, необходимо изменить значение параметра «Встроенная проверка подлинности» в значение «нет». В качестве имени пользователя указать значение «sa» (как включить и настроить данного пользователя описано в соответствующем разделе). В поле «Пароль пользователя» указать пароль пользователя «sa».

Далее нажать кнопку «ОК». Система предложит выполнить пере подключение.

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

2.3. Если ничего не помогло

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

Базовое понимание безопасности SQL Server невозможно без осознания различий между учетными записями, пользователями, схемами и ролями. Кроме того, необходимо различать безопасность SQL Server и доверенную проверку подлинности Trusted Authentication. В настоящей статье я планирую разъяснить эти вопросы и поделиться основными знаниями, необходимыми начинающему администратору базы данных для успешной работы.

Безопасность SQL Server и доверенная проверка подлинности

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

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

Имена и пользователи

Существует два уровня доступа к экземпляру SQL Server: учетные записи пользователя сервера (или экземпляра) и пользователи базы данных. С помощью учетных записей серверы позволяют внешнему пользователю (далее в статье термин «пользователь» применяется для любого приложения, службы, API и т. д., пытающихся подключиться к SQL Server) выполнить начальное соединение с экземпляром SQL Server. В случае безопасности на основе SQL для этого требуются имя пользователя и пароль. В случае доверенной проверки подлинности это учетная запись домена.

Есть два способа создать эти учетные записи пользователя: с помощью Transact-SQL (https://msdn.microsoft.com/en-us/library/ms189751.aspx? f=255&MSPPError=-2147217396) или через графический интерфейс. Процедура использования T-SQL для создания учетных записей хорошо документирована, и лучше всего воспользоваться ссылкой на официальную документацию по Microsoft SQL Server. А пока рассмотрим способ создания учетной записи в графическом интерфейсе. Чтобы запустить диалоговое окно для создания учетных записей пользователей, подключитесь к экземпляру SQL Server в среде SQL Server Management Studio (SSMS) в обозревателе объектов, а затем разверните узел Security\Logins («Безопасность\Имена пользователя»). Щелкните правой кнопкой мыши на пункте Logins и выберите в контекстном меню пункт New Login («Создать учетную запись») (см. экран 1).

Экран 1. Создание учетной записи пользователя SQL Server

Вы увидите диалоговое окно для настройки параметров учетной записи, показанное на экране 2. Изменить имя пользователя можно в том же окне.

Экран 2. Настройка учетной записи пользователя SQL Server

Это вкладка General («Общие») для создания (и изменения) параметров учетной записи. Она отличается от двух ранее описанных схем безопасности. На вкладке General можно задать:

• Login name («Имя пользователя»). Используется при проверке подлинности. В случае Windows, или доверенной проверки подлинности, необходимо задать имя в формате DOMAIN\LOGIN, где LOGIN — имя пользователя внутри домена, из которого пользователь выполняет проверку подлинности. Если экземпляр SQL Server расположен в другом домене, то необходимы отношения доверия между этим доменом и доменом SQL Server.
• Password («Пароль»). При проверке подлинности SQL Server текстовое поле пароля включено, и вы вводите как имя пользователя, так и связанный с ним пароль.
• Password Policy («Настройки политики паролей») и Expiration («Срок действия»). Флажки для политики пароля и срока действия также установлены в режиме проверки подлинности SQL Server, и применяются те политики, которые действуют в Active Directory в домене, где размещается SQL Server. Назначая имя пользователя SQL Server, вы можете разрешить пользователям менять свои пароли после регистрации. В результате администратор базы данных лишается доступа к имени учетной записи конечного пользователя.
• Certificates («Сертификаты»), Keys («Ключи»), Credentials («Учетные данные»). В этой статье, предназначенной для начинающих, мы не будем рассматривать сертификаты, ключи и учетные данные.
• Default Database («База данных по умолчанию»). Когда подключение к SQL Server установлено, выполняются два шага: проверка подлинности (должно существовать имя пользователя для учетных данных домена пользователя, если используется Windows или доверенная проверка подлинности либо необходимо передать комбинацию имени пользователя и пароля в экземпляр SQL Server). Это первый барьер. Второй заключается в том, что у проверенного имени пользователя имеется связанный объект пользователя в базе данных по умолчанию — базе данных, первоначально настроенной как контекст имени пользователя после проверки удостоверения. Даже если первое препятствие преодолено, при отсутствии соответствующего пользователя базы данных в базе данных по умолчанию подключение не будет установлено, и соответствующая запись будет внесена в журнал ошибок SQL. Но есть исключения: если серверная роль пользователя настолько важна, что нужно установить для него по умолчанию неявные права в каждой базе данных, то необязательно наличие соответствующего пользователя в базе данных по умолчанию. Однако я забегаю вперед, так как мы еще не рассматривали пользователей базы данных или роли сервера. Достаточно отметить, что, когда вы выбираете базу данных по умолчанию в графическом интерфейсе, связанный пользователь базы данных не создается. Вы просто указываете, какой должна быть база данных по умолчанию. При этом вы используете вкладку User Mapping («Сопоставление пользователей») диалогового окна Create Login («Создание учетной записи»), чтобы создать связанного пользователя базы данных.

Перейдем к следующей вкладке Server Roles («Роли сервера»), показанной на экране 3. На этой странице можно выбрать любые роли на уровне SQL Server (экземпляра) для нового пользователя. Роли сервера представляют собой коллекции прав, также известные как защищаемые объекты, которые упаковываются в коллекцию, чтобы вам не приходилось назначать права каждому защищаемому объекту отдельно. По умолчанию каждая учетная запись является членом общедоступной роли, что позволяет установить основное подключение к экземпляру SQL Server. Далее в статье будет рассмотрена каждая роль сервера в составе Microsoft SQL Server.

Экран 3. Вкладка Server Roles

Следующая страница диалогового окна Create Login в среде SQL Server Management Studio предназначена для сопоставления учетных записей пользователей. Каждая учетная запись может иметь пользователя в одной или нескольких базах данных. На этой странице можно создать пользователей базы данных, связанных с новой учетной записью. Для этого нужно предоставить следующую информацию.

• Database («База данных»). Установите флажок рядом с базой данных, в которой нужно создать связанного пользователя для учетной записи.
• User Name («Имя пользователя»). Имя объекта пользователя не обязательно соответствует имени учетной записи, и далее будет показано, как это можно изменить.
• Default Schema («Схема по умолчанию»). Каждый пользователь базы данных должен быть назначен схеме по умолчанию. Схема представляет собой коллекцию объектов базы данных, отделенных логически (но не обязательно физически) от других объектов в базе данных. Можно предоставить пользователю или группе пользователей права для всех объектов данной схемы, например предоставить всем пользователям из бухгалтерии (или учетной записи службы бухгалтерского приложения) определенные права для всех объектов в схеме Billing, но не давать доступ к этим объектам другим пользователям. При назначении схемы по умолчанию для пользователя базы данных нет необходимости включать имя схемы в вызовы T-SQL к базе данных при адресации объектов в этой схеме. Это также означает, что если пользователю предоставлены права на создание объектов, то по умолчанию они будут созданы в этой схеме, если только не указать имя схемы при создании объектов. Далее в статье мы еще коснемся концепции схем.
• Database Role Membership («Членство в роли базы данных»). Точно так же, как на уровне экземпляра или сервера, каждая база данных располагает заранее определенной коллекцией прав, упакованных в ролях. Чуть позже мы рассмотрим роли базы данных, поставляемые с Microsoft SQL Server.

Обратимся к примеру диалогового окна для учетной записи пользователя SQLCRUISE\skipper (см. экран 4).

Экран 4. Пример настроек учетной записи пользователя

В этом примере пользователю SQLCRUISE\skipper предоставляются права для базы данных по умолчанию (lifeboat), где связанное имя пользователя — просто skipper. Схема по умолчанию — skipper_only. В двух других базах данных, в которых будут созданы пользователи для этой учетной записи, применяется то же имя пользователя, что и в имени пользователя (обычно ради упрощения идентификации), а схема по умолчанию — dbo, которая применяется по умолчанию в Microsoft SQL Server для всех определяемых пользователем объектов. Дополнительные сведения об этом будут приведены в следующем разделе. В случае с базой данных lifeboat мы предоставляем только членство в общедоступной роли базы данных, что предусматривает подключение к базе данных без дополнительных разрешений.

На следующей странице, Securables, представлены защищаемые объекты на уровне сервера или экземпляра. Как отмечалось выше, защищаемые объекты — это разрешения, предоставленные объектам. Защищаемые объекты обычно предоставляются в следующих случаях:

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

В нашем примере я предоставил SQLCRUISE\skipper членство в общедоступной роли сервера и разрешил просматривать любые определения объектов, существующие на уровне сервера (см. экран 5).

Экран 5. Назначение дополнительных прав

Наконец, переходим к странице Status («Состояние»). На этой странице можно разрешить или отменить доступ для пользователя (по умолчанию выбирается Grant — разрешить). Поэтому можно создать учетную запись, предоставить права, создать связанных пользователей, а затем отменить доступ. Вы можете вернуться в это окно для существующего пользователя и отменить доступ к экземпляру SQL Server. Аналогично происходит включение и отключение учетной записи (см. экран 6). Наконец, мы можем просмотреть состояние учетной записи пользователя и узнать, была ли учетная запись заблокирована из-за слишком большого числа неудачных попыток регистрации с неверным паролем.

Экран 6. Вкладка Status

Вы можете изучить код T-SQL в листинге 1, который формируется и выполняется при выполнении этих настроек в графическом интерфейсе.

На данном этапе важно отметить, как организована связь пользователей базы данных с учетной записью пользователя сервера. Как я уже указывал, соответствие имен между двумя объектами необязательно. Это объясняется тем, что объекты объединены в системных таблицах не по имени, а по идентификатору, именуемому sid (идентификатор безопасности). Это позволяет избавиться от привязки к учетной записи, соответствующей имени пользователя, или избежать возникновения ситуации, в которой вы восстанавливаете базу данных с именем пользователя, например trevor на экземпляре SQL Server, где уже имеется учетная запись trevor, но это совершенно другое лицо, которое не должно иметь прав в вашей базе данных. Благодаря sid такая опасность исключается. Если посмотреть на два системных представления, отображающих данные об учетных записях и пользователях, то можно увидеть, как эти объекты выглядят внутри SQL Server. Я подготовил учетную запись и пользователя professor и назначил lifeboat базой данных по умолчанию, создав при этом соответствующего пользователя в lifeboat. Системное представление, отображающее информацию об именах пользователей, — sys.server_principals (sys — схема). Информация о пользователе базы данных выводится через представление sys.database_principals в каждой базе данных. Эти представления могут быть соединены на основе sid (см. листинг 2 и экран 7).

Экран 7. Объединение информации о пользователе

Существуют проблемы, возникающие при несоответствии идентификаторов sid. Это более сложная тема, поэтому в одной из следующих статей я расскажу, как определить такую ситуацию, смягчить ее и устранить в случае возникновения так называемых «потерянных пользователей» (orphaned users).

Схемы и роли

Теперь мы переходим к более подробному знакомству со схемами и ролями. И схемы, и роли представляют собой коллекции в терминологии SQL Server. Схемы — это коллекции объектов (таблиц, представлений, хранимых процедур и т. д.). Роли — коллекции прав: роли серверов для прав на уровне сервера/экземпляра и роли базы данных для прав в конкретной базе данных. Однако на этом сходство заканчивается.

Особого внимания заслуживают две схемы по умолчанию: sys и dbo. Схема sys — фактически владелец всех системных объектов в Microsoft SQL Server. Во многих системных представлениях и динамических объектах управления они именуются объектами ms_shipped, которые вы увидите отмеченными в столбцах bit-type в соответствующих представлениях как is_ms_shipped со значением 1 для системных объектов и 0 для пользовательских объектов. Вы можете сами создавать схемы, соответствующие вашим потребностям. Ранее я упоминал в качестве примера схему выставления счетов для бухгалтерских объектов. Если пользователь создает объект без указания схемы, объект будет создан в схеме по умолчанию для этого пользователя. Если схема по умолчанию не определена для пользователя, то в качестве схемы по умолчанию назначается dbo.

При направлении запросов к пользователям рекомендуется применять полные доменные имена, то есть указывать имя базы данных, имя схемы и имя объекта, а не только имя объекта. Как это выглядит на практике? Если имеется таблица с именем tblFoo в схеме dbo базы данных SQLCruise, то можно создать запрос, который будет выбирать все столбцы и строки из этой таблицы несколькими способами (см. листинг 3).

Каждый вариант работает успешно, если имеется только одна таблица с именем tblFoo в базе данных SQL_Cruise и текущим контекстом базы данных была база данных SQL_Cruise. Однако только первый вариант будет работать корректно, независимо от того, к какой базе данных в настоящее время вы подключены на экземпляре SQL Server, содержащем базу данных SQL_Cruise. Второй вариант будет выполнен, если вы подключены к базе данных SQL_Cruise, независимо от числа схем, имеющих tblFoo, так как вы указали схему dbo. Третий вариант выдаст сообщение об ошибке (см. экран 8), если в базе данных SQL_Cruise имеется несколько схем с tblFoo, как показано в листинге 4, где я создал как таблицу dbo.tblFoo, так и таблицу user.tblFoo.

Экран 8. Сообщение об ошибке, если в базе данных SQL_Cruise имеется несколько схем с tblFoo

Да, все верно — объект существует, но вы получаете сообщение об ошибке Invalid object name («Недопустимое имя объекта»). Никогда не будьте уверены заранее, что объекта с таким именем не существует. Сообщение может свидетельствовать о проблеме с синтаксисом.

Предопределенные роли входят в Microsoft SQL Server на уровне как сервера, так и базы данных. Однако вы можете создавать собственные роли, если возникают ситуации, в которых нужно назначить одинаковые разрешения многим пользователям. Создание особых ролей позволяет определить эти права лишь однажды: когда вы создаете роль, а не для каждого пользователя или учетной записи регистрации пользователя (в зависимости от ролей базы данных или сервера). Помимо экономии времени исключается несогласованность при назначении прав многочисленным пользователям или учетным записям.

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

Безопасность Microsoft SQL Server — очень важная тема. Она отличается глубиной, а также своеобразием терминологии. Надеюсь, мне удалось достичь своей цели — объяснить различные термины и дать общий обзор объектов, применяемых на практике. В статьях начального уровня мы рассмотрим еще несколько тем, но уже в скором времени я обращусь к более сложным вопросам, вытекающим из этой публикации. Как всегда, благодарю читателей за внимание и с нетерпением жду комментариев. Надеюсь, статья поможет начинающим администраторам баз данных овладеть тайнами SQL.

Листинг 1. Код, соответствующий настройкам, сделанным в графическом интерфейсе

USE [master]
GO
CREATE LOGIN [SQLCRUISE\skipper] FROM WINDOWS WITH DEFAULT_DATABASE=[lifeboat]
GO

USE [lifeboat]
GO
CREATE USER [skipper] FOR LOGIN [SQLCRUISE\skipper]
ALTER USER [skipper] WITH DEFAULT_SCHEMA=[skipper_only]
GO

CREATE SCHEMA [skipper_only] AUTHORIZATION [skipper]
GO

USE [lifeboat_blank]
GO
CREATE USER [SQLCRUISE\skipper] FOR LOGIN [SQLCRUISE\skipper]
ALTER USER [SQLCRUISE\skipper] WITH DEFAULT_SCHEMA=[dbo]
GO

USE [Lifeboat_Messy]
GO
CREATE USER [SQLCRUISE\skipper] FOR LOGIN [SQLCRUISE\skipper]
ALTER USER [SQLCRUISE\skipper] WITH DEFAULT_SCHEMA=[dbo]
GO

use [master]
GO
GRANT VIEW ANY DEFINITION TO [SQLCRUISE\skipper]
GO

Листинг 2. Информация о пользователях системы и базы данных

SELECT name
        , sid
        , principal_id
        , type_desc
        , default_database_name
FROM sys.server_principals
WHERE name = 'professor';


SELECT name
        , sid
        , principal_id
        , type_desc
        , default_schema_name
FROM lifeboat.sys.database_principals
WHERE name = 'professor';

Листинг 3. Пример запроса для выбора столбцов и строк таблицы

—=========================================================================
— ВАРИАНТ 1: полное доменное имя
—=========================================================================
SELECT *
FROM SQL_Cruise.dbo.tblFoo;
—=========================================================================
— ВАРИАНТ 2: имя, определенное через схему
—=========================================================================
SELECT *
FROM dbo.tblFoo;
—=========================================================================
— ВАРИАНТ 3: только имя объекта
—=========================================================================
SELECT *
FROM tblFoo;
Листинг 4. Создание таблиц с несколькими схемами
USE [SQL_Cruise]
GO
CREATE SCHEMA [user] AUTHORIZATION [dbo]
GO
CREATE TABLE dbo.tblFoo (id INT);
CREATE TABLE [user].tblFoo (id INT);
SELECT *
FROM tblFoo;

В данной статье будет подробно, в деталях, рассказано как создать нового пользователя в Microsoft SQL Server 2012 (в более старых редакциях, например в Microsoft SQL Server 2008 R2, набор действий аналогичен).

0. Оглавление

1. Добавление нового пользователя
2. Проверка подлинности SQL Server
3. Проверка подлинности Windows

1. Добавление нового пользователя

Запускаем утилиту «SQL Server Management Studio». В Microsoft Windows server 2012 R2 ее можно найти в списке всех программ.

В Microsoft Windows Server 2008 R2 в меню «Пуск» (Start) — «Microsoft SQL Server 2012» — «Среда SQL Server Management Studio».

Вводим имя сервера, данные для авторизации и нажимаем «Соединить» (Connect).

В обозревателе объектов раскрываем вкладку «Безопасность» (Security), кликаем правой кнопкой мыши по вкладке «Имена входа» (Logins) и в контекстном меню выбираем «Создать имя входа…» (New Login…)

Откроется окно создания имени входа (Login — New). Теперь необходимо определиться с вариантом аутентификации нового пользователя. Возможны 2 варианта:

• Аутентификация с помощью пароля — Проверка подлинности SQL Server (SQL Server Authentication).
• Доступ для конкретного пользователя Windows — Проверка подлинности Windows (Windows authentication).

2. Проверка подлинности SQL Server

Для начала рассмотрим первый способ аутентификации. Например, создадим пользователя для работы сервера 1С:Предприятие. Укажем имя входа (Login name), выберем «Проверка подлинности SQL Server» (SQL Server Authentication) и введем пароль (Password) пользователя. Далее снимаем / отмечаем галочки у следующих параметров:

• Требовать использование политики паролей (Enforce password policy)
• Задать срок окончания действия пароля (Enforce password expiration)
• Пользователь должен сменить пароль при следующем входе (User must change password at next login)

Для данной задачи оставляем включенным только первый параметр.

Также сразу рекомендую выбрать язык по умолчанию. Если вы используете английскую версию SQL Server, то и служебные сообщения, которые SQL Server будет передавать приложению, подключенному под данным пользователем (в данном случае программе 1С:Предприятие, следовательно и конечному пользователю, работающему в программе) будут передаваться на английском языке. Если язык по умолчанию для пользователя выбрать, например, русский, то и служебные сообщения будут передаваться на русском языке.

Устанавливаем необходимые параметры и переходим на вкладку «Роли сервера» (Server Roles).

Здесь выбираем набор прав добавляемого пользователя. Для этого отмечаем необходимые роли сервера. С полным описанием предопределенных ролей сервера и их возможностями можно ознакомиться здесь. Для текущей задачи выбираем:

• dbcreator
• processadmin
• public

После чего нажимаем «ОК» для сохранения выполненных действий.

3. Проверка подлинности Windows

Теперь добавим администратора SQL Server, выбрав его из текущих пользователей Windows. Для этого создадим нового пользователя и способ аутентификации укажем «Проверка подлинности Windows» (Windows authentication). Далее, чтобы ввести имя входа, нажмем «Найти» (Search…), затем «Дополнительно» (Advanced…), в следующем окне «Поиск» (Find Now) и выбрав необходимого пользователя из списка, закроем все окна нажав на «ОК».

Перейдем на вкладку «Роли сервера» (Server Roles) и в соответствии с поставленной задачей укажем роли:

• public
• sysadmin

Нажмем «ОК» для сохранения нового пользователя.

Теперь в списке имен входа среди прочих мы можем увидеть только что созданных пользователей.

В
«Какие именно данные приложения следует хранить в базе данных»
мы разобрали вопрос о том, какие данные стоит, а какие не стоит хранить в базе данных. В данной лекции будут рассмотрены основные концепции обеспечения безопасности баз данных, которые должен знать каждый разработчик, чтобы создавать защищенные базы данных.

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

Проектирование механизма сетевой безопасности для обеспечения безопасности системы базы данных

SQL Server 2005 – первая версия SQL Server, которая была разработана в рамках инициативы Microsoft Trustworthy Computing (Защищенные информационные системы). Один из принципов этой инициативы – Security by Default (Безопасность по умолчанию). Реализуя этот принцип, SQL Server 2005 по умолчанию отключает некоторые сетевые настройки, чтобы сохранить максимально возможный уровень безопасности среды SQL Server.

Разрешение удаленного доступа

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

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

При установке SQL Server с параметрами по умолчанию многие функции отключены, чтобы уменьшить уязвимость системы базы данных против атак. Например, в SQL Server 2005 по умолчанию не разрешаются удаленные подключения (за исключением версии Enterprise), поэтому для того, чтобы задействовать удаленные подключения, мы воспользуемся инструментом SQL Server Surface Area Configuration (Настройка контактной зоны SQL Server), как показано на рис. 2.1.

Для этого выполните перечисленные ниже действия:

Разрешаем удаленные соединения

1. В меню Start (Пуск) выберите All Programs,. Microsoft SQL Server 2005, Configuration Tools, SQL Server Surface Area Configuration. (Все программы, Microsoft SQL Server 2005, Средства настройки, Настройка контактной зоны SQL Server).
2. Под заголовком Configure Surface Area For Localhost (Настроить контактную зону для localhost) в нижней части окна щелкните ссылку Surface Area Configuration For Services And Connections (Настройка контактной зоны для служб и соединений).
3. С левой стороны открывшегося окна отображается список компонентов, которые можно настроить. В этом списке разверните дерево элемента Database Engine и щелкните мышью элемент Remote Connections (Удаленные соединения).
4. Выберите вариант Local And Remote Connections (Локальные и удаленные соединения), а затем выберите нужные протоколы.

Допускается использование удаленных соединений с использованием сетевых протоколов TCP/IP или Named Pipes (именованные каналы). В интересах безопасности и производительности рекомендуется использовать протокол TCP/IP.

Обеспечение безопасности внешнего доступа

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

Дополнительная информация.SQL Server допускает использование соединений с различными типами конечных точек. О конечных точках рассказывается в лекции 5 курса «Оптимизация работы серверов баз данных Microsoft SQL Server 2005».

Управление доступом к экземплярам SQL Server

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

В SQL Server 2005 участники – это лица, группы или процессы, которые запрашивают доступ к ресурсам базы данных. Участники иерархически упорядочены по уровням операционной системы, сервера и базы данных и могут быть индивидуальными (неделимыми) или коллективными.

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

Выбор режима проверки подлинности

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

Настраиваем режим проверки подлинности

1. В меню Start (Пуск) выберите All Programs,. Microsoft SQL Server 2005, SQL Server Management Studio (Все программы, Microsoft SQL Server 2005, Среда SQL Server Management Studio).
2. В диалоговом окне Connect To Server (Соединение с сервером) нажмите кнопку Connect (Соединить).
3. В панели Object Explorer (Обозреватель объектов) щелкните правой кнопкой мыши на значке экземпляра SQL Server и выберите из контекстного меню пункт Properties (Свойства).
4. В панели Select A Page (Выбор страницы) выделите значок Security (Безопасность).
5. В секции Server Authentication (Серверная проверка подлинности) выберите нужный режим проверки подлинности, как показано на рис. 2.2 (см. рис. вверху следующей страницы).

Важно. Для того, чтобы изменение режима проверки подлинности вступило в силу, перезапустите экземпляр SQL Server.

При выборе режима проверки подлинности рекомендуется следовать приведенным ниже рекомендациям:

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

  

  увеличить изображение
  Рис.
  2.2.
  Установка режима проверки подлинности через интерфейс SQL Server Management Studio

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

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

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