How to check the Active Directory user password policy When Get-ADUserResultantPasswordPolicy Returns Nothing

Recently, I have had to troubleshoot a variety of SQL login issues, and often the problem was related to the user's Active Directory account.

I am aware that my organization implements security policies that include, among other things, an Active Directory password policy. Of course, there's also the SQL Server user security policy, which I am quite familiar with.

To better support my users, I decided it would be beneficial to familiarize myself with the current Active Directory password policy. There is a document that outlines this policy, but I want to verify the settings that are actually in effect. After all, documentation can sometimes be outdated.

For this purpose, I am using PowerShell to retrieve the password policy values. Note that you will need to have the ActiveDirectory PowerShell module installed to do this.

# To check if the AD module is installed

Get-Module -Name ActiveDirectory

# Lets now check if a password policy is assigned to a user
Get-ADUserResultantPasswordPolicy -Identity aduser1

In this case, the 'Get-ADUserResultantPasswordPolicy' command returns nothing. This likely indicates that the password policy is not assigned on an individual user basis, known as Fine-Grained Password Policies. If your AD environment doesn't have Fine-Grained Password Policies (FGPP) defined or if they are not applied to the specific user you're querying, the command will return no result. FGPP allows different password policies within the same domain, but if it's not set up, users will just follow the default domain password policy

This approach makes sense for most organizations. Assigning unique password policies to each user can be impractical. Instead, for groups of users who require stricter (e.g., users with administrative rights) or more lenient password policies, it's more efficient to segment them into different domains or organizational units (OUs). These segments can have tailored settings to mitigate any associated risks.

I am now going to verify the default password policy in the domain.

Get-ADDefaultDomainPasswordPolicy

When you run this command, it returns various properties of the default domain password policy, such as:

• ComplexityEnabled: Indicates whether password complexity requirements are enabled.
• LockoutDuration: The duration for which an account remains locked after reaching the specified number of failed login attempts.
• LockoutObservationWindow: The time window in which consecutive failed login attempts are counted towards the lockout threshold.
• LockoutThreshold: The number of failed login attempts that will trigger an account lockout.
• MaxPasswordAge: The maximum age of a password before it must be changed.
• MinPasswordAge: The minimum age of a password before it can be changed.
• MinPasswordLength: The minimum number of characters required in the password.
• PasswordHistoryCount: The number of unique new passwords a user must use before an old password can be reused.

For example:

ComplexityEnabled           : True
DistinguishedName           : DC=internal,DC=external,DC=org
LockoutDuration             : 00:30:00
LockoutObservationWindow    : 00:30:00
LockoutThreshold            : 6
MaxPasswordAge              : 90.00:00:00
MinPasswordAge              : 1.00:00:00
MinPasswordLength           : 8
objectClass                 : {domainDNS}
objectGuid                  : xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
PasswordHistoryCount        : 8
ReversibleEncryptionEnabled : False

To get the default policy in a different AD domain than you are currently logged into:

Get-ADDefaultDomainPasswordPolicy -Server MyADDomain 

And, if you are curious what all these values mean, please refer to the following MS document:


https://learn.microsoft.com/en-us/powershell/module/activedirectory/set-addefaultdomainpasswordpolicy

How do you use PowerShell to check if an active directory user locked out, disabled etc.?

If your organization uses a password policy (there are very good odds these days that it does) and, especially stricter password requirement for administrative users, your might have experienced instances where yours or your users Active Directory user might be locked out.

How do you check if that is the case? Well, for one thing the Windows will tell you so when you try to login and/or failed login attempts are logged in to sql log, event logs etc.  What if user does not logout or have more than one user account, one for regular use and one for administrative tasks? There maybe other scenarios where you have a need to check status of a user account in the Active Directory.

I don't have admin privileges in Active Directory and presumably you don't either.  However, I do have read permission on the AD so I could have used Active Directory Users and Groups snap-in i.e. GUI tool.

But, here I am going to show you the PowerShell way.

You will need to have the ActiveDirectory PowerShell module installed for the following cmdlets to work. To check if you already have it:

Get-Module -Name ActiveDirectory

To cherck if it is available to import into your current session:

Get-Module  -ListAvailable -name ActiveDirectory

If it is available, you can import it using the following command. You will need to be running the elevated PowerShell for this:

Import-Module -Name ActiveDirectory

Let's check if the user account is disabled:

# Is account disabled?

