Adding enhanced password constraints to SQL Server

Published Mar 23 2019 12:00 PM 698 Views
Microsoft
First published on TECHNET on Mar 29, 2011

[Prior Post in Series] [Next Post in Series]


In my prior posts on applying PCIDSS standards to protect your company’s data I showed now a DDL trigger (FOR LOGON) may be used to enhance logon security . In this post I will look at a different pattern to improve password constraints.


In this post I will look at these facets of Payment Card Industry (PCI) Data Security Standard Requirements and Security Assessment Procedu... (PCIDSS)



  • Change user passwords at least every 90 days.

  • Require a minimum password length of at least seven characters.

  • Use passwords containing both numeric and alphabetic characters.

  • Do not allow an individual to submit a new password that is the same as any of the last four passwords he or she has used.


  • The ability to set password complexity.




What is in the box?




Enforce password policy


This results in the password passing to the NetValidatePasswordPolicy API for validation against windows policies set on the box that SQL Server is located on. Starting with Windows 2003, the windows password policy checks:



  • Password minimum length,

  • Password history (password can’t be used if it is the same as previous N passwords),

  • Minimum and Maximum Password Age

  • Complexity Requirements


Since this is a SQL Server blog, I will not go into details, apart from showing the dialogs located at Control Panel / Administrative Tools/ Local Security Policy.



For further information see:



Enforce password expiration


To enable this, password policy must be checked.


The Coding Solution


After setting Windows policy, the only issue remaining is enforcing this policy. Enforce password policy and Enforce password expiration must be selected whenever a change of password occurs. This is done with a DDL trigger such as the one shown below.



CREATE trigger [t_PasswordChange]
ON ALL server for ALTER_LOGIN, CREATE_LOGIN
As
DECLARE @EventData XML
DECLARE @CommandText nvarchar(max)
SET @EventData=EVENTDATA()
SET @CommandText = @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)')
IF CHARINDEX('CHECK_EXPIRATION=ON, CHECK_POLICY=ON', @CommandText ) < 1
BEGIN
RAISERROR('Expiration and Policy must be on',16,1)
ROLLBACK
END



The result of trying to change a password or create a user without both Expiration and Policy on is:


The RAISERROR message does not bubble through. This means that you need to document well what you have done. I should also mention where you can find these triggers in SSMS, they are under Server Objects as shown below.



SQL Only Password Change Enforcement


It is possible to force a password change at the SQL level for SQL Login (Not local or domain logins) but things can get a little complex depending on how SQL Logins are used.


First, we create a table in the Security database cited in earlier posts:



CREATE TABLE [dbo].[PasswordChange](
[LoginName] [nvarchar](128) NOT NULL,
[LastPasswordChange] [datetime] NOT NULL Default(GetDate()),
CONSTRAINT [PK_PasswordChange] PRIMARY KEY CLUSTERED
(
[LoginName] ASC
))



Capturing When a Password is Changed


Next we set up a trigger to record when SQL Server passwords are changed.



CREATE trigger [t_PasswordChangeWhen]
ON ALL server for ALTER_LOGIN, CREATE_LOGIN
As
DECLARE @EventData XML
DECLARE @CommandText nvarchar(max)
DECLARE @LoginName SysName
SET @EventData=EVENTDATA()
SET @CommandText = @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)')
SET @LoginName = RTRIM(
LTRIM(
Replace(
Replace(
SubString(@CommandText ,
charindex('LOGIN',@CommandText)+5,
charindex('WITH',@CommandText)- charindex('LOGIN',@CommandText)-5)
,'[','')
,']','')
)
)
IF CHARINDEX('PASSWORD=N''******''', @CommandText ) > 0
BEGIN
IF NOT EXISTS(SELECT 1 FROM [Security].[dbo].[PasswordChange]
WHERE [LoginName]=@LoginName)
BEGIN
INSERT INTO [Security].[dbo].[PasswordChange]
([LoginName],[LastPasswordChange])
VALUES(@LoginName,GetDate(),0)
END
ELSE
BEGIN
UPDATE [Security].[dbo].[PasswordChange]
SET [LastPasswordChange]=GetDate()
WHERE [LoginName]=@LoginName
END
END


Some manipulation of the TSQLCommand is needed to get a consistent login name.


