Finance | Project Operations, Human Resources, ...

Connecting to the wrong company

(0) Share

Report

Posted on by olegvi

809

Hello Group,

I have a strange situation where, in the upgraded environment, the new user could not log in to GP 2018R2.

I ruled out the password policy issue, missing sql user and other things described in the various kbs.

It boiled down to the fact that aside from intended companies the new login was trying to connect to one other company that user didn't have access to. Once I've given user access to that company the login went through. Tested the same with another new user and got the same results.

The message in the DEXSQL.LOG file was: "Cannot open database "ABC" requested by the login. The login failed"

Did anybody see that? Why would GP try to connect to the database that user has no access to?

TIA.

I have the same question (0)

All responses (10)

Answers (0)

Suggested answer

Almas Mahfooz 11,009 User Group Leader on at

Like (0)

Report

RE: Connecting to the wrong company

Check in SQL Security if your GP user have SQL login and have assigned DYNGRP roll or not?

Was this reply helpful? Yes No
Derek Albaugh on at

Like (0)

Report

RE: Connecting to the wrong company

What is the script in the dexsql.log that is being run when you see this "Cannot open database 'ABC' requested by the login" message?

Usually when we see this type of error message, it's a trigger or other object that is hard-coded to look for a specific company database, and when the user doesn't have access to that object, it throws the type of error being seen. This is also why giving the user access to the company database resolves the error message.

If you can identify the object, then you could look at it in SQL Server Mgmt Studio to see if it is hard-coded to specify the wrong company database, then fix accordingly.

Let us know if you have any questions.

Thanks

Was this reply helpful? Yes No
olegvi 809 on at

Like (0)

Report

RE: Connecting to the wrong company

Hi Derek,

Here is what I see in a very fresh log:

/*

/* Date: 10/15/2019 Time: 8:58:43

SQLSTATE:(37000) Native Err:(4060) stmt(0):*/

[Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot open database "PKAC" requested by the login. The login failed.*/

/*

/* Date: 10/15/2019 Time: 8:58:43

SQLSTATE:(28000) Native Err:(18456) stmt(0):*/

[Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user 'test'.*/

/*

/* Date: 10/15/2019 Time: 8:58:43

SQLSTATE:(00000) Native Err:(18456) stmt(0):*/

*/

/*

/* Date: 10/15/2019 Time: 8:58:43

SQLSTATE:(00000) Native Err:(18456) status(24):*/

SWSTATUS DUMP*/

We do not employ custom database triggers.

What else might that be?

Thank you,

Oleg.

Was this reply helpful? Yes No
olegvi 809 on at

Like (0)

Report

RE: Connecting to the wrong company

Interestingly, when I change the user to sa, here is what I see:

/*

/* Date: 10/15/2019 Time: 9:21:00

SQLSTATE:(01000) Native Err:(5701) stmt(0):*/

[Microsoft][SQL Server Native Client 11.0][SQL Server]Changed database context to 'PKAC'.*/

/*

/* Date: 10/15/2019 Time: 9:21:00

SQLSTATE:(01000) Native Err:(5703) stmt(0):*/

[Microsoft][SQL Server Native Client 11.0][SQL Server]Changed language setting to us_english.*/

Why the database context changes to PKAC (the company that creates an issue).

Thank you,

Oleg.

Was this reply helpful? Yes No
Derek Albaugh on at

Like (0)

Report

RE: Connecting to the wrong company

What line or script do you see right before the "cannot open database 'xxxx' requested by the login", in the dexsql.log?

We need to see that to determine what Dynamics GP is trying to do when it throws this error.........it's usually not just going to pop an error out of the air saying, 'hey, user A doesn't have access to database xxxx'....., there has to be something that is hard-coded or referencing this other database, PKAC, that the user doesn't have access to, thus the error.

Also, what is the user(s) doing in Dynamics GP when they see this type of error message? I ask because the majority of cases where we see this type of error, it's usually not referencing a company db but more the GPCONFIGURATION db for Web Client and/or security store database for Web Services, which is why we usually see something like this occur with objects that are hard-coded with a database name in them, being run from the wrong database.

Depending on what script/object is causing the error, we could potentially re-create it via SQL Maintenance or the Database Maintenance Utility, to have the correct database name built in, thus resolving this type of error.

Thanks,

Was this reply helpful? Yes No
olegvi 809 on at

Like (0)

Report

RE: Connecting to the wrong company

That's the problem, this error happens on the login to GP. This is all I get in the log. Here is what I got when switching the user from sa to 'test':

/* Date: 10/15/2019 Time: 12:18:08

stmt(163818840):*/

{ CALL DYNAMICS.dbo.zDP_SY01400SS_1 ( 'sa' ) }

/* Date: 10/15/2019 Time: 12:18:08

stmt(163894984):*/

{ CALL DYNAMICS.dbo.zDP_SY10550SS_1 ( 'sa', 11 ) }

/* Date: 10/15/2019 Time: 12:18:08

stmt(163891368):*/

{ CALL DYNAMICS.dbo.zDP_SY10800SS_1 ( 'DEFAULTUSER', 0, 898, 2 ) }

/* Date: 10/15/2019 Time: 12:18:08

stmt(163894984):*/

{ CALL DYNAMICS.dbo.zDP_SY01402SS_1 ( 'GLOBALUSER', 71 ) }

/* Date: 10/15/2019 Time: 12:18:13

stmt(163818840):*/

{ CALL DYNAMICS.dbo.zDP_SY01400SS_1 ( 'test' ) }

/* Date: 10/15/2019 Time: 12:18:13

stmt(163818840):*/

{ CALL DYNAMICS.dbo.zDP_SY01400SS_1 ( 'sa' ) }

/*

/* Date: 10/15/2019 Time: 12:18:13

SQLSTATE:(37000) Native Err:(4060) stmt(0):*/

[Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot open database "PKAC" requested by the login. The login failed.*/

/*

/* Date: 10/15/2019 Time: 12:18:13

SQLSTATE:(28000) Native Err:(18456) stmt(0):*/

[Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user 'test'.*/

We do not use web client.

Is there another way to see what is happening?

Thank you,

Oleg.

Was this reply helpful? Yes No
Derek Albaugh on at

Like (0)

Report

RE: Connecting to the wrong company

I'm still not seeing anything that stands out as to why it is saying this 'test' user doesn't have access to the PKAC database, which it doesn't have access to, when you're trying to switch over from the sa user to the 'test' user.

If you create a new user in Dynamics GP and give it access to one company database along with a non-PowerUser security role like Bookkeeper or something like that, then have that new user login to Dynamics GP, can the user login successfully without these errors?

If so, can you then switch users from this new test user to 'sa' login and then change users back again to this new test user, again without these error messages?

If you can do all the above, then it may just be an issue with this one user, if that is the only user that is experiencing this issue.

If this newly created user has the same issue, then we'd probably need a support case opened so we can look at the users and your environment.

Thanks

Was this reply helpful? Yes No
Derek Albaugh on at

Like (0)

Report

RE: Connecting to the wrong company

Hello Oleg,

I believe you may have opened a support case for this, but I was thinking, as you mentioned this issue occurs when user(s) are attempting to login to Dynamics GP........on the ODBC DSN being used to connect Dynamics GP to the SQL Server, is there a default database selected?

If so, that may be the cause of this error, as we do not want any default database selected in the ODBC DSN, even if users only have access to one company database, we want those options in the DSN all un-marked, as per KB 870416.

You can verify this on the machine(s) under Administrative Tools > ODBC Data Sources (32-bit), and looking at the configuration of the ODBC DSN being used for Dynamics GP. All options, to include 'Change the default database to: ' should be un-checked.

Thank you,

Was this reply helpful? Yes No
Suggested answer

olegvi 809 on at

Like (0)

Report

RE: Connecting to the wrong company

Hi Derek,

Yes, we opened the case and found that the registry HKEY_CURRENT_USER/Software/ODBC/ODBC.INI needed to be adjusted.

We discovered that there was a folder named “Dynamics GP 2018” and it had an entry in it that was pointing to the specific database. We deleted the entire “Dynamics GP 2018” folder and that fixed our ussie.

I did check earlier the ODBC for the default company and it was not checked and no database selected. The original ODBC was set up under SYSTEM which in turn was created by the initial install of the client. I wonder if something wrong happened during that process.

Thank you for your help,

Oleg.

Was this reply helpful? Yes No
Derek Albaugh on at

Like (0)

Report

RE: Connecting to the wrong company

Thank you for the update and additional information.

Have a great day.

Was this reply helpful? Yes No