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/