If you think you checked every setting in MSSQL Server that allows remote connections, you may have missed the Windows firewall setting. I have installed MSSQL 2005 many times on Windows 2000 Server, 2003, and 2008 and never ran into the remote connection problem. What’s happening is I’m unable to connection using the Management Studio on another machine into the server.
- Checked the Configuration Tools/SQL Server Configuration Manager
- Checked SQL Browser service is running
- Pinged the IP address
- Checked the Surface Area Configurations and made sure it allowed local and remote connections using TCP/IP only
It turns out that Windows firewall didn’t add MSSQL to the exceptions list. So here’s how you do it. Run firewall.cpl to bring up the Windows Firewall settings. On the left click on “Allow a program through Windows Firewall”. Make sure you are on the “Exceptions” tab and click on “Add program…”. If it’s not on the list, browse for the sqlservr.exe. You will find this in [INSTALL_DIR]MSSQL.1MSSQLBinnsqlservr.exe.
I never had this issue before. Not sure what caused it. Maybe Windows 2008 SP2. The versions I’m using are Windows Server 2008 RC2 and MSSQL Server 2005 Standard.