Problem Connecting .NET app to SQL Server

Hi,

I am having a problem connecting my .net applications from the
application server to the database server. When I run the application
from my windows xp (sp2) box it works fine. When I try to connect via
SQL Management Studio to the database server from the application
server I get the same error.

Here is the error:

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

Here is the Environment:

App Server:

Windows Server 2003 Standard Edition

Inside Company's Firewall/ Network


Database Server:

Windows Server 2000 Advanced Edition

SQL Server 2000 SP4

Remote Connections to the Server is checked

Enable Protocols: Named Pipes & TCP/IP

TCP/IP Port: 1402 (I don't know why it isn't the default of 1433)

The db server is sitting out side the Company's firewall (don't ask me
why). I can access it fine from inside the firewall on my XP box but
not from windows server 2003. There is a web server outside the our
network that also connects to the db server with no problem and that
is running Windows Server 2003 Web Edition.

I can ping the db server from the app server using the IP address.

I tried using the IP address and the port 1402 in my connection string
but that didn't work from any machine (XP and Server).

I imagine the issue is somehow related to the company's firewall but
why would it only block Windows Server 2003 and not XP?

What do I tell the network admin to change?

Any help would be appreciated.

Thanks,

Oran

Re: Problem Connecting .NET app to SQL Server

orandov (orandov@gmail.com) writes:
> I imagine the issue is somehow related to the company's firewall but
> why would it only block Windows Server 2003 and not XP?

Some firewall could be set up to accept the IP address of your XP
box, but not the application server. Apparently, you are able to ping
the db server from the app server, but the firewall on the db server
may not be open to everyone on port 1433. (You said it was on port 1402,
but maybe you looked in the wrong place? The best place to look is
the SQL Server errorlog.) Also check UDP 1434.

Also, when you tried using port 1402 in the connection string, how
did you actually do it?

Not a very conclusive response, but issues like these are difficult to
diagnose on a distance.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Problem Connecting .NET app to SQL Server

Hi Erland,

Thank you for your reply.

> Some firewall could be set up to accept the IP address of your XP
> box, but not the application server. Apparently, you are able to ping
> the db server from the app server, but the firewall on the db server
> may not be open to everyone on port 1433. (You said it was on port 1402,
> but maybe you looked in the wrong place?

I went to the physical server -> Enterprise Manager -> Server
Properties -> Network Configuration -> TCP/IP Properties.

>The best place to look is the SQL Server errorlog.

I looked there and there isn't any logs of failed connections or
anything like that. I have been trying this connection many times and
I don't see any thing related in the logs

> Also check UDP 1434.

Whats that? How?

> Also, when you tried using port 1402 in the connection string, how
> did you actually do it?

These are the connection strings that I have tried:
"Data Source=xx.xxx.xxx.xxx,1433;Network Library=DBMSSOCN;Initial
Catalog=DBNAME;Persist Security Info=True;User
ID=sa;Password=PASSWORD"
"Data Source=xx.xxx.xxx.xxx,1402;Network Library=DBMSSOCN;Initial
Catalog=DBNAME;Persist Security Info=True;User
ID=sa;Password=PASSWORD"

These don't work even from my XP workstation. I can only get it to
work with the following connection string:
"Data Source=SERVERNAME;Initial Catalog=DBNAME;Persist Security
Info=True;User ID=sa;Password=PASSWORD"

Re: Problem Connecting .NET app to SQL Server

orandov (orandov@gmail.com) writes:
>>The best place to look is the SQL Server errorlog.
>
> I looked there and there isn't any logs of failed connections or
> anything like that. I have been trying this connection many times and
> I don't see any thing related in the logs

Sorry, I should have been more clear. In the top of the SQL Server
Errorlog you can see what ports SQL Server is listening on.

>> Also check UDP 1434.
>
> Whats that? How?

UDP is a protocol just like TCP that sits on top of the IP protocol.
The difference is that with UDP you don't have established connection,
but each message sets up the connection. Anyway, when you configure
ports you need to configure both TCP and UDP ports.

With SQL Server 2000, UDP 1434 is used for communication to find out the
port number for a named instance

>> Also, when you tried using port 1402 in the connection string, how
>> did you actually do it?
>
> These are the connection strings that I have tried:
> "Data Source=xx.xxx.xxx.xxx,1433;Network Library=DBMSSOCN;Initial
> Catalog=DBNAME;Persist Security Info=True;User
> ID=sa;Password=PASSWORD"
> "Data Source=xx.xxx.xxx.xxx,1402;Network Library=DBMSSOCN;Initial
> Catalog=DBNAME;Persist Security Info=True;User
> ID=sa;Password=PASSWORD"
>
> These don't work even from my XP workstation. I can only get it to
> work with the following connection string:

So that is the right format, possibly not the right IP address. (You
will need to check that part with your network admin). However, take
out the Network Library part.





--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Problem Connecting .NET app to SQL Server

I let the visual studio dialog build the connection string for me with
the IP Address and now it is working with port 1402.

What I am still puzzled by is that SQL Server Management Studio &
Enterprise Manager (on a Windows Server 2003) can't connect to the
Server with only the server name and it requires the IP Address but on
my XP workstation it works fine with only the server name.

My network admin says there is no difference in the way the Windows
Servers are set up on the network than the XP workstations.

Thank for your help.

Oran