Remote connection to a SQL Server 2005/2008 instance

If you’re like me, you might have had an occasion or two where you needed to connect to an instance of SQL Server from a workstation or server that wasn’t where the SQL Server was actually located.  Because of the gosh darn important security measures that have been implemented in more recent editions of Windows Server and SQL Server itself, this might not work well by default (not to be critical of gosh darn important security measures, as they are gosh darn important).

As a note to myself, if you need to do this, there are a couple of things to check:

a) Check the protocols that are enabled for your SQL Server instance, using the SQL Server Configuration Manager.  Depending on your situation, you will need to enable TCP/IP and/or Named Pipes.

b) Open up Windows Firewall.  Yeah, I know, dangerous.  But not really.  Be sure to allow the program within the Windows Firewall interface (usually found through Control Panel), and typically found at something like: C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\sqlservr.exe, though that will vary depending on which version you have installed.

c) if you do use TCP/IP, check to see if it is using a dynamic port.  Even once you open up Windows Firewall, you will need to make sure the client is hitting the correct port.

Once you know/remember what to check, it is usually easy to set this up.  Obviously, if the SQL Server is in a production environment, you have to be careful about what you are doing (well, you should be careful, regardless), but I find I typically have to do this when I need to connect a workstation to a Dev or QA SQL instance that is not local.

posted on Monday, December 14, 2009 9:28 PM Print
No comments posted yet.

Post Comment

Title *
Name *
Comment *  
Please add 8 and 8 and type the answer here: