Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics GP (Archived)

One PC user account can't login to GP

Posted on by 75

I'm having a bit of an issue with a PC that won't login to GP 9 for one particular user.

If I log into the PC with the problem user account, I then can't get logged into GP with any any account other than SA.

I tried logging into the PC as administrator, and was then able to log into GP using any GP account.  This led me think it was maybe a permission based problem/

However, I logged into the PC as a different standard user account, and that too allowed me to login to GP ok.  Also, adding the problem user into the local Administrators group still wouldn't let it log into GP with any account other than SA.  So I don't think it's permission based.

I tried logging into GP as SA, and resetting the users password in case it was something odd with the ODBC encryption in the password, but no GP accounts work if i'm logged into the PC with this problematic users account.

Before I go and try deleting the local profile, has anyone else came accross this before, or maybe got something to try as a fix.

 

*This post is locked for comments

  • Perry Smith - CRi Profile Picture
    Perry Smith - CRi 1,690 on at
    Re: Re: Re: Re: Re: Re: One PC user account can't login to GP

    That is true.  Another reason to ensure that you use a seperate DSN for any data access outside of the Dynamics GP Application.

     

    Perry

  • Carl Griffiths Profile Picture
    Carl Griffiths 75 on at
    Re: Re: Re: Re: Re: One PC user account can't login to GP

    Problem is, if someone changes a default db in the SQL connection screen within the likes of Excel, the system DSN doesn't change to reflect it, and so it's not something that's obvious.

  • Perry Smith - CRi Profile Picture
    Perry Smith - CRi 1,690 on at
    Re: Re: Re: Re: One PC user account can't login to GP

    Carl,

    The standard GP ODBC DSN should point to either no default db (which will allow it to use the logins default, DYNAMICS) or be set to the DYNAMICS db.  All GP users will have access to the DYNAMICS db.

    I always create a seperate DSN for any reporting pointing to the company db that I need to access.

     Perry

  • Gavin Whittaker Profile Picture
    Gavin Whittaker 295 on at
    Re: Re: Re: One PC user account can't login to GP

    Hi Carl,

    That's great news! I'm glad you got it sorted.

    As I mentioned in my post I just gave the user access to the SQL database that was mentioned in the log (not ideal but it just so happened they could have access to the DB) but I'll be sure to check this out if it happens in future.

    Thanks

    Gav

  • L Vail Profile Picture
    L Vail 65,271 on at
    Re: Re: Re: One PC user account can't login to GP

    Carl,

    Good to know, thank you for posting the solution!

    Kind regards,

    Leslie

  • Carl Griffiths Profile Picture
    Carl Griffiths 75 on at
    Re: Re: One PC user account can't login to GP

    Gavin.

    Success

    I enabled the log, and it came up with

    /*  Date: 08/16/2010  Time: 14:17:29
    SQLSTATE:(37000) Native Err:(4060) stmt(0):*/
    [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database "Pricing" requested by the login. The login failed.*/

    I've seen similar things before, and it's down to the 'default database'. But it's not within the ODBC connection setup.

    Here how I managed to track and fix it.

    We use Excel documents to link to data stored in our GP databases for reporting.
    At one point, we used to use our GP's ODBC connection for the datasource within excel when creating these reports.
    We found that when you connect to the ODBC connection and get to the SQL Server Login screen, if you click "options" it gives you the default database.  If you change that to another database, it does something screwy to GP's connection. Namely you end up not being able to login to GP.  Even though the ODBC connection itself looks and acts fine, and doesn't show this default database as being selected.

    This is the offending little default database setting;

    SQL Server Login

    To fix this, I opened Excel 2007, went to the data tab, chose "From Other Sources >> From Microsoft Query", and .
    chose my GP ODBC connection. Then at the SQL login screen, entered the SA login details, clicked on Options, and then changed the default database back from the one that doesn't work, to one of the company databases.
    I then made up a quick query from of our GP tables, returned the data to Excel, then closed excel without saving.

    GP then seemed to login without any problems at all.

    There's probably more than one way to get to this, but this is how we made this error, and fixed it. 

    This is what prompted us to create a separate ODBC connection for any excel reports. This way, the default database can be changed for the report connection, leaving the GP on unmodified.

  • Gavin Whittaker Profile Picture
    Gavin Whittaker 295 on at
    Re: One PC user account can't login to GP

    Hi Carl,

    I've experienced something similar in the past and creating a DEXSQL.log to log what was happening during the login process helped me to find the problem. In the log it said something like "Login failed in Database...." and it mentioned a SQL database that the user didn't have access to???

    I found that the only way around this was to give the user acess to this database.

    Maybe you could setup the DEXSQL.log and see if this helps identify the problem. You can set this up by changing these two setting in the DEX.INI file.

    SQLLogSQLStmt=TRUE
    SQLLogODBCMessages=TRUE

    I never got to the bottom of why this users account always tried to access this database though...(i checked the "default database" on the users in SQL but this wasn't it either...)

    If it is the same problem maybe deleting the users profile would resolve once and for all?

    Hope this helps

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,269 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans