Three Attack Vectors in SQL Server 2005

Question

Post reply

Three Attack Vectors in SQL Server 2005

derek.colley

SSCrazy Eights

Points: 8040
More actions
March 6, 2012 at 10:54 pm

#265551

Comments posted to this topic are about the item Three Attack Vectors in SQL Server 2005
---
Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:
Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

Viewing 15 posts - 1 through 15 (of 23 total)

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

Carlo Romagnano SSC-Insane Points: 22680 More actions · Answer 1

Carlo Romagnano

SSC-Insane

Points: 22680

March 7, 2012 at 1:18 am

#1455784

Good, very good article!

Thank you!

🙂

Abrar Ahmad_ SSCarpal Tunnel Points: 4244 More actions · Answer 2

[font="Verdana"]Good work,

But if we can summarize/list the three attack vectors in bulleted form here?

Thank you[/font]

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 3

The article is a bit wordy...

1. Attempt access using DAC connection. (-m in the startup parameters). Works if BUILTIN\Administrators is a valid server login with the default sysadmin credentials and you are a member of it.

2. Attempt access by imitating the SQL service account group(s). If you have service account groups set up for e.g. application or Agent use, you can add yourself to these groups to hitch a ride into SQL Server then create yourself a new credential.

3. Lift n' shift the database .mdf files from one instance to another. Doing it this way means you'll lose much proprietary info such as logins, certificates etc. but if you're not using these features and are more concerned with data salvage, this will be your last option.

And finally, check the registry, web.config, text files on the server since often a password will be in plaintext. Particularly for modified CRM systems.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 4

derek.colley (3/7/2012)
3. Lift n' shift the database .mdf files from one instance to another. Doing it this way means you'll lose much proprietary info such as logins, certificates etc. but if you're not using these features and are more concerned with data salvage, this will be your last option.

For this third option, I'd like to add a word of warning. The deprecated sp_attach_single_file_db procedure, or its replacement, the FOR ATTACH_REBUILD_LOG option of the CREATE DATABASE statement, are mainly intended as a means of disaster recovery after losing a transaction log; they are not guaranteed to always work without data loss (especially if the database has not been shutdown cleanly).

Obviously, when you are locked out of a server and the alternative to FOR ATTACH_REBUILD_LOG is simply losing the entire database, it is a good option to consider.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

RichB SSCrazy Eights Points: 9651 More actions · Answer 5

While most of the article is quite sensible, and occasionally useful, I am somewhat concerned towards the end where it suddenly veers into general hacking advice... Surely the American authorities treat this type of posting as terrorism, or some equally disappearable crime?

:unsure:

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

I used to take exception to these types of articles. My thought was "Are you nuts? Why would you teach the world how to hack?"

After seeing what many people do with their systems, I'm actually glad to see these types of articles now because I no longer have to prove their systems are hackable. They can prove it themselves and take the proper corrective action.

The only problem now is that the same nearly careless attitude that caused their sloppy security to begin with will likely keep them from reading such articles. 🙂

--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)

richardd Hall of Fame Points: 3965 More actions · Answer 7

You could also use single-user mode from the command-line, even if BUILTIN\Administrators is not in the sysadmin role:

http://dba.stackexchange.com/a/11302

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 8

@richb-2 - fair point, but there's a clear difference between a locksmith and a burglar - I definitely wear a white, not black, hat.

@jeff Moden - Following on from above - it does prove that 2005 is hackable and I believe 2008/R2/2012 is immune from at least one of these approaches since BUILTIN\Administrators is not included as a default login at installation time. I had to implement these approaches when my employer took on a contract where the previous IT incumbent had left abruptly, not bothering to leave behind password lists, and the client didn't have these. In this case there was a legitimate reason for forced entry.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

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

Hello,

Two things:

1- Restarting SQL in single mode (using the -m flag) will allow any person that is a member o the Windows Local Admin group to be automatically a SYSADMIN in SQL is valid for SQL2K5, SQL2K8 and SQL2K8R2 (haven't test it on 2008).

2- Is the above a security gap? I don't think so, it's just a back door. Hopefully you have monitoring in place that will alert you if someone stops and restart SQL, and auditing in place that will tell you who did such a thing.

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 10

MiguelSQL (3/7/2012)
Hello,
Two things:
1- Restarting SQL in single mode (using the -m flag) will allow any person that is a member o the Windows Local Admin group to be automatically a SYSADMIN in SQL is valid for SQL2K5, SQL2K8 and SQL2K8R2 (haven't test it on 2008).
2- Is the above a security gap? I don't think so, it's just a back door. Hopefully you have monitoring in place that will alert you if someone stops and restart SQL, and auditing in place that will tell you who did such a thing.

Doesn't this require console access as well? So not a remote (outside of RDP) attack?

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 11

The three methods mentioned in the article all require (assuming that the domain and SQL Server have been setup wisely) that the person performing the "attack" has access to a domain account with elevated privileges.

I would not consider these to be attack vectors used by hackers. Most hackers try to get access to the domain by using SQL Server as a stepping stone, not the other way around.

And a DBA that worries about this kind of attacks is like a man that leaves the front door of his house unlocked, with clear pointers to all valuables (TV, computers, jewelry, etc), and then places secure locks on his bedroom door. 🙂

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 12

I have to point out that this article wasn't about hacking SQL Server 🙂 but about using the tools at hand to gain access to databases in difficult circumstances.

Yes, the first two approaches definitely require elevated privileges. The third doesn't, though - the only protection is file/folder security. Can you say with hand on heart (without checking!) that you are CERTAIN all your folders/drives containing datafiles and backup files are secure?

Failing to be able to gain access to the database in an emergency because the admin credentials have been mislaid is a brown-trousers moment for anyone, and this article is intended to help those in that situation - that's all.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 13

derek.colley (3/7/2012)
I have to point out that this article wasn't about hacking SQL Server 🙂

I know. I was responding to the discussion here, that was slightly leaning in that direction.

Yes, the first two approaches definitely require elevated privileges. The third doesn't, though - the only protection is file/folder security. Can you say with hand on heart (without checking!) that you are CERTAIN all your folders/drives containing datafiles and backup files are secure?

My domain consists of a single laptop, with me as the only user, so I guess I'm as safe as my password (and Dell's hard drive encryption algorithm, in case my laptop ever gets stolen).

And this argument is exactly why I added the remark "assuming that the domain and SQL Server have been setup wisely" to my previous post! 😀

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Nadrek SSC-Insane Points: 20039 More actions · Answer 14

MiguelSQL (3/7/2012)
Hello,
Two things:
1- Restarting SQL in single mode (using the -m flag) will allow any person that is a member o the Windows Local Admin group to be automatically a SYSADMIN in SQL is valid for SQL2K5, SQL2K8 and SQL2K8R2 (haven't test it on 2008).
2- Is the above a security gap? I don't think so, it's just a back door. Hopefully you have monitoring in place that will alert you if someone stops and restart SQL, and auditing in place that will tell you who did such a thing.

As Steve Jones mention, anyone doing this already has command line access to the SQL Server's host OS as a local administrator.

I might suggest updating the article to list Starting SQL Server in Single User mode as the #1 option. There are other options out there, but if you're doing this for a business, then using a standard MS technique on a machine you're a valid local administrator of seems to make a lot of sense, is very simple, and is a technique used in other circumstances as well.

I would also comment that none of these options, used by themselves, recover the original SA password.