Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««678910

How to Connect to a SQL 2005 Server When You Are Completely Locked Out Expand / Collapse
Author
Message
Posted Monday, March 14, 2011 2:03 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 5:15 AM
Points: 91, Visits: 55
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



Post #1077979
Posted Tuesday, March 15, 2011 5:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 11, 2012 2:43 PM
Points: 10, Visits: 101
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.

Post #1078246
Posted Tuesday, March 15, 2011 7:01 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 5:15 AM
Points: 91, Visits: 55
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



Post #1078309
Posted Tuesday, March 15, 2011 7:11 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 1:14 PM
Points: 312, Visits: 1,106
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



Post #1078317
Posted Wednesday, March 16, 2011 7:44 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:47 PM
Points: 1,651, Visits: 4,707
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.
Post #1078962
Posted Wednesday, March 16, 2011 11:16 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
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
Post #1079465
Posted Thursday, March 17, 2011 12:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 31, 2014 9:53 AM
Points: 2, Visits: 51
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).
Post #1079491
Posted Friday, March 18, 2011 7:06 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 1:14 PM
Points: 312, Visits: 1,106
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



Post #1080343
« Prev Topic | Next Topic »

Add to briefcase «««678910

Permissions Expand / Collapse