Share via

After restarting SQL Server, only 'sa' user can log into Microsoft Dynamics GP

  • Article

David Meego - Click for blog homepageLast week, I resolved a rather bizarre case for a customer.

The Situation

Each time the customer's SQL Server was restarted for whatever reason, only the 'sa' user was able to log into Microsoft Dynamics GP.  Once the 'sa' user had logged into Microsoft Dynamics GP at least once, then all the other "normal" users could log in and everything was fine until the next time the SQL Server was restarted.

I asked for screenshots, the DEXSQL.LOG and the SQL Profile Trace so we could see what was happening.

From the user interface, they received the following error:

Your attempt to log into the server failed because of an unknown error. Attempt to log in again.

 Your attempt to log into the server failed because of an unknown error. Attempt to log in again. 

  

The Cause

I did not need the SQL Profile Trace and SQL Logs and the DEXSQL.LOG had enough information to understand what was going wrong. Below is the excerpt from the log:

/* Date: 09/05/2014 Time: 12:33:58
stmt(195184136):*/
set nocount on
insert into tempdb..DEX_SESSION values (@@spid)
select @@identity
/*
/* Date: 09/05/2014 Time: 12:33:58
SQLSTATE:(S0002) Native Err:(208) stmt(195184136):*/
[Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'tempdb..DEX_SESSION'.*/
/*
/* Date: 09/05/2014 Time: 12:33:58
SQLSTATE:(00000) Native Err:(208) stmt(195184136):*/
*/

The error is that the system could not find the tempdb..DEX_SESSION table. If we looked at the SQL Server after the restart would probably find that the tempdb..DEX_LOCK table was also missing.

The DEX_SESSION and DEX_LOCK tables are used by Dexterity's Optimistic Currency Control (OCC) system which allows the passive locking approach to table updates which would allow two users to update the same record in a table as long as they don't try and change the same field.

The fact that the tables are missing is causing the unknown error and preventing users from logging in.

Logging on as the 'sa' provides enough privileges to create the tables. Dexterity can create tables automatically if they are missing as long as the user has sufficient rights.

Once 'sa' has logged in and the tables have been created, other "normal" users can log in.

So why would they have been dropped from the database? Well, the truth is that the entire tempdb database is recreated every time SQL Server restarts.

A better question is: When the SQL Server restarted, why weren't the tempdb..DEX_SESSION and tempdb..DEX_LOCK tables recreated?

Another questions is: What mechanism does the system have for recreating the tables when the SQL Server is restarted?

  

The Resolution

If you look on your system under the master database, you should find 2 Stored Procedures: dbo.smDEX_Build_Locks and dbo.smDEX_Max_Char. It is the dbo.smDEX_Build_Locks stored procedure which is meant to be executed on start up to create the tables in the tempdb.  If the stored procedure is missing or is not running on startup, we would have the issue described above.

To fix the issue:

  1. Locate the dex_req.sql file in the application folder under SQL/Util, for example: C:\Program Files(x86)\Microsoft Dynamics\GP2013\SQL\Util\dex_req.sql.
    Use the attached SQL_dex_req.sql script attached to the bottom of this blog post as the one in the SQL/Util folder is not up to date.
     
  2. Execute this script in SQL Server Management Studio. Notice it has the line sp_procoption 'smDEX_Build_Locks','startup','true' to make the script run automatically on start up.

Next time you restart the stored procedure should run and all users can log in.

 

Hope you find this helpful. 

David

10-Sep-2014: Update script to create the stored procedure and DEX_LOCK and DEX_SESSION table as required for current system and attach to this blog post.

SQL_dex_req.zip

Comments

  • Anonymous
    November 25, 2014
    Blog post from Mark Polino at DynamicAccounting.net mpolino.com/.../restarting-sql-server-sa-user-can-log-microsoft-dynamics-gp-developing-dynamics-gp

  • Anonymous
    December 16, 2014
    I have been going back and forth on the partner boards on this and your script fixed it immediately!!!  THANK YOU DAVE!!!!!

  • Anonymous
    December 16, 2014
    GP works for all users after this but I cannot run Utilities as 'sa' from any machine - same unknown error.

  • Anonymous
    May 17, 2016
    You are genius!!!!!