locked
sysadmin credentials lost RRS feed

  • Question

  • Hi,

    I have a SQL Server (2008 Express) with no sysadmins defined and the SA credentials have been misplaced. I'm following the MS advise to start SQL in single user mode then connecting with a local admin account. No matter what I do though, I always get an error stating that the server is in single user mode and only one Admin can connect at this time. I've tried further restricting connecting by using -m"SQLCMD" as suggested by other but still I get the same error when connecting using SQLCMD. I've also confirmed that SQL Agent isn't running and there are no other SQL Services on this server.

    If there a bullet proof method of blocking competing connections or is there any other way around this that anyone can suggest?

    Thanks 

    Paul

    Friday, January 25, 2019 4:12 PM

Answers

  • Ok, get your point, Paul.

    Yes, for that particular question, I guess we have to say "No", I'm afraid.

    You'd want some option *when you connect* which would mean "throw out all other connections", and to my knowledge no such option exists. The reason for it to be a connection option is that without that, you always have the risk that somebody beat you to it.

    Perhaps the option to specify an application name with -m was introduced in a more recent version of SQL Server?

    ...now, hold on a minute! I tried adding in Services -m"SQLCMD" and I couldn't connect either. So I checked the errorlog file, and I found the option there but with a \ as some type of escape character. That made me suspicious. I thing that the documentation is incorrect and it should really be -mSQLCMD. I.e., no quotes. You currently only allow the application "SQLCMD" (with the quotes) to connect. 

    When I instead have -mSQLCMD I can connect perfectly fine. Still not an answer to the question at hand, but hopefully you'll be able to connect... I guess that a documentation feedback for the incorrectness would be in place here...


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, January 30, 2019 10:37 AM

All replies

  • Hello,

    Do you have local Windows Admin permissions? Then see Connect to SQL Server When System Administrators Are Locked Out

    And you have to start SSMS or SqlCmd explict with "Run as Administrator"


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, January 25, 2019 4:23 PM
  • Thanks Olaf,

    That's the KB I mentioned in my post and I have been starting both with "Run as Administrator". The problem I'm having though is I'm unable to connect to SQL when in single user mode. I'm always getting the error that only one admin can connect at a time.

    Regards 

    Paul

    Friday, January 25, 2019 4:33 PM
  • I used that procedure several times and it works.

    I guess an other application accesses the SQL Server before you do it.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, January 25, 2019 4:37 PM
  • You need to hunt down whoever managed to connect before you. don't use SSMS since it tries to use several connections. Use SQLCMD. Hunt down other services that might be connected. Stop Agent. Etc. Be the detective! :-)

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Monday, January 28, 2019 12:14 PM
  • Hi Tibor,

    I restricted connections to the DB using -m"SQLCMD" but this made no difference (I see the same error when I try to connect using SQLCMD). I had stopped any services that I thought might be trying to connect to even if there were other services, I would have assumed restricting to SQLCMD would have blocked them?

    Regards 

    Paul

    Wednesday, January 30, 2019 9:07 AM
  • I haven't seen using an option with the -m switch before. Nor do I see it documented. Where did you read about it?

    Anyhow, you just need to be persistent enough for this. Stop services. Etc. In the end you will manage to remove everybody connected. We can't do that for you...


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, January 30, 2019 9:15 AM
  • Thanks for your insights Tibor. The option to restrict connections to specific applications including SQLCMD is documented here - https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/start-sql-server-in-single-user-mode?view=sql-server-2017

    I will do my very best to be persistent as you suggest but, with respect, I wasn't asking anyone to do anything for me. I was hoping to get an answer to the original question posed . In the original question, I noted that, having stopped all known services and restricting connections to the Database using Microsoft's documented approach so that only connection from SQLCMD are allowed I'm still seeing an error that only one connection allowed. My question therefore was "Is there a bullet proof method of blocking competing connections"

    I must assume that there isn't.

    Regards 

    Paul

    Wednesday, January 30, 2019 9:34 AM
  • Ok, get your point, Paul.

    Yes, for that particular question, I guess we have to say "No", I'm afraid.

    You'd want some option *when you connect* which would mean "throw out all other connections", and to my knowledge no such option exists. The reason for it to be a connection option is that without that, you always have the risk that somebody beat you to it.

    Perhaps the option to specify an application name with -m was introduced in a more recent version of SQL Server?

    ...now, hold on a minute! I tried adding in Services -m"SQLCMD" and I couldn't connect either. So I checked the errorlog file, and I found the option there but with a \ as some type of escape character. That made me suspicious. I thing that the documentation is incorrect and it should really be -mSQLCMD. I.e., no quotes. You currently only allow the application "SQLCMD" (with the quotes) to connect. 

    When I instead have -mSQLCMD I can connect perfectly fine. Still not an answer to the question at hand, but hopefully you'll be able to connect... I guess that a documentation feedback for the incorrectness would be in place here...


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, January 30, 2019 10:37 AM
  • Thanks Tibor, 

    That did the trick.

    Regards 

    Paul

    Monday, February 4, 2019 9:05 AM