Execute SQL scripts on Agent Server

[antony.booth_1029]

I am trying to run a deployment plan that executes sql scripts on a Production database, via the agent on a Production server. I know the Production server can connect to SQL server, because it's working.

The Buildmaster server has no rights to connect to the database server directly, which is why the action is nested within an agent context.

The connection string has been verified, but I still get an error: -

ERROR: 2019-01-25 20:17:46Z - Unhandled exception: System.Data.SqlClient.SqlException (0x80131904): 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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) ---> System.ComponentModel.Win32Exception (0x80004005): The network path was not found
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Inedo.Agents.InedoAgentClientBase.<SendMessageAsync>d__28.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Inedo.Agents.InedoAgentClientBase.<ExecuteCommandAsync>d__26`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Inedo.BuildMaster.Extensibility.Operations.Database.ExecuteChangeScriptsOperation.<ExecuteAsync>d__8.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Inedo.BuildMaster.Windows.ServiceApplication.Executions.PlanExecuter.BuildMasterExecuter.<ExecuteActionAsync>d__41.MoveNext()
ClientConnectionId:00000000-0000-0000-0000-000000000000

The error indicates the server is unreachable. It shouldn't be unreachable from the Production server, so my question is; Regardless of the location of the action withing the workflow, do sql script always execute from the Buildmaster server and not on an Agent?

for server Production
{
    with async
    {
        # Does this actually run on server: 'Production', because it doesn't appear to!
        Execute-ChangeScripts
        (
            Connection: Production Server,
            InitializeDatabase: true
        );
    }
}

Product: BuildMaster
Version: 6.1.0

[thoven]

That operation will be remoted to the server in context, so in your case, Production. Are the SQL Server services are running on the remote machine? Are you certain the connection string is correct? Oftentimes the instance name is missing e.g. Data Source=prod.db-01\SQLEXPRESS;Initial Catalog...

[antony.booth_1029]

The SQL server and the application are both on server 'Production' and the application is successfully retrieving data from the SQL server. The connection string used in the application configuration is the same connection string being used in the SQL connection in Buildmaster. It is using a SQL account, so not integrated.

Here is a redacted connection string: -

Data Source=*************;Initial Catalog=*************;User Id=*************;Password=*************;MultipleActiveResultSets=true;