I have a web application developed in ASP and SQL-server. One of my clients wanted the SQL DB to reisde in a separate machine than the IIS box. Also he doesn't want the SQL - DB box to be protected from public access. So only the IIS box is assiged a public IP, and the SQL box is in the same network(LAN) as of the IIS box. Now using TCP/IP can I connect to the SQL server from my ASP? or should I have to use Named pipes?
Here is the Connection string that I used:
constr="Provider=SQLOLEDB;Network Library=DBMSSOCN;SERVER=xxx;Initial Catalog=mydb;User Id=user;Password=xxxxxx;"
When I use the above, I don't know what is happening.. but my IE animated picture at the top right corner is moving(i.e its talking to the server) without any response. Can anyone of you give me a clue why this is happening?
Also I am NOT receiving any error, like 'Connection timed out' , or Script Timed out. That makes me nervous.
Try creating a dsn and test the connection and/or write a vb app that uses the ado data control and use the automated connection string creator and test the connection. If that works, copy the connection string from your ado data control to your asp page.
I like to use TCP/IP to connect, even though you hear that Name Pipe is faster. The simplest thing to do to see if you can use TCP/IP from your IIS machine is to physically logon to your IIS machine and go to DOS and enter the PING command to your SQL Server machine. Make sure you PING by hostname and not by IP address. If you get a response, perfect use TCP/IP to connect. If you don't get a response and your SQL Server machine has a static IP address, then PING by IP Address. If that is fine then I would enter my SQL Server machine into an LMHost file.