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/

6 comments:

Ryan Hobbs 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