Announcements
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:
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 "";
}
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.
[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.
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
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.
Go to ODBC >> Systen DSN>>connection>>test connection.
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.
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.
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".
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?
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.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 290,807 Super User 2024 Season 2
Martin Dráb 229,135 Most Valuable Professional
nmaenpaa 101,156