Allen White

Accessing SAC after Cluster Upgrade to SQL 2005

I ran into a problem after upgrading production servers running under Microsoft Cluster Server, Windows Enterprise Server 2003 SP1, and SQL Server 2005 SP1 Enterprise Edition.

Once I completed the upgrade and applied the patch, I needed to examine the values set in the Features component of the Surface Area Configuration tool. The tool attempts to connect to localhost\instancename, but reports the following error:

    An error has occurred while establishing a connection to the server.
    When connecting to SQL Server 2005, this failure may be caused by the fact
    that under the default settings SQL Server does not allow remote connections.
    (provider: SQL Network Interfaces, error:26 - Error Locating Server/Instance
    Specified)(Microsoft SQL Server)

I'm logged into the virtual server via Remote Desktop, but the SAC tool thinks I'm logged into the physical server supporting the virtual server. I seem to be caught between a rock and a hard place here, as I can't enable remote connections without first gaining access on the server itself.

Well, after a lucky break I found the answer.

sp_configure 'remote admin connections', 1
go
reconfigure
go

Once this was executed on the server I was able to go into Surface Area Configuration, enter the virtual server name, and connect successfully and make the other changes necessary. A simple solution to a scary problem. (Even though I'd used sp_configure for years on both Sybase and MSSQL servers it's easy to forget about the simple solutions.)

Allen