В ssms в настройках sql сервера включите аутентификацию windows
Обновлено: 07.07.2024
Устанавливаю на “Windows Server 2008 R2 Enterprise English” (srv-sql.polygon.local) базу данных “MS SQL Server 2008 R2 Standard English”.
На следующем предоставления доступа к « Analysis Services » опять же через кнопку « Add Current User » добавляем себя ( POLYGON \ ekzorchik ) и также нажимаем кнопку « Next » для перехода на новый этап мастера установки.
На этапе «Reporting Services Configuration» выбираем пункт «Install the native mode default configuration» ( Установить конфигурация по умолчанию для работы в собственном режиме ) и нажимаем «Next» ( Далее ).
На этапе « Error Reporting » (Отчет об ошибках) ничего не отмечаем, а нажимаем кнопку « Next » (Далее), « Next » (Далее), « Install » (Установить).
Теперь ожидаем, по куда установятся файлы СУДБ…
По окончании установки должно быть вот так:
Нажимаем « Close » (Закрыть).
Перезапускаем службу « MSSQLServer » либо перезагружаемся .
Вызываем консоль командной строки и набираем в консоли команды:
- net stop mssqlserver
- net start mssqlserver
На текущий момент подключение для управления средой действует только с использованием доменной аутентификации :
«Start» – «All Programs» – «Microsoft SQL Server 2008 R2» – запускаем оснастку «SQL Server Management Studio».
При нажатии на кнопку « Connect » соединение проходит успешно. Теперь я собственно и подошел с решения поставленной в этой заметке задаче: Настроить смешанную аутентификацию , т.е. и « Windows и sql ».
Сейчас «SQL» учетная запись – «sa» – «Properties» – «Status» – «Login: Disabled» ( выключена ).
Включаем « SQL » учетную запись « sa » и назначаем ей пароль, но при попытке подключиться под ней вылетает ошибка соединения :
Суть проблемы в следующем : при разворачивании « MS SQL Server 2008 R2» отключен так называемый « Mixed mode» режим авторизации, соответственно, войти в систему могут только пользователи, у которых есть « Windows»-аккаунт .
«HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Name\SQL»
Зная именование экземляра открывает уже другой ключ реестра :
« HKLM \ SOFTWARE \ Microsoft \ Microsoft SQL Server \ MSSQL 10_50. MSSQLSERVER \ MSSQLServer » в значении параметра « LoginMode » равном единица «1» необходимо поменять на значение равное двум «2»:
Для справки: Поле « LoginMode » может принимать два значения:
SQL Server and Windows Authentication mode
Чтобы изменения вступили в силу нужно перезапустить « MS SQL Server» , но для этого сперва, поменяем запуск « SQL » служб не от имени учётной записи под которой я сейчас работаю, а на запуск из-под « SYSTEM » (Системы) для следующих служб:
SQL Server (MSSQLSERVER) – POLYGON\ekzorchik
SQL Server Agent (MSSQLSERVER) – POLYGON\ekzorchik
SQL Server Analysis Services (MSSQLSERVER) – POLYGON\ekzorchik
SQL Server Intergration Services 10.0 Properties – POLYGON\ekzorchik
SQL Server Reporting Services (MSSQLSERVER) – POLYGON\ekzorchik
После задействуем консоль командной строки, вызвав искомую: « Win + R » – « cmd . exe » и в ней наберем команды для остановки « SQL Server ’а», а после запуска:
net stop mssqlserver
net start mssqlserver
После можно будет использовать « SQL Server -авторизацию»,
«Start» – «All Programs» – «Microsoft SQL Server 2008 R2» – запускаем оснастку «SQL Server Management Studio».
Нажимаем кнопку “ Connect ” и видим, что авторизация с использованием « SQL Server Authentication » успешно прошла:
, что собственно и мне требовалось. Теперь работает так называемая смешанная аутентификация, как « Windows », так и « SQL ». Результат достигнут. С уважением, ekzorchik.
One comment
Спасибо чувак! Я пытался сделать логин по sql аккаунту кучу времени, но только твоя статья спасла меня
Comments are closed.
Используйте прокси ((заблокировано роскомнадзором, используйте vpn или proxy)) при использовании Telegram клиента:
Поблагодари автора и новые статьи
будут появляться чаще :)
Карта МКБ: 4432-7300-2472-8059
Большое спасибо тем кто благодарит автора за практические заметки небольшими пожертвованиями. С уважением, Олло Александр aka ekzorchik.
В этом разделе описывается, как изменить режим проверки подлинности сервера в SQL Server с помощью среды SQL Server Management Studio или Transact-SQL. В процессе установки компонент Компонент SQL Server Database Engine настраивается на использование режима проверки подлинности Windows или режима проверки подлинности SQL Server и Windows. После установки вы можете изменить режим проверки подлинности в любое время.
Если во время установки был выбран Режим проверки подлинности Windows , то имя входа sa отключено, а пароль присваивается программой установки. Если впоследствии изменить режим проверки подлинности на проверку подлинности SQL Server и Windows, то имя входа sa останется отключенным. Чтобы можно было пользоваться именем входа sa, включите его и присвойте ему новый пароль с помощью инструкции ALTER LOGIN. Имя входа sa может подключаться к серверу только с использованием проверки подлинности SQL Server .
Перед началом
Учетная запись sa — хорошо известная учетная запись SQL Server и часто становится мишенью злоумышленников. Не включайте учетную запись sa, если это не требуется для работы приложения. Для имени входа sa очень важно использовать надежный пароль.
Изменение режима проверки подлинности с помощью SSMS
В обозревателе объектов среды SQL Server Management Studio щелкните правой кнопкой мыши сервер и выберите пункт Свойства.
На странице Безопасность , в разделе Серверная проверка подлинности выберите новый режим проверки подлинности сервера, а затем нажмите кнопку ОК.
В диалоговом окне среды SQL Server Management Studio нажмите кнопку ОК , чтобы подтвердить необходимость перезапуска SQL Server.
В обозревателе объектов щелкните правой кнопкой мыши сервер и выберите пункт Перезапустить. Если работает агент SQL Server , он тоже должен быть перезапущен.
Включение имени входа sa
Имя входа sa можно включить с помощью SSMS или T-SQL.
использование SSMS;
В обозревателе объектов разверните узел Безопасность, разверните "Имена входа", щелкните правой кнопкой мыши имя входа sa и выберите Свойства.
На вкладке Общие, возможно, придется создать и подтвердить пароль для имени входа sa.
На странице Состояние в разделе Имя входа щелкните Включить и нажмите кнопку ОК.
Использование Transact-SQL
В следующем примере включается имя входа sa и устанавливается новый пароль. Замените <enterStrongPasswordHere> надежным паролем.
Изменение режима проверки подлинности (T-SQL)
В следующем примере проверка подлинности сервера переключается со смешанного режима (Windows + SQL) на Windows.
В следующем примере для изменения реестра сервера используется расширенная хранимая процедура. При неправильном изменении реестра могут возникнуть серьезные проблемы. В результате может потребоваться переустановка операционной системы. Корпорация Майкрософт не гарантирует, что эти проблемы можно устранить. Ответственность за изменение реестра лежит на пользователе.
Для изменения режима аутентификации необходимы разрешения системного администратора или сервера контроля
Настройка и управление безопасностью SQL Server является важной частью построения и обслуживания среды вашего SQL Server. Безопасность SQL Server - это огромная тема, которая не может быть полностью раскрыта в одной статье. Данная статья начинает с нескольких фундаментальных вопросов безопасности SQL Server: методов авторизации 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. На листинге ниже представлен код для вывода режима авторизации.
Листинг 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 для создания того же пользователя, которого я только что создал визуальными средствами.
Перед установкой базы данных необходимо проверить аутентификацию сервера и настроить SQL Server для работы в сети.
Настройка аутентификации сервера
Для работы программ по сети нужно создать в Users Manager учетные записи пользователей. (Меню «Администрирование»).
Перед тем как это сделать, необходимо изменить способ аутентификации в настройках сервера.
Запустите программу «Среда SQL Server Management Studio». Подключитесь к вашему серверу и затем кликните правой кнопкой по имени сервера и выберите пункт «Свойства»
Во вкладке «Безопасность» укажите «Проверка подлинности SQL Server и Windows»
Среда SQL Server Management Studio Express
Нажмите «Ок» и затем перезапустите службу SQL Server.
Политика паролей
Если у Вас установлена Window Server любой версии, то откройте через меню «Пуск» - «Администрирование» - «Локальная политика безопасности».
Во вкладке «Политика паролей» посмотрите на значение пункта «Пароль должен отвечать требованиям …..».Если в строке стоит статус «Включен», то кликните по пункт 2-м щелчком и выберите галочку «Отключен».
После установки базы данных и создания в Users Manager учетных записей политику паролей можно включить обратно.
Настройка MS SQL Server 2005-2017 для работы в сети
После установки SQL Server, по умолчанию, он не доступен по сети. Если SQL сервер не доступен или при запуске программы (Деканат, ПК, Ведомости и т.д) возникает ошибка, то это может свидетельствовать о следующем:
- Не установлен режим подлинности Windows аутентификация;
- Не включена возможность удаленного соединения (для SQL server 2005);
- Не доступен протокол TCP/IP;
- Отсутствует физическое подключение к сети;
- Блокирование сервера Брандмауэром Windows.
После установки SQL Server необходимо настроить его для работы в сети. Настройки зависят от версии сервера.
Выберите в меню Пуск - Программы - Microsoft SQL Server 2005>Средства настройки> Настройка контактной зоны SQL Server.
В открывшейся форме выберите «Настройка контактной зоны для служб и соединений» и нажмите «Сохранить»
Настройка контактной зоны
Настройка контактной зоны
2.3 В списке компонентов перейдите на пункт «SQL Server Browser». Убедитесь, что служба запущена. Укажите режим запуска «Авто» и запустите службу.
Откройте меню «Пуск» – «Все программы» – «Microsoft Sql Server 2008/2012» – «Средства настройки» – «Диспетчер конфигурации SQL Server»
Во вкладке «Службы SQL Server» убедитесь, что служба «SQL Server, обозреватель» запущена. В колонке «Состояние» должно быть значение «Работает», а в колонке «Режим запуска» - «Авто». Также и для службы SQL Server (sqlexpress).
Агент SQL Server можно не включать
Перейдите в пункт «Сетевая конфигурация SQL Server». Проверьте состояние протокола TCP/IP (должна быть состояние «Включено»).
После смены состояния перезапустите службу SQL Server.
Настройка Брэндмаура
Если после выполнения всех перечисленных действий сервер не доступен по сети, то проверьте настройки брандмауэра/антивируса/сетевого экрана.
Если на серверном компьютере включен брандмауэр или установлен сетевой экран (Firewall), то это может препятствовать соединению с сервером.
Для быстрого получения ответа можно временно выключить работу «защитной» программы (не рекомендуется).
Открытые порта 1433 в брандмауэре:
1. Откройте панель управления и выберите пункт Брандмауэр Windows.
2. Перейдите в дополнительные параметры
3. Выберите узел "Правила для входящих подключений" и затем, в правой панели - Создать правило
3. Выберите пункт "Для порта" и нажмите Далее
4. Укажите Протокол TCP и пункт "Определенные локальные порты". В поле укажите порт 1433 и нажмите Далее
6. Укажите пункт "Разрешить подключение" и нажмите Далее
7. Укажите имя правилу (на свое усмотрение)
Удаление схем и пользователей
Данный пункт может применяться если у Вас уже была установлена БД Деканат. Данные действия надо выполнить, если не удается создать учетные записи программы.
1. Запустите «Среда SQL Server Management Studio»;
2. Откройте и перейдите во вкладку «Деканат» - «Безопасность» - «Схемы»;
3. Удалите следующие схемы: Abit, Dek, VedKaf, Kaf, Plany, Test, GraphGroups;
4. Отройте вкладку «Деканат» - «Безопасность» - «Пользователи»
5. Удалите имена пользователей с именами: Abit, VedKaf, Kaf, Plany, Test, GraphGroups.
Читайте также: