c# System.Data.SqlClient.SqlException (0x80131904)

Question

Using c# .net 4.6.1 I have a program where I'm trying to query a MS SQL Server 2016 database. I have 2 remote servers, Server A and Server B. My c# program runs on Server A with no problems. When I run the program on Server B I get the following error:

System.Data.SqlClient.SqlException (0x80131904): A network-related or instance-s pecific 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 a nd that SQL Server is configured to allow remote connections. (provider: Named P ipes Provider, error: 40 - Could not open a connection to SQL Server) ---> Syste m.ComponentModel.Win32Exception (0x80004005): Access is denied at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdent ity identity, SqlConnectionString connectionOptions, SqlCredential credential, O bject providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData r econnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTra nsientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOp tions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConn ectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)

at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConn ectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConne ctionPoolKey poolKey, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owning Object, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection o wningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection ) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection ow ningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean o nlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& co nnection) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection ow ningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbCon nectionInternal& connection) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions , DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(Db Connection outerConnection, DbConnectionFactory connectionFactory, TaskCompletio nSource1 retry, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 re try) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.Open() at YetAnotherSmallConsoleApp.Program.Main(String[] args) in C:\Repository\Pro jects\YetAnotherSmallConsoleApp\YetAnotherSmallConsoleApp\Program.cs:line 28 ClientConnectionId:00000000-0000-0000-0000-000000000000

Here is my program:

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.IO;

namespace YetAnotherSmallConsoleApp
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection con = new SqlConnection("Server = IP_ADDRESS; User Id = USER_NAME; Password = PASSWORD;");
            string[] arrExcludeLines = File.ReadAllLines(FILEPATH_TO_QUERY);
            string strExcludeQuery = default(string);
            foreach (string line in arrExcludeLines)
            {
                strExcludeQuery = strExcludeQuery + line + Environment.NewLine;
            }
            try
            {
                using (con)
                {
                    con.Open();
                    SqlCommand cmdExclude = new SqlCommand(strExcludeQuery, con);
                    SqlDataReader dr1 = cmdExclude.ExecuteReader();
                    while (dr1.HasRows)
                    {
                        while (dr1.Read())
                        {
                            Console.WriteLine(dr1[0].ToString());
                            Console.ReadLine();
                        }
                        dr1.NextResult();
                    }
                }
            }
            catch(Exception ex)
            {
                Console.WriteLine(ex.ToString());
                Console.ReadLine();
            }
        }
    }
}

The error is thrown from the line that reads con.Open(); . Here's a few facts about the servers:

1. Both Server A and Server B are on the same network, and this network is not same network my MS SQL Server 2016 database is on.
2. Server A has an instance of MS SQL Server 2012 installed on it, Server B does not have MS SQL Server installed on it.
3. Even though my program runs correctly on Server A I can't ping the IP Address of the MS SQL Server 2016 database server from either Server A nor Server B.
4. There are no firewalls on Server A or Server B.
5. Both Server A and Server B are running MS Server 2012 R2.

These are the only things I can think of to compare between Server A and Server B. Obviously there is something different between Server A and Server B that is allowing the program to run successfully on Server A. I don't know if there is something configured differently on Server A that is configured differently on Server B or if there is something configured on my MS SQL Server 2016 database server that allows Server A to connect to the database successfully when the program is ran but prevents the program from connecting to the database and running successfully on Server B. If someone could give me any suggestions on how to troubleshoot this to be able to get the program to run on Server B I would greatly appreciate it. Thanks in advance.

EDIT: when I try to connect to the MS SQL Server 2016 database from Server B using the same credentials I use to connect to the database from Server A using SSMS I get the following error:

EDIT: one other thing: I've tried to run the program with the computer name for the MS SQL Server 2016 database server in the connection string as well as the actual IP address for the database server in the connection string, both ways result in being able to run the program successfully on Server A but I get the error on Server B.

Answer 1

This type of error must surely be resulting from a wrong connection string. Confirm to make sure the connection strings are the same for the servers,

Answer 2

Try this:

• Right click on cmd
• chose run as administrator
• then type in the command prompt:

mofcomp "%programfiles(x86)%\Microsoft SQL Server\140\Shared\sqlmgmproviderxpsp2up.mof"

140 is the version of your sql server, change this for the correct version you are trying to use.

This works for me but I'm not secure that's be your problem