+ Post New Thread
Results 1 to 7 of 7
MIS Systems Thread, SQL Server 2005 Surface Area Configuration Error in Technical; My users are so keen to use SIMS that they keep trying to log in whilst I'm performing updates. Easiest ...
  1. #1

    Ric_'s Avatar
    Join Date
    Jun 2005
    Location
    Boston, MA
    Posts
    7,601
    Thank Post
    110
    Thanked 771 Times in 599 Posts
    Rep Power
    183

    SQL Server 2005 Surface Area Configuration Error

    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!

  2. #2

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,956
    Thank Post
    775
    Thanked 1,487 Times in 1,234 Posts
    Rep Power
    367

    Lightbulb

    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...
    Attached Images Attached Images

  3. Thanks to vikpaw from:

    superfletch (23rd September 2009)

  4. #3
    superfletch's Avatar
    Join Date
    Nov 2007
    Location
    South
    Posts
    445
    Thank Post
    153
    Thanked 77 Times in 61 Posts
    Rep Power
    32
    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...

  5. #4

    Join Date
    Jun 2007
    Location
    Wakefield, West Yorkshire
    Posts
    636
    Thank Post
    101
    Thanked 135 Times in 105 Posts
    Rep Power
    68
    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.

  6. #5

    Ric_'s Avatar
    Join Date
    Jun 2005
    Location
    Boston, MA
    Posts
    7,601
    Thank Post
    110
    Thanked 771 Times in 599 Posts
    Rep Power
    183
    @vikpaw: Being a lot lazier than you I prefer to use the following...

    Code:
    sp_who
    A handy stored procedure

  7. #6

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,956
    Thank Post
    775
    Thanked 1,487 Times in 1,234 Posts
    Rep Power
    367
    @RIC_ - a smoker, lazy? i don't believe it!
    i'll check that sp out all the same, could come in handy.

  8. #7

    Join Date
    Jan 2007
    Posts
    101
    Thank Post
    0
    Thanked 35 Times in 24 Posts
    Rep Power
    22
    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.



SHARE:
+ Post New Thread

Similar Threads

  1. Moving from SQL Server 2005 to 2008
    By speckytecky in forum MIS Systems
    Replies: 19
    Last Post: 9th July 2009, 04:34 PM
  2. SQL Server 2005 Not installing properly
    By Bugs in forum Windows
    Replies: 4
    Last Post: 20th May 2009, 02:29 PM
  3. SQL Server 2000/2005
    By techie08 in forum Windows Server 2000/2003
    Replies: 6
    Last Post: 13th February 2009, 12:06 AM
  4. SIMS SQL Server 2005 re-install
    By philserre in forum MIS Systems
    Replies: 6
    Last Post: 14th October 2008, 09:44 PM
  5. Sims SQL 2005 upgrade on a new server?
    By zag in forum MIS Systems
    Replies: 11
    Last Post: 21st November 2007, 09:46 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •