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
Any ideas?
Best Answer
Some of the mystery is solved - The reason that some sites could connect and some couldn't is that there was an additional connection string stored in the machine.config file that two of the sites were using. The site that worked when a port number was specified did not use this connection string, so worked. The other sites didn't work when port number was specified because I hadn't added the port number to this connection string in machine.config. Adding the port number to that connection string as well as the ones stored in web.config allowed all the sites to work.
Additionally, the reason that iisreset had an effect is that once IIS knows the port number that SQL is using, it seems to 'remember' the port number, and so it no longer has to rely on using port 1434. Once I did an iisreset it 'forgot' what port number was used, so had to make a request on port 1434, which for some reason doesn't work.
So my question has gone from a very confusing 'works sometimes' situation to a basic 'cannot communicate on port 1434' situation.