Connecting to Linked Server (External SQL Database)

Hello all,

After many attempts and support of Aras Support, we are still unable to execute SQL Stored Procedures on an external SQL database.

Context

• Aras Innovator Version 26
• SQL Server 2017

Goal

Execute a Stored Procedure on an external SQL server (NOT the Aras SQL server itself) from a C# Server Method. 

Attempts

1. Making a connection using Microsoft.Data.SqlClient and the legacy System.Data.SqlClient. This involves using a connection string and executing a plain command. 
   
   1. Used code: 
      [embed:dc8ab71f-3b98-42d9-b0f6-e21e02a0f8e2:f6796336-b0f8-4284-bca3-4d7d91f97dd5:type=csharp&text=Innovator%20inn%20%3D%20this.getInnovator%28%29%3B%0A%0Astring%20connectionString%20%3D%20%40%22Data%20Source%3DServerName%5CInstanceName%3BInitial%20Catalog%3DDatabaseName%3BUser%20ID%3DUserID%3BPassword%3DPassword%3BConnection%20Timeout%3DTimeoutValue%3B%22%3B%0Astring%20sqlQuery%20%3D%20%40%22Exec%20%5BServerName%5CInstanceName%5D.%5BDatabaseName%5D.%5Bdbo%5D.%5BStoredProcName%5D%20%40Variant%20%3D%20VariantValue%2C%20%40State%20%3D%20%27StateValue%27%2C%20%40ID%20%3D%20%27GUID%27%2C%20%40Test%3DTestValue%3B%22%3B%0A%0A%0Ausing%20%28Microsoft.Data.SqlClient.SqlConnection%20connection%20%3D%20new%20Microsoft.Data.SqlClient.SqlConnection%28connectionString%29%29%0A%7B%0Aconnection.Open%28%29%3B%0A%0Ausing%20%28Microsoft.Data.SqlClient.SqlCommand%20command%20%3D%20new%20Microsoft.Data.SqlClient.SqlCommand%28sqlQuery%2C%20connection%29%29%0A%7B%0Aobject%20result%20%3D%20command.ExecuteScalar%28%29%3B%20%2F%2F%20Executes%20query%20and%20returns%20the%20first%20column%20of%20the%20first%20row%0A%0Aif%28result%20%21%3D%20null%29%0A%7B%0Areturn%20inn.newError%28result.ToString%28%29%29%3B%20%2F%2F%20Logs%20the%20value%0A%7D%0Aelse%0A%7B%0Areturn%20inn.newError%28%22No%20result%20found.%22%29%3B%0A%7D%0A%7D%0A%7D]
   2. Result: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
2. Adding the external server as 'Linked Server' to the Aras SQL server. Then using innovator.applySQL() referencing the external database.
   1. Used code: [embed:dc8ab71f-3b98-42d9-b0f6-e21e02a0f8e2:4f6ce192-841a-4134-8fa4-3bb5694c5708:type=csharp&text=Innovator%20inn%20%3D%20this.getInnovator%28%29%3B%0A%0Astring%20sqlQuery%20%3D%20%40%22Exec%20%5BServerName%5CInstanceName%5D.%5BDatabaseName%5D.%5Bdbo%5D.%5BStoredProcName%5D%20%40Variant%20%3D%20VariantValue%2C%20%40State%20%3D%20%27StateValue%27%2C%20%40ID%20%3D%20%27GUID%27%2C%20%40Test%3DTestValue%3B%22%3B%0A%0AItem%20response%20%3D%20inn.applySQL%28sqlQuery%29%3B]
   2. Result: This makes the browser client crash and we suspect another timeout in the back-end. 

Question

Does anyone have a method (or workaround) to execute SQL on an external SQL server (NOT the Aras SQL server itself) from a C# server method?

Best regards,

Daan