7

I'm seeing the following error when attempting to open a DB connection from within my C# application. I realize this error has probably shown up on 100's of questions before. However, in this scenario the error is only showing up on C# apps running on my specific desktop PC. I've scoured the internet via Google and within this website, but I can't find a solution.

ERROR:

"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)"

WHY THIS SCENARIO IS UNIQUE:

I am able to connect to the SQL server from SQL Server Management Studio (SSMS). This application works on another computer and is able to establish a connection to the SQL server from there. So this error scenario is specific to C# applications running on my specific desktop PC. Other apps work (SSMS). Other PC's work.

When I "sniff the wire" using WireShark, the trace shows me that my app is trying to connect via NamedPipes (i.e. \Server\IPC$). I can't seem to force it to use TCP/IP.

THINGS I'VE TRIED:

  • Re-installed.NET Framework
  • Re-installed Visual Studio (C# - Express version 2010)
  • Created an alias within cliconfg.exe.

Is there something I missed?

Here are Connection Strings I've tried...

Data Source=<servername>;Initial Catalog=HIE;Integrated Security=true
Server=tcp:10.240.11.81;Integrated Security=SSPI; database=HIE
Data Source=10.240.11.81,1433;Network Library=DBMSSOCN;Initial Catalog=HIE;User ID=<SqlUserIdThatISetUpAsSysAdmin>;Password=<password>

Here's the code snippet:

_conn = new SqlConnection();    // _conn declared globally
_conn.ConnectionString = <the connection string above>;
_conn.Open();
John Saunders
  • 160,644
  • 26
  • 247
  • 397
Jay Lee
  • 71
  • 1
  • 3
  • 1
    Put the `tcp:` also in for the `Data Source`. Hard code the connection string into the C# source code. Your wireshark debugging was very good because it shows that it is not using tcp. – usr Jul 11 '13 at 22:04
  • Thanks for the quick reply @usr. Man.. you guys are FAST! I tried "Data Source=TCP:;Initial Catalog=HIE;Integrated Security=True" and "Data Source=TCP:10.240.11.81;Initial Catalog=HIE;Integrated Security=True" and got new messages: (provider: TCP Provider, error: 0 - A non-recoverable error occurred during a database lookup.) and (provider: TCP Provider, error: 0 - An invalid argument was supplied.) Google gave me nothing on these error messages. The "HIE" database does exist on the server. – Jay Lee Jul 11 '13 at 22:27
  • That is crazy. The error points to very weird conditions according to Google. Can you telnet the SQL Server using that IP and port 1433? Is the app being run as an administrator? Turn off UAC or run elevated please. Please run Wireshark again. What does that turn out? – usr Jul 11 '13 at 22:34
  • @usr - I can telnet to port 1433 on that server. Wireshark is showing some strange behavior on the wire. When I try opening the SqlConnection using TCP provider (Network Library=DBMSSOCN or Data Source=TCP:HHVSVTSQL01) , there is NO network activity. Just to be sure, I switched back to the original ConnectionString and WireShark again shows NamedPipes access attempts (SMB protocol). This is CRAZY. .NET's not even trying to connect using TCP provider. I'm going to run a .NET Framework update (Windows Update) just to factor out .NET Framework issues. I'd appreciate any other ideas. – Jay Lee Jul 11 '13 at 23:39
  • @JayLee: I strongly suggest that you run cliconfg on your machine to make sure tcp/ip library is enabled. You should also break on the assignment of connectionstring in your code in order to be 100% certain the connection string isn't being overwritten by other code. One other important thing to know is what type of application this is being run in. If it is a desktop application, then it should be fine; if it is IIS, then integrated security could be a problem. – competent_tech Jul 11 '13 at 23:45
  • Did you really use the IP in the first example or was it a DNS name like in the last comment of yours? Use a fixed IP to rule out DNS issues. Also, I think there is something called ADO.NET tracing. – usr Jul 12 '13 at 11:01
  • @competent_tech. Thanks. I did run cliconfg early on, enabling only TCP/IP and setting up an alias for the server. ADO.NET does not seem to look at the settings in cliconfg. – Jay Lee Jul 15 '13 at 16:16
  • @usr - I did try connecting using both hostname and IP (to factor our DNS issues). Still no go. Nothing shows up on the wire. This is the strangest thing I've EVER seen. I've done ADO.NET programming hundreds of times, but this is the first I've seen of this type of issue. The only thing I can guess is that my .NET framework is corrupt. I'm going to try re-installing .NET 4.0 (again). Banging my head against the wall... – Jay Lee Jul 15 '13 at 16:19
  • As a final debugging step try running this in a clean virtual machine... – usr Jul 15 '13 at 16:52
  • The program works from other machines. But I need to run it on my machine because it's got some horsepower (16GB RAM). I also just tried connecting to another SQL server and it works (using the default named pipes provider! - confirmed via WireShark as SMB protocol), which is weird since I cannot access \\theotherserver\IPC$ . Thanks @usr. I'm going to do some more server-side troubleshooting before I ask any more questions here... – Jay Lee Jul 15 '13 at 16:56

3 Answers3

2

The most likely scenario is that Windows Firewall is block the SQL Server communication. From MSDN (an article about named pipes, but relevant nonetheless):

Microsoft Windows XP Service Pack 2 enables Windows Firewall, which closes port 445 by default. Because Microsoft SQL Server communicates over port 445, you must reopen the port if SQL Server is configured to listen for incoming client connections using named pipes. For information on configuring a firewall, see "How to: Configure a Firewall for SQL Server Access" in SQL Server Books Online or review your firewall documentation.

Another scenario is that the client configuration on the local machine is not configured correctly. From the run prompt, you can execute cliconfg (the SQL Server Client Configuration Utility) to see the enabled protocols and the aliases that are used for the protocols.

competent_tech
  • 44,465
  • 11
  • 90
  • 113
  • Yep. Been bitten by port 445 being closed a few times and each time seems to take an epiphany to realize the firewall needs updating. – Metro Smurf Jul 11 '13 at 22:48
  • This does not explain the first error (no TCP connection was even attempted). Secondly I see no relation to the new error messages the OP receives. It is not like "connection refused" or something like that. – usr Jul 11 '13 at 22:51
  • I've addressed firewall and cliconfg issues. It's not a firewall issue - as SSMS on my machine (where my program is failing to connect) is able to connect to the SQL server (it's remote, not on my box). ADO.NET did not appear to use cliconfg settings. I had to explicitly name the TCP provider (Network Library=DBMSSOCN or Data Source=TCP:HHVSVTSQL01) in order to get ADO.NET to use it. But I started getting a different error message with no traffic showing up on the wire. – Jay Lee Jul 15 '13 at 16:22
  • There were the error messages: (provider: TCP Provider, error: 0 - A non-recoverable error occurred during a database lookup.) and (provider: TCP Provider, error: 0 - An invalid argument was supplied.) – Jay Lee Jul 15 '13 at 16:54
0

Either redefine your Data Source as suggested by usr's comment, or configure your SQL Server to allow TCP connections (Why would you not allow TCP connections anyway?).

Here is the link to enable TCP on your SQL Server (The accepted answer is where you want to look at):

Enable remote connections for SQL Server Express 2012

Community
  • 1
  • 1
Fabian Bigler
  • 10,403
  • 6
  • 47
  • 70
  • The client lib is not even using TCP according to Wireshark. The server's TCP endpoint cannot be the problem. – usr Jul 11 '13 at 22:09
  • @usr Weird. I had this error before and I always resolved it by configuring the SQL Server properly. – Fabian Bigler Jul 11 '13 at 22:12
  • @usr Just have a look at the link please. The other user had the exact same issue and apparently it was resolved by configuring the SQL Server properly. – Fabian Bigler Jul 11 '13 at 22:13
  • 1
    He had the same *error message* but not the same problem. He needed to reconfigure the server to allow a TCP connect. Wireshark shows that this is not the problem here. ADO.NET does not even try to connect. – usr Jul 11 '13 at 22:17
  • Btw, the error message also says that named pipes are being used. Not TCP. – usr Jul 11 '13 at 22:18
  • Fair enough. My bad then, sorry! – Fabian Bigler Jul 11 '13 at 22:19
0

Are you running your code off of local disk or a network drive? I encountered a very similar problem where my network drive was not trusted in .Net and that was why my connections failed.

Codure
  • 754
  • 1
  • 7
  • 12