get-aduser aduser1 -Properties enabled | ft Enabled

Enabled
-------
   True 

# Is account locked out?

get-aduser aduser1 -Properties LockedOut | ft LockedOut

LockedOut
---------
    False

# When does the password expire?

Get-ADUser aduser1 -properties msDS-UserPasswordExpiryTimeComputed | select @{N="PasswordExpiryDate";E={[DateTime]::FromFileTime($_."msDS-UserPasswordExpiryTimeComputed")}}


PasswordExpiryDate  
------------------  
2/13/2020 2:58:26 PM
 

# Finally, view all properties for a user account

get-aduser aduser1 -Properties *

If the user account is in a different AD domain:

get-aduser aduser1 -Server ad_domain_name -Properties *

To find out more about the Get-ADUser command:

Get-Help  Get-ADUser -ShowWindow

That will pop up a window with the Get-ADUser help file.

And to see all the commands available in ActiveDirectory module:

Get-Command  -Module ActiveDirectory | Select-Object Name

Name                                                
------------------------------------                                                
Add-ADCentralAccessPolicyMember                     
Add-ADComputerServiceAccount                        
Add-ADDomainControllerPasswordReplicationPolicy     
Add-ADFineGrainedPasswordPolicySubject              
Add-ADGroupMember                                   
Add-ADPrincipalGroupMembership                      
Add-ADResourcePropertyListMember                    
Clear-ADAccountExpiration                           
Clear-ADClaimTransformLink                          
Disable-ADAccount                                   
Disable-ADOptionalFeature                           
Enable-ADAccount                                    
Enable-ADOptionalFeature                            
Get-ADAccountAuthorizationGroup                     
Get-ADAccountResultantPasswordReplicationPolicy     
Get-ADAuthenticationPolicy                          
Get-ADAuthenticationPolicySilo                      
Get-ADCentralAccessPolicy                           
Get-ADCentralAccessRule                             
Get-ADClaimTransformPolicy                          
Get-ADClaimType                                     
Get-ADComputer          
....
....
....

 

SQL Server Event Notifications Example

Event notifications are kinda like a trigger in the sense that they respond to specific event, specifically in response to DDL statements and SQL Trace events.

The major difference between the triggers and event notifications is that the triggers are fired in synchronous mode to execute a specific code within the same session and transaction. Whereas, the event notification do not execute any code, it only sends information in asynchronous mode which then can be logged and acted upon later on.


Here is a snippet from the Microsoft Documentation that explains the differences:

Event Notifications vs. Triggers

The following table compares and contrasts triggers and event notifications.

Triggers	Event Notifications
DML triggers respond to data manipulation language (DML) events. DDL triggers respond to data definition language (DDL) events.	Event notifications respond to DDL events and a subset of SQL trace events.
Triggers can run Transact-SQL or common language runtime (CLR) managed code.	Event notifications do not run code. Instead, they send xml messages to a Service Broker service.
Triggers are processed synchronously, within the scope of the transactions that cause them to fire.	Event notifications may be processed asynchronously and do not run in the scope of the transactions that cause them to fire.
The consumer of a trigger is tightly coupled with the event that causes it to fire.	The consumer of an event notification is decoupled from the event that causes it to fire.
Triggers must be processed on the local server.	Event notifications can be processed on a remote server.
Triggers can be rolled back.	Event notifications cannot be rolled back.
DML trigger names are schema-scoped. DDL trigger names are database-scoped or server-scoped.	Event notification names are scoped by the server or database. Event notifications on a QUEUE_ACTIVATION event are scoped to a specific queue.
DML triggers are owned by the same owner as the tables on which they are applied.	The owner of an event notification on a queue may have a different owner than the object on which it is applied.
Triggers support the EXECUTE AS clause.	Event notifications do not support the EXECUTE AS clause.
DDL trigger event information can be captured using the EVENTDATA function, which returns an xml data type.	Event notifications send xml event information to a Service Broker service. The information is formatted to the same schema as that of the EVENTDATA function.
Metadata about triggers is found in the sys.triggers and sys.server_triggerscatalog views.	Metadata about event notifications is found in the sys.event_notifications and sys.server_event_notifications catalog views.

---

In the following sample SQL script, I am creating an Extended Event to capture  ALTER TABLE events in a database.

-- Create a brand new database for the testing purpose
use master
go
if db_id('temp_event_notification_test_929368242990-321836') is not null
  drop database [temp_event_notification_test_929368242990-321836]
