Friday, May 29, 2015

"A SQL Server login already exists for . Delete the user and create a new user" error when switching a user ID from web client only to mixed mode access

I'm completing the deployment of Microsoft Dynamics GP 2013 R2's Project Accounting and Time Entry approval workflow for a customer in Alabama who is currently deploying web client for extranet access (and timesheet entry) and desktop client for intranet access.

As a result, since time entry employees are remote, we decided to setup these user accounts as web client only. However, the customer wanted to provide an alternative to the web client in case it became unavailable. This meant that these users would needed the ability to access the Microsoft Dynamics GP desktop client via a Terminal Server provisioned (albeit temporarily) to support this goal.

The Problem

To meet this requirement, we needed to adjust the user accounts in Dynamics GP to also support SQL Server authentication (in addition to web client access), by deselecting the Web Client user only check mark and completing the SQL Login credentials information. Upon attempting to save the changes we received the following error:

A SQL Server login already exists for userID. Delete the user and create a new user.


As it so turns out with most application errors, you cannot just follow the instructions literally to fix the problem. In this case, we had more than 85 web client only user accounts created so "Delete the user and create a new user" wasn't an option and I don't know why I had a gut instinct this recommendation would not work anyways.

It also turns out that I tested this in Microsoft Dynamics GP 2015 and had no issues switching a user account from Web Client user only to a mixed mode access user account.

NOTE: "mixed mode access" user is a terminology I came up with to identify a Microsoft Dynamics GP user account with access capabilities from both the web client and desktop client.

The Solution

In troubleshooting this problem, the first thing is to focus on the first part of the error message, "A SQL Server login already exists". If a login already exists on SQL Server, then it should be visible under Management Studio - bear in mind that each Dynamics GP user account was setup as a web client only account to begin with. In principle, there should be no SQL Server security principal for these type of accounts.

Management Studio did not show any account created for the Dynamics GP under the Security folder. So the next logical thing would be to run a statement to check for a SID for the account in question:

SELECT suser_sid('TheUserID');

This statement returned an actual SID number (a long hexadecimal set of characters), confirming at least there was a corresponding SID for the account, even though we could not see the actual account in Management Studio.

Next was to verify then, what account was assigned to that SID. In order to do this, I ran the following SQL statement:

SELECT name FROM sys.server_principals where sid = (SELECT suser_sid('TheUserID'));

As it turned out, this statement returned no results. So here's the conundrum... GP thinks there's a SQL login for a user account that doesn't really exists on SQL, but has a SID.

Now, because there wasn't an actual SQL login for the user ID in question, I went ahead and setup a new SQL login with the user account by running the following statement:

sp_addlogin('TheUserID', 'somePassword');

The statement was successful and I could now see the SQL login under the security folder.

The next thing was to then reset the Dynamics GP web client user flag and assign the SQL login to the user account:

UPDATE SY01400 SET WCUser = 0,  SQLLoginID = 'TheUserID' WHERE USERID = 'TheUserID';

Since Microsoft Dynamics GP passwords are encrypted on SQL Server, you will now need to log into Dynamics GP, pull up the user account under the User Maintenance window, and change the password in the SQL login credentials section.

I cannot assess whether this is a bug or not, but suffice to say the same behavior is not present in GP 2015, so I must lean towards it being a bug.

I hope you find these troubleshooting steps useful.

Until next post!

MG.-
Mariano Gomez, MVP

7 comments:

1antares1 said...

Certainly I appreciate all your contributions and share resources in your blog. The most complete and free to learn at any level site.

Best Regards, Mariano Gomez.

Nathan Reid said...

Hi Mariano,

Thanks for debugging this one. I'm running into an issue after running the script to add the login. I run sp_addlogin 'XXX', 'password' and it runs successfully, but the user does not appear in the SY01400 table. So, when I run the final script in your instructions I receive a message that no rows are affected.

Of course, I tried to ignore that and tried recreating the user in GP, but I have a new error stating that the userid is already in use and to use a different one. Any advice?

Mariano Gomez said...

Nathan,

It seems in your case the user wasn't there to begin with. So, I would drop the login from SQL Server and setup the user in GP directly. This in turn should create the login on SQL Server.

MG.-

Nathan Reid said...

Simple response for a simple fix. Thank you

My problem originally occurred when I mistakenly created a Web Client user in GP. When I went back to delete it I had the same errors that you listed in your article. There was no easy way to delete through GP or SQL since the SQL login didn't exist or was not visible. My problem is solved now though. Thanks!

Lisa W said...

Incredibly helpful, Mariano. THANK YOU SO MUCH for taking the time to write this up and share with us!

Lisa Williams

Mariano Gomez said...

Lisa,

Thanks for the kind words! Keep up the readership.

MG.-
Mariano Gomez, MVP

Tamara DeStefano said...

Thanks for this info Mariano! I am running into an issue similar to Nathan Reid. I deleted the web client users in GP and I am not getting this error. When I get to this step: sp_addlogin 'XXX', 'password' it fails to create.

Do you have a script to delete the suser_sid?

Thanks!