Unable to connect to SQL Server in a C# program but system is able to connect

Question

I am attempting to connect to a SQL Server 2014. A week or so ago it seemed to work, now it does not. The systems team did upgrade recently to 2014. What's weird is that I'm able to use SQL Server Management Studio to connect, ODBC, and telnet. It's just an issue with C#. Additionally, this exact program runs on another server as compiled/release. It's just not running on my system using debug (or fully compiled/release).

I checked through the documentation -- disabled all the firewalls, had STP check the server... nada

Error:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: 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)

My C# code:

namespace SQLTest
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["SQLConn"].ConnectionString;
            string OrganizationSqlStr = @"SELECT * FROM school";

            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand command = new SqlCommand(OrganizationSqlStr, connection);
            connection.Open();

            SqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                Console.Write(reader["School_ID"] + "  |  ");
                Console.Write(reader["title"] + "\n\r");
            }

            Console.ReadKey();
        }
    }
}

app.config:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" />
    </startup>
    <connectionStrings>
        <add name="SQLConn" 
             connectionString="server=SERVER;database=DATABASE;uid=USERNAME;password=PASSWORD"/>
    </connectionStrings>
</configuration>

Maybe an obvious question or comment, but did you replace `DATABASE`, `USERNAME` and `PASSWORD` with actual values, in your connection string? — Olivier De Meulder, Jul 06 '16 at 15:58
@HarporSydney are you connecting to the SQL server via a SQL account or using your windows credentials? If using the SQL account, is your database configured to allow mixed authentication? — user1666620, Jul 06 '16 at 16:30

score 1 · Accepted Answer · edited May 23 '17 at 12:33

1

I would try to force the .net program to use tcp

http://www.connectionstrings.com/sqlconnection/

find the section labeled

Connect via an IP address

Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;
Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;

Network Library=DBMSSOCN is the magic sugar.

see also:

What does DBMSSOCN stand for?

APPEND:

If that still produces an error, then something is probably blocking your access.

Here is the in-general tool to determine.

https://www.microsoft.com/en-us/download/details.aspx?id=24009

Specify the IP and Port Number, and it'll tell you it the port is blocked or filtered.

My personal history is that we had a new router installed that was filtering the port number and stopping my sql server access. The portqueryui revealed the issue. PortQry works "outside" of sql server, so the network guys can't blame sql-server for the lack of connectivity.

APPEND

The OP posted this in the comments of this answer

portqry.exe -n db-alexandrite.domain.edu -e 1433 -p TCP exits with return code 0x00000000

This is your problem. "db-alexandrite.domain.edu" is not resolving.

You can fix it by either changing "db-alexandrite.domain.edu" to be the actual IP address of that machine.

OR figuring out why "db-alexandrite.domain.edu" does not resolve.

edited May 23 '17 at 12:33

Community

1
1

answered Jul 06 '16 at 16:22

granadaCoder

26,328
10
113
146

