SQL: Типы аутентификации в Microsoft SQL Server
1. По сравнению с аутентификацией SQL — она более безопасна, тк не передается логин и пароль, а используется встроенные механизмы безопасности Windows, токены или сертификаты
2. Централизованное управление (создание\изменение\блокирование) учетными записями на уровне windows машины или инфраструктуры AD
3. Удобство пользователя: локально подключение осуществляется через сессию Windows
Преимущества аутентификации SQL сервер
1. Обеспечение поддержки устаревших систем
2. Возможность минимизировать взаимодействие с внешними системами. Например, при предоставлении доступа только УЗ SQL, можно ограничить перечень УЗ, которым позвонено получать доступ к данным, причем централизованное упраление УЗ не позволит к ним подключиться просто сменив пароль на уровне AD
3. Возможность в рамках одной сессии настроит разные процессы с разными правами доступа.
Как изменить тип аутентификации в Microsoft SQL Server 2019
1. Запустить SSMS, и указать имя целевого SQL сервера
2. Подключиться и выбрать свойства сервера (Properties)
3. Перейти на закладку Security и выбрать необходимый режим проверки подлинности.
4. Нажать ОК
5. в случае, если режим аутентификации менялся, то для применения настроек, необходимо выполнить перезапуск службы SQL Server или перезагрузить сервер целиком.
4 Ways to Enable Mixed Mode Authentication for SQL Server
This article introduces four ways for SQL Server users to enable Mixed Mode Authentication from installation to use.
Way 1: Enable Mixed Mode Authentication during SQL Server Installation
If you have paid attention to the SQL Server installation, you would find there is a step setting Authentication mode. If you don’t change the default settings, Windows Authentication will become default authentication mode. If you choose Mixed Mode Authentication, the installation progress will ask you to set strong password for SA account.
Once you set SA account during SQL Server installation, Mixed Mode Authentication would be available after finishing installing SQL Server. When you connect to SQL Server database, there would be two authentication modes for you to choose.
Even though you don’t choose Mixed Mode Authentication in SQL Server setup progress, following two ways still could help to enable SQL Server Mixed Mode Authentication.
Way 2: Enable SQL Server Mixed Mode Authentication by Changing Instance Properties
1. Right click on the SQL server instance.
2. Click on Properties.
3. Click on Security on the left pane.
4. Check SQL Server and Windows Authentication mode under Server authentication section. Click OK.
Way 3: Enable Mixed Mode Authentication with SQL Query
EXEC xp_instance_regwrite N’HKEY_LOCAL_MACHINE’,
N’Software\Microsoft\MSSQLServer\MSSQLServer’, N’LoginMode’, REG_DWORD, 2
[Note: 2 indicates mixed mode authentication. 1 is for windows only authentication]
Way 4: Enable Mixed Mode Authentication in Registry Editor
This way applies to users who have no administrator privileges to SQL Server, and cannot run SQL query easily.
Step 1: Open the registry editor.
Step 2: Navigate to:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.10.SQLEXPRESS\MSSQLServer
Step 3: Change the value of LoginMode from 1 to 2.
Step 4: Close the registry editor.
Tips: One thing to note is I had a few different options under the Microsoft SQL Server branch so you may need to take an educated guess if yours is different.
Related Articles:
iSunshare is dedicated to providing the best service for Windows, Mac, Android users who are in demand for password recovery and data recovery.
How can I change from SQL Server Windows mode to mixed mode (SQL Server 2008)?
I have installed SQL Server 2008 Express Edition, but by mistake I kept the Windows authentication mode.
Now I want to change that to SQL Server mixed mode. How can I do this?
7 Answers 7
You can do it with SQL Management Studio —
Server Properties — Security — [Server Authentication section] you check Sql Server and Windows authentication mode
I had no success with other attempts on a SQL Server 2012. What I did was use SQL Server Management Studio to generate a script to change the value, and got this:
After that, I enabled the sa account using this:
. then, I restarted the service, and everything worked!
One can catch that you may change it through windows registry key
. and restart service
- Open up SQL Server Management Studio and connect to your database server.
- Right Click The Database Server and click Properties.
- Set the Server Authentication to SQL Server and Windows Authentication Mode.
To change security authentication mode:
In 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.
In the SQL Server Management Studio dialog box, click OK to acknowledge the requirement to restart SQL Server.
In Object Explorer, right-click your server, and then click Restart. If SQL Server Agent is running, it must also be restarted.
To enable the SA login:
In Object Explorer, expand Security, expand Logins, right-click SA, and then click Properties.
On the General page, you might have to create and confirm a password for the login.
On the Status page, in the Login section, click Enabled, and then click OK.