Denying the Login of an Expired Password


The following trigger disables an account with an expired password.



CREATE TRIGGER t_Logon_ChangePasswordCheck
ON ALL SERVER
FOR LOGON
AS
BEGIN
Declare @DenyAt int
Declare @Data Xml
DECLARE @UserName varchar(120)
Set @Data=EVENTDATA()
SET @DenyAt=37
SET @UserName=@Data.value('(EVENT_INSTANCE/LoginName)[1]','varchar(120)')
IF NOT EXISTS(SELECT 1 FROM [Security].[dbo].[PasswordChange] WHERE [LoginName]=@UserName)
INSERT INTO [Security].[dbo].[PasswordChange]
([LoginName],[LastPasswordChange],[Warnings])
VALUES(@UserName,GetDate(),0)
END
IF EXISTS(SELECT 1 FROM [Security].[dbo].[PasswordChange]
WHERE [LoginName]=@UserName
AND DateAdd(dd,@DenyAt,[LastPasswordChange]) < GetDate())
ROLLBACK TRAN


The problem is that it is not possible to display a message to the user advising them to change the password (RAISERROR, PRINT and SELECT do not bubble up).


Summary


There is little native support in SQL Server to support PCI password policies. SQL Server uses Window’s password policy and this is where you should implement passwords policy. The trigger above prevents any SQL Logins being exempt from the Window’s password policy unless you explicitly white-list in the trigger.

