AX 2012 to external DB connectivity

Finance and Operations/AX Developer Forum
1

Dear ALL

I am writing a code in AX 2012 to connect to external DB without creating Data Source Name(DSN) by just passing severName, DBName, UserID, Password. How i can pass connection string to execute this code.

Please share it how can i connect to external DB by X++ code.

Thanks & Regards

Atul Jain

2

How do you want to communicate with the external DB? You can, for example, create a VS project and use LINQ to SQL. In such a case, pass the connection string to data context’s constructor. SqlConnectionStringBuilder will help you to easily prepare connection strings.

I used this approach recently and it worked extremely well.

3

First

thanks for Reply

I have written this code to connect to external DB in X++ editor with connection string as credential for this connectivity but it is throwing error which i highlighted at bottom.

public static void main(Args _args)
{
ODBCConnection myODBC;
Statement myStatement;
LoginProperty myLoginProperty;
Resultset myResultset;
SqlStatementExecutePermission sqlPermission;
str mySQLStatement;
str myConnectionString;

//str myDSN=“yourdatasourcenamehere”;
str myUserName=“BWCINC\tbwir”;
str myPassword="********";
;
myConnectionString=strfmt(“UID=%1;PWD=%2”,myUserName,myPassword);
myLoginProperty = new LoginProperty();
myLoginProperty.setServer(“BWIRAXSVR04”);
myLoginProperty.setDatabase(“MicrosoftDynamicsAX”);
myLoginProperty.setOther(myConnectionString);
try
{
myODBC = new OdbcConnection(myLoginProperty);
myStatement=myODBC.createStatement();

mySQLStatement=“SELECT * FROM CustTable”;
sqlPermission = new SQLStatementExecutePermission(mySQLStatement);
sqlPermission.assert();
myResultSet=myStatement.executeQuery(mySQLStatement);
while (myResultSet.next())
{
info(strFmt("%1, %2", myResultSet.getString(1), myResultSet.getString(4)));
//info(int2str(myResultSet.getInt(2)));
}
CodeAccessPermission::revertAssert();
} catch
{
error(‘Unexpected error’);
}
}

Where i can make changes to make it workable.?
THSI CODE IS NOT BRING VALUE FROM CUSTTABLE AND SHOWING THE ERROE LIKE THAT—"[Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user ‘BWCINC\Jatul’" LIKE THAT".
PLEAE REPLY FOR IT AS SOON AS POSSIBLE.

4

The message said Login failed for user ‘BWCINC\Jatul’, so you probably don’t have right permissions.

5

Thanks for reply

This error should not come becuase i am passing Admin credential as a connection string…to fetch data from MS SQL server 2008 R2.

6

My guess is that SQL authentication is not allowed in your SQL server.

7

Thanks for Reply

can you share your code for SqlConnectionStringBuilder to connecting to external DB .

…

8

