By: Svetlana Golovko | Comments (9) | Related: > Auditing and Compliance
Problem
One of the Support Analysts asked me if it is possible to send him notifications 5 days before a user's password expires for a SQL Server Standard login. He wants to be proactive and help users reset their passwords before they expired. The application uses SQL Server authentication and does not have password management functionality. Check out this tip to learn more.
Solution
In one of our last tips we have provided several scripts for the SQL Server audit, including logins audit. But there is more to it. DBAs can dig deeper and see how many bad login attempts a user had, when was the last time the password was set and get other useful audit related information using LOGINPROPERTY function. One of the past tips covers this function and it's usage in details.
In this tip we will provide a SQL Server Reporting Service (SSRS) report for DBAs that could be emailed and reviewed daily. Also we will provide scripts to setup an alert for the Support Analyst, to notify them about upcoming passwords expirations.
SQL Server Audit Logins Report for DBAs
Report Requirements:
The report will display a list of logins that meet the following criteria:
- Created or modified during the last 24 hours
- Expired logins
- Logins that will expire in 5 days or sooner
- Locked out logins
- Disabled logins
- Logins that have access denied
- Logins with Password Policy not checked
- Logins with Password Expiration not checked
- Logins with more than 2 bad password entries
- Logins that must change the password when user logs in next time to SQL Server
Create the Report:
If you are not familiar with SSRS a good place to start is this tutorial.
Create the new report using the query below as a dataset:
SELECT l.name,
CAST(CASE sp.[state] WHEN N'D' THEN 1 ELSE 0 END AS bit) AS DenyWindowsLogin,
CASE WHEN N'U' = l.type THEN 0
WHEN N'G' = l.type THEN 1
WHEN N'S' = l.type THEN 2
WHEN N'C' = l.type THEN 3
WHEN N'K' = l.type THEN 4 END AS LoginType,
CAST(CASE WHEN (sp.[state] IS NULL) THEN 0 ELSE 1 END AS bit) AS HasAccess,
CAST(sl.is_policy_checked AS bit) AS PasswordPolicyEnforced,
CAST(sl.is_expiration_checked AS bit) AS PasswordExpirationEnabled,
l.create_date AS CreateDate,
l.modify_date AS DateLastModified,
LOGINPROPERTY(l.name, N'BadPasswordCount') AS BadPasswordCount,
LOGINPROPERTY(l.name, N'BadPasswordTime') AS BadPasswordTime,
LOGINPROPERTY(l.name, N'DaysUntilExpiration') AS DaysUntilExpiration,
LOGINPROPERTY(l.name, N'IsExpired') AS IsExpired,
LOGINPROPERTY(l.name, N'IsLocked') AS IsLocked,
LOGINPROPERTY(l.name, N'IsMustChange') AS IsMustChange,
LOGINPROPERTY(l.name, N'LockoutTime') AS LockoutTime,
LOGINPROPERTY(l.name, N'PasswordLastSetTime') AS PasswordLastSetTime,
l.is_disabled AS IsDisabled
FROM sys.server_principals AS l
LEFT OUTER JOIN sys.server_permissions AS sp
ON sp.grantee_principal_id = l.principal_id
AND sp.[type] = N'COSQ' -- Connect permissions
LEFT OUTER JOIN sys.sql_logins AS sl
ON sl.principal_id = l.principal_id
LEFT OUTER JOIN sys.credentials AS c
ON c.credential_id = l.credential_id
WHERE
l.[type] IN ('U', 'G', 'S', 'C', 'K')
AND l.principal_id NOT BETWEEN 101 AND 255 -- ##MS% certificates
AND
( sp.[state] = N'D' -- DenyWindowsLogin
OR sp.[state] IS NULL -- HasAccess
OR CAST(sl.is_policy_checked AS bit) = 0
OR CAST(sl.is_expiration_checked AS bit) = 0
OR l.create_date > GETDATE()-1
OR l.modify_date > GETDATE()-1
OR l.is_disabled > 0
OR LOGINPROPERTY(l.name, N'DaysUntilExpiration')<= 5
OR LOGINPROPERTY(l.name, N'IsExpired') > 0
OR LOGINPROPERTY(l.name, N'IsLocked') > 0
OR LOGINPROPERTY(l.name, N'IsMustChange') > 0
OR LOGINPROPERTY(l.name, N'BadPasswordCount') > 2
)Create a simple table layout and use all of the fields from the query above:
Set conditional formatting to display text in red (using "Color" property) as following:
- for the fields - "DenyWindowsLogin", "IsDisabled", "BadPasswordCount", "IsExpired", "IsMustChange", "IsLocked":
=iif(Value=0,"Black","Red")
- for the fields - "PasswordPolicyEnforced", "PasswordExpirationEnabled":
=iif(Value=True,"Black","Red")
- for the field - "DaysUntilExpiration":
=iif(Value>5,"Black","Red")
Read this tip to find out more about SSRS conditional formatting.
Run the report and review the results.
Actions:
- Review and delete (if required) disabled logins and/or logins that have access denied issues.
- Review new or modified logins (created or modified during the last 24 hours).
- Review and update logins with password policy and expiration issues.
- Contact users that had many bad passwords attempts or that are locked out. Unlock login(s) if required.
- Contact users that have expired login or users whose login will expire soon (to reset the password).
- Contact users that "must change" their password and the "password last set" time exceeds you password policy.
Configure SQL Server Agent Operator, Alert and Job for the Support Analyst
We will use a query that utilizes the same (LOGINPROPERTY) function. Also, we will create custom error message, alert, operator and job that will run daily to monitor the logins. Read more about SQL Server Agent (including Notifications and Operators) here.
First, create a custom error message (you can use any number above 50000 that is not in use in your environment):
EXEC msdb.dbo.sp_addmessage
@msgnum = 60001,
@severity = 10,
@msgtext = 'Password is about to expire (or expired) for the user(s): %s.
Please, contact DBA and/or user(s) to reset the password(s).',
@with_log ='TRUE'
Create operator:
EXEC msdb.dbo.sp_add_operator @name=N'Support_App1', @enabled=1, @weekday_pager_start_time=90000, @weekday_pager_end_time=163000, @pager_days=62, @email_address=N'[email protected]'
Create alert using the error message we have created above:
EXEC msdb.dbo.sp_add_alert @name=N'SQL Login Change Password - action required', @message_id=60001, @severity=0, @enabled=1, @delay_between_responses=0, @include_event_description_in=1
Create notification for the Support Analyst:
EXEC msdb.dbo.sp_add_notification @alert_name=N'SQL Login Change Password - action required', @operator_name=N'Support_App1', @notification_method = 1
Now create the SQL Server Agent Job that will run daily. The SQL Server Agent Job will have one Transact-SQL step. The command for the step is:
IF EXISTS (SELECT name, LOGINPROPERTY(name, N'DaysUntilExpiration')
FROM sys.server_principals
WHERE type = 'S'
AND LOGINPROPERTY(name, N'DaysUntilExpiration') < 6
AND default_database_name = 'APP1_Prod')
BEGIN
DECLARE @str VARCHAR(200)
SELECT @str = COALESCE(@str + ',', '') + name FROM sys.server_principals
WHERE type = 'S' AND LOGINPROPERTY(name, N'DaysUntilExpiration') < 6
AND default_database_name = 'APP1_Prod'
RAISERROR (60001, 10, 1, @str );
ENDYou can add exceptions to the notifications by filtering this statement, for example:
- exclude "sa" and application's service account:
....
AND default_database_name = 'APP1_Prod'
AND name NOT IN ('sa', 'app_service_login')
....
- exclude disabled or locked out logins:
.... AND default_database_name = 'APP1_Prod' AND is_disabled = 0 AND LOGINPROPERTY(name, N'IsLocked') = 0 ....
Next Steps
- Run the script we used in SSRS report on multiple servers using Central Management Server as in this tip.
- Review and fix issues.
- Schedule the report to run daily and review it on a daily basis.
- Retain the latest reports in case you need to provide evidence of the login review to auditors.
- Find out if your Support Analyst wants to be notified about expiring passwords.
- UPDATE - Click here for the RDL file for this tip.
About the author
Svetlana Golovko is a DBA with 13 years of the IT experience (including SQL Server and Oracle) with main focus on performance. This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
