Skip to main content

Notifications

Dynamics 365 Community / Forums / Finance forum / CONNECTING D365 F&...
Finance forum
Unanswered

CONNECTING D365 F&O On Premise to EXTERNAL DB Using X++

Posted on by

I have a requirement to connect External DB from X++ in D365. In the previous version, AX 2012, I was using the below code. Now when I use the same code, the following error throw:

pastedimage1676281201140v1.png

Note:

I have created a system DSN in the AOS server and using that DSN I am trying to connect. 

This is a DEMO environment where I am doing some development. The environment was downloaded from LCS.

static server public str EmployeeIntegrationSQLConnect(String50 _DSNName, String50 _DB, str sql)
{
LoginProperty loginProperty;
OdbcConnection odbcConnection;
ENSEmployeeIntegrationDetailsTmp ENSEmployeeIntegrationDetailsTmp;
Statement statement;
ResultSet resultSet;
str criteria,myConnectionString;
SqlStatementExecutePermission perm;
string255 result;
str 100 _date;
int rowcnt;


// Set the information on the ODBC.
loginProperty = new LoginProperty();



loginProperty.setDSN("DB");
loginProperty.setDatabase("HR");

//Create a connection to external database.
odbcConnection = new OdbcConnection(loginProperty);

if (odbcConnection)
{
//Assert permission for executing the sql string.
perm = new SqlStatementExecutePermission(sql);
perm.assert();

//Prepare the sql statement.
statement = odbcConnection.createStatement();
resultSet = statement.executeQuery(sql);

delete_from ENSEmployeeIntegrationDetailsTmp;

//Cause the sql statement to run,
//then loop through each row in the result.
rowcnt = 0;
while (resultSet.next())
{
ttsBegin;
ENSEmployeeIntegrationDetailsTmp.Campus = resultSet.getString(1);
ENSEmployeeIntegrationDetailsTmp.PersonnleNUmber = resultSet.getString(2);
ENSEmployeeIntegrationDetailsTmp.FirstName = resultSet.getString(3);
ENSEmployeeIntegrationDetailsTmp.MiddleName = resultSet.getString(4);
ENSEmployeeIntegrationDetailsTmp.LastName = resultSet.getString(5);
ENSEmployeeIntegrationDetailsTmp.Email = resultSet.getString(6);

_date= resultSet.getString(7);
ENSEmployeeIntegrationDetailsTmp.JoinDate = str2Date( _date,123);
ENSEmployeeIntegrationDetailsTmp.Category = resultSet.getString(8);
ENSEmployeeIntegrationDetailsTmp.Employee = ENSEmployeeIntegrationDetailsTmp.PersonnleNUmber;
ENSEmployeeIntegrationDetailsTmp.School = resultSet.getString(9);
ENSEmployeeIntegrationDetailsTmp.Grades = resultSet.getString(10);
ENSEmployeeIntegrationDetailsTmp.LastworkingDate = str2Date(resultSet.getString(11),321);

ENSEmployeeIntegrationDetailsTmp.insert();
ttscommit;
rowcnt ++;
}
info(strFmt("%1 rows inserted",rowcnt));
//Close the connection.
resultSet.close();
statement.close();
return result;
}
else
{
error("Failed to log on to the database through ODBC.");
}
return "";
}

  • Martin Dráb Profile Picture
    Martin Dráb 229,135 Most Valuable Professional on at
    RE: CONNECTING D365 F&O On Premise to EXTERNAL DB Using X++

    This isn't about a certifacate for F&O. You're trying to make a connection to your database and the error is related to the connection.

  • Safeer PS007 Profile Picture
    Safeer PS007 on at
    RE: CONNECTING D365 F&O On Premise to EXTERNAL DB Using X++


    [Microsoft][ODBC SQL Server Driver][DBNETLIB]SSL Security error

    Microsoft.Dynamics.Ax.Xpp.InternalException: Object 'OdbcConnection' could not be created at
    Microsoft.Dynamics.Ax.MSIL.Interop.throwException(Int32 ExceptionValue, interpret* ip) at
    Microsoft.Dynamics.Ax.MSIL.cqlClassIL.create(Int32 classId, Object[] parameters, Type[] types, Object[] varargs, Type[] varargsTypes, IProxy ILObject)
    at Microsoft.Dynamics.Ax.Xpp.XppObjectBase.createKernelClass(Object[] parameters, Type[] paramTypes, Object[] varargs) at
    Dynamics.AX.Application.OdbcConnection..ctor(LoginProperty property1) at Dynamics.AX.Application.ENSSysUtil.`
    ENSEmployeeIntegrationSQLConnect(String _DSNName, String _DB, String sql) in xppSource://Source/ENS Foundation
    \AxClass_ENSSysUtil.xpp:line 763 at Dynamics.AX.Application.ENSSysUtil.ENSEmployeeIntegrationSQLConnect(String _DSNName, String _DB, String sql) at
    Dynamics.AX.Application.AH_KayanTesting.`main(Args _args) in xppSource://Source/ENS Foundation\AxClass_AH_KayanTesting.xpp:line 13 at
    Dynamics.AX.Application.AH_KayanTesting.main(Args _args) at AH_KayanTesting::Main(Object[] , Boolean& ) at Microsoft.Dynamics.Ax.Xpp.ReflectionCallHelper.MakeStaticCall
    (Type type, String MethodName, Object[] parameters) at Dynamics.AX.Application.SysClassRunnerHelper.`
    runMainMethod(SysClassRunnerState state) in xppSource://Source/ApplicationFoundation\AxClass_SysClassRunnerHelper.xpp:line 30
    at Dynamics.AX.Application.SysClassRunnerHelper.runMainMethod(SysClassRunnerState state) at
    Dynamics.AX.Application.Forms.SysClassRunner.FormControl.timerControl.elapsed() in xppSource://Source/ApplicationFoundation\AxForm_SysClassRunner.xpp:line 52
    ODBC operation failed. Unable to log on to the database.

  • Safeer PS007 Profile Picture
    Safeer PS007 on at
    RE: CONNECTING D365 F&O On Premise to EXTERNAL DB Using X++

    If you see the error, SSL Secuirty Error. As per my understanding, SSL certificates are not required for Demo Env.

    Is it something with usage of HTTPS:: in the server link

  • Martin Dráb Profile Picture
    Martin Dráb 229,135 Most Valuable Professional on at
    RE: CONNECTING D365 F&O On Premise to EXTERNAL DB Using X++

    Okay, it seems that you can reach the DB server, but maybe the web server don't have permissions to access the DB or so.

    Personally, I would avoid the ODBC client, because things like Dapper or LINQ to SQL are easier to use and safer, but if you want it, consider testing the ODBC connection through a simple console application and System.Data.Odbc.OdbcConnection. It might confirm your suspicion that F&O is to blame, make the actual problem easier to find or so.

  • Safeer PS007 Profile Picture
    Safeer PS007 on at
    RE: CONNECTING D365 F&O On Premise to EXTERNAL DB Using X++

    Go to ODBC >> Systen DSN>>connection>>test connection.

    pastedimage1676368730965v1.png

  • Martin Dráb Profile Picture
    Martin Dráb 229,135 Most Valuable Professional on at
    RE: CONNECTING D365 F&O On Premise to EXTERNAL DB Using X++

    You said you tested the connection. How?

    You seem to be claiming that other ODBC clients on the same VM works and the problem is specific to F&O, but I would like to know how you tested this assumption.

  • Safeer PS007 Profile Picture
    Safeer PS007 on at
    RE: CONNECTING D365 F&O On Premise to EXTERNAL DB Using X++

    I downloaded VHD from LCS As We are stills setting up our DEV and Prod Environment.

    The following li...nk describes the way I have done it.

    https://learn.microsoft.com/en-us/dynamicsax-2012/developer/how-to-connect-to-an-external-database-from-x-code

  • Martin Dráb Profile Picture
    Martin Dráb 229,135 Most Valuable Professional on at
    RE: CONNECTING D365 F&O On Premise to EXTERNAL DB Using X++

    Please tell us more about your set up. First of all, On-Premise deployment is used for production environments and test sandboxes. I guess you're currently talking about a development environment, right? Do you mean that you downloaded a VHD and created a VM by yourself?

    How did you tested that the ODBC connection from this machine to the external database".

  • Safeer PS007 Profile Picture
    Safeer PS007 on at
    RE: CONNECTING D365 F&O On Premise to EXTERNAL DB Using X++

    I have already created ODBC Connection in 64 bit, and tested DSN connection and its connected sucessfully.

    May be I need to see the External DB accept connections from D365 F&O. As i Said, From AX 2012, connection is already working. So it must work in D365 F&O.

    Can u help me how to check the same?

  • Martin Dráb Profile Picture
    Martin Dráb 229,135 Most Valuable Professional on at
    RE: CONNECTING D365 F&O On Premise to EXTERNAL DB Using X++

    You said nothing about configuring Data Source Name. If you didn't do it, that's the first problem. Also, the setup there may be wrong or the database doesn't accept connections from the system where you're running the F&O environment. Review things like these.

    Bty the way, please always use Insert > Code (in the rich formatting view) to paste source code to this forum. Then you won't lose line indentation, as you did, and get a few extra features, such as line numbers.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Give Back to the Community this Month

Quick Links

Forum Structure Changes Coming on 11/8!

In our never-ending quest to help the Dynamics 365 Community members get answers faster …

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,807 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,135 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans