Getting SQL Admin Studio to connect to your SQL Cluster
I had hell yesterday figuring out what was wrong with this.
I built a new SQL 2005 cluster, on Server 2003x64, to install my SCOM R2 release candidate environment into. I used iSCSI for my cluster, and my nodes are VM guests under Hyper-V, the same basic process that I documented here: Setting up a 2 node Server 2008 failover cluster under HyperV
So – when I was all done – my SQL 2005 Admin Studio would not connect to the remote SQL clustered named instance. This was odd – because this same admin studio WILL connect fine to any of my non-named instances… which are installed on stand-alone machines.
The machine I am running SQL Admin Studio on is a new Windows 2008 Terminal Server… with the Windows 2008 firewall. I know when connecting to a named instance, we first try and establish a connection over 1433, and if it is a named instance, we will talk to the browser service, and the browser service will tell us the dynamic port that SQL has assigned to that named instance. At this point, I could “cheat” and just hard code that port, or create a SQL alias in my SQL client… but that could lead to problems down the road. I would rather solve the problem. At first I thought something was blocking 1434, but basically, what I ended up needing to do – is to allow my “program” of SQL Admin Studio to be granted access, in the Windows 2008 firewall, on the terminal server running SQL admin studio.
The path is: %ProgramFiles% (x86)\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe
Then allow the connection, any profile, and give it a name. After this – my Admin studio connected right up to the named instance.
