SQL Server Connection using GPConnNet.dll

Question

I am attempting to make a connection to a Dynamics GP SQL Server Database using the currently logged in credentials from GP. (for context http://blogs.msdn.com/b/developingfordynamicsgp/archive/2008/10/02/why-does-microsoft-dynamics-gp-encrypt-passwords.aspx)

Using the code provided from the documentation of GPConnNet.dll I should be able to get a connection but have been unable to do so for non-sa users, sa and dynsa work fine. I am receiving a login failed sql server error.

SqlConnectionStringBuilder cb = new SqlConnectionStringBuilder(connectionString);
SqlConnection sqlConn = new SqlConnection();
if (sqlConn.State != ConnectionState.Open)
{
    GPConnection.Startup();
    var gpconn = new GPConnection();
    gpconn.Init(<Key1>, <Key2>);
try
{
    sqlConn.ConnectionString = string.Format("database={0}", cb.InitialCatalog);
    gpconn.LoginCompatibilityMode = false;
    gpconn.Connect(sqlConn, cb.DataSource, cb.UserID, cb.Password);
    if (gpconn.ReturnCode != 1)
         throw new AuthenticationException("Could not authenticate with the GP credentials.");
}
catch (System.Runtime.InteropServices.SEHException)
{
    throw new AuthenticationException("Could not authenticate with the GP credentials.");
}
}

The information in the connection string is coming from the Microsoft Dexterity toolkit.

public class GPUser
{
    public readonly static string DataBase = Dynamics.Globals.IntercompanyId.Value;
    public readonly static string UserID = Dynamics.Globals.UserName.Value;
    public readonly static string Password = Dynamics.Globals.SqlPassword.Value;
    public readonly static string DataSource = Dynamics.Globals.SqlDataSourceName.Value;
    public readonly static string ApplicationName = string.Format("{0}{1}", App.ProductName, "(gp)");
    public static string Server
    {
        get
        {
            //Returns the Server from the ODBC DSN
        }
    }
    public static SqlConnectionStringBuilder ConnectionString
    {
        get 
        {
            return new SqlConnectionStringBuilder
            {
                DataSource = Server,
                UserID = UserID,
                Password = Password,
                ApplicationName = ApplicationName,
                InitialCatalog = DataBase
            };
        }

    }
}

Is there something that is required on the user? Is there something in the GPConnection code that I'm missing?

Thanks

score 0 · Accepted Answer · answered Aug 09 '12 at 16:10

This Class will retrieve the proper data that you need for the connection.

public class GPUser
{
    public readonly static string DataBase = Dynamics.Globals.IntercompanyId.Value;
    public readonly static string UserID = Dynamics.Globals.UserId.Value;
    public readonly static string Password = Dynamics.Globals.SqlPassword.Value;
    public readonly static string DataSource = Dynamics.Globals.SqlDataSourceName.Value;
    public readonly static string ApplicationName = string.Format("{0}{1}", App.ProductName, "(gp)");
    public static SqlConnectionStringBuilder ConnectionString
    {
        get 
        {
            return new SqlConnectionStringBuilder
            {
                DataSource = DataSource,
                UserID = UserID,
                Password = Password,
                ApplicationName = ApplicationName,
                InitialCatalog = DataBase
            };
        }

    }
    public readonly static short CompanyId = Dynamics.Globals.CompanyId.Value;
    public readonly static DateTime UserDate = Dynamics.Globals.UserDate.Value;
}

Passing GPUser.ConnectionString into this code will create a valid SQLConnection object that you can use to connect to the GP Database.

SqlConnectionStringBuilder cb = new SqlConnectionStringBuilder(connectionString);
SqlConnection sqlConn = new SqlConnection();
if (sqlConn.State != ConnectionState.Open)
{
    GPConnection.Startup();
    var gpconn = new GPConnection();
    gpconn.Init(<Key1>, <Key2>);
    try
    {
        sqlConn.ConnectionString = string.Format("database={0}", cb.InitialCatalog);
        gpconn.LoginCompatibilityMode = false;
        gpconn.Connect(sqlConn, cb.DataSource, cb.UserID, cb.Password);
        if (gpconn.ReturnCode != 1)
            throw new AuthenticationException("Could not authenticate with the GP    credentials.");
    }
    catch (System.Runtime.InteropServices.SEHException)
    {
        throw new AuthenticationException("Could not authenticate with the GP credentials.");
    }
}

I am using GPUser class. i am getting this connection string : string connection = "data source=Dynamic GP 2010;initial catalog=TWO;persist security info=False;User ID=sa;Password=123"; But i am getting login error.Is this correct datasource as a Dynamic GP 2010. — Kiran Reddy, Sep 05 '12 at 12:50
Yes that looks about right the "Dynamic GP 2010" is the default dynamics GP ODBC32 connection name. What was the exception you get? — dustinchilson, Sep 07 '12 at 00:06
Thanks your reply ! Datasource name not "Dynamic GP 2010", that is sql data source name. if i use this connection string for DYNAMCI Database :Data Source=Rohri;Initial Catalog=TWO;Persist Security Info=False;User ID=client1. This is working fine. if i use same connection string instead of DYNAMIC i used TWO database. i am getting error login failed for " client1".can you tell me why i am getting error only for TWO database. — Kiran Reddy, Sep 07 '12 at 09:20

SQL Server Connection using GPConnNet.dll

1 Answers1

Linked