Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Rudy Panigas
Rudy Panigas
SSC-Addicted
SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)

Group: General Forum Members
Points: 440 Visits: 1303
Comments posted to this topic are about the item How to Connect to a SQL 2005 Server When You Are Completely Locked Out



Michael Valentine Jones
Michael Valentine Jones
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3254 Visits: 11771
You can gain access to SQL Server 2005 by adding your Windows login to one of the following local groups on the server , depending on if it is default or named instance (replace ComputerName and InstanceName with the actual names)
Default instance: SQLServerMSSQLUser$ComputerName$MSSQLSERVER
Named instance: SQLServerMSSQLUser$ComputerName$InstanceName

You should not have to restart SQL Server to gain access this way.
harriga.rabie-1008938
harriga.rabie-1008938
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 293
this way is not good, because yoiu dont have the permission to connect just with sqlcmd -E, this way is not excat.
You must add your login to local group created by installation sql
pmal
pmal
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 53
harriga.rabie-1008938 (11/3/2009)
this way is not good, because yoiu dont have the permission to connect just with sqlcmd -E, this way is not excat.
You must add your login to local group created by installation sql


That is correct.

Also, using sqlcmd -E uses the trusted account you are logged into as your sql login. Well, if you are completely locked out, how would this work? If you could get in this way and had permissions to create logins, why couldn't you just login to sql management studio and just change the sa password?
Rudy Panigas
Rudy Panigas
SSC-Addicted
SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)

Group: General Forum Members
Points: 440 Visits: 1303
Hello everyone and thank you for reading my article.

I would like to make two points on the comments I have seen here.

There are some questions about the "sqlcmd -E" command. Once you place the SQL server into single user mode (sqlservr.exe -m), this is the best and recommended way to connect to the SQL server in this mode. You could use an id/account to connect but if you have an id/account already then you would not be locked out.

Yes, by adding your own id/account to the SQL server, you should not get locked out. But this is not always the case. Here are some scenarios that could happen where you can not add your id/account and thus be locked out.

1) The application is very sensitive and you are not allowed by the business owner, auditors and/or government agencies to just add your id/account and are forced to use a supplied account for access.

2) The SQL server is inherited. When a company is bought out by another and people are let go, not everyone is nice enough to leave all the information.

3) Someone has deliberately deleted id/account from the SQL server. Possibly an unhappy employee/staff member or worse.

As you can see there are many reasons why you many not be able to connect to SQL server and if you ever run into this problem, I hope you will find this article helpful.

Thanks,

Rudy



chrismj
chrismj
SSC Veteran
SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)

Group: General Forum Members
Points: 244 Visits: 241
The real way is to use the Dedicated Administrator Connection. You need to be on the physical box and member of sysadmin, and you can use either sqlcmd or Management Studio. This way you will not remove any information as to why you could not get in in the first place.

See this very short and precise way to do this:
http://msdn.microsoft.com/en-us/library/ms178068.aspx

Christian Mark Jensen
DBA



einman33
einman33
Mr or Mrs. 500
Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)

Group: General Forum Members
Points: 575 Visits: 512
I still do not understand how you can use integrated security to connect to an instance that the user your logged into the machine with does not have permission to? I am confused here. does -e in single user mode bypass all security and let anyone log in? I would be very shocked at that.
john.vanda
john.vanda
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 181
Per MSDN:

Under certain circumstances, you may have to start an instance of SQL Server in single-user mode by using the startup option -m. For example, you may want to change server configuration options or recover a damaged master database or other system database. Both actions require starting an instance of SQL Server in single-user mode.

Starting SQL Server in single-user mode enables any member of the computer's local Administrators group to connect to the instance of SQL Server as a member of the sysadmin fixed server role.

Thank you for the article OP. Very useful
john.vanda
john.vanda
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 181
chrismj (11/3/2009)
The real way is to use the Dedicated Administrator Connection. You need to be on the physical box and member of sysadmin, and you can use either sqlcmd or Management Studio. This way you will not remove any information as to why you could not get in in the first place.

See this very short and precise way to do this:
http://msdn.microsoft.com/en-us/library/ms178068.aspx

Christian Mark Jensen
DBA


The DAC, which has to be enabled as it is off by default, still requires sql server credentials. Starting the sql server service in single-user-mode only requires that you're an admin on the server, not the sql server. The article is about how to get yourself access when all other methods are lost.
einman33
einman33
Mr or Mrs. 500
Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)

Group: General Forum Members
Points: 575 Visits: 512
From the article:

The builtin\administrators account has been removed for security reasons

??
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search