What might cause linked server query to fail when executed using SqlCommand?

Question

Background

My C# application is a Windows service which allows a user to configure SQL queries that involve linked servers to move data around similar to an ETL (Extract Transform Load) application. The queries have been tested directly in SSMS and work without any problems.

Below is an example query inserting data from the LocalDB database on the local server to the RemoteDB database on the RemoteServer. The RemoteServer configuration is listed with it:

EXEC master.dbo.sp_addlinkedserver @server = N'RemoteServer', @srvproduct=N'SQL Server'

INSERT INTO RemoteServer.RemoteDB.dbo.tst_Address (Address, FirstName, LastName, DateOfBirth)
    SELECT t1.Address, t1.FirstName, t1.LastName, t1.DateOfBirth  
    FROM LocalDB.dbo.tst_Address t1 
    LEFT JOIN RemoteServer.RemoteDB.dbo.tst_Address t2 ON t2.Address = t1.Address
    WHERE t2.Address IS NULL 

Problem

When executed directly from SSMS there is no issue, but when executed from the c# application using SqlCommand.ExecuteNonQuery() I find various errors depending on which computer I am running the application from. For example, one exception I have on hand is

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

Another computer gives:

Named Pipes Provider: Could not open a connection to SQL Server [5]. OLE DB provider "SQLNCLI11" for linked server "RemoteServer" returned message "Login timeout expired". OLE DB provider "SQLNCLI11" for linked server "RemoteServer" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".

Named pipes is enabled and the timeouts are occurring in about 5-10 seconds.

The login is a windows user that is in the sysadmin role on both servers, but I have also tried setting an SQL user via sp_addlinkedsrvlogin but it makes no difference to the exceptions thrown.

I cannot see why there should be any difference as I expect SqlCommand to be sending the SQL to the SQL Server service to process. What am I missing?

Answer 1

So it appears there were two issues here:

1. that queries executed in SSMS behaved differently to those in .NET via SqlCommand, and
2. that MSDTC requires more knowledge than just where to tick the box

SSMS vs .NET

When I was testing via SSMS I was trying to replicate the .NET approach as best possible.

.NET code

var queryText = @"INSERT INTO RemoteServer.RemoteDB.dbo.tst_Address (Address, FirstName, LastName, DateOfBirth)
    SELECT t1.Address, t1.FirstName, t1.LastName, t1.DateOfBirth  
    FROM LocalDB.dbo.tst_Address t1 
    LEFT JOIN RemoteServer.RemoteDB.dbo.tst_Address t2 ON t2.Address = t1.Address
    WHERE t2.Address IS NULL"
using (var trans = cxn.BeginTransaction())
{
    try
    {
        var cmd = this.BuildSqlCommand(queryText, parameters);
        cmd.ExecuteNonQuery();
        trans.Commit();
    }
    catch (Exception ex)
    {
        logger.Error(ex);
        throw;
    }
}

SSMS query

begin tran
begin try

INSERT INTO RemoteServer.RemoteDB.dbo.tst_Address (Address, FirstName, LastName, DateOfBirth)
    SELECT t1.Address, t1.FirstName, t1.LastName, t1.DateOfBirth  
    FROM LocalDB.dbo.tst_Address t1 
    LEFT JOIN RemoteServer.RemoteDB.dbo.tst_Address t2 ON t2.Address = t1.Address
    WHERE t2.Address IS NULL
commit

end try
begin catch

    SELECT  
        ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ,ERROR_PROCEDURE() AS ErrorProcedure  
        ,ERROR_LINE() AS ErrorLine  
        ,ERROR_MESSAGE() AS ErrorMessage;  
    if @@TRANCOUNT > 0
        rollback

end catch

The catch with SSMS is that by default the query is not required to be distributed and doesn't involve the DTC unless it is explicitly told to by using begin distributed tran. Once I added the distributed keyword then SSMS also kindly failed with similar errors to .NET.

DTC configuration

The well enough documented tick boxes are found in Component Services (dcomcnfg from command line) and the typical settings are shown below.

What caught me out was that the Windows firewall rules, while preexisting, are disabled by default. Potentially the difference between the two computers that were attempting to access the RemoteServer was that one had the firewall disabled so was not blocked by the outbound rule, but both were blocked by the inbound rule disabled on the RemoteServer. Here are the outbound rules

and inbound rules that are shown now enabled.