Finance | Project Operations, Human Resources, ...

Windows authentication for external Sql Server

(0) Share

Report

Posted on by Kanchan KB

Hello

I want to create a batch job in D365 which will sent the pickroutes in AX to external DB, but since D365 uses aad authentication for SQL server how is it possible.

I have created runnable class and added connection string with integrated security as SSPI but I am getting anonymous login issues.

I have the same question (0)

All responses (9)

Answers (0)

Suggested answer

A.Prasanna 8,223 on at

Like (0)

Report

I think you will need to setup ODBC connection.

Just look at this sample : docs.microsoft.com/.../how-to-connect-to-an-external-database-from-x-code

Was this reply helpful? Yes No
Martin Dráb 237,959 Most Valuable Professional on at

Like (1)

Report

What kind of authentication is used by D365FO is irrelevant in your case, since you don't want to call D365FO. You want to authenticate with SQL server.

I would avoid the old OdbcConnection mentioned above - it's cumbersome and error-prone. There are many nice .NET APIs for working with databases (e.g. Entity Framework) that you can call from D365FO.

Was this reply helpful? Yes No
Kanchan KB 15 on at

Like (0)

Report

Martin,

Thanks but when I try to connect, using SQLCLient, I receive below error

"Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'."

My Sample code:

public static void main(Args _args)

{

str connectionString;

str sqlInsert;

connectionString = strFmt(@'Data Source=TestServerName;Initial Catalog=TestDB;Integrated Security=SSPI;');// Min Pool Size = 5; Max Pool Size=100; ');

SqlConnection conn = new SqlConnection(connectionString);

//InteropPermission( InteropKind::ClrInterop ).assert();

try

{

conn.Open();

if(conn)

{

sqlInsert = strFmt('Insert into TestDB([Id], [name]'

+" ) Values('%1','%2')",

"testvalue1", //%1

"testvalue2" //%2

); //%25

new System.Data.SqlClient.SqlCommand(sqlInsert, conn).ExecuteNonQuery();

Info("data added succesfully");

}

}

catch(Exception::Error)

{

error(infolog.text());

}

Was this reply helpful? Yes No
WillWU 22,361 on at

Like (1)

Report

Hi Kanchan,

Please try to use SQL server credential to connect instead of Windows Authentication.

Have a look at this blog:

https://allaboutmsdynamics.wordpress.com/2019/02/16/d365-ax7connect-to-an-external-sql-database-using-x/

Was this reply helpful? Yes No
Martin Dráb 237,959 Most Valuable Professional on at

Like (0)

Report
Your code is very difficult to read - please use Insert > Insert Code (in the rich formatting view to paste source code). Look at the result:

public static void main(Args _args) { str connectionString = strFmt(@'Data Source=TestServerName;Initial Catalog=TestDB;Integrated Security=SSPI;');// Min Pool Size = 5; Max Pool Size=100; '); SqlConnection conn = new SqlConnection(connectionString); try { conn.Open(); if(conn) { str sqlInsert = strFmt('Insert into TestDB([Id], [name]' " ) Values('%1','%2')", "testvalue1", //%1 "testvalue2" //%2 ); //% new System.Data.SqlClient.SqlCommand(sqlInsert, conn).ExecuteNonQuery(); info("data added succesfully"); } } catch (Exception::Error) { error(infolog.text()); } }

Does it work when you use username and password instead of integrated security?

Where is the SQL server? Are you calling a SQL Server instance on your local servers from a Service Fabric cluster?

Was this reply helpful? Yes No
Kanchan KB 15 on at

Like (0)

Report

Hi Martin,

Yes it is working with the integrated security = false and adding username and password (i.e. sql server authentication).

The external SQL server is in same domain but on different server, which I am able to connect and operate through SSMS.

Was this reply helpful? Yes No
Martin Dráb 237,959 Most Valuable Professional on at

Like (0)

Report

I'm still not sure about your scenario. Are you talking about making a connection from a Service Fabric cluster using the account used for the web server hosting an on-premises deployment of D365FO?

Also, you tested something else than what I meant (sorry, I didn't say it in the right way). I didn't mean switching to SQL Server Authentication mode (which generally shouldn't be used); I meant using Windows Authentication and providing credentials explicitly (for the account you're trying to use) instead of depending on them being passed automatically. If you prefer composing connection strings manually (rather than using SqlConnectionStringBuilder, for instance), it'll be something like Data Source=TestServerName;Initial Catalog=TestDB;Integrated Security=SSPI;User ID=myDomain\d365foServiceAccount;Password=myPassword;.

Was this reply helpful? Yes No
Kanchan KB 15 on at

Like (0)

Report

Martin,

No I was not talking abut the service fabric cluster,

And Using windows authentication along with the account is working for me. Thanks

Was this reply helpful? Yes No
wafaa mostafa kamel 10 on at

Like (0)

Report

When i uesd this code there was an stange error

Error The name 'SqlConnection' does not denote a class, a table, or an extended data type.

can you please explain why SqlConnection Class doesn't appear

Was this reply helpful? Yes No