ConnectionString is incorrect in axapta

Question

I want to connect to the other database using LoginProperty class. I did it in two different ways:

1. Create a DNS system object, using loginProperty class and connect to the DB. This way it works fine.

2. In the other way, i wants to creat a 'Connection String' and connect to the DB. In this way i am getting an error.

ODBC operation failed. Unable to logon to the database. [Microsoft][ODBC Driver Manager] Data source name not found and there is no standard driver.

My Connection string is:

connectionString = "Driver={SQL Server Native Client 10.0}; Server=MSS2008-term\\\\SQLServer; Database=db; user ID=sa; Password=ABC.123";
loginProperty.setOther(connectionString); 
conn = new ODBCConnection(loginProperty);

What is missing in the connection string?

Answer 1

Try using a raw string assignment (so no \ magic):

connectionString = @"Driver={SQL Server Native Client 10.0}; Server=MSS2008-term\SQLServer; Database=db; user ID=sa; Password=ABC.123";

Some others have problems, see ODBC connection to external data.

Maybe you can create the DSN by code, see What is the simplest, most maintainable way to create a SQL Server ODBC Data Source?

Answer 2

I notice you have Server and Database in your connection string where I use Data Source and Initial Catalog for some reason. You can do it like this, just changing/adding relevant parameters:

public str getConnectionString() {
  System.Data.SqlClient.SqlConnectionStringBuilder dbConnectionStringBuilder;

  dbConnectionStringBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder();

  dbConnectionStringBuilder.set_Item('Data Source', 'YourServer');
  dbConnectionStringBuilder.set_Item('Initial Catalog', 'YourDatabase');
  dbConnectionStringBuilder.set_Item('Integrated Security', true);

  return dbConnectionStringBuilder.ToString();
}