Web Client Wednesday - Microsoft Dynamics GP Reset User Password not working

Continuing with the Web Client Wednesday series, today I wanted to highlight the fact that the web client is still a work in progress. While a lot of ground has been covered over the past 3 years, the product continues to evolve, and of course, in the meantime there will be little issues here and there that may affect the way you work.

Previous articles in the series:

Web Client Wednesday - Browser Support

Web Client Wednesday - User Interface Differences

---

Just recently I ran into a Microsoft Dynamics GP 2013 web client deployment case where the advanced SQL Server options are enabled to enforce password expiration. These properties are a part of the Enforce Password Policy options on the User Setup window.

User Setup

When a password expires and a user attempts to log into the desktop client, they are immediately prompted to change their password and the Change User Password dialog window displays to allow the user to enter the old password, and enter and confirm the new one.

Change User Password window

The behavior is different under the web client, however. Whenever a password expires, the Change User Password dialog displays as expected, but in this case, the Old Password field is disabled and the user would only be prompted to enter the new password and confirm.

Change User Password dialog on Web Client 

Microsoft Dynamics GP uses the old password to establish a connection to the database - at this point, the user has not validated their SQL credentials - in order to set the new password. If the old password is not supplied the connection cannot be established, which in turn fails the attempt to change the new password.

This issue has been identified as a bug by Microsoft Support in Fargo and is currently in the queue for resolution. If you are affected by this problem, you are encouraged to open a support case and add yourself to the list for a fix.

---

For more information on how the password system works in Microsoft Dynamics GP, please take a look at the following articles:

Developing for Microsoft Dynamics GP - Why does Microsoft Dynamics GP encrypt passwords?
Developing for Microsoft Dynamics GP - Microsoft Dynamics GP Application Password System
Microsoft Support - FAQ about the Advanced SQL Server options in the User Setup Window

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More

Microsoft SQL Server security roles and Microsoft Dynamics GP SSRS reports

It's not uncommon to find system administrators and consultants alike struggling to deploy Microsoft Dynamics GP SRS reports in an environment, while trying to keep these reports secured. I find that the main issue revolves around understanding the different moving parts: domain accounts, GP user logins, the SQL Server database roles created for reporting, the reports web site security, the reports folder security and the list goes on.

So here is a list of resources that I believe will help with this:

The best place to start is the SQL Server Reporting Services Guide, which has all of the information for installing, deploying and setting up security for SRS reports to use with Dynamics GP. This guide is available for download from the Microsoft Download Center.

Microsoft Dynamics GP 2013 Guide: SQL Server Reporting Services

In order for users to access SRS reports (default) for Dynamics GP, they need to have the following permissions:

1. Access to the Reporting Services web site (native mode) or report library (SharePoint Integrated). I have found the following resources to be extremely useful when configuring Reporting Services security in native mode:

Granting Permissions on a Native Mode Report Server

If you are dealing with Reporting Services in SharePoint Integrated mode, the following is a good resource:

Security Overview for Reporting Services in SharePoint Integrated Mode


2. Permissions to the SRS report files themselves. The following TechNet video shows just that:


3. Database security roles (RPT_ ) under the DYNAMICS and/or company databases in order to pull data into the SRS reports. You will want to take a look at the following KB article 954242 - Frequently asked questions about the integration of SQL Server Reporting Services (SSRS) with Microsoft Dynamics GP 10.0 and Microsoft Dynamics GP 2010, which can be found at:

http://support.microsoft.com/kb/954242

I hope you find these resources useful.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More

Microsoft Dynamics GP displaying overlapping text on home page

Scouting the Microsoft Dynamics GP Partner Online Technical Forum, I came across this interesting case, in which a partner reported seeing overlapping options text on the home page and missing picture bars. The problem, as reported by the partner, was only happening with a user on a specific workstation, but not with his domain account and Microsoft Dynamics GP user.

Home page with overlapping text and missing pictures

Initially, the partner attempted to reduce Internet Explorer's Internet zone's security to the bare minimum, which would allow all home page scripts to be executed, with no results. Still puzzled, they began running a ProcMon trace on Microsoft Dynamics GP to look for ACCESS DENIED events, as instructed by Microsoft's Partner Online Technical Community support engineer, Jason Larson.

The partner recorded all trace results, but SUCCESS, and found the following being displayed several times in the log:

FILE LOCKED WITH ONLY READERS
FAST IO DISALLOWED
NAME NOT FOUND
REPARSE
NO MORE ENTRIES
NAME COLLISION

