SqlConnection using Dynamics GP username and password -- Login failed for user

Question

I am writing an add-in for Dynamics GP and I'm attempting to run a custom stored procedure, but when I try to open the connection I get an error message saying "Login failed for user: ".

If I manually use the system administrator username and password, everything executes correctly. However, I'd like to use the username and password of the person logging into the Dynamics GP.

string gpUsername = Dynamics.Globals.UserId.Value;
string gpPassword = Dynamics.Globals.SqlPassword.Value;
string companyDb = Dynamics.Globals.IntercompanyId.Value;
string dataSource = "sql-server-name";

SqlConnectionStringBuilder constringBuilder = new SqlConnectionStringBuilder();
constringBuilder.UserID = gpUsername;
constringBuilder.Password = gpPassword;
constringBuilder.InitialCatalog = companyDb;
constringBuilder.DataSource = dataSource;
constringBuilder.WorkstationID = Environment.MachineName;
constringBuilder.MultipleActiveResultSets = true;
constringBuilder.ApplicationName = $"{FileVersionInfo.GetVersionInfo(Assembly.GetExecutingAssembly().Location).ProductName} (GP)";
constringBuilder.IntegratedSecurity = false;
string constring = constringBuilder.ConnectionString;
SqlConnection con = new SqlConnection(constring);
con.Open();

This is the full stack trace:

Dynamics.exe Warning: 0 : Exception occurred while updating Item class 1255. Review the error message below.
    System.Data.SqlClient.SqlException (0x80131904): Login failed for user '<username>'.
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at Dyn2Bib.GPAddIn.ProcessTransaction(String srs, String srsName, String changeType)
ClientConnectionId:1a4c93c1-0efd-4590-8c9f-98265eaafaa2
Error Number:18456,State:1,Class:14

If I change the DataSource in constringBuilder to use Dynamics.Globals.SqlDataSourceName.Value, it looks like it can't find the data source on the network at all and gives the error:

Dynamics.exe Warning: 0 : Exception occurred while updating Item class 1255. Review the error message below.
    System.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) ---> System.ComponentModel.Win32Exception (0x80004005): The network path was not found
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at Dyn2Bib.GPAddIn.ProcessTransaction(String srs, String srsName, String changeType)
ClientConnectionId:00000000-0000-0000-0000-000000000000
Error Number:53,State:0,Class:20

If I print the connection string to the screen, it looks correct and as I mentioned, if I manually use the system administrator account it works.

It would make sense to me that I'd be able to connect directly to the SQL server with the Dynamics GP account? There is a corresponding login on the server, so it seems like this should be configured correctly already. Does anyone know what I'm doing wrong here? Do I need to make any changes to the Dynamics GP account to allow it to connect using SQL authentication?

try logging in with the account to sql direct(eg yourself using sql manager), and check there is an SQL login of that name with the same password — BugFinder, Jul 17 '18 at 09:40
If I use SQL Server Authentication from the SQL Server Management connection window, then I'm unable to log in with the GP account. The login for the server does definitely exist, however, and is able to log in to Dynamics GP. — ryansin, Jul 17 '18 at 09:44
the only thing ive found (which Im sure you googled too) was that the server name has to be 100% exactly the same for where you managed the users to connection such as server01 is not the same as Server01 or server01.domain etc — BugFinder, Jul 17 '18 at 09:54

score 1 · Answer 1 · answered Jul 17 '18 at 10:21

I assume the username and password of the person logging into the Dynamics GP is a dynamics GP application user only, not an actual database user.

You could solve this by changing your authentication to windows authentication instead

This is achieved as follows:

Create a windows group
Populate this windows group with every windows user who should be able to run your custom SP
Using SQL Server Management Studio, Add this windows group as a SQL Server login
In your code,

Remove these lines:

constringBuilder.UserID = gpUsername;
constringBuilder.Password = gpPassword;

and change this line like so:

 constringBuilder.IntegratedSecurity = true;

This means the connection will be made using their windows credentials, not using (invalid) SQL credentials

