views:

281

answers:

6

Hello everyone,

I am using VSTS 2008 + .Net 3.5 + C# + SQL Server 2008 Enterprise on Windows Server 2003. I am using the following connection string, and labtest1 is the local machine name and I connect from local machine using ADO.Net. Then it always fail with connection error. But when I change in the connection string from "labtest1" to ".", connection has no issue with the same ADO.Net client code. Any ideas what is wrong?

Data Source=labtest1;Initial Catalog=CustomerDB;Trusted_Connection=true;Asynchronous Processing=true

Here is the detailed error message I got,

System.Data.SqlClient.SqlException: 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)

thanks in advance, George

+1  A: 

Looks like a Sql Server configuration issue to me : have you tried to tune protocols in Sql Server Network configuration ? Named Pipes or TCP/IP should be enabled.

Cédric Rup
When I open SQL Server Configuration Manager, there are 5 items,1. SQL Server Services;2. SQL Server Network Configuration(32bit);3. SQL Native Client 10.0 Configuration(32bit);4. SQL Server Network Configuration;5. SQL Native Client 10.0 Configuration.Which one do you mean I should tune?
George2
I have posted my detailed error message, any ideas what is wrong?
George2
In SQL Server Network Configuration, you will find a "Protocol" node. Which protocols are enabled ? Try to enable named pipes or tcp/ip.
Cédric Rup
After enable Named Pipe, it works. Why SQL Server will use named pipe? I think TCP/IP should be used?
George2
As Dean suggested, it may be a firewall issue. Do you use a personnal firewall or Windows Firewall ?
Cédric Rup
After enable Named Pipe it works, so I do not think it is a firewall issue. I want to know why Named Pipe is used? Why TCP/IP is not used?
George2
I think Shared Memory only works this "." notation and that you've got something blocking your TCP/IP access to SQL Server. Named Pipes on local machine does not require network to function
Cédric Rup
A test you can make is to disable Named Pipes and replace your datasource by "localhost" or "127.0.0.1". If it's working, you've got something blocking your tcp port... if not, well I don't think you can assume anything ;o)
Cédric Rup
Thanks Cédric, my confusion is how to know which protocol (TCP/IP or Named Pipe) is used?
George2
+1  A: 

When you use "." or "(local)" it connects to the default instance on your PC, perhaps the SQL Server was installed with instances, in which case you have to specify the instance name in the connection string in the format "SERVER\INSTANCE_NAME".

In SQL Management studio execute this query to see your full server\instance name

select @@servername
Wez
I have tried to use SSMS to connect to "labtest1", then open a new Query Windows to execute select @@servername, the result is "LABTEST1". Seems nothing is wrong? Is server name case sensitive? Any ideas to analyze further?
George2
I have posted my detailed error message, any ideas what is wrong?
George2
So it's not instances playing tricks. tzup's suggestion would be my next step too.
Wez
After enable Named Pipe in SQL Server Configuration Manager, it works. Why SQL Server will use named pipe? I think TCP/IP should be used?
George2
+1  A: 

I have seen this issue previously with ZoneAlarms blocking the connection (on the machine trying to connect to the SQL server). I would spend some time investigating this area around firewalls etc.

Hope this helps

Dean
What is your suggestion to fix my issue?
George2
What do you mean "ZoneAlarms"?
George2
I have seen 2 examples of this. the example I saw yesterday had a significant windows update occur, ZoneAlarm then had to be reconfigured to allow the connections through again. If you have a firewall I would be checking the blocked list. Hope this helps
Dean
What is ZoneAlarm? A Windows component?
George2
ZoneAlarm is a firewall
Cédric Rup
Is it developed by Microsoft or 3rd paty?
George2
tzup
+1  A: 

Do you have the Named Pipes network protocol enabled in the network config? (In the SQL Server Configuration Manager - sql server 2005, that's what I have, might be different in 2008 - you should be able to verify this setting)

tzup
For SQL Server 2005 it's called "SQL Server Surface Area Configuration", under the "Remote Connections" section is the option to allow remote connections, not just local.
Wez
Thanks tzup! It is not enabled. After enable Named Pipe, it works. Why SQL Server will use named pipe? I think TCP/IP should be used?
George2
Hi kB, I am confused why Named Pipe is used? I think TCP/IP should be used?
George2
@George2: Check out the "SQL Native Client Configuration" in almost the same place where you enabled Named Pipes. There is a node "Client Protocols" where you can change the order of the protocols to be used. Try putting TCP/IP before Named Pipes (right-click and then "Order").
tzup
Thanks. I have enabled both TCP/IP for both client and server, but disable Named Pipe on server (while enabled Named Pipe on client). Why TCP/IP is not selected automatically since I select both client and server?
George2
+1  A: 

Try to ping by the computer name: ping labtest1. if it does not find the server, then try ping labtest1.mydomainname.com with the domain name. If this works, then you just need to add/fix the DSN aliases in the domain controller or just re-login to the machine.

van
Ping works. After enable Named Pipe in SQL Server Configuration Manager, it works. Why SQL Server will use named pipe? I think TCP/IP should be used? Any further ideas?
George2
+1  A: 

One other thins - and may not be relevant - but you've not specified the security model, at least not in the string provided as a sample.

I would expect to see: Integrated Security=True (given that its the local machine) in the connection string.

The other thing that may be relevant is - as has already been mentioned - the protocols, I'd look to make sure that TCP/IP is enabled.

Murph
TCP/IP is enabled. But Named Pipe is not enabled. After enable Named Pipe, it works. But why Named Pipe is used other than TCP/IP?
George2
Presumably because its a local connection - though to be honest I'd've expected it to fail the other way round!
Murph
Do you mean local connection will always use Named Pipe? If yes, do you have any document to prove?
George2
I have made sure for both client and server, TCP/IP is enabled (find from SQL Server Configuration Manager) for both "SQL Native Client Configuration" and "SQL Server Network Configuration". Why TCP/IP is not used in my scenario?
George2