Archive for October, 2018


With IT groups becoming more security-conscious, I have been asked repeatedly if it is possible to have lower-level IT personnel install GP workstations without causing security risks by giving out the SQL sa password.
I tested a user created in GP solely for GP installs, and it did work in 2018R2.  I have not tested it in other versions at this point.
I created a user in GP named GPADMIN and set the password for the user.  I purposely also did not grant any company access or security.  I then edited the dex.ini SYNCHRONIZE= to TRUE to force dictionary synchronization with the DYNAMICS account framework (this would emulate the activity of running Utilities following a client install).  I then ran Dynamics Utilities ‘as administrator’ and logged in as GPADMIN, and it did successfully synchronize the dictionaries.
I then ran Dynamics and was able to log in successfully as far as the company selection screen.  Since I granted no company access, there were none on the drop-down.
It appears that you could use this to allow installers to run a template install and keeping the sa and DYNSA accounts secure.
Note that this will only work for workstation installs or service pack updates.  For adding companies in GP Utilities, you will need to use either sa or DYNSA.  Make certain DYNSA is dbo for DYNAMICS and all companies listed in the SY01500 table.  To take it one step further, the initial installation and database creation must be done using the sa account, since the installer needs to create DYNSA and set the login as dbo for Dynamics-related databases.
DYNSA has security to log into GP, log into companies, and perform most maintenance tasks.  GPADMIN (or whatever name you choose) will be able to install or update clients.  Neither will be able to log into SSMS (SQL Server Management Studio) since their passwords are encrypted in the DYNAMICS database.

One of Microsoft’s buried SQL gems.  I was working with a client with serious inter-server Dynamics GP performance issues, so slow as to be virtually unusable, but GP ran fine at the SQL Server.  We discovered SQL ODBC was communicating using Named Pipes only.

Without getting into troubleshooting detail, their IT group changed the default SQL port for Microsoft SQL 2016, and Microsoft, in all their infinite wisdom, removed port selection settings from the client ODBC DSN Configuration interface a number of versions back.  So how do you tell ODBC what port to connect to?

The answer is simple.  During configuration, when you select the server, enter the SQL Server and instance followed by a comma (,) followed by the port number it will connect with – no spaces.

For example, If your SQL Server is on CONTOSO1\GP, but needs to use TCP/IP Port 12345, enter CONTOSO1\GP,12345 in the Server line. If SQL is on the default instance, it would be CONTOSO1,12345.  It’s that simple.