I have a few questions, assuming that I've set up the correct user, assigned that user owner permissions on the restored database, and the user part of this is correct:
1. How do I tell if my MS SQL Server instance is "serving" to the outside world?
2. If I change the data source info from "localhost" to my actual server name and MS SQL instance "SERVER\SQLEXPRESS" the behavior changes. Instead of the error message, the page just loads forever when accessing the database from an ASP page on the site. Is this a clue of some kind? If I name this anything else (localhost, dilbert, etc) I get the error message from above.
3. Any other suggestions for checking the functionality of my new database instance and getting it reconnected?
Question 1: Determining if your db server is listening to the outside world:
1.
Go to the Windows menu --> Microsoft Sql Server 2008 --> Configuration Tools --> Sql Server Configuration Manager
2.
Drill down on Sql Server Network Configuration --> Protocols for <your instance here>
3.
Double-click on TCP/IP in the right-hand pane
4.
Make sure enabled is set to "Yes"
5.
Click on the "IP Addresses" tab and find the IP address of the server in the list
6.
Make note of the TCP Port being used (default is 1433). If it's not the default, your connection string needs to reflect that by using a :#### after the server name in the string (#### = the TCP port)
7.
Make sure the Windows firewall is permitting the specified TCP port inbound
8.
Make sure your internet firewall is permitting the specified TCP port inbound for only the IP address of your web serverQuestion 2: Response as a clue
Yes, that is a big clue. If your web application is not on the same server as your SQL database, then localhost will not work. If indeed they are separate, then you have to specify the actual server name or IP address of the SQL server.
As for the long-running of the page, is it possible you have a long-running query? Are you doing any error trapping for SqlExceptions? What version of ASP?
Question 3: Additional suggestions
Try opening the SQL management studio using the same username and password you specified in your string. See if that gives you a better error message.
1.
Go to the Windows menu --> Microsoft Sql Server 2008 --> Configuration Tools --> Sql Server Configuration Manager2.
Drill down on Sql Server Network Configuration --> Protocols for <your instance here>3.
Double-click on TCP/IP in the right-hand pane4.
Make sure enabled is set to "Yes"5.
Click on the "IP Addresses" tab and find the IP address of the server in the list6.
Make note of the TCP Port being used (default is 1433). If it's not the default, your connection string needs to reflect that by using a :#### after the server name in the string (#### = the TCP port)7.
Make sure the Windows firewall is permitting the specified TCP port inbound8.
Make sure your internet firewall is permitting the specified TCP port inbound for only the IP address of your web serverQuestion 2: Response as a clueYes, that is a big clue. If your web application is not on the same server as your SQL database, then localhost will not work. If indeed they are separate, then you have to specify the actual server name or IP address of the SQL server.
As for the long-running of the page, is it possible you have a long-running query? Are you doing any error trapping for SqlExceptions? What version of ASP?
Question 3: Additional suggestions
Try opening the SQL management studio using the same username and password you specified in your string. See if that gives you a better error message.