%3CLINGO-SUB%20id%3D%22lingo-sub-383793%22%20slang%3D%22en-US%22%3EAdding%20enhanced%20password%20constraints%20to%20SQL%20Server%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-383793%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20TECHNET%20on%20Mar%2029%2C%202011%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3E%3CA%20href%3D%22http%3A%2F%2Fblogs.technet.com%2Fb%2Fsql_server_isv%2Farchive%2F2011%2F03%2F28%2Ftemporal-constraints-on-login-in-sql-server.aspx%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%20%5BPrior%20Post%20in%20Series%5D%20%3C%2FA%3E%20%3CA%20href%3D%22http%3A%2F%2Fblogs.technet.com%2Fb%2Fsql_server_isv%2Farchive%2F2011%2F03%2F02%2Fimproving-sql-server-security-to-pcidss-standards.aspx%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%20%5BNext%20Post%20in%20Series%5D%20%3C%2FA%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EIn%20my%20prior%20posts%20on%20applying%20%3CA%20href%3D%22http%3A%2F%2Fblogs.technet.com%2Fb%2Fsql_server_isv%2Farchive%2F2011%2F03%2F02%2Fimproving-sql-server-security-to-pcidss-standards.aspx%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%20PCIDSS%20standards%20to%20protect%20your%20company%E2%80%99s%20data%20%3C%2FA%3E%20I%20showed%20now%20a%20%3CA%20href%3D%22http%3A%2F%2Fblogs.technet.com%2Fb%2Fsql_server_isv%2Farchive%2F2011%2F03%2F07%2Fadding-failed-sql-server-logon-support-to-a-plm-sql-server.aspx%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%20DDL%20trigger%20(FOR%20LOGON)%20may%20be%20used%20to%20enhance%20logon%20security%20%3C%2FA%3E%20.%20In%20this%20post%20I%20will%20look%20at%20a%20different%20pattern%20to%20improve%20password%20constraints.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EIn%20this%20post%20I%20will%20look%20at%20these%20facets%20of%20%3CA%20href%3D%22https%3A%2F%2Fwww.pcisecuritystandards.org%2Fdocuments%2Fpci_dss_v2.pdf%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%20Payment%20Card%20Industry%20(PCI)%20Data%20Security%20Standard%20Requirements%20and%20Security%20Assessment%20Procedures%20Version%202.0%20%3C%2FA%3E%20(PCIDSS)%3C%2FP%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EChange%20user%20passwords%20at%20least%20every%2090%20days.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3ERequire%20a%20minimum%20password%20length%20of%20at%20least%20seven%20characters.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EUse%20passwords%20containing%20both%20numeric%20and%20alphabetic%20characters.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EDo%20not%20allow%20an%20individual%20to%20submit%20a%20new%20password%20that%20is%20the%20same%20as%20any%20of%20the%20last%20four%20passwords%20he%20or%20she%20has%20used.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CBR%20%2F%3E%3CP%3EThe%20ability%20to%20set%20password%20complexity.%3C%2FP%3E%3CBR%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3CH2%20id%3D%22toc-hId-1680695753%22%20id%3D%22toc-hId--1438356939%22%3EWhat%20is%20in%20the%20box%3F%3C%2FH2%3E%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F97397i631FA4946C18C2DD%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CH3%20id%3D%22toc-hId--1067974713%22%20id%3D%22toc-hId--747795465%22%3EEnforce%20password%20policy%3C%2FH3%3E%3CBR%20%2F%3E%3CP%3EThis%20results%20in%20the%20password%20passing%20to%20the%20%3CA%20href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Faa370661(VS.85).aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20NetValidatePasswordPolicy%20API%20%3C%2FA%3E%20for%20validation%20against%20windows%20policies%20set%20on%20the%20box%20that%20SQL%20Server%20is%20located%20on.%20Starting%20with%20Windows%202003%2C%20the%20windows%20password%20policy%20checks%3A%3C%2FP%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EPassword%20minimum%20length%2C%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EPassword%20history%20(password%20can%E2%80%99t%20be%20used%20if%20it%20is%20the%20same%20as%20previous%20N%20passwords)%2C%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EMinimum%20and%20Maximum%20Password%20Age%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EComplexity%20Requirements%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3CP%3ESince%20this%20is%20a%20SQL%20Server%20blog%2C%20I%20will%20not%20go%20into%20details%2C%20apart%20from%20showing%20the%20dialogs%20located%20at%20Control%20Panel%20%2F%20Administrative%20Tools%2F%20Local%20Security%20Policy.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F97398i10AB837AF71A7FDA%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFor%20further%20information%20see%3A%3C%2FP%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3E%3CA%3E%20Configuring%20Password%20Policies%20%3C%2FA%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CA%20href%3D%22http%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Flibrary%2Fdd277400.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20Configuring%20Account%20Lockout%20Policies%20%3C%2FA%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CA%20href%3D%22http%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Flibrary%2Fcc783860(WS.10).aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20Account%20Passwords%20and%20Policies%20in%20Windows%20Server%202003%20%3C%2FA%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CA%20href%3D%22http%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Flibrary%2Fcc757692(WS.10).aspx%23w2k3tr_sepol_accou_set_tdtx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20Account%20Lockout%20Policy%20%3C%2FA%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CA%20href%3D%22http%3A%2F%2Fblogs.technet.com%2Fb%2Fseanearp%2Farchive%2F2007%2F10%2F06%2Fwindows-server-2008-fine-grained-password-policy-walkthrough.aspx%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%20Windows%20Server%202008%20-%20Fine%20Grained%20Password%20Policy%20Walkthrough%20%3C%2FA%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CBR%20%2F%3E%3CP%3E%3CA%20href%3D%22http%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Flibrary%2Fcc754544(WS.10).aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20Appendix%20A%3A%20Fine-Grained%20Password%20and%20Account%20Lockout%20Policy%20Review%20%3C%2FA%3E%3C%2FP%3E%3CBR%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3CH3%20id%3D%22toc-hId-674835622%22%20id%3D%22toc-hId-1739717368%22%3EEnforce%20password%20expiration%3C%2FH3%3E%3CBR%20%2F%3E%3CP%3ETo%20enable%20this%2C%20password%20policy%20must%20be%20checked.%3C%2FP%3E%3CBR%20%2F%3E%3CH3%20id%3D%22toc-hId--1877321339%22%20id%3D%22toc-hId--67737095%22%3EThe%20Coding%20Solution%3C%2FH3%3E%3CBR%20%2F%3E%3CP%3EAfter%20setting%20Windows%20policy%2C%20the%20only%20issue%20remaining%20is%20%3CI%3E%20enforcing%20this%20policy.%20%3C%2FI%3E%20Enforce%20password%20policy%20and%20Enforce%20password%20expiration%20must%20be%20selected%20whenever%20a%20change%20of%20password%20occurs.%20This%20is%20done%20with%20a%20DDL%20trigger%20such%20as%20the%20one%20shown%20below.%3C%2FP%3E%3CBR%20%2F%3E%3CDIV%20id%3D%22scid%3A9D7513F9-C04C-4721-824A-2B34F0212519%3A31b377af-e37c-4960-a990-d429f3c85257%22%3E%3CBR%20%2F%3E%3CDIV%3ECREATE%20trigger%20%5Bt_PasswordChange%5D%20%3CBR%20%2F%3E%20ON%20ALL%20server%20for%20ALTER_LOGIN%2C%20CREATE_LOGIN%20%3CBR%20%2F%3E%20As%20%3CBR%20%2F%3E%20DECLARE%20%40EventData%20XML%20%3CBR%20%2F%3E%20DECLARE%20%40CommandText%20nvarchar(max)%20%3CBR%20%2F%3E%20SET%20%40EventData%3DEVENTDATA()%20%3CBR%20%2F%3E%20SET%20%40CommandText%20%3D%20%40EventData.value('(%2FEVENT_INSTANCE%2FTSQLCommand)%5B1%5D'%2C%20'nvarchar(max)')%20%3CBR%20%2F%3E%20IF%20CHARINDEX('CHECK_EXPIRATION%3DON%2C%20CHECK_POLICY%3DON'%2C%20%40CommandText%20)%20%26lt%3B%201%20%3CBR%20%2F%3E%20BEGIN%20%3CBR%20%2F%3E%20RAISERROR('Expiration%20and%20Policy%20must%20be%20on'%2C16%2C1)%20%3CBR%20%2F%3E%20ROLLBACK%20%3CBR%20%2F%3E%20END%20%3CBR%20%2F%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EThe%20result%20of%20trying%20to%20change%20a%20password%20or%20create%20a%20user%20without%20both%20Expiration%20and%20Policy%20on%20is%3A%20%3CBR%20%2F%3E%20%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F97399i374F3C45847B2733%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EThe%20RAISERROR%20message%20%3CI%3E%20does%20not%20%3C%2FI%3E%20bubble%20through.%20This%20means%20that%20you%20need%20to%20document%20well%20what%20you%20have%20done.%20I%20should%20also%20mention%20where%20you%20can%20find%20these%20triggers%20in%20SSMS%2C%20they%20are%20under%20Server%20Objects%20as%20shown%20below.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F97400iCA7EECF22938E7CB%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%3CH2%20id%3D%22toc-hId-62002501%22%20id%3D%22toc-hId--78240199%22%3ESQL%20Only%20Password%20Change%20Enforcement%3C%2FH2%3E%3CBR%20%2F%3E%3CP%3EIt%20is%20possible%20to%20force%20a%20password%20change%20at%20the%20SQL%20level%20for%20SQL%20Login%20(Not%20local%20or%20domain%20logins)%20but%20things%20can%20get%20a%20little%20complex%20depending%20on%20how%20SQL%20Logins%20are%20used.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFirst%2C%20we%20create%20a%20table%20in%20the%20Security%20database%20cited%20in%20earlier%20posts%3A%3C%2FP%3E%3CBR%20%2F%3E%3CDIV%20id%3D%22scid%3A9D7513F9-C04C-4721-824A-2B34F0212519%3Aeb193751-3ef5-49b4-9bb7-a88c1cc05aab%22%3E%3CBR%20%2F%3E%3CDIV%3ECREATE%20TABLE%20%5Bdbo%5D.%5BPasswordChange%5D(%20%3CBR%20%2F%3E%20%5BLoginName%5D%20%5Bnvarchar%5D(128)%20NOT%20NULL%2C%20%3CBR%20%2F%3E%20%5BLastPasswordChange%5D%20%5Bdatetime%5D%20NOT%20NULL%20Default(GetDate())%2C%20%3CBR%20%2F%3E%20CONSTRAINT%20%5BPK_PasswordChange%5D%20PRIMARY%20KEY%20CLUSTERED%20%3CBR%20%2F%3E%20(%20%3CBR%20%2F%3E%20%5BLoginName%5D%20ASC%20%3CBR%20%2F%3E%20))%20%3CBR%20%2F%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CH3%20id%3D%22toc-hId-1608299331%22%20id%3D%22toc-hId-612321275%22%3ECapturing%20When%20a%20Password%20is%20Changed%3C%2FH3%3E%3CBR%20%2F%3E%3CP%3ENext%20we%20set%20up%20a%20trigger%20to%20record%20when%20SQL%20Server%20passwords%20are%20changed.%3C%2FP%3E%3CBR%20%2F%3E%3CDIV%20id%3D%22scid%3A9D7513F9-C04C-4721-824A-2B34F0212519%3A13afd7c4-f809-4844-b887-518eb52053d3%22%3E%3CBR%20%2F%3E%3CDIV%3ECREATE%20trigger%20%5Bt_PasswordChangeWhen%5D%20%3CBR%20%2F%3E%20ON%20ALL%20server%20for%20ALTER_LOGIN%2C%20CREATE_LOGIN%20%3CBR%20%2F%3E%20As%20%3CBR%20%2F%3E%20DECLARE%20%40EventData%20XML%20%3CBR%20%2F%3E%20DECLARE%20%40CommandText%20nvarchar(max)%20%3CBR%20%2F%3E%20DECLARE%20%40LoginName%20SysName%20%3CBR%20%2F%3E%20SET%20%40EventData%3DEVENTDATA()%20%3CBR%20%2F%3E%20SET%20%40CommandText%20%3D%20%40EventData.value('(%2FEVENT_INSTANCE%2FTSQLCommand)%5B1%5D'%2C%20'nvarchar(max)')%20%3CBR%20%2F%3E%20SET%20%40LoginName%20%3D%20RTRIM(%20%3CBR%20%2F%3E%20LTRIM(%20%3CBR%20%2F%3E%20Replace(%20%3CBR%20%2F%3E%20Replace(%20%3CBR%20%2F%3E%20SubString(%40CommandText%20%2C%20%3CBR%20%2F%3E%20charindex('LOGIN'%2C%40CommandText)%2B5%2C%20%3CBR%20%2F%3E%20charindex('WITH'%2C%40CommandText)-%20charindex('LOGIN'%2C%40CommandText)-5)%20%3CBR%20%2F%3E%20%2C'%5B'%2C'')%20%3CBR%20%2F%3E%20%2C'%5D'%2C'')%20%3CBR%20%2F%3E%20)%20%3CBR%20%2F%3E%20)%20%3CBR%20%2F%3E%20IF%20CHARINDEX('PASSWORD%3DN''******'''%2C%20%40CommandText%20)%20%26gt%3B%200%20%3CBR%20%2F%3E%20BEGIN%20%3CBR%20%2F%3E%20IF%20NOT%20EXISTS(SELECT%201%20FROM%20%5BSecurity%5D.%5Bdbo%5D.%5BPasswordChange%5D%3C%2FDIV%3E%0A%20%20%20%3CDIV%3EWHERE%20%5BLoginName%5D%3D%40LoginName)%20%3CBR%20%2F%3E%20BEGIN%20%3CBR%20%2F%3E%20INSERT%20INTO%20%5BSecurity%5D.%5Bdbo%5D.%5BPasswordChange%5D%20%3CBR%20%2F%3E%20(%5BLoginName%5D%2C%5BLastPasswordChange%5D)%20%3CBR%20%2F%3E%20VALUES(%40LoginName%2CGetDate()%2C0)%20%3CBR%20%2F%3E%20END%20%3CBR%20%2F%3E%20ELSE%20%3CBR%20%2F%3E%20BEGIN%20%3CBR%20%2F%3E%20UPDATE%20%5BSecurity%5D.%5Bdbo%5D.%5BPasswordChange%5D%20%3CBR%20%2F%3E%20SET%20%5BLastPasswordChange%5D%3DGetDate()%20%3CBR%20%2F%3E%20WHERE%20%5BLoginName%5D%3D%40LoginName%20%3CBR%20%2F%3E%20END%20%3CBR%20%2F%3E%20END%20%3CBR%20%2F%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3CP%3ESome%20manipulation%20of%20the%20TSQLCommand%20is%20needed%20to%20get%20a%20consistent%20login%20name.%3C%2FP%3E%3CBR%20%2F%3E%3CH3%20id%3D%22toc-hId--943857630%22%20id%3D%22toc-hId--1195133188%22%3EDenying%20the%20Login%20of%20an%20Expired%20Password%3C%2FH3%3E%3CBR%20%2F%3E%3CP%3EThe%20following%20trigger%20disables%20an%20account%20with%20an%20expired%20password.%3C%2FP%3E%3CBR%20%2F%3E%3CDIV%20id%3D%22scid%3A9D7513F9-C04C-4721-824A-2B34F0212519%3A8b1899e0-5bbc-4fe0-937a-2e296f1f14c9%22%3E%3CBR%20%2F%3E%3CDIV%3ECREATE%20TRIGGER%20t_Logon_ChangePasswordCheck%20%3CBR%20%2F%3E%20ON%20ALL%20SERVER%20%3CBR%20%2F%3E%20FOR%20LOGON%20%3CBR%20%2F%3E%20AS%20%3CBR%20%2F%3E%20BEGIN%20%3CBR%20%2F%3E%20Declare%20%40DenyAt%20int%20%3CBR%20%2F%3E%20Declare%20%40Data%20Xml%20%3CBR%20%2F%3E%20DECLARE%20%40UserName%20varchar(120)%20%3CBR%20%2F%3E%20Set%20%40Data%3DEVENTDATA()%20%3CBR%20%2F%3E%20SET%20%40DenyAt%3D37%20%3CBR%20%2F%3E%20SET%20%40UserName%3D%40Data.value('(EVENT_INSTANCE%2FLoginName)%5B1%5D'%2C'varchar(120)')%20%3CBR%20%2F%3E%20IF%20NOT%20EXISTS(SELECT%201%20FROM%20%5BSecurity%5D.%5Bdbo%5D.%5BPasswordChange%5D%20WHERE%20%5BLoginName%5D%3D%40UserName)%20%3CBR%20%2F%3E%20INSERT%20INTO%20%5BSecurity%5D.%5Bdbo%5D.%5BPasswordChange%5D%20%3CBR%20%2F%3E%20(%5BLoginName%5D%2C%5BLastPasswordChange%5D%2C%5BWarnings%5D)%20%3CBR%20%2F%3E%20VALUES(%40UserName%2CGetDate()%2C0)%20%3CBR%20%2F%3E%20END%20%3CBR%20%2F%3E%20IF%20EXISTS(SELECT%201%20FROM%20%5BSecurity%5D.%5Bdbo%5D.%5BPasswordChange%5D%20%3CBR%20%2F%3E%20WHERE%20%5BLoginName%5D%3D%40UserName%20%3CBR%20%2F%3E%20AND%20DateAdd(dd%2C%40DenyAt%2C%5BLastPasswordChange%5D)%20%26lt%3B%20GetDate())%20%3CBR%20%2F%3E%20ROLLBACK%20TRAN%20%3CBR%20%2F%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3CP%3EThe%20problem%20is%20that%20it%20is%20not%20possible%20to%20display%20a%20message%20to%20the%20user%20advising%20them%20to%20change%20the%20password%20(RAISERROR%2C%20PRINT%20and%20SELECT%20do%20not%20bubble%20up).%3C%2FP%3E%3CBR%20%2F%3E%3CH2%20id%3D%22toc-hId-995466210%22%20id%3D%22toc-hId--1205636292%22%3ESummary%3C%2FH2%3E%3CBR%20%2F%3E%3CP%3EThere%20is%20little%20native%20support%20in%20SQL%20Server%20to%20support%20PCI%20password%20policies.%20SQL%20Server%20uses%20Window%E2%80%99s%20password%20policy%20and%20this%20is%20where%20you%20should%20implement%20passwords%20policy.%20The%20trigger%20above%20prevents%20any%20SQL%20Logins%20%3CI%3E%20being%20exempt%20%3C%2FI%3E%20from%20the%20Window%E2%80%99s%20password%20policy%20unless%20you%20explicitly%20white-list%20in%20the%20trigger.%3C%2FP%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-383793%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20TECHNET%20on%20Mar%2029%2C%202011%20%5BPrior%20Post%20in%20Series%5D%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%5BNext%20Post%20in%20Series%5DIn%20my%20prior%20posts%20on%20applying%20PCIDSS%20standards%20to%20protect%20your%20company%E2%80%99s%20data%20I%20showed%20now%20a%20DDL%20trigger%20(FOR%20LOGON)%20may%20be%20used%20to%20enhance%20logon%20security.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-383793%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESQLServerISV%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Mar 23 2019 12:00 PM
Updated by: