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

Question

Post reply

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

Viewing 15 posts - 61 through 75 (of 97 total)

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

Lonely Rogue SSCrazy Points: 2578 More actions · Answer 1

Hey,

Is there any other way when dealing with SQL Server 2008; I tried the same trick on 2008 but it didn't work.

So, will this trick only works for SQL 2005??

[font="Comic Sans MS"]In 'Thoughts',

Lonely Vampire.[/font]

--In 'thoughts'...
Lonely Rogue

robertmcook Grasshopper Points: 19 More actions · Answer 2

Created a PowerShell Advanced Function to perform this as well you can find it here: http://www.sqlmashup.com/powershell-advanced-function-for-when-you-are

Rudy Panigas SSChampion Points: 10702 More actions · Answer 3

Hello and Happy New Year to everyone 🙂

I have just tested this process on a SQL 2008 SP2 (x32 and x64) and it does work.

You can also add a windows users instead of the builtin\administrators group and assign it sysadmin rights.

I will be testing this soon on SQL 2008 R2 (x32 and x64) environment.

Thanks,

Rudy

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 4

It was a good article. I have not read all the comments but has anyone mentioned that you reference images, but I did not see them in the article?

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

Rudy Panigas SSChampion Points: 10702 More actions · Answer 5

Thanks for the info and I'll see what has happened to the images.

Rudy

Bill Stutters SSChasing Mays Points: 660 More actions · Answer 6

When I try the sqlcmd -E i get an error saying sql is in single user mode and no one else can login.

Rudy Panigas SSChampion Points: 10702 More actions · Answer 7

Are you sure the server is in single user mode? What version of SQL server are you using?

No sure why you cannot connect.

Rudy

Bill Stutters SSChasing Mays Points: 660 More actions · Answer 8

SQL Server is 2005. All SQL default services were shut down and then SQL restarted with -m. There are other SQL instances running on the server. The error states that since SQL is in single user mode, no other administrator can log on. I get this erro in the second command prompt window when the sqlcmd -E command.

Bill Stutters SSChasing Mays Points: 660 More actions · Answer 9

i think this instance was installed as part of a vendor application. That could be why it is locked down.

Rudy Panigas SSChampion Points: 10702 More actions · Answer 10

Bill Stutters (3/11/2011)
SQL Server is 2005. All SQL default services were shut down and then SQL restarted with -m. There are other SQL instances running on the server. The error states that since SQL is in single user mode, no other administrator can log on. I get this erro in the second command prompt window when the sqlcmd -E command.

Looks like something or someone else is connected (maybe the application has a service running) and therefore is not letting you in.

You should see if that's the case and try again.

Rudy

MiguelSQL Hall of Fame Points: 3112 More actions · Answer 11

Rudy,

Thank you so much for posting this article.

I found myself in this situation several times and this trick works well. However I find myself in a case where I cannot recover the SA password (or connect to the server in question)

Scenario:

MSSQL 2008 EE

Server build by a DBA that left the company

I don't have the SA password.

There are no SYSADMIN than can be found.

Server was build outside the domain, and the it joined the domain. (not sure if this is important)

My windows ID is part of the Local Administrators Group

When I start the server in single mode, and I try to log in, I get the error:

Login Failed for user ". The user is not associated with a trusted SQL Server connection.

Any suggestions?

I've been in this situation for weeks and I've been just lucky that the server is running smoothly, but it's a matter of time before it breaks and I'll be in a pickle!

Thank you

Craig A. Silvis Ten Centuries Points: 1038 More actions · Answer 12

Great article - I've been locked out of servers by vendors who won't share the sa password and have had to go through hoops like this just so I could add a link to their server on the SQL server we use for monitoring all the SQL servers at our site.

Rudy Panigas SSChampion Points: 10702 More actions · Answer 13

I think you need to be part of the domain administrator's group. Check with your network team to see if they can add you until you have connected.

Hope this helps,

Rudy

Dave62 SSCertifiable Points: 7459 More actions · Answer 14

Great article thanks for sharing!

Some have mentioned that one must still have a local administrator account in order use this technique. Not entirely true because there are boot CD's available that have utilities to reset the local administrator account. So if anyone has physical access and the server can be booted by CD or USB the local administrator account can be reset and the technique from the article used to gain access to the SQL Server instance.

It's tough to trump physical access but if enough security and encryption is in place it may be possible but this will result in the original problem. How to gain access if "completely" locked out?

Bill Stutters SSChasing Mays Points: 660 More actions · Answer 15

Thanks Rudy,

Once I found all the application services and shut them down I was able to add my group to SQl and then login.

Bill