IIS 6 connection to SQL Server 2005  
Author Message
New2Net2





PostPosted: .NET Framework Data Access and Storage, IIS 6 connection to SQL Server 2005 Top

Hi,

I have an aspx page with a connection to SQL Server 2005 which works fine on the local VS2005 setting. When I deployed it to an IIS 6 box it could not establish the connection. Could someone help shed some light

Scenarios,

Local to sql 2000 -- OK

Local to sql 2005 -- OK

IIS box to sql 2000 -- OK

IIS box to sql 2005 -- Error

The connection string is,

"Data Source=DBServer;Initial Catalog=DB;User ID=uid;Password=pwd;Pooling=true;Max Pool Size=50;Min Pool Size=10;"

I get the error message

SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

If connection string is,

"Network=DBMSSOCN;Data Source=10.10.10.4,1433;Initial Catalog=DB;User ID=uid;Password=pwd;"

Then the error

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: 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.)

Both IIS and SQL Server 2005 are on Windows Server 2003 (separate boxes). Initially the windows firewalls were not enabled. During the troubleshoot, I turned them on and gave exceptions on port 1433 on the sql box. Still didn't work. And it is only this "IIS on winserv2003 and sql 2005 on winserv2003" won't work. Did I miss something in the configuration specially in iis to sql server 2005

Thanks in advance!

Huachun



.NET Development20  
 
 
Paul Domag





PostPosted: .NET Framework Data Access and Storage, IIS 6 connection to SQL Server 2005 Top

Hi,

Check if the network protocols in your sql server is enabled. YOu can do this by using the "Sql Server Configuration" which can be found in the configuration tools. You must enable the TCP IP protocol to allow network connections.

cheers,

Paul June A. Domag



 
 
New2Net2





PostPosted: .NET Framework Data Access and Storage, IIS 6 connection to SQL Server 2005 Top

Thanks Paul for your reply.

I have checked both the "Sql server configuation" and "surface area management" on the sql server. Only TCP/IP protocol was enabled and the port is open. Acutually that can be verified by the fact that I can establish the connection from my local machine. The problem happens when I deploy it to the IIS box running on the web server. Is there any special settings on IIS that I forgot to turn on

Huachun


 
 
Paul P Clement IV





PostPosted: .NET Framework Data Access and Storage, IIS 6 connection to SQL Server 2005 Top


What type of authentication is your web application configured for Is impersonation enabled

 
 
New2Net2





PostPosted: .NET Framework Data Access and Storage, IIS 6 connection to SQL Server 2005 Top

On the virtual directory, it is configured as Integrated Windows authentication. In web.config, authentication mode is "windows". I don't believe impersonation is enabled.

Huachun


 
 
New2Net2





PostPosted: .NET Framework Data Access and Storage, IIS 6 connection to SQL Server 2005 Top

Just an update of my investigation, --

I've tried to set up an ODBC connection to the DB server from the IIS box and it failed (server does not exist or access denied), while on my desktop I can without any problems. Is there anything special between IIS and SQL Server 2005 on Windows Server 2003 Do I need to open up anything from the IIS box  Or did I not get the correct install

Very puzzled...

Huachun


 
 
Paul P Clement IV





PostPosted: .NET Framework Data Access and Storage, IIS 6 connection to SQL Server 2005 Top


Can you run a non ASP.NET app from your web server to see if you can establish connectivity to the SQL Server machine

 
 
New2Net2





PostPosted: .NET Framework Data Access and Storage, IIS 6 connection to SQL Server 2005 Top


Can you run a non ASP.NET app from your web server to see if you can establish connectivity to the SQL Server machine

Thanks Paul. Please refer to my previous posting -- does that qualify for "a non ASP.NET app" BTW, I have NO problem on that machine to run an ASP.NET app to connect to a SQL Server 2000 on a separate machine on Window 2000 Server. It appears to me the problem just lies within IIS on Server 2003 and SQL 2005 on Server 2003, unless I did not have a good install.

Huachun


 
 
Sarah Parra - MSFT





PostPosted: .NET Framework Data Access and Storage, IIS 6 connection to SQL Server 2005 Top

Have you gone through everything in this blog post already

SQL Server 2005 Remote Connectivity Issue TroubleShooting
http://blogs.msdn.com/sql_protocols/archive/2006/09/30/SQL-Server-2005-Remote-Connectivity-Issue-TroubleShooting.aspx

It contains a ton of good of things to check to narrow down where the problem is coming from.

Thanks,
Sarah

Please Mark as Answer if this answers your question, or Unmark as Answer if it is marked and you feel it is not answered.



 
 
Paul P Clement IV





PostPosted: .NET Framework Data Access and Storage, IIS 6 connection to SQL Server 2005 Top


Actually I was wondering whether you had attempted to connect with SQL Server using a non web application running from the Windows Server 2003 machine, not from a desktop machine. Removing IIS from the equation might make it easier to resolve the issue.



 
 
New2Net2





PostPosted: .NET Framework Data Access and Storage, IIS 6 connection to SQL Server 2005 Top

 


Actually I was wondering whether you had attempted to connect with SQL Server using a non web application running from the Windows Server 2003 machine, not from a desktop machine. Removing IIS from the equation might make it easier to resolve the issue.

Yes, Paul. I was trying to connect db server with ODBC. It reported "SQL server not found or access denied". That should get IIS out of the picture. While in the mean time I was able to locate another 2003 server and did the same thing (connect through ODBC) -- it went through. So that made SQL Server out of the problem. I have disabled the firewall on both the IIS and SQL machines so that's out of the picture too.

Now, I'm assuming .NET does not use MDAC to connect to SQL Server. If it is true, the fact that both MDAC and .NET component cannot establish connection to the db server means the problem resides on the machine running the IIS. What is making the machine not being able to see the db server on the network

If the above assumption is not true -- meaning .NET uses MDAC to connect, the focus then is on MDAC. Is there a way to re-install it

I hope I'm getting closer. Thanks for your help.

Huachun


 
 
Paul P Clement IV





PostPosted: .NET Framework Data Access and Storage, IIS 6 connection to SQL Server 2005 Top


I don't think it's MDAC. Did you check out the link that Sarah posted (Specifically the client-side testing)

 
 
New2Net2





PostPosted: .NET Framework Data Access and Storage, IIS 6 connection to SQL Server 2005 Top

OK, I got it to work. It's really bizarre -- the two ports on the network switch that these two servers are connected to will communicate with anybody else but not each other!!!

Sarah's suggested article is great. It helped me heading straight down to the problem. I'll keep it handy. A big THANKS to Sarah!

A big THANKS to Paul too!

To summarize for the folks who might in the future be reading this thread -- if you have connection problem here are some QUICK tips you might want to check,

1. Make sure the two machines can communicate with each other (use ping or at least they should have access to each other in the "My Network Places");

2. Check firewall settings on SQL Server. The port 1433 (or whatever port you intend to use) should be open;

3. Use the Surface Area Configuration on SQL Server 2005 utility to make sure the remote connection is set properly for the protocol you intend to use;

4. Use SQL Server Configuration Manager to make sure the protocols you intend to use are enabled;

5. Here you can, for example, create an ODBC connection on the client machine to make sure some sort of connection could be established to the SQL Server;

Well, this is what helped solve my problem. For a complete troubleshooting list, check the article Sarah has suggested,

http://blogs.msdn.com/sql_protocols/archive/2006/09/30/SQL-Server-2005-Remote-Connectivity-Issue-TroubleShooting.aspx

Thanks again folks!

Huachun