The SQL Protocols Blog has a very useful post on how to troubleshoot connectivity issues with SQL Server 2005. One tool that they did not mention is the old Microsoft Data Link trick to test connectivity. This will work with most recent Microsoft operating systems, without having any special tools (like SQL Server Management Studio or Visual Studio installed).
- Create a text file on your Desktop, and change the file extension to .udl
- Double-click on the udl file, and go to the Provider tab, and select Microsoft OLE DB Provider for SQL Server
- Go to the Connection tab, and enter the appropriate credentials. Remember, if you use Windows NT Integrated Security, it will use the Windows Credentials of the current interactive user.
- Click on the Test Connection button.
This will verify that the SQL Server instance is running, that the login username and password are correct, that the login has an associated user in the database, and that you have connectivity to the DB server. I like to run this on the SQL Server machine first, then on a remote machine. If this works, then your issue is probably a bad connection string or some other application level issue.