Connecting to MSSQL clusters from a NAT’ed VM
So, as noted in my previous post, I’ve changed from running Vista on my work laptop to running Ubuntu and Windows 7 in a VM.
One of the tools we use a lot at work is SQL Server Management Studio (SSMS), used to connect to our clustered MS SQL server instances. I’ve been using SSMS 2008, since the 2005 version can’t connect to MS SQL 2008 instances, and we’re now running both versions of the servers.
I installed SSMS 2008 in my Windows 7 VM, typed in the connection string and the authentication information, and… it can’t connect. This worked perfectly on my old Vista install, so I’m pretty sure I should be able to connect to the clusters – it’s remotely possible that IT changed the firewalls, but unlikely, a lot of people would be complaining.
I started digging into how SQL Server connections actually work, particularly with clusters. And I came across this great post on a MSDN blog that explains what actually happens when you connect to a SQL Server cluster instance, and things became more clear.
Basically, when you connect to a named instance on a cluster, there’s a bunch of UDP traffic going back and forth to tell you where you should really be connecting to, and the responses aren’t necessarily coming back to the client from the same server IP on the cluster it sent them to. This can be seen as broken if your firewall doesn’t expect it, and I rather expect VirtualBox’s NAT handling will have no idea what to do with it and just drop the inbound UDP packets.
I’ve tried an end-run around this by installing SQuirrel SQL directly in Ubuntu, which is moderately working – I can connect to the named instance on the cluster if I specify the TCP port directly, but for some reason I can see databases but not the tables or procedures or other actually useful objects. Not sure what’s causing this, but I ran out of time yesterday.
More work is needed – in the mean time I’ll go with what the dev architect who runs Linux does, and RDP into some other machine that has SQL Management Studio installed. It’s not a perfect solution, but I’m sure I’ll find some workable solution.