There is really nothing special in my code (C#):

private static string BuildConnectionString(string dbServer, string database)
{
    SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();

    builder.DataSource = dbServer;
    builder.InitialCatalog = database;
    builder.IntegratedSecurity = true;

    return builder.ToString();
}
9

Thanks for reply

How can i execute the same code in X++ by sending (USERID , PASSWORD).

10

Dear Martin

Thanks for reply

How can i execute the same code in X++ by sending (USERID , PASSWORD).

Like writing a code in job and execute it to get the data from target DB.

Thanks & Regards

Atul Jain

11

The same code as… what?

As I said, I guess that your code doesn’t work because the only allowed authentication is via Active Directory (Windows authentication), therefore you can’t use login/password (SQL authentication). Do you say that you checked configuration of your SQL Server and SQL authentication is allowed?

12

Hi,

I have exactly the same problem. I can connect directly from DataSource ODBC. But when I use the same settings(server name, dabase name, UID and password) through AX2012, I can not connect. I receive error like mentioned above.

I don’t know how to solve this problem.

13

Hi,

Could someone get the solution to this problem??

14

Hi,

Below you have Microsoft’s answer why you can not connect using ODBC and user and password(in AX 2012 as well). In such cases, instead of ODBC, I use OLE DB with success. Write, if you want some sample, how to use OLE DB. Alhough, there are many samples on websites.

This is official Microsoft’'s answer:

Resolution
Problem was escalated to escalation engineers where we confirmed the behavior of the application.
In AX 4.0 for the LoginProperty class, we had two properties on this class – setUsername & setPassword – these were the properties you used if you wanted to change the login, see link below…
http://msdn.microsoft.com/en-us/library/aa623556(v=ax.10).aspx

In AX 2009 onwards, these were removed for security reasons – see link below, they setUsername and setPassword are no longer valid properties.
http://msdn.microsoft.com/en-us/library/aa623556(v=ax.50).aspx

I have looked at the kernel code, and in AX 4.0 and before if you specified the setUsername and setPassword then the kernel would use these to create the ODBC connection. If they were not specified, then they would use the login for the AX AOS service (or with AX 3.0 or before the client login in two tier mode).

So as it is no longer possible to set these properties from AX 2009 onwards, the kernel will always use the login for the AOS service. So even if you try and set these in the “setOther” property on the LoginProperty class, they are ignored as in the connection string we create in the kernel code is always using trusted authentication of the AOS Service account – so these additional properties are ignored.

15

Thank you Piotr Kulakowski,

Now I know why it takes my user from windows! I was thinking that my code was wrong! I will do the OLE DB connection, but I can’t find an example, could you please provide me a link or the steps to do that in AX 2012???

Thanks in advance,

David

16

void new()
{

LoginProperty loginProperty;
ODBCConnection odbcConnection;

System.Data.OleDb.OleDbConnection objConn;

ProdParameters prodParam = ProdParameters::find();
//str dsnConnection;
str connectStr;
str exceptionStr;
InteropPermission perm;
System.Exception e;

;
try
{
loginProperty = new LoginProperty();

connectStr = ‘Provider=sqloledb;Data Source=XXXXXX;’;
connectStr += ‘Initial Catalog=’ + DBName + ‘;’;
connectStr += ‘UID=’ + DBUser_bon + ‘;’;
connectStr += ‘PWD= password;’; // + cryptoblob2str(WinapiServer::cryptUnProtectData(DBPass_bon)) + ‘;’;

objConn = new System.Data.OleDb.OleDbConnection(connectStr);

}
catch(Exception::CLRError)
{
CodeAccessPermission::revertAssert();
perm = new InteropPermission(InteropKind::ClrInterop);
if (perm == null)
{
return;
}
perm.assert();
e = ClrInterop::getLastException();
CodeAccessPermission::revertAssert();
while( e )
{
exceptionStr += e.get_Message();
e = e.get_InnerException();
}
info(exceptionStr);
}
catch
{
error(“sssdsds”);
}
if(objConn)
objConn.Close();

}

17

Hi,

I can connect to an external database, but in the database I have fields that are floats and datetime. I need to convert it to real and date but I can’t.

I use datetime2date function and it doesn’t work, I need to convert all this fields to put it into a table in AX, can somebody help me?

Thanks

18

Hi,

Try something like this:

System.Data.OleDb.OleDbDataReader reader;

Date aDate;
int aTime;
real aValue;

aDate = reader.GetString(6); / /str2date(reader.GetString(6), 321);
aTime = reader.get_Item(7); // reader.GetInt64(7);//str2int(reader.GetString(7));
aValue = reader.get_Item(8);

19

Hi Piotr,

I tried the aValue for real fields in AX and it works, thanks a lot!!! But when I try to get the date, I can’t, it sends me an error message. Do you have another example of that???

Thanks

20

I forgot to write in previes post. I use conversion ‘data’ to ‘string’ in SQL query : convert(varchar(10),res.ResultTime,120) as SampleData.

I don’t remember, may be it was reason, I could not find nothing more.