These windows credentials have previously been granted server login access by completing steps 1-3

That's not the end of the story. Now you need to enable the group to just have access to your SP

Create a database role in the database with the stored procedure
Grant this role execute access to your custom SP
Lastly, to link it all up, edit the login you created at step 3, grant it access to the database and add it to the role you created in step 5

Complicated? Yes. But granting user access to a database is a very big deal. This way

The users can login and run the SP and nothing else.
To grant and remove users you add or remove them from the windows group (You could reuse a preexisting GP windows group if you wish)
If you create other DB objects that they need access to, you grant the database role access to them

If this solution appeals to you, I can expand on it if you wish.

Windows auth was actually my original plan but as it required the maintenance of an additional security group going forwards I wanted to see if it was possible to use the credentials for the Dynamics GP user, which is should be as they are set up with database logins/users. Plus, it doesn't account for when someone might log in to GP on another person's computer for whatever reason. — ryansin, Jul 17 '18 at 10:27
If you're certain that the Dynamics GP user is also a database user, I suggest you actually try logging in directly using SSMS using some GP credentials. I'm going to guess that that password used for the SQL user is transformed in some way by the GP application. You can also use the SQL Server logs to identify specifically if the login was correct but the password wasn't. — Nick.Mc, Jul 17 '18 at 10:31
With regards to the additional security group, usually one already exists. With regards to the the windows and GP user being different, you could save the GP user in the connection string in a number of ways: https://stackoverflow.com/questions/25581002/how-to-add-custom-attributes-to-sql-connection-string — Nick.Mc, Jul 17 '18 at 10:33

score 1 · Accepted Answer · answered Jul 22 '18 at 22:05

the way to do it is to use the connection object from GP

using Dex = Microsoft.Dexterity.Applications;

        internal static SqlConnection conn = new SqlConnection();

        internal static SqlConnection GetOpenGPConnection()
        {
            try
            {
  //Call Startup
  int resp = GPConnection.Startup();

  // Create the connection object
  Microsoft.Dexterity.GPConnection GPConn = new GPConnection();

  //  Initialize
  GPConn.Init("MyVSTDCompanyid", "myVSTDGPAuthCode");  // get this from Microsoft GP Dev Tools Support
  conn.ConnectionString = String.Format("Database = {0}", Dex.Dynamics.Globals.IntercompanyId.Value);

  GPConn.Connect(conn, Dex.Dynamics.Globals.SqlDataSourceName, Dex.Dynamics.Globals.UserId, Dex.Dynamics.Globals.SqlPassword);

  if ((GPConn.ReturnCode & (int)GPConnection.ReturnCodeFlags.SuccessfulLogin) == (int)GPConnection.ReturnCodeFlags.SuccessfulLogin)
  {
  return conn;
  }
  else
  {//...  user authenticaltion failed - backup plan?  fallback to windows auth?  
  return null
  }
 
            }
            catch (Exception ex)
            {
             // your catch handling mechanism
            }
        }

`Microsoft.Dexterity.Applications` does not exist. Where is the assembly to make this work? — nairware, Sep 20 '19 at 17:44
`Applications.Dynamics.dll` is found within the GP installation directory (ex. `C:\Program Files (x86)\Microsoft Dynamics\GP2018`) as well as the `GP2018 VS Tools SDK` directory. This is the assembly that includes the `Microsoft.Dexterity.Applications` namespace. — nairware, Sep 24 '19 at 16:51

score 0 · Answer 3 · answered Jul 20 '18 at 00:09

0

GP uses SQL Authentication. GP users are SQL Logins. (You can use Windows Auth with GP using FastPath. increasingly popular, and MS has been talking of native support for it too)

Although the user has a SQL login, the application creates the account on SQL and hashes the password. this is to prevent the user from using something like Crystal to query data that they don't have access to

answered Jul 20 '18 at 00:09

Habib

70
7

Thank you for your comment but you aren't suggesting a resolution. – ryansin Jul 20 '18 at 13:22

SqlConnection using Dynamics GP username and password -- Login failed for user

3 Answers3