Many people, including myself, have problems whenever we setup a new machine with SQL Server and have to connect to SQL Server remotely. I thought I’d make a note of the fix here as a quick reference.
The most common ways of connecting to a SQL Server are with using named pipes (specifying the computer name) or tcp/ip (specifying the Server IP and port)
If you’re using a SQL Server client to connect to the remote server, in the auth dialog, click on Options and select network protocol = named pipes.
Note the weird UI change as the Connection Properties now becomes a tab.
Specify the name of the remote computer and other info and you should be good to go.
In order to access via TCP/IP for JDBC and other clients, you need to first configure SQL Server to accept TCP/IP connections. This is done by going to Start -> Microsoft SQL Server xxx – Configuration Tools – SQL Configuration Manager. Expand the Network Configuration tree item and you should see Protocols for MSSQLSERVER. In the details pane, right-click on the required prototocol, in this case TCP/IP and enable it. So now, your SQL Server engine can accept TCP/IP connections, however your computer may not.
If you use XP or Vista out of the box, most likely that the Windows Firewall would be active and needs to be tweaked to allow incoming TCP/IP requests to SQL Server.
To do this, open the Windows Firewall (via Control Panel), go to the Exceptions tab and add a Port 1433 (used by default by SQL Client) or whatever port number you specify in your client connection.
Get the IP address of the Server (Choose Start – Run, type cmd to get a command prompt and type ipconfig and hit Enter) and specify it with the port in your client, if required, and you should be good to go.