How to Connect to a SQL 2005 Server When You Are Completely Locked Out

Question

How to Connect to a SQL 2005 Server When You Are Completely Locked Out

Viewing 8 posts - 91 through 97 (of 97 total)

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

Regis Marchal SSC Enthusiast Points: 135 More actions · Answer 1

Hello,

I tried the same way to add the builtin\administrators to a SQL SERVER 2008 instance and it works. Thanks a lot for your article, I was facing a simiolar problem in our DEV environment.

Note that to connect to a named instance, you have to type: SQLCMD -SServerName\InstanceName,PortNumber -E

Regards,

Régis Marchal

Olalekan Omodara Grasshopper Points: 14 More actions · Answer 2

It did not work for me, I have only one user Builtin\Administrator as member of public. I started the SQL server in single user mode. Open another window and typed SQLCMD -E and I got this error msg

C:\Documents and Settings\Administrator.XXXXXXX>SQLCMD -E

Msg 18461, Level 14, State 1, Server XXXXX, Line 1

Login failed for user 'xxxxxx\Administrator'. Reason: Server is in single us

er mode. Only one administrator can connect at this time.

Regis Marchal SSC Enthusiast Points: 135 More actions · Answer 3

Olalekan Omodara (3/15/2011)
It did not work for me, I have only one user Builtin\Administrator as member of public. I started the SQL server in single user mode. Open another window and typed SQLCMD -E and I got this error msg
C:\Documents and Settings\Administrator.XXXXXXX>SQLCMD -E
Msg 18461, Level 14, State 1, Server XXXXX, Line 1
Login failed for user 'xxxxxx\Administrator'. Reason: Server is in single us
er mode. Only one administrator can connect at this time.

Hello,

Are you sure that there is no services or program still working that connects automatically to your server? Because the error message tells that there is already someone connected on your server.

You can try to create a batch file that starts sql server in a single command window and just after it starts a SQLCMD in another command window.

Normally, it should solve you problem

Rudy Panigas SSChampion Points: 10702 More actions · Answer 4

If you cannot connect because the error msg is stating that there is already a connection, then you have to first see what application is still running and stop it before you can proceed.

When SQL server is running in single user mode it can still accept an application's connection and therefore not allowing you to connect.

See previous posts as someone else was having the same issue.

Before starting check your running processes and stop all application related processes then start SQL server in single user mode. You should be fine.

Hope this helps,

Rudy

Eric M Russell SSC Guru Points: 125561 More actions · Answer 5

There are many different scenarios, but if what you need is access to the database itself, then you can always attach a copy of the mdf file(s) to a SQL Server instance that you do have a working SYSADMIN account on. This would be useful in a scenario where an abandoned or inherited SQL Server instance contains some needed data or code.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 6

I think the SOX issue is overblown, without using encryption you simply can't keep someone who has administrative access to the server away from the database data. We can add layers such as taking out the built-in admins but I just don't think this is truly an issue. Think about it for a second... An admin could stop the sql server and copy the database files and then attach them elsewhere and read the data, this is true for most database products. What sox requires is due diligence and due care to prevent access it also demands some auditing. So if we had our servers setup with a good amount of auditing we would see a server stop (system log and the errorlog) and the files being read by a user that is not usual (security log) then a server restart (system log and the errorlog). In the shops that I have worked in that had SOX/JSOX compliance requirements all prod server stops/starts needed to be reviewed. If an admin were to follow these steps it would be visible. I was told a story about a staff member who did some looking around on the network, that isn't what burned him, it was the altering of the logs to hide that he was looking.. For the same reason I don't know EXACTLY what logging/auditing my server team is doing, they are not privy to all that the DB team is doing, nor would most know how to circumvent it.

SOX/JSOX doesn't demand the controls be absolute, just that when they can't be that auditing and reviews of the logs are done.

In the cases where we have highly personal PII then encryption is appropriate, this effectively negates all the rest of what I was talking about, your admin might have the DB and a lot of data, but he can't decode it all..

CEWII

yuvi50 Grasshopper Points: 16 More actions · Answer 7

As per my opinion, we have two kinds of logins

1) local computer logins

2) sql server logins

So according to given scenario we are totally logged out of sql server logins and we cannot login there and neither we can make any changes into sql server configuration like to enable DAC etc. connecting through it.

so recommended solution is connect the instance through single user mode(.exe-mode).

Rudy Panigas SSChampion Points: 10702 More actions · Answer 8

Hello Everyone,

Just wanted to comment on the security aspect of this article.

The only purpose of this article is to help DBA's to get into a SQL server that the access has been removed, tampered, password changed, etc. That's it. Yes this shows that SQL server is not the safest application in the world (nether is any other database vendor out there) but if you are only relying on SQL server for all your security needs then I feel sorry for you.

There are many applications/appliances our there to help with security. I personally recommend Secure Sphere from Imperva. This appliance acts as a SQL firewall and records anything and everything you do remotely and/or from the console with no impact on performance. You really need to look else where for additional help on security and compliance.

If someone has physical access to the SQL server then you have much bigger problems then worrying about logging into SQL server.

So do yourself a favor, talk to your security analyst, your boss, etc. and get proper security for all your SQL servers. Don't just rely on the functions built-in to SQL server and/or the OS or you will be sorry.

Just my 2 cents worth,

Rudy