They also reported one message that appeared several times, NAME NOT FOUND pointing to the registry entry:

HKU\S-1-5-21-2191439342-1828296097-4069510072-1121\Software\Microsoft\Internet Explorer\Security\DisableSecuritySettingsCheck

The partner also noticed that if they assigned the affected users to the Domain Administrators group via Active Directory, they would no longer experience the issue. The other groups they are in have Full Access to the Microsoft Dynamics GP program files folder.

The partner also found only one ACCESS DENIED entry in the trace pointing to the following key:

HKEY_CLASSES_ROOT\Wow6432Node\MIME\Database\Content Type\application\futuresplash

After doing some research, the partner assigned Read permissions on the above key and the problem went away.

You can find more information on and download Process Monitor over at the Windows Sysinternals team blog at:

http://technet.microsoft.com/en-us/sysinternals/bb896645

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More

SQL: Assigning Microsoft Dynamics GP Users to SSRS Database Roles

As I begin to wrap up a Microsoft Dynamics GP 2010 R2 production upgrade from Microsoft Dynamics GP 9.0, I ran into a small issue at my client. After deploying the new SSRS reports, and as users were getting ready to try them out, we realized that some 15 logins needed to be assigned to a number of the 24 default database security roles created for the SSRS reports.

User Mappings (some information blurred to protect the client's identity)

This would be a bit cumbersome giving the share number of clicks required to accomplish this feat. In addition, we had just setup Microsoft Dynamics GP security, and given that the SSRS database roles were similar to those in GP, something needed to be done to automate the assignment of these roles based on Microsoft Dynamics GP security roles.

As a result, I created the following script:

-- Created by Mariano Gomez, MVP

--  This code is licensed under the Creative Commons 

--  Attribution-NonCommercial-ShareAlike 2.5 Generic license.

use DYNAMICS;

go



DECLARE @userid varchar(50), @companyid varchar(5), @securityroleid varchar(200), @ssrsRole varchar(200);

DECLARE @sqlStmt varchar(255);



DECLARE c_reportsecurity CURSOR FOR 

	SELECT a.USERID, b.INTERID, a.SECURITYROLEID FROM SY10500 a

		LEFT OUTER JOIN SY01500 b ON (A.CMPANYID = b.CMPANYID)

	WHERE a.USERID not in ('sa', 'DYNSA', 'LESSONUSER1', 'LESSONUSER2') and a.SECURITYROLEID NOT LIKE ('MBS%')

	ORDER BY a.USERID;



OPEN c_reportsecurity;

FETCH NEXT FROM c_reportsecurity INTO @userid, @companyid, @securityroleid;



WHILE @@FETCH_STATUS = 0

BEGIN

	SELECT @ssrsrole = 

		CASE 

			WHEN @securityroleid = 'ACCOUNTING MANAGER*      ' THEN 'rpt_accounting manager'

			WHEN @securityroleid = 'AP CLERK*                ' THEN 'rpt_accounts payable coordinator'

			WHEN @securityroleid = 'AR CLERK*                ' THEN 'rpt_accounts receivable coordinator'

			WHEN @securityroleid = 'BOOKKEEPER*              ' THEN 'rpt_bookkeeper'

			WHEN @securityroleid = 'CA AGENT*                ' THEN ''

			WHEN @securityroleid = 'CA MANAGER*              ' THEN ''

			WHEN @securityroleid = 'CA STAKEHOLDER*          ' THEN ''

			WHEN @securityroleid = 'CERTIFIED ACCOUNTANT*    ' THEN 'rpt_certified accountant'

			WHEN @securityroleid = 'CL AGENT*                ' THEN ''

			WHEN @securityroleid = 'CL DISPATCHER*           ' THEN 'rpt_dispatcher'

			WHEN @securityroleid = 'CL MANAGER*              ' THEN ''

			WHEN @securityroleid = 'CL STAKEHOLDER*          ' THEN ''

			WHEN @securityroleid = 'CUSTOMER SERVICE REP*    ' THEN 'rpt_customer service rep'

			WHEN @securityroleid = 'DP MANAGER*              ' THEN ''

			WHEN @securityroleid = 'DP STAKEHOLDER*          ' THEN ''

			WHEN @securityroleid = 'DP TECHNICIAN*           ' THEN ''

			WHEN @securityroleid = 'FA MANAGER*              ' THEN 'rpt_accounting manager'

			WHEN @securityroleid = 'FA STAKEHOLDER*          ' THEN 'rpt_certified accountant'

			WHEN @securityroleid = 'IT OPERATIONS MANAGER*   ' THEN ''

			WHEN @securityroleid = 'MBS DEBUGGER ADMIN       ' THEN ''

			WHEN @securityroleid = 'MBS DEBUGGER USER        ' THEN ''

			WHEN @securityroleid = 'OPERATIONS MANAGER*      ' THEN 'rpt_operations manager'

			WHEN @securityroleid = 'ORDER PROCESSOR*         ' THEN 'rpt_order processor'

			WHEN @securityroleid = 'PAYROLL CLERK*           ' THEN 'rpt_payroll'

			WHEN @securityroleid = 'PM AGENT*                ' THEN ''

			WHEN @securityroleid = 'PM MANAGER*              ' THEN ''

			WHEN @securityroleid = 'PM STAKEHOLDER*          ' THEN ''

			WHEN @securityroleid = 'POWERUSER                ' THEN 'rpt_power user'

			WHEN @securityroleid = 'PURCHASING AGENT*        ' THEN 'rpt_purchasing agent'

			WHEN @securityroleid = 'PURCHASING MANAGER*      ' THEN 'rpt_purchasing manager'

			WHEN @securityroleid = 'RT AGENT*                ' THEN ''

			WHEN @securityroleid = 'RT MANAGER*              ' THEN ''

			WHEN @securityroleid = 'RT STAKEHOLDER*          ' THEN ''

			WHEN @securityroleid = 'SHIPPING AND RECEIVING*  ' THEN 'rpt_shipping and receiving'

			WHEN @securityroleid = 'WAREHOUSE MANAGER*       ' THEN 'rpt_warehouse manager'

			WHEN @securityroleid = 'WENNSOFT SMS CONTRACTS*  ' THEN ''

			WHEN @securityroleid = 'WENNSOFT SMS DISPATCHER* ' THEN ''

			WHEN @securityroleid = 'WENNSOFT SMS POWER USER* ' THEN ''

			WHEN @securityroleid = 'WENNSOFT SMS SETUP*      ' THEN ''

			WHEN @securityroleid = 'WSJC ACCOUNTANT*         ' THEN ''

			WHEN @securityroleid = 'WSJC ACCOUNTING MANAGER* ' THEN ''

			WHEN @securityroleid = 'WSJC ADMIN*              ' THEN ''

			WHEN @securityroleid = 'WSJC BILLING CLERK*      ' THEN ''

			WHEN @securityroleid = 'WSJC POWERUSER*          ' THEN ''

			WHEN @securityroleid = 'WSJC PROJECT MANAGER*    ' THEN ''

			WHEN @securityroleid = 'WSTT PAYROLL CLERK*      ' THEN ''

			WHEN @securityroleid = 'WSTT POWERUSER*          ' THEN ''

		END

	

	IF (@ssrsRole <> '') 

	BEGIN

		SET @sqlStmt = 'USE ' + rtrim(@companyid) + '; EXEC sp_addrolemember ' + QUOTENAME(@ssrsRole, '''') + ',' + QUOTENAME(rtrim(@userid), '''');

		EXEC(@sqlStmt);

	END

	FETCH NEXT FROM c_reportsecurity INTO @userid, @companyid, @securityroleid;

END



CLOSE c_reportsecurity;

DEALLOCATE c_reportsecurity;

The script looks at the Security Assignment User Role table (SY10500) and retrieves the physical company database from the Company Master table (SY01500), then assign an SSRS database security role to each of the Microsoft Dynamics GP default roles. If a role does not exist, you can choose to leave the assignment blank.

The script then proceeds to evaluate the database security role obtained, then creates a SQL string that can be executed. The SQL string uses the sp_addrolemember system stored procedure to add the corresponding SQL login to the role. A cursor is used to loop through each user, company, and security role combination to obtain and assign the proper SSRS database role.

You can choose to add custom security roles or roles for third party applications that deploy their own SSRS reports to the above script.

This definitely helped saving some time... phew!

Until next post!

MG.-

Mariano Gomez, MVP

IntellPartners, LLC

http://www.IntellPartners.com/

Read More

Resetting Security Roles and Task with the Support Debugging Tool

This is my first after Convergence post and I have to say, I came away with enough questions from attendees to fill the blog for the remainder of the year. One such questions came from someone attending the interactive discussion session on security, IDGP04-R2 Tips and Tricks for Maintaining Security in Microsoft Dynamics GP - see Microsoft Dynamics Convergence Atlanta 2011: Day 4 Morning Closing Session (Cont.) for more on what transpired that day.

The individual in question wanted to know if there is a way or method to "reset" all Microsoft Dynamics GP security roles and tasks to the out-of-the-box defaults once these had been changed. After thinking about it for a bit, there is effectively a way using the Support Debugging Tool.

Step 1 - Export the security tables from a brand new installation of Microsoft Dynamics GP
In a brand new installation of Microsoft Dynamics GP, install and use the Support Debugging Tool's XML Table Export feature to create a SECURITY profile ID, including all the security tables as shown below.


XML Table Export - SECURITY profile ID

 Step 2 - Optional: Clear the destination system's security tables

You can then create a script using the Support Debugging Tool's SQL Execute that will clear all the security tables in your destination system, as shown below.


SQL Execute - Clears security tables

By saving the script ID we can reuse the code as many times as needed or for future resets. Note also, that I have used the Dexterity table notation in the DELETE statement as I already had the names in the XML Table Export window.

Step 3 - Import the records exported in step 1 into the destination system

Finally, we can use Support Debugging Tool's XML Table Import feature to reset security and reload the defaults from the XML file exported in step 1. Note that I have marked the Overwrite Table Contents option, which would render step 2 not necessary. However, having step 2 reduces your chances of forgetting to select this checkmark.

XML Table Import

I have attached the SECURITY profile ID and the RESET_SECURITY script ID at the bottom of this post. I am also including the exported security tables XML files with the default security tasks, roles and assignments. Remember that you can choose a reduce set of data to export or remove tables that you may not want to import. It all depends what level of granularity you are trying to achieve.

Downloads

DebuggerInfo.zip - contains SECURITY profile ID, RESET_SECURITY script ID, and default security roles, tasks and operations for Microsoft Dynamics GP 2010.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More

Granting Microsoft Dynamics GP user minimal access at the database level to setup additional users

After the long title of this post, you probably already have the idea of what the article will be about. However, back in April of 2009, I wrote about the POWERUSER role and the Microsoft SQL Server sysadmin server role - see Microsoft Dynamics GP 10 POWERUSER role vs Microsoft SQL Server sysadmin role - and explained the key differences between the two. Among other things, I discussed how a GP user login that's assigned to the sysadmin server role on Microsoft SQL Server becomes able to setup new users in GP.

However, those of you who are database administrators have been quite reluctant to add logins to the sysadmin group, and quite understandably so. After all, logins added to the sysadmin server role can do anything on the database server, and we sure don't want that to happen either.

In response to this, and to the many requests lately on the forums, my friend Robert Cavill, with Emeco Group in Australia, has submitted the following script, which gives a specific user ID in Microsoft Dynamics GP, minimal but sufficient permissions at the Microsoft SQL Server level to create new users. In addition, this script allows Robert's first level support staff with access to Microsoft Dynamics GP, the ability to reset passwords for their user base.

GrantUserRights.sql

--Created by Robert Cavill
--This code is licensed under the Creative Commons 
-- Attribution-NonCommercial-ShareAlike 2.5 Generic license.

DECLARE @sUSERID NVARCHAR(15)
SET @sUSERID = 'LESSONUSER1'

SET NOCOUNT ON;
SELECT 'EXEC master..sp_addsrvrolemember @loginame = ''' + @sUSERID + ''', @rolename = N''securityadmin'';'

SELECT
'USE [DYNAMICS];
GO
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = ' + QUOTENAME(@sUSERID, CHAR(39)) + ') 
  CREATE USER '+ QUOTENAME(@sUSERID) + ' FOR LOGIN ' + QUOTENAME( @sUSERID ) + '; 
EXEC sp_addrolemember N''db_accessadmin'', ' + QUOTENAME(@sUSERID, CHAR(39)) + ';
EXEC sp_addrolemember N''db_securityadmin'',' + QUOTENAME(@sUSERID, CHAR(39)) + ';
EXEC sp_addrolemember N''DYNGRP'', ' + QUOTENAME(@sUSERID, CHAR(39)) + ';
GO'

SELECT 
'USE [' + RTRIM(INTERID ) + '];
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = ' + QUOTENAME(@sUSERID, CHAR(39)) + ') 
  CREATE USER '+ QUOTENAME(@sUSERID) + ' FOR LOGIN ' + QUOTENAME( @sUSERID ) + '; 
EXEC sp_addrolemember N''db_accessadmin'', ' + QUOTENAME(@sUSERID, CHAR(39)) + ';
EXEC sp_addrolemember N''db_securityadmin'',' + QUOTENAME(@sUSERID, CHAR(39)) + ';
EXEC sp_addrolemember N''DYNGRP'', ' + QUOTENAME(@sUSERID, CHAR(39)) + ';
GO'
FROM DYNAMICS..SY01500

When this script is executed against the DYNAMICS database for a specified Microsoft Dynamics GP user (@sUSERID variable), the result is another script granting the correct access to all the Microsoft Dynamics GP company databases.

Result

EXEC master..sp_addsrvrolemember @loginame = 'LESSONUSER1', @rolename = N'securityadmin';

USE [DYNAMICS];
GO
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'LESSONUSER1') 
  CREATE USER [LESSONUSER1] FOR LOGIN [LESSONUSER1]; 
EXEC sp_addrolemember N'db_accessadmin', 'LESSONUSER1';
EXEC sp_addrolemember N'db_securityadmin

USE [TWO];
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'LESSONUSER1') 
  CREATE USER [LESSONUSER1] FOR LOGIN [LESSONUSER1]; 
EXEC sp_addrolemember N'db_accessadmin', 'LESSONUSER1';
EXEC sp_addrolemember N'db_securityadmin','LESSON

Upon running the result script, the new database permissions will enable the Save button on the User Maintenance window, and allow users to be assigned to companies in the User Access window.

Here are a few additional tips:

1. With this approach, the Microsoft Dynamics GP user is not a member of the sysadmin fixed server role.
2. The user ID must already exist in Microsoft Dynamics GP with access to at least one company so they can log on.
3. If, after executing this script, you attempt to delete the user ID from GP, it will fail.

In the following post, I will publish the script that will reverse the outcome to allow deletion of the user ID from Microsoft Dynamics GP.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Read More

Is the new "Remember User and Password" feature in Microsoft Dynamics GP 2010 safe?

Increasingly, a number of you have been asking about the security of the new "Remember User and Password" login feature in Microsoft Dynamics GP 2010. I will start with the short answer to this question...Yes! Very safe indeed.

Back in January, I posted an article about the new feature - see Microsoft Dynamics GP 2010 - Security Enhancements. In this article, I described the new automatic login capabilities in Microsft Dynamics GP 2010. As you may know by now, these settings are stored in the Windows Registry under the HKEY_CURRENT_USER registry hive as shown below:


HKEY_CURRENT_USER/Software/Microsoft/Dynamics GP

The ProtectedValueA and ProtectedValueB keys correspond to the user Id and password values respectively and are stored as REG_BINARY values. You may be thinking, it would be simple enough to get a cracker from any website that decodes the value into a readable string. Even if you did, the development team has taken extra precautions to introduce an entropic encryption key to prevent user Ids and passwords from being decoded with external applications.

The entropic encryption key is passed to Dexterity's new Registry_SetProtectedKeyString() function to ensure maximum encryption and no, you cannot see it with Script Profiler. Now, don't ask me about the algorithm used by the Dexterity function or the entropic encryption key, because I simply don't know the former or don't have the latter, just know your user Id and passwords are completely safe!

Related Articles

Why does Microsoft Dynamics GP encrypt passwords @ Developing for Dynamics GP
Microsoft Dynamics GP Application Password System @ Developing for Dynamics GP
The Scoop On Dynamics GP’s Application Password System @ Inside Microsoft Dynamics GP

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Read More

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/

Read More

DYNSA

There are a number of good blog posts in the community explaining the differences between sa, Microsoft SQL Server's defacto administrative user; and DYNSA, Microsoft Dynamics GP defacto administrative user. In reading a number of these articles I realized one thing: they address little about the DYNSA user.

So what's DYNSA?

You only have to read back a few lines to obtain this answer, however, I want take another direction. Let's start by saying that in Microsoft SQL Server, every database is created with a dbo user. Take for example this blank database created using SQL Server Management Studio:



The dbo is a user that has implied permissions to perform all activities in the database. Any member of the sysadmin fixed server role (like sa for example) who uses a database is mapped to the special user inside each database called dbo. Also, any object created by any member of the sysadmin fixed server role belongs to dbo automatically.

For example, if user DYNSA is a member of the sysadmin fixed server role and creates a table RM00101, RM00101 belongs to dbo and is qualified as dbo.RM00101, not as DYNSA.RM00101. Conversely, if DYNSA is not a member of the sysadmin fixed server role but is a member only of the db_owner fixed database role and creates a table RM00101, RM00101 belongs to DYNSA and is qualified as DYNSA.RM00101. The table belongs to DYNSA because he did not qualify the table as dbo.RM00101... and hear is the catch! Because DYNSA is not created as a member of the sysadmin fixed role (otherwise we would have a lot of angry database administrators), it must be associated to fixed database role db_owner, hence all object creation operations performed from Dynamics Utilities must qualify the object creation preceeded by "dbo.".

The dbo user cannot be deleted and is always present in every database.

Only objects created by members of the sysadmin fixed server role (or by the dbo user) belong to dbo. Objects created by any other user who is not also a member of the sysadmin fixed server role (including members of the db_owner fixed database role):

•Belong to the user creating the object, not dbo.

•Are qualified with the name of the user who created the object.

This explains two things:

1) Only sa can perform the first time installation of Microsoft Dynamics GP, since this is when all databases, database objects, and the DYNSA user are created. During installation, DYNSA is made a member of the db_owner fixed database role.

2) In lieu of the sa user, Microsoft Dynamics GP system administrators can use DYNSA to perform any upgrade or maintenance activities. Since DYNSA is the defacto database owner, then it can perform any activities related to that database. This is the reason why you should ensure that DYNSA remains the database owner prior to initiating any upgrade activity. The following script should help with this:


1. Run the following making sure that DYNSA is the database owner:

sp_helpdb DYNAMICS;
go
sp_helpdb %COMPDB%;
go

2. If the above returns anything different than DYNSA, use the following script to set the database owner to DYNSA:

use DYNAMICS;
go
sp_changedbowner 'DYNSA';
go
use %COMPDB%;
go
sp_chagedbowner 'DYNSA';

In the above scripts, replace %COMPDB% for your company database name on SQL Server.

Related Articles:

Microsoft Dynamics GP 10 POWERUSER role vs Microsoft SQL Server sysadmin role

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Read More

Urban Legends - "I cracked Dynamics GP encryption algorithm!"

It is just about right that I start out this article with the definition of Urban Legend. According to Wikipedia, "An urban legend, urban myth, urban tale, or a contemporary legend, is a form of modern folklore consisting of apocryphal stories believed by their tellers to be true. As with all folklore and mythology, the designation suggests nothing about the story's factuality or falsehood, but merely that it is in non-institutional circulation, exhibits variation over time, and carries some significance that motivates the community in preserving and propagating it." The definition seems just about right for what you will read next.

If you are the type who believes everything you read without questioning it, then for your own sake, please stay off the Internet! Rumors -- as they can only be referred to -- began circulating today on claims of someone cracking the Microsoft Dynamics GP user password encryption algorithm -- if you are interested in the original article, click here.

Instead of ranting about the misleading content of the article, I will provide my unbiased, fact-based knowledge of the user password encryption algorithm and Microsoft Dynamics GP security.

Fact 1 - Microsoft Dynamics GP user password encryption algorithm takes into account things like the actual database server's host name as part of the encrypted password. Hence the reason why passwords need to be reset when the application databases are transferred from one server environment to another without the use of the famous Capture_Logins.sql script.

Fact 2 - Microsoft Dynamics GP user passwords are encrypted on SQL Server using a proprietary encryption code. Hence, the encryption algorithm is not commercially available to any other software vendor or ratherly available on the Internet.

Fact 3 - Having the Microsoft Dynamics GP source code DOES NOT give you access to the password encryption or decryption algorithms.

Fact 4 - You cannot access Microsoft Dynamics GP system or company databases via ODBC with the SQL Server logins corresponding to the Microsoft Dynamics GP users. As a result of Fact 2, a user attempting to establish a connection to SQL Server would be required to authenticate with the encrypted password. The clear-text version of their password, used to authenticate to GP simply DOES NOT WORK. The only way to achieve a connection to GP from an external application is by obtaining a copy of the GPConnectNet.dll .NET assembly or the GPConnect.dll COM component by opening a support case with the Tools team.

Fact 5 - While not impossible, it is virtually impossible to decrypt a Microsoft Dynamics GP user password without having access to the algorithm itself... good luck getting a copy of it anywhere!

Fact 6 - Having access to the Microsoft Dynamics GP system password IS NOT a guarantee of access to the system setup - you should -- by now -- be taking advantage of the new Role Based pessimistic security model. The fact is, the system password had more relevance in the days of palette menus when options could not be hidden from a user based on their security settings.

Fact 7 - You do not need Microsoft SQL Server 'sa' to perform all administrative tasks in GP. In fact, any company who provides their Microsoft Dynamics GP application administrators with the 'sa' password should consider firing their database administrators. 'sa' is only required to setup new companies and occassionally -- read, very occassionally -- run third party setup code that has been hardcoded to setup tables and stored procedures with the 'sa' user... oh, yes! You know who you are out there.

Other Factual Resources

Microsoft Dynamics GP POWERUSER role vs Microsoft SQL Server sysadmin role @ this blog
Why does Microsoft Dynamics GP encrypt passwords? @ Developing for Dynamics GP
KB article 878449 - How to tranfer an existing Microsoft Dynamics GP installation to a new server

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Read More

SQL - Copying Microsoft Dynamics GP Navigation Bar shortcuts from one user to another

I have seen this question come up multitude of times on public newsgroups and forums. In fairly large Dynamics GP installations, Navigation Bar shortcuts tend to be deployed in a standard fashion for groups of users at a time, this is, to ensure all users have the same set of options to perform their functions, or access to company documents, etc. With this in mind, it is very common to setup one user's shortcuts and wanting to replicate these across other Dynamics GP user accounts. Unfortunately, there isn't a facility in GP to allow this.

The following T-SQL script allows you to copy the Navigation bar shortcuts from one user to another:

CopyNavShortcuts.sql

1 : use DYNAMICS;
2 : GO
3 :
4 : declare @sourceUser char(20); set @sourceUser = 'LESSONUSER2';
5 : declare @destntUser char(20); set @destntUser = 'LESSONUSER1';
6 :
7 : if exists(select * from SY01990 where ScbOwnerID = @destntUser)
8 : delete from SY01990 where ScbOwnerID = @destntUser;
9 :
10: insert into SY01990 (
11:   ScbGroupType,
12:   ScbOwnerID,
13:   ScbNodeID,
14:   ScbParentNodeID,
15:   ScbShortcutType,
16:   ScbSubType,
17:   ScbDisplayName,
18:   ScbShortcutKey,
19:   ScbTargetStringOne,
20:   ScbTargetStringTwo,
21:   ScbTargetStringThree,
22:   ScbTargetLongOne,
23:   ScbTargetLongTwo,
24:   ScbTargetLongThree,
25:   ScbTargetLongFour,
26:   ScbTargetLongFive,
27:   ScbCompanyID)
28: select
29:   ScbGroupType,
30:   @destntUser,
31:   ScbNodeID,
32:   ScbParentNodeID,
33:   ScbShortcutType,
34:   ScbSubType,
35:   ScbDisplayName,
36:   ScbShortcutKey,
37:   ScbTargetStringOne,
38:   ScbTargetStringTwo,
39:   ScbTargetStringThree,
40:   ScbTargetLongOne,
41:   ScbTargetLongTwo,
42:   ScbTargetLongThree,
43:   ScbTargetLongFour,
44:   ScbTargetLongFive,
45:   ScbCompanyID
46: from SY01990
47: where ScbOwnerID = @sourceUser
48: GO

You can enhance this script by adding your own transaction commit and rollback features or use as a mechanism to ensure users do not change their shortcuts based on a template user ID. The options are many.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Read More

Microsoft Dynamics GP 2010 - Security Enhancements

This article opens the series on the new features and enhancements available in Microsoft Dynamics GP 2010. I will start out with something dear to the heart of systems administrators and that's security. So let's take a look at the first set of enhancements that have been introduced to the new release.

Automatic Login

Now you have the option to log on to Microsoft Dynamics GP and a default -- favorite if you will -- company automatically. In order for the login to take place automatically, the system administrator must mark the Enable Remember User field in the System Preferences window.

You will notice a new checkmark on the Welcome to Microsoft Dynamics GP window to Remember user and password.



And you will also notice a checkmark on the Company Login window to Remember this company.



As mentioned before, having the ability to access these new enhancements is a system wide administrative option set via the System Preferences window - MSDGP > Tools > Setup > System > System Preferences.


Copy User Security Settings

System administrators lost a lot of the cool features available in version's 8.0 and 9.0's Advanced Security module after upgrading to Microsoft Dynamics GP 10.0 role base security model. One feature in particular that put dents in a lot of desks was the fact that security could not be copied across user accounts.

For Microsoft Dynamics GP 2010, you can now use the Copy User Security window and the User Setup window to copy security settings (roles, tasks, and company access) you want to copy from one user to another.




For example, if you hire a new purchasing agent, you can copy the security settings from an existing purchasing agent instead of manually specifying the security settings. If the new purchasing agent is transferring from another position in your company, copying replaces any existing security settings for the user you are copying to.

Security Roles and Tasks Filters

You can now filter out only the security tasks and items to which access has been granted in the Security Task Setup window, the Security Role Setup window, and the User Security Setup window instead of viewing all security tasks, roles, and items. This makes understanding security settings assigned to tasks, roles, and items a breeze.




These enhancements will certainly facilitate security administration for the system administrator, while allowing users the ability to speed up login into the company of their preference. Please let me know what you think about these enhancements.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Read More

Post through from Microsoft Dynamics GP Manufacturing

As if Post Through wasn't hard enough to understand for the core financial and distribution modules (take a look at my previous article Why my transactions don't post through GL even when I have the flag selected?) here comes Manufacturing. Take a look at these comments from Microsoft's Becky Berginski:

"Within Microsoft Dynamics GP there is a place to setup your modules posting options. However, there is not an option specifically for Manufacturing. Manufacturing looks at the Inventory and General Ledger options to determine the MFG posting options. If the transactions you enter in Manufacturing post adjustments to Inventory then the system looks at the settings for your Inventory module. (i.e. To determine if it is stops in GL or posts through GL.) If the transactions don't update inventory (i.e. closing MO), but GL adjustments are made then it looks at your GL settings to determine how it posts.

Go to Tools Setup Posting Posting. Review the settings for both Inventory and General ledger transaction entry options.The posting journals that print after a posting of an MO Receipt are those associated with Inventory Adjustments. You can turn these off (or have them saved to a file) using the Posting Setup window (Tools - Setup - Posting - Posting). Be sure to choose the Inventory series and the Transaction Entry origin. Keep in mind that these settings are now on those reports for regular Inventory posting also and not just the posting of Manufacturing Components.

There are certain labor transactions that will not post through the GL. Data Collection transaction never post Through the GL. You would need to locate the Journal Entry and adjust the Transaction Date. Data Collection entries will have a Source Doc code of 'DC_ADJ' and reference to the MO number."

Becky then goes on to clarify that,

"There are some transactions that will not post to GL. Most of them do. Below is a listing of what does. There are various postings that will come from Manufacturing. Some of these postings go directly to the GL. Some of the postings go through Inventory first and then update the GL. The transactions that go through Inventory utilize the posting setup for that series. The transactions that post directly from manufacturing to GL utilize the posting setup for the financial series. Here are some examples of the various places that one can post from in Manufacturing:

1. If they are using the Quick MO Entry window, postings will occur when they click on the Close MO button.

A. They will have one adjustment posted in Inventory to reduce quantities for the components. This entry will update GL when it finishes posting in IV.

B. They will have a second adjustment posted in Inventory for the receipt of the finished goods. This also will update GL when it finishes posting in IV.

C. If there is labor or machine time set up for the finished good, a journal entry for those costs will post directly to the GL.

D. Also if there are any variances, a journal entry to record those will post directly to the GL.

2. If they are using the basic MO Entry window, postings may occur a number of different times and from different windows:

A. Components can be issued from the Component Transaction Entry window. This posts a decrease adjustment in IV and then GL when the posting completes in IV.

B. Any labor and machine time recorded in any of the WIP windows will post costs directly to the GL.

C. The posting of MO receipts will post an increase adjustment for the finished good in IV and then will update GL. If components are backflushed a decrease adjustment will be posted to IV and GL will be update after the IV posting is complete. Labor and machine costs may post to GL if they are backflushed.

D. When closing the MO, variances will be posted directly to GL. Also additional component quantities may be included--this means a decrease adjustment in IV and a related posting to GL.

One other setting that sometimes prevents users from posting thru GL is in the Security Setup window (Tools - Setup - System - Security). Choose product of Microsoft Dynamics GP, Type of Windows, and Series of Financial. Be sure that the users posting in Mfg have access to the following two windows:

GL Open Files
GL Open Financial Files - internal

As I mentioned transactions stemming from labor or machine data collection will never post through the GL."

Hope you find this information useful and a good 2010 opening article.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Read More