Which protocols are used when connecting using SQLClient if none are specified?

[JimLad]

Hi,

<add name="ConnectionString"
connectionString="Data Source=xyz;Initial Catalog=xyz;Persist Security
Info=True;User ID=xyz;Password=xyz"
providerName="System.Data.SqlClient"/>

We are having connectivity problems between the IIS web server and the
db server. There is a firewall in between them.

SQL Server 2000 is listening on 1433 and only TCP/IP is enabled. The
error message mentions named pipes:

Exception type: SqlException
Exception message: An error has occurred while establishing a
connection to the server. When connecting to SQL Server 2005, this
failure may be caused by the fact that under the default settings SQL
Server does not allow remote connections. (provider: Named Pipes
Provider, error: 40 - Could not open a connection to SQL Server)

ASP.NET is running under NETWORK SERVICE.

I'm pretty sure it's not a problem with the connectionstring, but can
anyone see anything wrong here? Also which protocols does the
System.Data.SqlClient provider use by default and in which order they
are tried?

Cheers,

James

 

[LVP]

It looks like the problem is on your SQL server side.
Under the properties window select Connections and make sure that
"Allow remote connections to this server" is checked.
that might help

 

[sloan]

You can define and force the connection to use tcp/ip (dbmssocn) via the
connection string:

http://www.connectionstrings.com/article.aspx?article=howtodefinewichnetworkprotocoltouse

Also:
Go here
http://www.connectionstrings.com/?carrier=sqlserver2005
search for "1433" and it'll show you how to specify the port number.

........

As already mentioned, in 2005 you explicitly have to setup Sql Server
Configuration Tool to allow remote connections.
Its turned off as the default.

 

[JimLad]

You can define and force the connection to use tcp/ip (dbmssocn) via the
connection string:

http://www.connectionstrings.com/article.aspx?article=howtodefinewich...

Also:
Go herehttp://www.connectionstrings.com/?carrier=sqlserver2005
search for "1433" and it'll show you how to specify the port number.

.......

As already mentioned, in 2005 you explicitly have to setup Sql Server
Configuration Tool to allow remote connections.
Its turned off as the default.












- Show quoted text -

Hi guys,

Thanks for that. I did mention that I'm using SQL Server 2000, and I
think that makes quite a lot of difference. Remote connections
certainly aren't disabled. And I really want to know what protocols it
uses by default.

I believe I can also select tcp/ip by prefixing tcp: in the data
source. You mentioned Network Library=DBMSSOCN. Which is better?

Cheers,

James

 

[JimLad]

Hi guys,

Thanks for that. I did mention that I'm using SQL Server 2000, and I
think that makes quite a lot of difference. Remote connections
certainly aren't disabled. And I really want to know what protocols it
uses by default.

I believe I can also select tcp/ip by prefixing tcp: in the data
source. You mentioned Network Library=DBMSSOCN. Which is better?

Cheers,

James- Hide quoted text -

- Show quoted text -

I have now specified tcp/ip and port 1433.

<add name="ConnectionString"
connectionString="Data Source=tcp:xyz,1433;Initial Catalog=xyz;Persist
Security
Info=True;User ID=xyz;Password=xyz"
providerName="System.Data.SqlClient"/>

I now get the following error:
provider: TCP Provider, error: 0 - A connection attempt failed because
the connected party did not properly respond after a period of time,
or established connection failed because connected host has failed to
respond.

The db server is deliberately not pingable from the web server. Will
that be causing the problem?

James

 

[sloan]

A: I'd stick with the dbmssocn syntax. Just my hunch.

B. Try putting in the IP address (100.100.100.100 'ish style)...if you've
deliberately turned off the ping-ability of the machine.

I don't know the answer to that one, I've never done something like that
before. But maybe you've answered your own question.

Even with 2000, most (if not all) of the connectionstrings.com values are
legit.

...

Try the IP address with the dbmssocn syntax...(by specifying the IP address,
the port number, and the network protocol ...that's just about as specific
as you can get)


If that doesn't work , sounds like the pingablilty disabling might be your
culprit.

 

[JimLad]

A: I'd stick with the dbmssocn syntax. Just my hunch.

B. Try putting in the IP address (100.100.100.100 'ish style)...if you've
deliberately turned off the ping-ability of the machine.

I don't know the answer to that one, I've never done something like that
before. But maybe you've answered your own question.

Even with 2000, most (if not all) of the connectionstrings.com values are
legit.

..

Try the IP address with the dbmssocn syntax...(by specifying the IP address,
the port number, and the network protocol ...that's just about as specific
as you can get)

If that doesn't work , sounds like the pingablilty disabling might be your
culprit.











- Show quoted text -

The problem wasn't the connection string it was the fact that ping
wasn't working. Our systems guys couldn't fathom why.
So we gave up and we used a different db server that was pingable. I
specified protocol and port in the end although it wasn't really
necessary.