Hi All,
Recently I solved quite tricky issue happened on D365 Test environment running in Azure.
Original task was developed and tested on local D365 environment and works well, but suddenly failed on client's environment with a message:
Exception has been thrown by the target of an invocation.A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No such host is known.)No such host is known
Exception has been thrown by the target of an invocation.
ExecuteReader requires an open and available Connection. The connection's current state is closed
The issue was that SQLSystem.loginServer() executed on Dynamics 365 for Financials and Operations connected to Azure SQL Database, returned only server name, without a full domain part "database.windows.net"
So here is working example of getting SQLClient.SQLConnection that works both with MS SQL and Azure SQL.
private System.Data.SqlClient.SqlConnection GetSQLClientConnection()
{
System.Data.SqlClient.SqlConnection con;
SQLSystem sql;
str fullServerName;
str loginConnectString, noDriverConnectString;
int beginPos, endPos;
try
{
new InteropPermission(InteropKind::ClrInterop).assert();
sql = new SQLSystem();
loginConnectString = sql.loginConnectString();
beginPos = strScan(loginConnectString, "Driver=", 0, strLen(loginConnectString));
endPos = strScan(loginConnectString, ";", beginPos, strLen(loginConnectString));
noDriverConnectString = subStr(loginConnectString, 1, beginPos-1);
noDriverConnectString = subStr(loginConnectString, endPos + 1, strLen(loginConnectString));
noDriverConnectString = strRem(noDriverConnectString, "{}");
con = new System.Data.SqlClient.SqlConnection(noDriverConnectString);
con.Open();
}
catch (Exception::CLRError)
{
//BP Deviation Documented
ex = ClrInterop::getLastException();
while(ex)
{
exceptionStr += ex.get_Message();
ex = ex.get_InnerException();
}
info(exceptionStr);
}
return con;
}
*This post is locked for comments
I have the same question (0)
