I have been asked by a few developers recently how can they connect to a remote SQL server over the web using their locally installed Management Studio.
By default the SQL instance listens on port 1433 so all that needs to be done is the opening of a port within the remote firewall. Obviously there are some security implications of doing this but you can lock traffic down to your own IP address if the remote firewall permits this. However what if the server has multiple instances running on the same server say SQL 2005 and SQL 2008 and you want to connect to a specific one how can this be achieved. You may think well how often does that happen. This can occur as part of a migration or if you have applications that require specific SQl versions.
When another instance in installed on the server by default it uses Dynamic TCP ports. To connect to the instance you will need to configure it with a static port. This is done in the SQL Server Configuration Manager on your SQL Server. Here you will see all the instances installed.
If you look at the first instance that was installed under TCP/IP settings you will see that the TCP Port is set to 1433. On the other instances you will see that the TCp Dynamic Ports will have a value in. Simply remove this value and set the TCP Port to a value. As you can see from the image above I selected the next port along 1434. You will need to restart the SQL service for the changes to take effect. Then you need to allow the port you slected through the firewall.
To connect your local Management Studio type the IP Address and port in the server name box seperating the IP address and port using a comma not a semicolon as most applications use. To connect to the other instances all you need to do is change the port number. I have found it easier to create a SQL account in each instance and use SQL authentication to connect to the SQL server rather than Windows Authentication due to the remote connection.
No comments:
Post a Comment