Trouble connecting in single user mode

  • jay-h

    SSCoach

    Points: 18816

    I have an SQL server which I need to get admin access to.

    Logged on as administrator to the server I attempt to start in single user (-m) mode. However in this case, instead of dropping into single user (as on other servers in this project),  I get a screen full of continuous login failures

    ...2019-12-09 10:07:22.87 Logon Error: 18461, Severity: 14, State: 1.

    2019-12-09 10:07:22.87 Logon Login failed for user 'xxx\yyy'. Re

    ason: Server is in single user mode. Only one administrator can connect at this

    time. [CLIENT: ...

    Hundreds of times. What is happening?

    ...

    -- FORTRAN manual for Xerox Computers --

  • frederico_fonseca

    SSChampion

    Points: 14744

    possibly another process grabbing the admin session before you can do it.

     

    I would try and use the -m startup option with a program name associated. e.g. -mSQLCMD and then connect to the server using sqlcmd to do your main tasks  (SSMS is another option but if that is the app trying to connect you have the same issue)

    see https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/database-engine-service-startup-options?view=sql-server-ver15 for info on the flag

  • Eirikur Eiriksson

    SSC Guru

    Points: 182509

    jay-h wrote:

    I have an SQL server which I need to get admin access to.

    Logged on as administrator to the server I attempt to start in single user (-m) mode. However in this case, instead of dropping into single user (as on other servers in this project),  I get a screen full of continuous login failures

    ...2019-12-09 10:07:22.87 Logon Error: 18461, Severity: 14, State: 1.

    2019-12-09 10:07:22.87 Logon Login failed for user 'xxx\yyy'. Re

    ason: Server is in single user mode. Only one administrator can connect at this

    time. [CLIENT: ...

    Hundreds of times. What is happening?

    Are you using SSMS?

    😎

  • Thom A

    SSC Guru

    Points: 98719

    I find it's better avoiding SSMS in single user mode. SSMS will open multiple connection initially to populate things like the object explorer, which can result in the single connection being used. You'll be better off using sqlcmd.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • frederico_fonseca

    SSChampion

    Points: 14744

    not just ssms.

     

    SQL Server Agent will grab it immediately if it is active - always stop the service if doing maintenance

    but other servers (monitoring tools for example) can also grab the connection before the local user can grab it - hence setting up the appname with the flag as that will block most of the other applications (will be unlucky if a remote server also using sqlcmd to connect)

  • jay-h

    SSCoach

    Points: 18816

    I had to wait till this morning to try the solutions.

    1. I'm working from the command line, not using SSMS
    2. I tied again this morning attempting to connect to sqlcmd directly (SQLSERVR.EXE -mSQLCMD) however still the same problem. It gets hammered by connections (and somehow they grab it first?).
    3. This is located in a production machine shop. The machines try to connect as soon as they can, shutting down all the machines is not practical.

    • This reply was modified 9 months, 2 weeks ago by  jay-h.

    ...

    -- FORTRAN manual for Xerox Computers --

  • Thom A

    SSC Guru

    Points: 98719

    So you';re saying that the machines in the network are connecting by sqlcmd? That sounds very unlikely, but if it is the case, also sounds like a very odd set up. You may well have to (temporarily) create a firewall rule on the Server to stop any external connections to port 1433. Afterwards, when fixed, I would look at your system design and remove the requirement for the remote computers to require sqlcmd to perform their tasks and use a different process.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • jay-h

    SSCoach

    Points: 18816

    No. What appears to be the case (I am not located on that site) is that the machines maintain a continuous connection (They are always looking for new instructions, even though their queues might often be empty)

    When I try to bring up sqlcmd in single user mode (they've lost their SA and admin credentials!!!) it appears that something else grabs the single connection before I can and my SQLCMD gets refused.

     

    ...

    -- FORTRAN manual for Xerox Computers --

  • Thom A

    SSC Guru

    Points: 98719

    jay-h wrote:

    No. What appears to be the case (I am not located on that site) is that the machines maintain a continuous connection (They are always looking for new instructions, even though their queues might often be empty)

    When I try to bring up sqlcmd in single user mode (they've lost their SA and admin credentials!!!) it appears that something else grabs the single connection before I can and my SQLCMD gets refused.

    But starting the Server in Single user Mode kills those connections because you're restarting the instance. A continuous connection cannot be maintained if the service is off. The fact that someone else is grabbing the connection, when you limit it to sqlcmd means something is using sqlcmd to connect.

    If they have seriously lost their sa and Admin permissions, you can recover those on Windows: Connect to SQL Server When System Administrators Are Locked Out. Then, after you've recovered access they need to learn from the mistake of losing said credentials and storing them somewhere secure.

    Of course, you still need to find out what is connecting via sqlcmd and stop it to do recover the intance, but it will not be the remote PCs, unless someone really has set them up to connect via sqlcmd (and if they have, then like I said, that seems like a odd design choice that needs changing).

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • jay-h

    SSCoach

    Points: 18816

    When I attempt to start in single user mode (-m), immediately I start getting dozens (ultimately hundreds) of login fails like this one right after another. My single user connection gets refused because (it appears) another connection got there first.

    ...2019-12-09 10:07:22.87 Logon Error: 18461, Severity: 14, State: 1.

    2019-12-09 10:07:22.87 Logon Login failed for user 'xxx\yyy'. Re

    ason: Server is in single user mode. Only one administrator can connect at this

    time. [CLIENT: ...

    Keeps on going endlessly.

    ...

    -- FORTRAN manual for Xerox Computers --

  • frederico_fonseca

    SSChampion

    Points: 14744

    Just thinking... are you starting sqlserver from the command line or are you changing the service properties and (re)starting the service?

    if on the command line then that window will indeed show that - but on another command prompt window you should be able to connect using sqlcmd - this assuming that no one else is also using sqlcmd to connect which seems weird.

  • jay-h

    SSCoach

    Points: 18816

    I am running from command line. I did try opening a second window, but my attempt at connecting SQLCMD was blocked because 'only one administrator can connect in single user mode'

    ...

    -- FORTRAN manual for Xerox Computers --

  • Thom A

    SSC Guru

    Points: 98719

    Then, like I suggested before, I suggest creating a Firewall rule to block all access attempts to the server on port 1433 (or whatever port your SQL Server is running on) and then running sqlcmd as the local administrator. If that still fails, I think it's time to get a contractor in who knows what they're doing.

    • This reply was modified 9 months, 2 weeks ago by  Thom A.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • frederico_fonseca

    SSChampion

    Points: 14744

    I would also try the following. it may be fast enough to allow connection

     

    change the service so it has the -mSQLCMD flag

    create a small cmd file with

    net stop service

    net start service

    sqlcmd .... -query "create login from ....; add login to sysadmin"

    exampls for net commands

    STOP:

    default instance: net stop mssqlserver

    named instance: net stop mssql$instance_name

    example: net stop mssql$ion

    START:

    default instance: net start mssqlserver

    named instance: net start mssql$instance_name

    example: net start mssql$ion

     

    Or change the SQL Server port to something that is unused, make sure to have both SQL Browser and SQL Agent stopped and then try it out

  • Eirikur Eiriksson

    SSC Guru

    Points: 182509

    Think it is time for a "Sneaker" network connection, walk over to the box, unplug the network cables, disable the SQL Server Agent and then carry out Frederico's suggestion on a local terminal.

    😎

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply