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: 

      Innovator inn = this.getInnovator();
      
      string connectionString = @"Data Source=ServerName\InstanceName;Initial Catalog=DatabaseName;User ID=UserID;Password=Password;Connection Timeout=TimeoutValue;";
      string sqlQuery = @"Exec [ServerName\InstanceName].[DatabaseName].[dbo].[StoredProcName] @Variant = VariantValue, @State = 'StateValue', @ID = 'GUID', @Test=TestValue;";
      
      
      using (Microsoft.Data.SqlClient.SqlConnection connection = new Microsoft.Data.SqlClient.SqlConnection(connectionString))
      {
      connection.Open();
      
      using (Microsoft.Data.SqlClient.SqlCommand command = new Microsoft.Data.SqlClient.SqlCommand(sqlQuery, connection))
      {
      object result = command.ExecuteScalar(); // Executes query and returns the first column of the first row
      
      if(result != null)
      {
      return inn.newError(result.ToString()); // Logs the value
      }
      else
      {
      return inn.newError("No result found.");
      }
      }
      }

   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: 

      Innovator inn = this.getInnovator();
      
      string sqlQuery = @"Exec [ServerName\InstanceName].[DatabaseName].[dbo].[StoredProcName] @Variant = VariantValue, @State = 'StateValue', @ID = 'GUID', @Test=TestValue;";
      
      Item response = inn.applySQL(sqlQuery);

   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