I've just moved all my databases from one server to another clustered server. The new SQL Server 2008 R2 (enterprise edition) install is using named instances on a non-standard port. SQL is running on a Windows Server 2008 R2 Enterprise server. My web server is running on Windows Server 2008 Enterprise. The databases are used by three different websites, all hosted on the one server. After moving the databases I changed the database connection strings in my website, and then checked that they were working ok.
Only one of the sites was working. I changed the connection string on the non-working sites to specify the port number that SQL is listening on. This got one more site working. The third site still didn't work.
I tried an iisreset (out of desperation really), and now the first site that was originally working did not work. Only the second site, that worked with the port number specified, was working.
I disabled the firewall rules between the web server and the database server, and now all three sites work. But the firewall is disabled, so I can't leave it like that.
I'm at a loss to figure out why the firewall would be preventing one site on a server from connecting to a database, but not another, and why a third site would only connect if the port number was explicitly added to the database connection string. I'm also confused as to how an iisreset had any effect (to stop the first site from working).
- Windows Firewall is turned off
- The SQL Browser service is running
- SQL is definitely running on the port I think it is running on
- TCP and UDP port 1434 and the port number SQL is running on are both opened on the firewall
- The old databases were all taken offline, so there's no possibility that the sites were still connecting to the original location somehow
- nslookup of the sql cluster from the website server works ok
- The sites all work ok when the firewall is disabled, so it has (I assume) got to be firewall related