SQL Server 2005 Surface Area Configuration Error

[Ric_]

My users are so keen to use SIMS that they keep trying to log in whilst I'm performing updates. Easiest way to prevent this is to disable remote connections using the Surface Area Configuration tool but some time ago it stopped working and presents the following error:

TITLE: Surface Area Configuration
------------------------------

Computer localhost does not exist on the network, or the computer cannot be configured remotely. Verify that the remote computer has the required Windows Management Instrumentation components and then try again. (SQLSAC)

------------------------------
ADDITIONAL INFORMATION:

An exception occurred in SMO while trying to manage a service. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdN...0&LinkId=20476

------------------------------

Initialization failure (System.Management)

------------------------------
BUTTONS:

OK
------------------------------

Anyone got any ideas?

BTW - that link is rubbish!

[vikpaw]

I don't know about the error, but i normally just disable access to the S drive (in your case that's probably an X !!). Then they can't connect.

Recently i found a great SQL script on a similar thread, which i modified, that tells me exactly who is logged into the database. much better than relying on access to the share or the open files areas of Computer Management, as this comes straight out of the sql db.

I usually give plenty of warning, remove permission to the share, and then use the script. It shows which machine the user is on so i can go and shut them down, and with no S drive access they cannot reconnect.

Works for me.

Script :

usemaster;
select sysprocesses.loginame,
sysprocesses.hostname,
sysprocesses.dbid,
sysdatabases.name
from sysprocesses
join sysdatabases on sysprocesses.dbid = sysdatabases.dbid
where sysdatabases.name ='sims2005'OR sysdatabases.name ='fmssqldb'
orderby sysdatabases.name



Sample output attached.

Not really solved your problem but maybe an alternative while you get an answer...

[superfletch]

If you don't have other SQL Databases/Services that people connect to in use on the server running SIMS you could temporarily disable the SQL Browser Service on the server.

This should stop new logins from happening, but I don't know how it deals with existing logins.

I know it is possible to diable logins for selected users through the management studio and then re-enable them afterwards, don't ask me how as I don't know the exact route... I bet this could be scripted though - ie SQL Script that disables all but the sysman/sa users and another one that enables them all again after.

If you try disabling the SQL Browser service then let me know if this works nicely as I don't have a live server to test on myself.

Another approach might be to take your SQL Exceptions out of your firewall temporarily??

Any other ideas anybody as I'd love to know them...

[michael2k6]

You can start SQL in Single User Mode by adding a switch to the service in the configuration manager.
The next user to connect will have sole access - so make sure it's you.
Or, you can specify the image name of the process to limit it further.

[Ric_]

@vikpaw: Being a lot lazier than you I prefer to use the following...

sp_who

A handy stored procedure

[vikpaw]

@RIC_ - a smoker, lazy? i don't believe it!
i'll check that sp out all the same, could come in handy.

[Greg]

Quick easy was is to disable TCP/IP and/or Named Pipes (depending on which you're using) for the upgrade, then you can only access SIMS on the server.