go
CREATE DATABASE [temp_event_notification_test_929368242990-321836]
GO


-- Enable the service broker if its not already
if not exists 

(select * from sys.databases 
 where name = '[temp_event_notification_test_929368242990-321836]' 
   and is_broker_enabled = 1
)

ALTER DATABASE [temp_event_notification_test_929368242990-321836] SET ENABLE_BROKER; 
go

-- set the trustworth property ON
if not exists 
(select * from sys.databases 
 where name = '[temp_event_notification_test_929368242990-321836]' 
   and is_trustworthy_on = 1
)

ALTER DATABASE [temp_event_notification_test_929368242990-321836] 
SET TRUSTWORTHY ON;
go

-- check if there is already a service broker end point running
if not exists (select * from sys.service_broker_endpoints 
               where type_desc = 'SERVICE_BROKER' 
			     and state_desc = 'STARTED' 
			  )
BEGIN
    -- check if there is a SB endpoint with same name
    if not exists 
	(select * from sys.service_broker_endpoints 
	 where NAME = 'en_service_broker_929368242990-321836')
 
 BEGIN
    -- check to make sure the tcp port is not already in use
    if not exists (SELECT * FROM SYS.tcp_endpoints where port = 5122)
	   CREATE ENDPOINT [en_service_broker_929368242990-321836]
	   STATE = STARTED
	   AS TCP (  LISTENER_PORT = 5122)
	   FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS  );

    else
		raiserror(
		'Error: An end point cannot be created. 
		 Please check if there is already one with same port.', 16,1)
 END
 else
    raiserror(
	'Error: An end point cannot be created. 
	 Please check if there is already one with same name.', 16,1)
END
GO
USE [temp_event_notification_test_929368242990-321836]

go
CREATE QUEUE [ent_929368242990-321836] ;  
GO  
CREATE SERVICE [ens_929368242990-321836]  
ON QUEUE [ent_929368242990-321836]  
(  
[http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]  
);  
GO  

CREATE ROUTE [enr_929368242990-321836]  
WITH SERVICE_NAME = 'ens_929368242990-321836',  
ADDRESS = 'LOCAL';  
GO  

CREATE EVENT NOTIFICATION [enen_929368242990-321836]  
ON DATABASE  
FOR ALTER_TABLE  
TO SERVICE 'ens_929368242990-321836',  'current database'

-- Test
-- Generate the events
if object_id('entt_929368242990-321836') is not null
drop table [entt_929368242990-321836]
go
create table [entt_929368242990-321836] (i int)
go
alter table [entt_929368242990-321836] add b int
go

-- verify/display that the event notification was captured
SELECT TOP (1000) *, casted_message_body = 
CASE message_type_name WHEN 'X' 
  THEN CAST(message_body AS NVARCHAR(MAX)) 
  ELSE message_body 
END 
FROM 
[temp_event_notification_test_929368242990-321836].[dbo].[ent_929368242990-321836] 

if @@ROWCOUNT = 0
   RAISERROR(
   'Error: Something is not right. Event notification was not captured.', 16,1)

else 
   SELECT 'Success!' Msg
go


-- clear the records from the queue
-- RECEIVE display the event as well as removes it from the queue

RECEIVE * FROM [ent_929368242990-321836]
go
-- verify that the queue is now empty

SELECT TOP (1000) *, casted_message_body = 
	CASE message_type_name WHEN 'X' 
	  THEN CAST(message_body AS NVARCHAR(MAX)) 
	  ELSE message_body 
	END 
FROM 
[temp_event_notification_test_929368242990-321836].[dbo].[ent_929368242990-321836]

/*
Since I am only testing, I am using the following code to clean up afterwards
*/

/* CLEAN UP
USE [temp_event_notification_test_929368242990-321836]
go
if exists 
(SELECT * FROM sys.event_notifications 
 where name = '[enen_929368242990-321836]' and parent_class_desc = 'DATABASE')

	DROP EVENT NOTIFICATION [enen_929368242990-321836]  ON DATABASE;  

go

if exists (select  * from sys.routes 
           where name = '[enr_929368242990-321836]' and address = 'LOCAL')

	DROP ROUTE [enr_929368242990-321836] 

if exists (SELECT * FROM sys.services 
           where name = 'ens_929368242990-321836')

	DROP SERVICE [ens_929368242990-321836]
GO
if exists (SELECT * FROM sys.service_queues 
           where name = 'ent_929368242990-321836' 
		     and schema_id = 1)

	DROP QUEUE [dbo].[ent_929368242990-321836]
GO
use master
go
if db_id('temp_event_notification_test_929368242990-321836') is not null
  drop database [temp_event_notification_test_929368242990-321836]
go

*/

Download this script at the GitHub:

SQL Server Event Notifications Example

What SQL Server Agent Alerts Do I have setup?

I am in a situation where I have to incorporate SQL Server Agent alerts in my monitoring and alerting strategy.

I needed a query (DMV) to get details on what alerts are setup on each server. And the result is the following query that I will run as a multi-server query.

SELECT a.[id]                        [alert_id], 
       a.[name]                      [alert_name], 
       a.[enabled]                   [is_alert_enabled], 
       o.[enabled]                   [is_operator_enabled], 
       o.[email_address]             [email_address], 
       o.[pager_address]             [pager_address], 
       o.[netsend_address]           [netsend_address],
       j.[name]                      [job_name], 
       a.[event_source]              [alert_event_source], 
       a.[event_category_id]         [alert_event_category_id], 
       sc.[name]                     [alert_category_name], 
       CASE sc.[category_class] 
         WHEN 1 THEN 'JOB' 
         WHEN 2 THEN 'ALERT' 
         WHEN 3 THEN 'OPERATOR' 
         ELSE '0' 
       END                           [alert_class_name], 
       sm.[description]              [alert_message_description],
       a.[event_id]                  [alert_event_id], 
       a.[message_id]                [alert_message_id], 
       a.[severity]                  [alert_severity], 
       a.[enabled]                   [alert_enabled], 
       a.[delay_between_responses]   [alert_delay_between_responses], 
       a.[last_occurrence_date]      [alert_last_occurrence_date], 
       a.[last_occurrence_time]      [alert_last_occurrence_time], 
       a.[last_response_date]        [alert_last_response_date], 
       a.[last_response_time]        [alert_last_response_time], 
       a.[notification_message]      [alert_notification_message], 
       a.[include_event_description] [alert_include_event_description], 
       a.[database_name]             [alert_database_name], 
       a.[event_description_keyword] [alert_event_description_keyword], 
       a.[occurrence_count]          [alert_occurrence_count], 
       a.[count_reset_date]          [alert_count_reset_date], 
       a.[count_reset_time]          [alert_count_reset_time], 
       a.[job_id]                    [alert_job_id], 
       a.[has_notification]          [alert_has_notification], 
       a.[flags]                     [alert_flags], 
       a.[performance_condition]     [alert_performance_condition], 
       a.[category_id]               [alert_category_id] 
FROM   msdb.dbo.sysalerts a 
       LEFT OUTER JOIN msdb.dbo.syscategories sc ON a.category_id = sc.category_id 
       LEFT OUTER JOIN msdb.dbo.sysnotifications sn ON ( a.id = sn.alert_id ) 
       LEFT OUTER JOIN msdb.dbo.sysoperators o ON ( o.id = sn.operator_id ) 
       LEFT OUTER JOIN msdb.dbo.sysjobs j ON j.job_id = a.job_id 
       LEFT OUTER JOIN msdb.dbo.sysmessages sm ON sm.error = a.message_id
   and sm.msglangid = SERVERPROPERTY('LCID')
ORDER  BY 1 

And here is the sample result:

Find The Most Cached Database In The Buffer Cache - The DMV Way

While there are many ways and criteria to find out what database is the most used, under optimized, trouble maker etc....., here is one more to find out the database/s using the most buffer cache.

SELECT 
CASE database_id   
        WHEN 32767 THEN 'ResourceDb'   
        ELSE db_name(database_id)   
        END AS database_name,
COUNT(*)AS cached_pages_count,
COUNT(*) / 128 / 1024 AS cache_size_gb
    
FROM sys.dm_os_buffer_descriptors  
GROUP BY DB_NAME(database_id) ,database_id  
ORDER BY cached_pages_count DESC; 

Here is the results I got on one of the production SQL Servers. 

Lucky for me, it turned out to be an extreme case. Now I knew where I should focus optimization efforts or even if that database belongs with rest of the databases on that same server.

I have tested this query for SQL 2008 (SP3) and up.

Hope you find this useful.