How to Set Up DNSSEC on Windows Server 2022

How to Configure SQL Server Authentication and Logins on Windows Server 2022

Securing access to SQL Server starts with understanding its authentication models and then carefully configuring logins, server roles, database users, and permissions. SQL Server 2022 on Windows Server 2022 supports two authentication modes, a rich role-based access control system, and built-in auditing capabilities. This guide covers the complete authentication and authorization configuration process for a production-grade deployment.

Windows Authentication vs Mixed Mode Authentication

SQL Server supports two authentication modes. Windows Authentication Mode (also called Integrated Security) relies entirely on Windows Active Directory or local Windows accounts. SQL Server trusts the identity established by the Windows login process, so no separate SQL Server password is required. This is the more secure option because it benefits from Windows password policies, Kerberos, and group policy enforcement. It is the recommended mode for environments where all users and applications connect from domain-joined machines.

Mixed Mode Authentication allows both Windows Authentication logins and SQL Server-native logins. SQL logins have usernames and passwords stored within SQL Server itself, independent of Windows. Mixed Mode is required when applications connect with a SQL login (common with third-party software), when clients connect from non-domain systems, or when legacy application compatibility requires it.

During installation, you choose the authentication mode. To change it after installation, use SQL Server Management Studio: right-click the server in Object Explorer, select Properties, go to the Security page, and change the Server Authentication setting. This requires restarting the SQL Server service to take effect.

Enabling Mixed Mode via T-SQL

If you need to enable Mixed Mode after a Windows-only installation, you can do it via a registry change and T-SQL. The following approach uses the registry to set the login mode and then restarts the service:

# Enable Mixed Mode via registry (LoginMode=2 means Mixed, 1 means Windows only)
Set-ItemProperty -Path "HKLM:SOFTWAREMicrosoftMicrosoft SQL ServerMSSQL16.MSSQLSERVERMSSQLServer" `
    -Name "LoginMode" -Value 2

# Restart SQL Server to apply the change
Restart-Service -Name MSSQLSERVER

You can also use the ALTER SERVER CONFIGURATION approach indirectly via sp_configure, but for authentication mode the registry is the definitive location. After restarting, SQL logins will be accepted.

The sa Account: Enabling and Securing It

The sa (System Administrator) login is the built-in SQL Server superuser account. When you install SQL Server in Windows Authentication Mode, the sa account is created but disabled. In Mixed Mode installations, it is enabled if you supply a password during setup.

In many environments, the sa account should remain disabled and locked to reduce attack surface. If you need it enabled (for example, for a specific application that requires it), enable it with a strong password and then monitor its use through auditing.

-- Enable the sa account and set a strong password
ALTER LOGIN sa ENABLE;
ALTER LOGIN sa WITH PASSWORD = 'S@_Str0ngP@ssword!2024';

-- Disable sa if it is not needed
ALTER LOGIN sa DISABLE;

Because the sa account is the most targeted login in SQL Server brute-force attacks, a common hardening practice is to rename it. Note that renaming sa changes the visible name but does not change the SID (0x01), so it is a cosmetic measure:

ALTER LOGIN sa WITH NAME = [sqladmin_svc];

Regardless of whether sa is enabled, configure a login audit (covered later) to alert on any sa login activity.

Creating SQL Server Logins

SQL Server logins are server-level security principals. A login grants access to the SQL Server instance but does not by itself grant access to a specific database. There are two types: Windows logins (mapped to a Windows user or group) and SQL logins (credential stored in SQL Server).

-- Create a Windows login for a domain user
CREATE LOGIN [DOMAINjsmith] FROM WINDOWS;

-- Create a Windows login for a domain group (preferred for role-based access)
CREATE LOGIN [DOMAINSQLServerAdmins] FROM WINDOWS;

-- Create a SQL Server login with a password
CREATE LOGIN appuser 
WITH PASSWORD = 'App!User#Secure99',
     DEFAULT_DATABASE = AppDatabase,
     CHECK_EXPIRATION = ON,
     CHECK_POLICY = ON;

-- Create a SQL login that does not expire (use sparingly, for service accounts)
CREATE LOGIN svc_etl
WITH PASSWORD = 'ETL$Service#2024!',
     DEFAULT_DATABASE = DataWarehouse,
     CHECK_EXPIRATION = OFF,
     CHECK_POLICY = ON;

The CHECK_POLICY option enforces the Windows password complexity policy for SQL logins. The CHECK_EXPIRATION option enforces password expiration. Always enable both for interactive user accounts. Service accounts that run automated processes may need CHECK_EXPIRATION = OFF to prevent connection failures on password expiry, but ensure those passwords are still strong and rotated on a schedule.

Server-Level Roles

SQL Server has fixed server-level roles that grant broad, instance-wide permissions. Assign logins to these roles carefully, as they bypass database-level security.

The most important fixed server roles are: sysadmin — full control over the SQL Server instance, equivalent to sa; serveradmin — can change server configuration options and shut down the server; securityadmin — can manage logins, reset passwords, and grant or deny server-level permissions; processadmin — can kill sessions; setupadmin — can add and remove linked servers; bulkadmin — can run BULK INSERT; diskadmin — can manage disk files; dbcreator — can create, alter, drop, and restore any database; public — every login is a member of public.

-- Add a login to the sysadmin role
ALTER SERVER ROLE sysadmin ADD MEMBER [DOMAINSQLServerAdmins];

-- Add a login to the dbcreator role
ALTER SERVER ROLE dbcreator ADD MEMBER [DOMAINjsmith];

-- Add a SQL login to the bulkadmin role
ALTER SERVER ROLE bulkadmin ADD MEMBER svc_etl;

-- View current server role memberships
SELECT r.name AS RoleName, m.name AS MemberName
FROM sys.server_role_members rm
JOIN sys.server_principals r ON rm.role_principal_id = r.principal_id
JOIN sys.server_principals m ON rm.member_principal_id = m.principal_id
ORDER BY r.name, m.name;

Avoid assigning sysadmin unnecessarily. Use least privilege: grant dbcreator to DBAs who need to create databases, and use database-level roles for application access.

Creating Database Users

A database user is a database-level principal mapped to a server login. You must create a user in each database you want the login to access. A login without a corresponding database user will receive an access denied error when attempting to connect to that database.

-- Switch to the target database
USE AppDatabase;
GO

-- Create a user for a Windows login
CREATE USER [DOMAINjsmith] FOR LOGIN [DOMAINjsmith];

-- Create a user for a SQL login
CREATE USER appuser FOR LOGIN appuser;

-- Create a user with a different name than the login
CREATE USER app_read FOR LOGIN appuser;

-- Create a contained database user (SQL Server 2012+, no login required)
CREATE USER contained_user WITH PASSWORD = 'C0ntained!Pass#9';

-- View all users in the current database
SELECT name, type_desc, create_date
FROM sys.database_principals
WHERE type IN ('S','U','G')
ORDER BY name;

Granting Database-Level Permissions and Roles

SQL Server provides fixed database roles for common permission sets. Key roles include: db_owner — full control over the database; db_datareader — SELECT on all user tables; db_datawriter — INSERT, UPDATE, DELETE on all user tables; db_ddladmin — can run DDL (CREATE, ALTER, DROP); db_securityadmin — manages database roles and permissions; db_backupoperator — can run BACKUP DATABASE.

USE AppDatabase;
GO

-- Assign a user to a fixed database role
ALTER ROLE db_datareader ADD MEMBER appuser;
ALTER ROLE db_datawriter ADD MEMBER appuser;

-- Grant specific object-level permissions
GRANT SELECT ON dbo.Orders TO appuser;
GRANT EXECUTE ON dbo.usp_GetOrderDetails TO appuser;
DENY DELETE ON dbo.Orders TO appuser;

-- Grant schema-level permission
GRANT SELECT ON SCHEMA::reporting TO [DOMAINjsmith];

-- Revoke a previously granted permission
REVOKE SELECT ON dbo.Orders FROM appuser;

Linked Server Logins

When SQL Server connects to a remote SQL Server or OLE DB data source via a linked server, it must authenticate to the remote system. Linked server logins define how the local SQL Server presents credentials to the remote server.

-- Create a linked server
EXEC sp_addlinkedserver
    @server = 'REMOTESERVER',
    @srvproduct = '',
    @provider = 'SQLNCLI',
    @datasrc = 'REMOTESERVERINSTANCE';

-- Map a local login to a remote login
EXEC sp_addlinkedsrvlogin
    @rmtsrvname = 'REMOTESERVER',
    @useself = 'FALSE',
    @locallogin = 'appuser',
    @rmtuser = 'remote_svc',
    @rmtpassword = 'Rem0te!Pass#2024';

-- Test the linked server connection
EXEC sp_testlinkedserver 'REMOTESERVER';

Login Auditing with SQL Server Audit

SQL Server Audit allows you to track login successes, failures, and specific T-SQL operations to a file or the Windows Event Log. Configuring audit for failed and successful logins is a basic security requirement in any regulated environment.

-- Create a server audit writing to a file
CREATE SERVER AUDIT [LoginAudit]
TO FILE (
    FILEPATH = 'D:AuditLogs',
    MAXSIZE = 100 MB,
    MAX_ROLLOVER_FILES = 10,
    RESERVE_DISK_SPACE = OFF
)
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);

-- Enable the audit
ALTER SERVER AUDIT [LoginAudit] WITH (STATE = ON);

-- Create a server audit specification to capture logins
CREATE SERVER AUDIT SPECIFICATION [LoginAuditSpec]
FOR SERVER AUDIT [LoginAudit]
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (LOGOUT_GROUP)
WITH (STATE = ON);

-- Query the audit file
SELECT event_time, action_id, succeeded, server_principal_name, client_ip, statement
FROM sys.fn_get_audit_file('D:AuditLogsLoginAudit_*.sqlaudit', DEFAULT, DEFAULT)
ORDER BY event_time DESC;

You can also enable basic login auditing through SQL Server instance properties without creating a formal audit object. In SSMS, right-click the server, select Properties > Security, and set Login auditing to Both failed and successful logins. This logs to the SQL Server error log and is a good quick-win even before deploying full SQL Server Audit.

Enforcing Strong Passwords for SQL Logins

SQL logins with CHECK_POLICY = ON inherit the Windows password complexity requirements from the local security policy. On Windows Server 2022, the default password policy requires at minimum a six-character password with three of the four character types (uppercase, lowercase, digit, special character). For stricter enforcement, configure a fine-grained password policy via group policy or Active Directory Password Settings Objects (PSOs) if the server is domain-joined.

To audit existing SQL logins and identify those with weak or policy-disabled settings:

SELECT name,
       is_disabled,
       is_policy_checked,
       is_expiration_checked,
       LOGINPROPERTY(name, 'IsLocked') AS is_locked,
       LOGINPROPERTY(name, 'BadPasswordCount') AS bad_password_count,
       LOGINPROPERTY(name, 'PasswordLastSetTime') AS password_last_set
FROM sys.sql_logins
ORDER BY name;

Any SQL login with is_policy_checked = 0 or is_expiration_checked = 0 should be reviewed and, where possible, updated to enforce these controls unless there is a documented operational reason for the exception.

Combining Windows Authentication where possible, strict password policies for SQL logins, least-privilege role assignments, and comprehensive auditing provides a strong security baseline for SQL Server authentication on Windows Server 2022.