Thursday, July 15, 2010

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

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/

8 comments:

Unknown said...

All of the user accounts in our instance indicate "NULL" in the DaysUntilExpiration column.

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?

babu said...

Dear Mariano, I am having problem with while users changing the password expired and after they change it its says your password haven't changed please contact your system administrator. This is happening on the new remote desktop with 2008 however we didn't have this problem on 2003 server. So I am login as DYNSA and changing the password of them in order to work. Do you know why this problem happening. Thanks

Mariano Gomez said...

Babu,

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

Marcus Marius said...

Hi Mariano,

Do you have a step-by-step guide on how to setup the password complexity of GP 10.0 into alphanumeric.

Thank a lot.

Anonymous said...

Sorry I'm not a SQL guy. So forgive me if my question is lame.
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

Mariano Gomez said...

Enforce Password Policy is actually a SQL Server capability. However, SQL Server enforces passwords based on your Active Directory settings. This article will give you more info and point you in the right direction:

https://redmondmag.com/articles/2011/08/01/managing-active-directory-password-policies.aspx

Dustin Jones said...

If anyone is interested you could look at using a script such as this to automate the e-mail notifications just schedule as a SQL agent job using a T-SQL job step or use Windows Task Scheduler running SQLCMD to process the script on the server in question on a schedule.

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;

Dustin Jones said...

--continued from comment above


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;