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 15 posts - 46 through 60 (of 97 total)

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

david.rowland SSC-Addicted Points: 484 More actions · Answer 1

If a hacker wants to read this article thinking they can use it to hack into SQL Server databases, then they will be disappointed.

The title is fine.

jswong05 Hall of Fame Points: 3553 More actions · Answer 2

removing builtin\admin account is becoming more and more common.

It is interesting you mention that, you are right. We sure don't want an outsourced IT domain admin accessing databases unlimitedly. However, this was an original feature, but now it has become an unwanted feature (due to its time, if I remember correctly before you could not even remove it). Has anybody (Microsoft MVPs) brought it up at PASS summit? What does Microsoft say?

Jason
http://dbace.us
😛

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 3

If you have the admin access on the server you could just stop the services, copy the mdf's and move them somewhere else. I wouldn't worry so much about the server side of things, just control what you can.

Shamless self promotion - read my blog http://sirsql.net

djjwu Old Hand Points: 316 More actions · Answer 4

Hi Rudy,

Thanks for the article.

If you check out MS article http://support.microsoft.com/kb/937682, it is similar to your solution. At the end of MS article, it says 'Important The method that is described in this article is a new feature in SQL Server 2005. You cannot disable this feature. However, use other methods to add a Windows user to the sysadmin fixed server role if other methods are available. For example, if you have a different login that is a member of the sysadmin fixed server role, use this different login to log on to the instance. Then, add the Windows user to the sysadmin fixed server role.'

Personally I think this is not a feature, but a backdoor security hole. I have tested this 'feature' long time ago and found that as long as you are in local administrators group, even though your domain account DOES NOT have any access to SQL, you can still grant your domain account or any account as 'sysadmin' role using the 'feature' mentioned in MS article.

Anyway, I think MS should disable this 'feature'. I tested on SQL 2008 and this 'feature' still there.

Best Regards,

David

jswong05 Hall of Fame Points: 3553 More actions · Answer 5

Got an idea for a great title for this one: how about....

"How to get in to SQL Server via back-door?" (because you don't have to use your regular account to do so, it is a kind of hacking)

David is smart. That is exactly right. http://support.microsoft.com/kb/937682

How would this feature pass SOX audit? which is trying to control access.

Jason
http://dbace.us
😛

david.rowland SSC-Addicted Points: 484 More actions · Answer 6

Removing access to the servers BUILTIN\Adminstrators group to an IT Infrastrure team just so they cannot create a login to SQL Server will be like not allowing DBA's access to some databases even though they have sysadmin role on the database server.

Remember, only those who are already administrators can perform this technique. Joe Blogs in Finance will never be able to grant themselves access to the database by trying this for two reasons. 1. He is not a BUILTIN\Admin on the server, and 2. He should not really have any login access to the windows server itself at all.

If you are worried that someone is going to hack into your sql servers, then you really need to address your current security policy at your site first.

jswong05 Hall of Fame Points: 3553 More actions · Answer 7

you are worried that someone is going to hack into your sql servers

That is correct for Joe in Finance. To prevent Joe from excessive previledge, you can worry less, he is not in BUILTIN\Admin.

However, any of you are involved in SOX audit? Windows local admin should not have a way to grant himself/herself access database. That is why SOX review security admin server role.

Jason
http://dbace.us
😛

john.vanda Old Hand Points: 334 More actions · Answer 8

If a SOX Audit requires that an administrator on a Windows Server not be able to grant himself access to a SQL Server, then please ask Microsoft to remove the single-user mode described in this article, as that is exactly what it "can" be used for.

jswong05 Hall of Fame Points: 3553 More actions · Answer 9

If a SOX Audit requires that an administrator on a Windows Server not be able to grant himself access to a SQL Server

That question is right on!!! (how much local admin should have and still meets the restricted access standard) If I face off a smart auditor and the scenario comes up auditing or being audited, I will post here.

Jason
http://dbace.us
😛

Joel Lister SSC Enthusiast Points: 103 More actions · Answer 10

If you are running SQL on Windows 7 or Vista and have UAC enabled, then you need to run the command window as Administrator otherwise you will receive errors running sqlservr.exe in single user mode.

Great life saving article though!!

Joel.

nassos Newbie Points: 5 More actions · Answer 11

Congratulations on Rudy Panigas' EXCELLENT article! It is precise and just helped quite a hectic situation

Rudy Panigas SSChampion Points: 10702 More actions · Answer 12

Thank you for your kind words. Glad to see that my article help you out.

I'm hoping to write more in 2010 and that people find the new article(s) useful.

Look for my article in "The Best of SQL Server Central Volume 7" on DAC (Dedicated Administrator Connection) and what you can do when connected to a sad SQL server.

Finally, you find some time, you too should write an article.

Rudy

Jeff Moden SSC Guru Points: 1004289 More actions · Answer 13

That's a keeper, Rudy. Very nicely written, too! Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vijaykumar.kakde1980 Grasshopper Points: 20 More actions · Answer 14

How To export To Excel data From Database in sql server 2005

Kranthi Babu Ten Centuries Points: 1094 More actions · Answer 15

In this scenario, start the SQL server in single user mode. When server starts in single user mode, all the builit admins of the machine will be the admins of SQL Server in single user mode.. So once logged in, you can add any other user as the admin..