"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
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
