December 9, 2019 at 4:21 pm
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 --
December 9, 2019 at 4:35 pm
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
December 9, 2019 at 4:51 pm
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?
😎
December 9, 2019 at 5:05 pm
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.
Larnu.uk
December 9, 2019 at 5:08 pm
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)
December 10, 2019 at 12:47 pm
I had to wait till this morning to try the solutions.
...
-- FORTRAN manual for Xerox Computers --
December 10, 2019 at 1:02 pm
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.
Larnu.uk
December 10, 2019 at 1:13 pm
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 --
December 10, 2019 at 2:24 pm
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.
Larnu.uk
December 10, 2019 at 2:37 pm
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 --
December 10, 2019 at 2:39 pm
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.
December 10, 2019 at 2:41 pm
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 --
December 10, 2019 at 2:44 pm
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.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 10, 2019 at 2:50 pm
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
December 10, 2019 at 2:57 pm
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 15 total)
You must be logged in to reply to this topic. Login to reply