Microsoft SQL Server, a leading database management system, is critical in many businesses. As a result, users frequently need to connect to SQL Server databases and be authenticated before interacting with the data. This article explores SQL Server authentication, its different methods, and how authentication works in various scenarios.
SQL Server supports three types of authentications:
Windows Authentication is the default option offered during SQL Server installation. With this method, users log in to Windows using their credentials, and if their Windows account is granted access to the SQL Server instance, they can connect without entering separate SQL Server credentials. This is the most secure authentication type, and it is recommended when SQL Server is managed from a Windows machine.
SQL Server Authentication requires a separate SQL Server login and password. These credentials are stored in the SQL Server's master database, which makes this method less secure than Windows Authentication. Credentials can be included in database backups, posing additional risks. Despite the security concerns, SQL Server Authentication is commonly used and can be enabled during installation.
Mixed Mode supports Windows and SQL Server Authentication. Therefore, it is useful in environments where some users or applications cannot authenticate using Windows credentials. However, because it includes the less secure SQL Server Authentication, it's recommended only when necessary.
You can define the authentication mode during the SQL Server installation process. Windows Authentication is selected by default, so you can simply accept it.
However, if you need to support Windows and SQL Server logins, choose Mixed Mode Authentication instead. When you select Mixed Mode, the system enables the sa (system administrator) account and prompts you to set a password.
You can change the authentication mode even after installation using SQL Server Management Studio (SSMS), the default IDE for SQL Server, or T-SQL commands.
Open SSMS, right-click your server and select Properties.
Go to the Security page and choose the server authentication mode you desire. Click OK.
Restart the SQL Server. If you use SQL Server Agent, restart that as well.
To switch from Mixed Mode to Windows Authentication, execute the below command:
USE [master];
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'LoginMode', REG_DWORD, 1;
Then, disable the sa account:
USE [master]; ALTER LOGIN sa DISABLE;
To switch from Windows Authentication to Mixed Mode, you need first to enable the sa account and assign a strong password to it. Execute the following script:
USE [master]; ALTER LOGIN sa ENABLE; ALTER LOGIN sa WITH PASSWORD = '<enterStrongPasswordHere>';
After that, change the authentication mode using the below query:
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'LoginMode', REG_DWORD, 2;
You can check the current authentication mode for the specific SQL Server instance in SSMS: right-click the server, select Properties, and navigate to Security. You will see the necessary information there.
Access to SQL Server is granted through a login, an entity authenticated by the system's security mechanisms. There are three types of logins stored in the master database:
Let us have a look at these login types.
Windows user login allows a single Windows user to access SQL Server. No password is required for this type of login because authentication is handled by logging into the Windows domain. Windows OS securely stores the password.
Windows group login grants SQL Server access to all members of a specified Windows group. It's useful when access needs to be provided to multiple users through a single login. Access is managed by adding or removing users from the Windows group.
SQL Server login provides access to an individual user with credentials that are separate from their Windows account. Both the username and password are stored in the master database.
Windows logins are considered more secure because Windows uses true encryption for password storage. Additionally, Windows logins are often managed by IT security specialists rather than database administrators, adding another layer of security.
A database user is not the same as a login. While a login grants access to a SQL Server instance, a database user allows that login to access a specific database. This separation guarantees precise access control, as logins can be mapped only to the databases they need, which is a key security best practice. Only logins with sysadmin privileges have unrestricted access to all databases by default.
The link between a login and a database user is called user mapping. This mapping can be configured when creating the login or later by editing the login settings.
We can configure user mapping using SQL Server Management Studio (SSMS):
In Object Explorer, right-click the Security folder under your server instance. Choose New > Login...
On the General page, enter the login name and select the desired authentication mode. Note that you will need to provide a password for SQL Server authentication. Enabling this option also allows you to enforce password policies.
Set the default database for the login. By default, it is master, but it is recommended to provide access to that database that the user will work with immediately after connecting. Click OK to proceed.
The new login will be created, and you will see it in the Logins folder of the server instance. Switch to the User Mapping page. Here, you can see all databases on the server, assign logins to specific databases, and select a user role, which is a predefined set of permissions.
For a more advanced and user-friendly experience, you can manage logins and users with dbForge Studio for SQL Server, a more powerful alternative to SSMS. It offers an enhanced interface and extended functionality.
Go to the Database menu and select Security Manager.
The Security Manager displays all databases, their users, and associated permissions.
To create a new login, click Create Login and fill in the Name field. Then, choose the Authentication type and provide the required details based on the user type.
On the User Mapping tab, assign specific databases to the user.
Use the Server Roles tab to define the user's role-based permissions. You'll see a list of predefined SQL Server roles and custom roles if you have created them.
You can also create custom roles with specific permissions and assign them to users as needed.
Click Save to apply changes. To make further updates, edit the login and click Save again. If needed, click Undo to discard any unsaved changes.
Password protection is a fundamental measure for securing access to SQL Server. Enforcing Windows password policies is regular practice, still, additional security practices are necessary to fully protect passwords.
To strengthen password security, enforce a password complexity policy with the following requirements:
Passwords can be up to 128 characters long. Using long, complex passwords to reduce the risk of hacking is strongly recommended. Additionally, enforcing password expiration policies ensures that users update their passwords regularly.
Password policies can be configured individually for each SQL Server login.
The sa (system administrator) account is a common target for attackers. To protect it, implement the following best practices:
One of the most effective security strategies across systems (including SQL Server) is restricting user capabilities using permissions. We use SQL Server roles and database roles to achieve this in SQL Server.
SQL Server roles are predefined at the server level and are typically assigned to logins. They simplify permission management by allowing you to apply permissions to multiple users through a single role. SQL Server includes a set of built-in roles, and you can also create custom roles to suit specific needs.
To view available server-level permissions, run the following query:
SELECT *
FROM sys.fn_builtin_permissions('SERVER')
ORDER BY permission_name;
Database roles operate at the database level and control access to database objects. These roles can be assigned to individual users or other roles within the same database. This allows for more granular permission control based on specific user needs.
To list the database roles, execute:
SELECT name FROM sys.database_principals WHERE type_desc = 'DATABASE_ROLE';
By implementing these practices, you can significantly improve the security of your SQL Server environment and data.
SQL Server authentication is key in securing access to the server and the data stored in its databases. This guide covers the available authentication types, their advantages and disadvantages, and various methods to enhance authentication security.
Modern database management tools, such as dbForge Studio for SQL Server, make it easier to manage authentication and user access alongside many other powerful features.
You can explore dbForge Studio's capabilities through a fully functional 30-day free trial. Test the tool in your day-to-day environment and see how it performs under real workload conditions.