?
Solved

Cannot log into Microsoft Dynamics GP 10 (one user and any new user) due to ODBC DSN

Posted on 2010-11-11
6
Medium Priority
?
1,581 Views
Last Modified: 2012-05-10
After a day of troubleshooting why (1) user (plus any newly created users) could not log into GP, we finally have a workaround.

Unfortunately, we don't have a solution or explanation as to what's happening.

Received a call from 'UserA' that they could not log into GP this morning. Checked logs and saw this:

Login failed for user ‘UserA'. Reason: Failed to open the explicitly specified database.
Error: 18456, Severity: 14, State:38

Turning on SQL logging in the dex.ini file, we find that it's opening a company database, by default, of a company she does not nor should have access to.

We cannot figure out why it's opening up to database 'compabc' when it is supposed to default either to 'master' or 'DYNAMICS'.

We checked the ODBC connections, and nothing was explicitly set there. Furthermore, this happens from any computer in the domain for this username, or for any username we newly create (all other older users work fine).

We use Dynamics GP as the DSN on all workstations; we found that we could go to a workstation and change the DSN to DynamicsGP (no space) and 'UserA' could login to GP just fine and we confirmed she was accessing the default database of DYNAMICS,

What is going on here? Somehow the Dynamics GP DSN for the Native Client connector is tied to the wrong default database for this user and all new users, but none of the other older users (some of which also explicitly do not have access to 'compabc' database, but are just fine).

Any ideas on what's going on?

0
Comment
Question by:Tercestisi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 18

Accepted Solution

by:
Steve Endow earned 2000 total points
ID: 34123588
Hi,

Very interesting scenario.  I have run into some very odd DSN related problems with specific GP users, but haven't heard of this specific one before.  I just tried to mess up my DSN and SQL user default DB and permission settings to see if I could reproduce the issue, but surprisingly I can't get the login to complain.

If you haven't done these yet, here's what I would recommend trying:

1) Make sure that all DSNs are spelled exactly the same on the GP SQL Server and all workstations, and make sure ALL of your DSNs use the same SQL server driver (i.e. Native Client).  They should all be identical.  With the Native Client driver, this is critical.

2) Note the user's GP security settings and any other user-specific options, favorites, etc.  Delete the user account from GP.  Check SQL Management Studio and verify that the login has been removed, and all database users for the login have been removed.  Recreate the login from within GP.


Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified Professional
0
 

Author Comment

by:Tercestisi
ID: 34123635
Sendow,

Thanks for the response.

I was surprised too when changing the DSN allowed login... I thought with the way GP encrypts the password that changing the DSN would make the login fail.

(1) We use native client and the same DSN everywhere... what was more odd is that we attempted during testing to try the non-native SQL Server client and that worked as well.

(2) We have exhausted this and I am fairly certain that it's not user-specific, since it is happening now to any newly created user.
0
 
LVL 18

Expert Comment

by:Steve Endow
ID: 34123697
Hi,

In that case, I have had a nearly identical issue.

I spent hours trying to figure out why a new GP user could not login.  After tons of searching and testing, I submitted a support case with Microsoft.

After trying a bunch of other things, as a hail mary, they had me try the old SQL Server driver on the DSN.

That resolved the issue.

When I asked why, and what I should do then, they said to just use the old driver.  They had no explanation as to why it worked or why I should have to use the old driver.  I was baffled.

Sounds very similar to what you are experiencing.

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified Professional
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

Author Comment

by:Tercestisi
ID: 34123739
Sendow,

Ugh... not what I wanted to hear but thanks for sharing =)

By that measure, I'd need to install the old driver on each new PC and then go around and switch the drivers on other PC's... because there are times different users log into different PC's and access GP with their own credentials.

I'd hate to do that and screw up any of the existing logins that work... I suppose changing back the driver if it borks should theoretically work; just don't like theoretical in a situation like this.

Thanks again for the response.
0
 
LVL 18

Assisted Solution

by:Steve Endow
Steve Endow earned 2000 total points
ID: 34124231
Hi,

Have you looked at all of the scenarios and possible resolutions in KB Article 919345?

https://mbs2.microsoft.com/Knowledgebase/KBDisplay.aspx?WTNTZSMNWUKNTMMYYUSKWOTVUNVLMRYSQYRNKZUPMLZUXSSYPQVMMVLPMUPYZOSL

One of them might be relevant.

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified Professional
0
 

Author Closing Comment

by:Tercestisi
ID: 34650557
Thanks for your help, but we still have not resolved the situation. We were able to get around it by using a different DSN, which is fine for this scenario as the user only logs onto the domain and queries from specific workstation.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question