Enforcing Password Policy with Microsoft Dynamics GP
The ability to enforce password policies was introduced since version 9 of Microsot Dynamics GP (see Why does Microsoft Dynamics GP encrypt passwords? over at Developing for Dynamics GP for more information).
Surprisingly, still many system administrators are not taking advantage of this feature, because they have found it difficult to manage without certain reporting necessary to follow up on Microsoft Dynamics GP logins activity. The typical complaints revolve around the lack of visibility on when a user password will expire or whether the account has been locked or not.
To make administrative tasks even more difficult, Dynamics GP systems administrators must rely on database administrators and Windows Server administrators to resolve any issues arising from a user being locked out the system, typically working their way through a helpdesk on a relatively simple issue.
With that said, I set out to create a query that could provide systems administrators with an insight into Microsoft Dynamics GP logins and their password expiration settings:
LoginPolicies.sql
When the above query is executed in Microsoft SQL Server Management studio, it produces the following results:
Note that this query uses a table variable. If you are looking for a more permanent solution, you can replace the table variable for an actual table.
You may also use Support Debugging Tool's SQL Execute option to run the above query -- I have attached the configuration file for the script to be imported into Support Debugging Tool using the Configuration Import/Export option.
Many system administrators would also want to know when was the last time a user logged into GP, but unfortunately, SQL Server does not keep track of login activity, unless you enable some of the auditing functions. Another alternative is to enable Activity Tracking in GP and track all successful login attempts sorted from the most recent. You may then incorporate this information in the above query for a cohesive result.
Related Resources
Microsoft Dynamics GP Application Level Security Series @ Developing for Dynamics GP
The Scoope on Dynamics GP's Application Password System @ Inside Dynamics GP
Downloads
Support Debugging Tool XML configuration file - LoginPolicies.dbg.xml
Until next post!
MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/
Surprisingly, still many system administrators are not taking advantage of this feature, because they have found it difficult to manage without certain reporting necessary to follow up on Microsoft Dynamics GP logins activity. The typical complaints revolve around the lack of visibility on when a user password will expire or whether the account has been locked or not.
To make administrative tasks even more difficult, Dynamics GP systems administrators must rely on database administrators and Windows Server administrators to resolve any issues arising from a user being locked out the system, typically working their way through a helpdesk on a relatively simple issue.
With that said, I set out to create a query that could provide systems administrators with an insight into Microsoft Dynamics GP logins and their password expiration settings:
LoginPolicies.sql
use master;
go
set nocount on;
go
declare @loginname varchar(200);
declare @logintbl table (
LoginName varchar(20)
,IsLocked char(5)
,DaysUntilExpiration int
);
declare c_logins cursor for
select [name] from sys.syslogins where name in
(select USERID from DYNAMICS..SY01400);
open c_logins;
fetch next from c_logins into @loginname;
while @@FETCH_STATUS = 0
begin
insert @logintbl(LoginName, IsLocked, DaysUntilExpiration)
select
@loginname
,case convert(smallint, LOGINPROPERTY(@loginname, 'IsLocked')) when 0 then 'No' when 1 then 'Yes' end
,convert(int, LOGINPROPERTY(@loginname, 'DaysUntilExpiration'));
fetch next from c_logins into @loginname;
end
close c_logins;
deallocate c_logins;
select * from @logintbl;
go
set nocount off;
go
When the above query is executed in Microsoft SQL Server Management studio, it produces the following results:
LoginName IsLocked DaysUntilExpiration
-------------------- -------- -------------------
sa No NULL
DYNSA No NULL
LESSONUSER1 No 0
LESSONUSER2 No NULL
Note that this query uses a table variable. If you are looking for a more permanent solution, you can replace the table variable for an actual table.
You may also use Support Debugging Tool's SQL Execute option to run the above query -- I have attached the configuration file for the script to be imported into Support Debugging Tool using the Configuration Import/Export option.
Many system administrators would also want to know when was the last time a user logged into GP, but unfortunately, SQL Server does not keep track of login activity, unless you enable some of the auditing functions. Another alternative is to enable Activity Tracking in GP and track all successful login attempts sorted from the most recent. You may then incorporate this information in the above query for a cohesive result.
Related Resources
Microsoft Dynamics GP Application Level Security Series @ Developing for Dynamics GP
The Scoope on Dynamics GP's Application Password System @ Inside Dynamics GP
Downloads
Support Debugging Tool XML configuration file - LoginPolicies.dbg.xml
Until next post!
MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/
Comments
This can't be right.. I had a user who's password expired while they were logged in and applying cash and it created an orphned payment that was only fixed by Check Links. Almost all users were set with expiration, yet they all show "NULL".
Any advice?
When I have seen this error it's typically related to some domain policy around password aging. Check the Minimum Password Age policy.
http://technet.microsoft.com/en-us/library/hh994570(v=ws.10).aspx
MG.-
Mariano Gomez
Do you have a step-by-step guide on how to setup the password complexity of GP 10.0 into alphanumeric.
Thank a lot.
I'm trying to find a way to get a screenshot of what the password policy is for GP.
I need documentation for SOX compliance. We are not using AD credentials for GP login. All of my users have the box checked for "Force Password Policy" but what is the actual policy? Thanks for your help
https://redmondmag.com/articles/2011/08/01/managing-active-directory-password-policies.aspx
This was written by myself but using references originally from this article and is provided for anyone's benefit, this relies on Database Mail being setup on the SQL Server in question and a default global profile being specified in Database Mail. Set a profile manually in the sp_send_dbmail execution where necessary otherwise or use alternate methods to send notification such as service broker, ole automation, clr etc.
--Change to alternate system database where necessary
USE DYNAMICS;
DECLARE @userNotifications TABLE ([Login Name] VARCHAR(15) COLLATE DATABASE_DEFAULT NOT NULL,
[Login Description] VARCHAR(31) COLLATE DATABASE_DEFAULT NOT NULL,
[Is Locked] VARCHAR(3) COLLATE DATABASE_DEFAULT NOT NULL,
[Days Until Expiration] SMALLINT NULL,
[Email Address] VARCHAR(255) COLLATE DATABASE_DEFAULT NOT NULL,
PRIMARY KEY CLUSTERED ([Login Name]));
DECLARE @cLoginName VARCHAR(15),
@cLoginDescription VARCHAR(31),
@cIsLocked VARCHAR(3),
@cDaysUntilExpiration SMALLINT,
@cEmailAddress VARCHAR(255);
DECLARE @CurrentMessage NVARCHAR(1000),
@CurrentSubject VARCHAR(255),
@CurrentError INT;
/* Send e-mail notifications when 0, don't send when 1*/
DECLARE @TestMode BIT = 0;
/* Print all messages when 1, print only errors when 0 */
DECLARE @DebugMode BIT = 0;
INSERT INTO @userNotifications ([Login Name],
[Login Description],
[Is Locked],
[Days Until Expiration],
[Email Address])
SELECT RTRIM(UM.USERID) [Login Name],
RTRIM(UM.USERNAME) [Login Description],
CASE LOGINPROPERTY(UM.USERID, 'IsLocked')
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE NULL END [Is Locked],
CONVERT(SMALLINT, LOGINPROPERTY(UM.USERID, 'DaysUntilExpiration')) [Days Until Expiration],
RTRIM(UM.Internet_Address) [Email Address]
FROM dbo.SY01400 UM
WHERE EXISTS ( SELECT 1
FROM sys.server_principals SP
WHERE SP.name = UM.SQLLoginID
/* Login is not disabled on SQL */
AND SP.is_disabled = 0
/* SQL Login */
AND SP.type = 'S')
AND UserStatus <> 2; /* Inactive in GP User Master */
DECLARE A CURSOR READ_ONLY FORWARD_ONLY LOCAL FOR
SELECT UEN.[Login Name],
UEN.[Login Description],
UEN.[Is Locked],
UEN.[Days Until Expiration],
UEN.[Email Address]
FROM @userNotifications [UEN]
WHERE (/* One week to go until password expiration */
UEN.[Days Until Expiration] <= 7
/* Expired already or today at 0, unknown which*/
AND UEN.[Days Until Expiration] >= 0)
/* Acount is locked out, different e-mail message */
OR [UEN].[Is Locked] = 'Yes'
ORDER BY UEN.[Login Name];
OPEN A;
FETCH NEXT FROM A
INTO @cLoginName,
@cLoginDescription,
@cIsLocked,
@cDaysUntilExpiration,
@cEmailAddress;
WHILE @@FETCH_STATUS <> -1
BEGIN
IF @cEmailAddress LIKE '%_@__%.__%'
BEGIN
SET @CurrentSubject
= CASE @cIsLocked
WHEN 'Yes' THEN 'Dynamics GP - Account Locked Out'
WHEN 'No' THEN
N'Dynamics GP - Password Expiry Warning - '
+ CAST(ISNULL(@cDaysUntilExpiration, -1) AS VARCHAR(5)) + ' Days Remaining'
ELSE NULL END;
SET @CurrentMessage
= CASE @cIsLocked
WHEN 'Yes' THEN
N'<b>Dynamics GP Account Locked</b>
<p>The Dynamics GP user account for ' + @cLoginDescription
+ N' is locked out.</p>
<p>Contact your System Administrator quoting your user id to get a reset performed.
<br />User ID:' + @cLoginName + N'</p>'
WHEN 'No' THEN
N'<h3>Dynamics GP Password Expiring Soon</h3>
<p>The Dynamics GP user account for ' + @cLoginDescription + N' is expiring in '
+ CAST(ISNULL(@cDaysUntilExpiration, -1) AS NVARCHAR(5))
+ N' Days.</p>
<p>Login to Dynamics GP as user id ' + @cLoginName + N' in the next '
+ CAST(ISNULL(@cDaysUntilExpiration, -1) AS NVARCHAR(5))
+ N' days to change your password or your account will be locked.</p>
<p>If your account becomes locked out you will need to contact your System Administrator for assistance.</p>'
ELSE NULL END;
IF @TestMode = 0
BEGIN
EXEC @CurrentError = msdb.dbo.sp_send_dbmail @recipients = @cEmailAddress, -- varchar(max)
@subject = @CurrentSubject, -- nvarchar(255)
@body = @CurrentMessage, -- nvarchar(max)
@body_format = 'HTML';
END;
ELSE
BEGIN
SET @CurrentError = 0;
END;
IF @CurrentError <> 0
BEGIN
PRINT 'Email Failure';
PRINT 'Recipient: ';
PRINT @cEmailAddress;
PRINT 'Subject: ';
PRINT @CurrentSubject;
PRINT 'Message: ';
PRINT @CurrentMessage;
END;
ELSE
BEGIN
IF @DebugMode = 1
BEGIN
PRINT 'Email Success';
PRINT 'Recipient: ';
PRINT @cEmailAddress;
PRINT 'Subject: ';
PRINT @CurrentSubject;
PRINT 'Message: ';
PRINT @CurrentMessage;
END;
END;
END;
ELSE
BEGIN
PRINT 'Invalid Email Address in Dynamics GP User Administration';
PRINT 'User ID: ';
PRINT @cLoginName;
PRINT 'Subject: ';
PRINT @CurrentSubject;
PRINT 'Message: ';
PRINT @CurrentMessage;
END;
FETCH NEXT FROM A
INTO @cLoginName,
@cLoginDescription,
@cIsLocked,
@cDaysUntilExpiration,
@cEmailAddress;
END;
CLOSE A;
DEALLOCATE A;