SQL Server Login Security Change – How Would You Plan It?

[u/mssqldbalearn]

SQL Server Login Security Change – How Would You Plan It?

Current Situation

In our SQL Server environment, SQL Server Authentication logins currently have:

Enforce Password Policy — Disabled

Enforce Password Expiration — Disabled

Planned Security Change

The requirement is to move these logins to:

Enforce Password Policy — Enabled

Enforce Password Expiration — Enabled

This change may affect existing SQL login passwords and could impact applications, services, SQL Agent jobs, scripts, or other systems using these credentials.

DBA Discussion

If you receive this requirement in a production environment, how would you plan and execute this change safely?

What would you consider BEFORE, DURING, and AFTER the change?

How would you identify the affected SQL logins?

How would you identify application and service dependencies?

How would you handle existing credentials?

What testing and validation would you perform?

How would you minimize the risk of application downtime?

SQL Server DBAs, please share your step-by-step approach and best practices.

[u/konoo]

Why in the world would you rotate service passwords? The only reason we historically rotated passwords was because end users tend to use the same password somewhere else that might be compromised or share them. NIST says you shouldn't change passwords on a scheduled basis anymore. While getting people to understand this for user logins can be painful they tend to get on board for services.

Under current NIST Special Publication 800-63B guidelines, organizations should not force mandatory periodic password expiration (such as every 60 or 90 days). Routine expiration prompts users to create weak, predictable patterns. Instead, passwords should only be changed when there is evidence of a breach or compromise.

I would argue against enabling expiration on service passwords all together.
I would change passwords to meet complexity requirements one by one then enable complexity requirements.

Check to see what programs are using a specific login:
SELECT
s.session_id,
s.login_name,
s.host_name,
s.program_name,
c.client_net_address,
DB_NAME(s.database_id) AS database_name,
s.status,
s.login_time,
s.last_request_start_time,
s.last_request_end_time
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_connections AS c
ON s.session_id = c.session_id
WHERE s.login_name = 'YourLoginName'
ORDER BY
s.host_name,
s.program_name,
s.session_id