tag:blogger.com,1999:blog-5285970135510371565.post6143664811524062297..comments2024-03-23T07:53:50.523-04:00Comments on The Dynamics GP Blogster: Enforcing Password Policy with Microsoft Dynamics GPMariano Gomezhttp://www.blogger.com/profile/13267738662239812289noreply@blogger.comBlogger8125tag:blogger.com,1999:blog-5285970135510371565.post-60258438046156827202019-07-05T06:00:35.460-04:002019-07-05T06:00:35.460-04:00--continued from comment above
SET @Cur...--continued from comment above <br /><br /><br /> SET @CurrentMessage<br /> = CASE @cIsLocked<br /> WHEN 'Yes' THEN<br /> N'<b>Dynamics GP Account Locked</b><br /> <p>The Dynamics GP user account for ' + @cLoginDescription<br /> + N' is locked out.</p><br /> <p>Contact your System Administrator quoting your user id to get a reset performed.<br /> <br />User ID:' + @cLoginName + N'</p>'<br /> WHEN 'No' THEN<br /> N'<h3>Dynamics GP Password Expiring Soon</h3><br /> <p>The Dynamics GP user account for ' + @cLoginDescription + N' is expiring in '<br /> + CAST(ISNULL(@cDaysUntilExpiration, -1) AS NVARCHAR(5))<br /> + N' Days.</p><br /> <p>Login to Dynamics GP as user id ' + @cLoginName + N' in the next '<br /> + CAST(ISNULL(@cDaysUntilExpiration, -1) AS NVARCHAR(5))<br /> + N' days to change your password or your account will be locked.</p><br /> <p>If your account becomes locked out you will need to contact your System Administrator for assistance.</p>'<br /> ELSE NULL END;<br /><br /> IF @TestMode = 0<br /> BEGIN<br /> EXEC @CurrentError = msdb.dbo.sp_send_dbmail @recipients = @cEmailAddress, -- varchar(max)<br /> @subject = @CurrentSubject, -- nvarchar(255)<br /> @body = @CurrentMessage, -- nvarchar(max)<br /> @body_format = 'HTML';<br /> END;<br /> ELSE<br /> BEGIN<br /> SET @CurrentError = 0;<br /> END;<br /><br /> IF @CurrentError <> 0<br /> BEGIN<br /> PRINT 'Email Failure';<br /> PRINT 'Recipient: ';<br /> PRINT @cEmailAddress;<br /> PRINT 'Subject: ';<br /> PRINT @CurrentSubject;<br /> PRINT 'Message: ';<br /> PRINT @CurrentMessage;<br /> END;<br /> ELSE<br /> BEGIN<br /> IF @DebugMode = 1<br /> BEGIN<br /> PRINT 'Email Success';<br /> PRINT 'Recipient: ';<br /> PRINT @cEmailAddress;<br /> PRINT 'Subject: ';<br /> PRINT @CurrentSubject;<br /> PRINT 'Message: ';<br /> PRINT @CurrentMessage;<br /> END;<br /> END;<br /><br /> END;<br /> ELSE<br /> BEGIN<br /> PRINT 'Invalid Email Address in Dynamics GP User Administration';<br /> PRINT 'User ID: ';<br /> PRINT @cLoginName;<br /> PRINT 'Subject: ';<br /> PRINT @CurrentSubject;<br /> PRINT 'Message: ';<br /> PRINT @CurrentMessage;<br /> END;<br /><br /> FETCH NEXT FROM A<br /> INTO @cLoginName,<br /> @cLoginDescription,<br /> @cIsLocked,<br /> @cDaysUntilExpiration,<br /> @cEmailAddress;<br /><br />END;<br /><br />CLOSE A;<br /><br />DEALLOCATE A;<br />Dustin Jonesnoreply@blogger.comtag:blogger.com,1999:blog-5285970135510371565.post-69312828211727879212019-07-05T05:49:41.387-04:002019-07-05T05:49:41.387-04:00If anyone is interested you could look at using a ...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.<br /><br />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.<br /><br />--Change to alternate system database where necessary<br />USE DYNAMICS;<br /><br />DECLARE @userNotifications TABLE ([Login Name] VARCHAR(15) COLLATE DATABASE_DEFAULT NOT NULL,<br /> [Login Description] VARCHAR(31) COLLATE DATABASE_DEFAULT NOT NULL,<br /> [Is Locked] VARCHAR(3) COLLATE DATABASE_DEFAULT NOT NULL,<br /> [Days Until Expiration] SMALLINT NULL,<br /> [Email Address] VARCHAR(255) COLLATE DATABASE_DEFAULT NOT NULL,<br /> PRIMARY KEY CLUSTERED ([Login Name]));<br /><br />DECLARE @cLoginName VARCHAR(15),<br /> @cLoginDescription VARCHAR(31),<br /> @cIsLocked VARCHAR(3),<br /> @cDaysUntilExpiration SMALLINT,<br /> @cEmailAddress VARCHAR(255);<br /><br />DECLARE @CurrentMessage NVARCHAR(1000),<br /> @CurrentSubject VARCHAR(255),<br /> @CurrentError INT;<br /><br />/* Send e-mail notifications when 0, don't send when 1*/<br />DECLARE @TestMode BIT = 0;<br /><br />/* Print all messages when 1, print only errors when 0 */<br />DECLARE @DebugMode BIT = 0;<br /><br />INSERT INTO @userNotifications ([Login Name],<br /> [Login Description],<br /> [Is Locked],<br /> [Days Until Expiration],<br /> [Email Address])<br />SELECT RTRIM(UM.USERID) [Login Name],<br /> RTRIM(UM.USERNAME) [Login Description],<br /> CASE LOGINPROPERTY(UM.USERID, 'IsLocked')<br /> WHEN 0 THEN 'No'<br /> WHEN 1 THEN 'Yes'<br /> ELSE NULL END [Is Locked],<br /> CONVERT(SMALLINT, LOGINPROPERTY(UM.USERID, 'DaysUntilExpiration')) [Days Until Expiration],<br /> RTRIM(UM.Internet_Address) [Email Address]<br /> FROM dbo.SY01400 UM<br /> WHERE EXISTS ( SELECT 1<br /> FROM sys.server_principals SP<br /> WHERE SP.name = UM.SQLLoginID<br /> /* Login is not disabled on SQL */<br /> AND SP.is_disabled = 0<br /> /* SQL Login */<br /> AND SP.type = 'S')<br /> AND UserStatus <> 2; /* Inactive in GP User Master */<br /><br />DECLARE A CURSOR READ_ONLY FORWARD_ONLY LOCAL FOR<br />SELECT UEN.[Login Name],<br /> UEN.[Login Description],<br /> UEN.[Is Locked],<br /> UEN.[Days Until Expiration],<br /> UEN.[Email Address]<br /> FROM @userNotifications [UEN]<br /> WHERE (/* One week to go until password expiration */<br /> UEN.[Days Until Expiration] <= 7<br /> /* Expired already or today at 0, unknown which*/<br /> AND UEN.[Days Until Expiration] >= 0)<br /><br /> /* Acount is locked out, different e-mail message */<br /> OR [UEN].[Is Locked] = 'Yes'<br /> ORDER BY UEN.[Login Name];<br /><br />OPEN A;<br /><br />FETCH NEXT FROM A<br /> INTO @cLoginName,<br /> @cLoginDescription,<br /> @cIsLocked,<br /> @cDaysUntilExpiration,<br /> @cEmailAddress;<br /><br />WHILE @@FETCH_STATUS <> -1<br />BEGIN<br /><br /> IF @cEmailAddress LIKE '%_@__%.__%'<br /> BEGIN<br /> SET @CurrentSubject<br /> = CASE @cIsLocked<br /> WHEN 'Yes' THEN 'Dynamics GP - Account Locked Out'<br /> WHEN 'No' THEN<br /> N'Dynamics GP - Password Expiry Warning - '<br /> + CAST(ISNULL(@cDaysUntilExpiration, -1) AS VARCHAR(5)) + ' Days Remaining'<br /> ELSE NULL END;<br />Dustin Jonesnoreply@blogger.comtag:blogger.com,1999:blog-5285970135510371565.post-16398351716732059512015-06-03T23:15:35.058-04:002015-06-03T23:15:35.058-04:00Enforce Password Policy is actually a SQL Server c...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: <br /><br />https://redmondmag.com/articles/2011/08/01/managing-active-directory-password-policies.aspxMariano Gomezhttps://www.blogger.com/profile/13267738662239812289noreply@blogger.comtag:blogger.com,1999:blog-5285970135510371565.post-70322901869263532252015-06-03T11:44:13.762-04:002015-06-03T11:44:13.762-04:00Sorry I'm not a SQL guy. So forgive me if my q...Sorry I'm not a SQL guy. So forgive me if my question is lame.<br />I'm trying to find a way to get a screenshot of what the password policy is for GP.<br />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 helpAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-5285970135510371565.post-37841118541630202702014-03-24T06:14:11.123-04:002014-03-24T06:14:11.123-04:00Hi Mariano,
Do you have a step-by-step guide on...Hi Mariano, <br /><br />Do you have a step-by-step guide on how to setup the password complexity of GP 10.0 into alphanumeric.<br /><br />Thank a lot.Marcus Mariushttps://www.blogger.com/profile/01044048270740738657noreply@blogger.comtag:blogger.com,1999:blog-5285970135510371565.post-22848892052575763082013-06-11T08:27:52.132-04:002013-06-11T08:27:52.132-04:00Babu,
When I have seen this error it's typica...Babu,<br /><br />When I have seen this error it's typically related to some domain policy around password aging. Check the Minimum Password Age policy.<br /><br />http://technet.microsoft.com/en-us/library/hh994570(v=ws.10).aspx<br /><br />MG.-<br />Mariano GomezMariano Gomezhttps://www.blogger.com/profile/13267738662239812289noreply@blogger.comtag:blogger.com,1999:blog-5285970135510371565.post-30662788494328446982013-06-10T10:27:02.437-04:002013-06-10T10:27:02.437-04:00Dear Mariano, I am having problem with while users...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. Thanksbabuhttps://www.blogger.com/profile/08156114492343773471noreply@blogger.comtag:blogger.com,1999:blog-5285970135510371565.post-29914585867011285092012-07-12T02:42:41.250-04:002012-07-12T02:42:41.250-04:00All of the user accounts in our instance indicate ...All of the user accounts in our instance indicate "NULL" in the DaysUntilExpiration column. <br /><br />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".<br /><br />Any advice?Anonymoushttps://www.blogger.com/profile/00698696726448284105noreply@blogger.com