Ok--It appears systems has an issue with urning on Pipes. I tried the string and got this error (is there anything you suggest?) An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll Additional information: 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: TCP Provider, error: 0 - An invalid argument was supplied.) – Harpor Sydney Jul 07 '16 at 13:59
I appended my answer. – granadaCoder Jul 07 '16 at 14:06
You may want to look at the accepted answer for this question (to know which port your sql server is running on) : http://stackoverflow.com/questions/12297475/how-to-find-sql-server-running-port – granadaCoder Jul 07 '16 at 14:45
first let me just say thank you for all your help. I really appreciate it--i'm like super stressed out because of this lol. Ok, I ran the prog--got back TCP port 1433 (ms-sql-s service): LISTENING portqry.exe -n db-alexandrite.domain.edu -e 1433 -p TCP exits with return code 0x00000000.. I tried telnet, sql studio, and ODBC--all work. Is it possible that it's my actual ide? my current connection string is this: – Harpor Sydney Jul 07 '16 at 15:11
the error is An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll Additional information: 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: TCP Provider, error: 0 - A non-recoverable error occurred during a database lookup.) – Harpor Sydney Jul 07 '16 at 15:11
Did you try PortQueryUI? A error like this "A network-related or instance-specific error occurred while establishing a connection to SQL Server." basically is the check-engine light of sql server. It means " something went wrong". Please report PortQueryUI results. PS Why is your "Initial Catalog" value "DBserver"? It should be a database name. – granadaCoder Jul 07 '16 at 15:14
Sorry i didnt include the full error: this is what i got back: TCP port 1433 (ms-sql-s service): LISTENING portqry.exe -n db-alexandrite.###.edu -e 1433 -p TCP exits with return code 0x00000000. – Harpor Sydney Jul 07 '16 at 15:15
Ok, that is weird. And if that passes, ODBC passes.......then this is a weird one. Here is another rabbit to chase. Try (temporarily) building your connection string with the "builder" to make sure its not a syntax error. http://www.c-sharpcorner.com/blogs/how-to-use-sqlconnectionstringbuilder-in-c-sharp1 – granadaCoder Jul 07 '16 at 15:25
And to go over the basics. Are you running sql-server on the default instance, or on a named-instance. And you understand that "Initial Catalog" is the name of the database.......not the SqlServer(?Instance) .. correct? – granadaCoder Jul 07 '16 at 15:27
portqry.exe -n db-alexandrite.domain.edu -e 1433 -p TCP exits with return code 0x00000000 .. This is what you need to follow. This is an error. In your connection string, put the IP address. If that works.......then you have a name->ip-address resolution problem. That would make sense in all this weirdness you have going on. – granadaCoder Jul 07 '16 at 16:03
Ok--after much troubleshooting, It appears to my related to my VDI--something's up with it. I'm going to go ahead reinstall VS then if that doesn't work just reimage it and see if that fixes it. I'll keep you updated. and thanks again. :) – Harpor Sydney Jul 07 '16 at 18:36
Keep PortQuery tool in your backpocket. It will take you to the root of the issue more often than not. I don't think a VS reinstall will help....but give it a shot I guess. – granadaCoder Jul 07 '16 at 18:51

score 0 · Answer 2 · answered Jul 06 '16 at 16:02

0

Ask your team to check whether they have enabled all network protocols the server. Either tcp or named pipe protocol should be enabled.

answered Jul 06 '16 at 16:02

Udo

449
3
13

score 0 · Answer 3 · answered Jul 06 '16 at 16:14

This error is helpful:

(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

It seems that your connection string, or SqlConnection by default, wants to use the named pipe provider to connect to the SQL server.

Given that your server was recently changed/upgraded, it's certainly possible that the named pipe provider was disabled. It's a common mistake.

You can force your connection to use simple TCP instead of named pipe by altering your connection string:

server=tcp:mysqlserver.domain.com;...

score 0 · Answer 4 · answered Jul 06 '16 at 16:22

I recently set up a new test server with sql and went through the usual checklist.

Make sure remote connections are allowed.
Make sure you select integrated mode is using both Windows and Sql Server logins.
Make sure port 1440/your port and tcp/your protocol is enabled in sql configuration manager.
Make sure your firewall allows incoming and outgoing.
Make sure your connection string is valid.

These 4 steps will eliminate most connection issues. However, on new servers, the Windows Defender will block by default. I had to disable private and public profiles in defender as it was blocking. Since we do not use windows defender this was ok. However, if you rely on it then you need to create a rule.

score -1 · Answer 5 · answered Jul 06 '16 at 16:06

-1

<add name="SQLConn" connectionString="Data Source=SERVER;Integrated Security=False;User ID=USER;Password=PASSWORD;Initial Catalog=DATABASE" providerName="System.Data.SqlClient" />

answered Jul 06 '16 at 16:06

Tomasz

135
1
9

2

This answer is not useful. What is the context for this data? Is it supposed to go in a configuration file? How do you propose that this will solve OP's problem? You need to provide more information. – antiduh Jul 06 '16 at 16:09

Unable to connect to SQL Server in a C# program but system is able to connect

5 Answers5