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

Security is the most operationally consequential aspect of SQL Server administration, and authentication is its foundation. Every connection to a SQL Server instance is made under an identity — either a Windows principal authenticated through Kerberos or NTLM, or a SQL login authenticated by SQL Server itself with a stored password hash. Windows Server 2025 ships with enhanced security defaults including Credential Guard and Protected Users group membership that interact directly with SQL Server’s Kerberos delegation behavior. Understanding how to create, manage, and audit logins and database users — and how to assign the right level of privilege through server and database roles — is essential for anyone responsible for a SQL Server 2022 instance. This guide covers the full authentication stack: enabling Mixed Mode, creating SQL and Windows logins, mapping database users, assigning roles, managing granular permissions with GRANT/DENY/REVOKE, and auditing authentication events.

Prerequisites

SQL Server 2022 installed on Windows Server 2025 (see the installation guide).
A login with sysadmin server role membership — typically the account used during installation.
SQL Server Management Studio (SSMS) 20+ or the SqlServer PowerShell module.
For Windows Authentication logins: an Active Directory domain or a local Windows account.

Step 1 — Enable Mixed Mode Authentication

If you chose Windows Authentication Mode during installation and now need SQL logins, enable Mixed Mode without re-running setup:

-- Run in SSMS connected as sysadmin
USE [master];
GO

-- Enable mixed mode via sp_configure (requires SQL Server restart)
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO

# Change authentication mode in the registry (requires SQL restart)
$regPath = 'HKLM:SOFTWAREMicrosoftMicrosoft SQL ServerMSSQL16.MSSQLSERVERMSSQLServer'
Set-ItemProperty -Path $regPath -Name 'LoginMode' -Value 2  # 1 = Windows only, 2 = Mixed

# Restart the SQL Server service
Restart-Service -Name MSSQLSERVER -Force
Write-Host "SQL Server restarted with Mixed Mode authentication"

After restarting, the sa account becomes usable. Enable and set a strong password for it:

-- Enable the sa login and set a secure password
ALTER LOGIN [sa] ENABLE;
ALTER LOGIN [sa] WITH PASSWORD = 'Sql$erver2025!SecurePass#01';
GO

Step 2 — Create SQL Server Logins

A server login is the principal that authenticates to the SQL Server instance. It exists in the master database. There are two types:

Windows Authentication Login

-- Add a domain user as a SQL Server login
CREATE LOGIN [CORPjane.smith] FROM WINDOWS
    WITH DEFAULT_DATABASE = [master],
         DEFAULT_LANGUAGE = [us_english];
GO

-- Add a domain security group (all members get access)
CREATE LOGIN [CORPDBA_Team] FROM WINDOWS
    WITH DEFAULT_DATABASE = [master];
GO

SQL Authentication Login

-- Create a SQL login with a strong password
-- MUST_CHANGE forces password change on first login
-- CHECK_POLICY enforces the Windows password policy
CREATE LOGIN [AppServiceAccount] WITH
    PASSWORD = 'App$vc2025!P@ss#87' MUST_CHANGE,
    DEFAULT_DATABASE = [SalesDB],
    CHECK_EXPIRATION = ON,
    CHECK_POLICY = ON;
GO

-- Create a login without forced password change (for service accounts)
CREATE LOGIN [ReportingService] WITH
    PASSWORD = 'Rpt$vc2025!P@ss#42',
    DEFAULT_DATABASE = [ReportingDB],
    CHECK_EXPIRATION = OFF,
    CHECK_POLICY = ON;
GO

To create a login with a pre-hashed password (useful for scripted migrations):

-- First, retrieve the hash from the source server
SELECT name, CONVERT(VARBINARY(256), password_hash) AS pwd_hash
FROM sys.sql_logins WHERE name = 'AppServiceAccount';

-- On the target server, create using the hash
CREATE LOGIN [AppServiceAccount]
    WITH PASSWORD = 0x020033A4B1C2D3... HASHED,
    CHECK_POLICY = OFF,
    CHECK_EXPIRATION = OFF;
GO

Step 3 — Create Database Users Mapped to Logins

A login grants access to the instance. A database user grants access to a specific database and is mapped to a login. They are separate objects.

USE [SalesDB];
GO

-- Create a user mapped to a SQL login
CREATE USER [AppServiceAccount] FOR LOGIN [AppServiceAccount]
    WITH DEFAULT_SCHEMA = [dbo];
GO

-- Create a user mapped to a Windows login
CREATE USER [JaneSmith] FOR LOGIN [CORPjane.smith]
    WITH DEFAULT_SCHEMA = [dbo];
GO

-- Create a contained database user (no login required — useful for Azure migration)
CREATE USER [ContainedApp] WITH
    PASSWORD = 'Cntnd$App2025!P@ss',
    DEFAULT_SCHEMA = [dbo];
GO

For contained database users to work, enable the server-level option:

EXEC sp_configure 'contained database authentication', 1;
RECONFIGURE;
GO

-- Then set the database containment type
ALTER DATABASE [SalesDB] SET CONTAINMENT = PARTIAL;
GO

Step 4 — Assign Server Roles

Server roles control instance-wide privileges. SQL Server 2022 ships with fixed server roles plus user-defined server roles introduced in SQL Server 2012.

sysadmin — full control over the entire instance. Restrict to DBAs only.
securityadmin — can manage logins and grant server-level permissions.
serveradmin — can configure server-wide settings and shut down the instance.
setupadmin — can manage linked servers.
processadmin — can kill connections.
diskadmin — can manage disk files.
dbcreator — can create, alter, drop, and restore databases.
bulkadmin — can run BULK INSERT statements.
public — every login is implicitly a member; grants minimal default permissions.

-- Grant sysadmin to a DBA login
ALTER SERVER ROLE [sysadmin] ADD MEMBER [CORPDBA_Team];
GO

-- Grant dbcreator to a developer login
ALTER SERVER ROLE [dbcreator] ADD MEMBER [CORPjane.smith];
GO

-- Create a custom server role for monitoring (SQL Server 2012+)
CREATE SERVER ROLE [MonitoringRole];
GRANT VIEW SERVER STATE TO [MonitoringRole];
GRANT VIEW ANY DATABASE TO [MonitoringRole];
ALTER SERVER ROLE [MonitoringRole] ADD MEMBER [ReportingService];
GO

Step 5 — Assign Database Roles

Database roles control permissions within a single database.

db_owner — full control within the database.
db_securityadmin — can manage database role membership and permissions.
db_accessadmin — can add or remove database users.
db_backupoperator — can run BACKUP commands.
db_datareader — can SELECT from all user tables.
db_datawriter — can INSERT, UPDATE, DELETE on all user tables.
db_ddladmin — can run all DDL commands (CREATE, ALTER, DROP).
db_denydatareader — explicitly denied SELECT (overrides datareader).
db_denydatawriter — explicitly denied write operations.

USE [SalesDB];
GO

-- Make AppServiceAccount a data reader and writer
ALTER ROLE [db_datareader] ADD MEMBER [AppServiceAccount];
ALTER ROLE [db_datawriter] ADD MEMBER [AppServiceAccount];
GO

-- Make JaneSmith a db_owner for this database
ALTER ROLE [db_owner] ADD MEMBER [JaneSmith];
GO

-- Create a custom database role for report users
CREATE ROLE [ReportReader];
GRANT SELECT ON SCHEMA::[Reporting] TO [ReportReader];
ALTER ROLE [ReportReader] ADD MEMBER [ReportingService];
GO

Step 6 — Granular Permission Management with GRANT, DENY, and REVOKE

For fine-grained control beyond role membership, use the permission triad:

USE [SalesDB];
GO

-- Grant SELECT on a specific table
GRANT SELECT ON [dbo].[Orders] TO [AppServiceAccount];
GO

-- Grant EXECUTE on a stored procedure
GRANT EXECUTE ON [dbo].[usp_GetCustomer] TO [AppServiceAccount];
GO

-- Deny access to a sensitive column via a view (column-level security)
DENY SELECT ON [dbo].[Employees]([Salary]) TO [AppServiceAccount];
GO

-- Revoke a previously granted permission (does not explicitly deny)
REVOKE SELECT ON [dbo].[HRNotes] FROM [AppServiceAccount];
GO

-- Check effective permissions for a user
EXECUTE AS USER = 'AppServiceAccount';
SELECT * FROM fn_my_permissions('dbo.Orders', 'OBJECT');
REVERT;
GO

Step 7 — Audit Logins with SQL Server Audit

SQL Server Audit (available in all editions) writes authentication events to a file, the Windows Application Log, or the Windows Security Log.

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

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

-- Create an audit specification to capture failed and successful logins
CREATE SERVER AUDIT SPECIFICATION [LoginAuditSpec]
FOR SERVER AUDIT [LoginAudit]
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP)
WITH (STATE = ON);
GO

-- Query the audit log
SELECT
    event_time,
    action_id,
    server_principal_name,
    client_ip,
    application_name,
    [statement]
FROM sys.fn_get_audit_file('G:SQLAuditLoginAudit*.sqlaudit', DEFAULT, DEFAULT)
ORDER BY event_time DESC;

Conclusion

Properly configured authentication and authorisation is the cornerstone of a secure SQL Server 2022 deployment on Windows Server 2025. The principle of least privilege should guide every decision: create SQL logins only when Windows Authentication is not possible, use gMSA service accounts for application connectivity, assign database roles rather than instance-wide server roles wherever the application scope permits, and use DENY sparingly — REVOKE is almost always the correct choice over DENY. Enable SQL Server Audit from day one so that you have a historical record of authentication events before you need it for an incident investigation. Combining these practices with Windows Server 2025’s Credential Guard and Protected Users Active Directory group creates a layered security posture that significantly reduces the risk of credential-based attacks against your database infrastructure.

Links

Newsletter

Contact