Disabling xp_CmdShell... Is it Really a "Best Practice"?
Download
Report
Transcript Disabling xp_CmdShell... Is it Really a "Best Practice"?
Disabling xp_CmdShell…
Is it Really a “Best Practice”?
by Jeff Moden
Disabling xp_CmdShell…
Is it Really a “Best Practice”?
Or Are You Just Missing Out on
One of the Most Powerful Tools
There is in SQL Server?
by Jeff Moden
Your Speaker - Jeff Moden
17 years experience working with SQL Server
Mostly Self Taught
One of Leading Posters on SQLServerCentral.com
More than 32,000 posts (heh… some are even useful)
30+ articles on the “Black Arts” of T-SQL
http://www.sqlservercentral.com/Authors/Articles/Jeff_Moden/80567/
Member since 2003
SQL Server MVP 2008 thru 2013
Winner of the Red Gate “Exceptional DBA” award for
2011
Sr. System/Application DBA, Data Architect, and SQL
Mentor for Proctor Financial, Inc.
SQL Server is both my profession and my hobby
(Yeah, I know… I need to get a life ;-)
Disabling xp_CmdShell... Is it Really a "Best Practice"?
3
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
Introduction
New Doubts
Teaser
XP_CmdShell is a powerful tool that can greatly simplify the life of DBAs.
You can quickly and easily write full ETL systems, up/down-load FTP files, call
PowerShell scripts to interrogate the status of hard-disks across the enterprise
with nary a 3rd party tool in sight, and much more.
And it can all be done under the control a scheduled job system that you already
know and that keeps its own logs.
Yet, you'll find millions of people that agree that disabling xp_CmdShell is a best
practice. Some even say that it should never be used. Why? Disabling it
supposedly decreases the system "surface area" of possible attack and never
using it supposedly solves the problem of someone elevating their privileges with
it.
Well, surprise! Disabling xp_CmdShell doesn't actually solve any of those
problems. In fact, disabling it may actually hurt security.
Come to this session to find out why turning off xp_CmdShell may be a "worst
practice" and how to allow individuals/applications to run it safely.
Disabling xp_CmdShell... Is it Really a "Best Practice"?
5
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
Start of a Doubt
Began search for White Papers to
show why having xp_CmdShell
enabled was a bad idea.
Found WPs but none of the “official”
documentation ever said “Don’t use
it”.
Disabling xp_CmdShell... Is it Really a "Best Practice"?
6
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
An Example of What I Found
Example White Paper
http://download.microsoft.com/downl
oad/8/F/A/8FABACD7-803E-40FCADF8355E7D218F4C/SQL_Server_2012_Se
curity_Best_Practice_Whitepaper_Ap
r2012.docx
Disabling xp_CmdShell... Is it Really a "Best Practice"?
7
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
Never Said “Don’t Use It”.
“xp_CmdShell” found in 4 places in
that reference…
“xp_cmdshell availability”
“xp_cmdshell - executes a command
in the underlying operating system”
“Disable xp_cmdshell unless it is
absolutely needed.”
That’s not the definitive answer I
was looking for.
Disabling xp_CmdShell... Is it Really a "Best Practice"?
8
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
The 4th Place
Never Explained “Why” a “Risk”
“Because some system procedures interact with the operating
system or execute code outside of the normal SQL Server
permissions, they can constitute a security risk. System stored
procedures such as xp_cmdshell or sp_send_dbmail are off by
default and should remain disabled unless there is a reason to
use them. In SQL Server 2005 and above, you no longer need to
use stored procedures that access the underlying operating system
or network outside of the SQL Server permission space. SQLCLR
procedures executing in EXTERNAL_ACCESS mode are subject to
SQL Server permissions, and SQLCLR procedures executing in
UNSAFE mode are subject to some, but not all, security checks. For
example, to catalog a SQLCLR assembly categorized as
EXTERNAL_ACCESS or UNSAFE, either the database must be
marked as TRUSTWORTHY (see Database Ownership and Trust) or
the assembly must be signed with a certificate or asymmetric key
that is cataloged to the master database. SQLCLR procedures
should replace user-written extended stored procedures in the
future.”
Set a database to TRUSTWORTHY? You CAN’T be
SERIOUS!!!
Disabling xp_CmdShell... Is it Really a "Best Practice"?
9
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
More New Doubts
“Use only if needed”?
If disabled, who can turn it on?
Can an attacker turn it on?
Am I depriving myself, as a DBA, of a
great tool?
Considering the “use only if needed”
recommendation, what does it buy me
to turn it off?
Is there any reason why it couldn’t be
turned on and left on?
WHAT IS THE REAL RISK???
Disabling xp_CmdShell... Is it Really a "Best Practice"?
10
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
Agenda
Define the Problem
Analysis
What if xp_CmdShell is Disabled
What if xp_CmdShell is Enabled
Other Concerns
Conclusion
Enable Stored Procedures to Safely
Use xp_CmdShell
Setup Procs to Work with xp_CmdShell
Avoid “DOS Injection”
Q’n’A
Disabling xp_CmdShell... Is it Really a "Best Practice"?
11
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
Define the Problem
The Scientific Method
Hypothesis, Prediction, and Testing
I left out the Hypothesis and
Prediction parts because I wanted
to eliminate any preconceived
notions that I may have had about
the use of xp_CmdShell.
I also left out most of the “Testing”
step because it’s all been tested and
very well documented in Books
Online and other documents.
Disabling xp_CmdShell... Is it Really a "Best Practice"?
13
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
Given:
Given #1:
The overwhelming recommendation
accepted by more of the world than not is
to disable xp_CmdShell for reasons of
security. To wit, it is currently considered
to be a "best practice".
Given #2:
xp_CmdShell is an incredibly powerful and
flexible tool that allows DBAs and master
developers to write stored procedures and
jobs to quickly and easily resolve many
common DBA, reporting, ETL, and other
tasks.
Disabling xp_CmdShell... Is it Really a "Best Practice"?
14
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
The Master Question
Master Question:
Does disabling xp_CmdShell provide
any layer of extra security?
If it does, then it might be a “Best
Practice”.
Disabling xp_CmdShell... Is it Really a "Best Practice"?
15
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
The “Negative” Path
Ancillary Question #1:
Does enabling xp_CmdShell and
leaving it enabled provide any
security DISadvantage?
If it doesn’t, then disabling it might
not be a “Best Practice”
Disabling xp_CmdShell... Is it Really a "Best Practice"?
16
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
Discover What’s Best
Ancillary Question #2:
Which is better?
Disabling it and never using it?
Enabling it only when needed and then
disabling it, again?
Enabling it and leaving it enabled?
Note that I didn't identify what "better"
means in this question because I didn't
want to limit any discoveries.
Disabling xp_CmdShell... Is it Really a "Best Practice"?
17
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
Analysis
What If xp_CmdShell is
DISABLED?
xp_CmdShell is DISABLED
Q1: IF xp_CmdShell is DISABLED,
who can use it?
A1: No one. It must be ENABLED in
order to use it.
Disabling xp_CmdShell... Is it Really a "Best Practice"?
19
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
xp_CmdShell is DISABLED
Q2: Who can enable xp_CmdShell?
A2: Only someone with “SA” or
“Control Server” privs.
These may be system logins,
application logins, logins that are
members of groups with the priv, or
individual logins.
Disabling xp_CmdShell... Is it Really a "Best Practice"?
20
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
xp_CmdShell is DISABLED
Q3: Does disabling xp_CmdShell
prevent logins with "SA" privs from
enabling it?
A3: No.
Any login that can gain access to the
server with "SA" privs can enable
xp_CmdShell including any attacker,
either internal or external.
Disabling xp_CmdShell... Is it Really a "Best Practice"?
21
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
xp_CmdShell is DISABLED
Q4: In reference to Answer #3, if an
attacker does gain access to the
system through a login having "SA"
privs, does having xp_CmdShell
disabled provide any level of extra
security?
A4: Yes… but it appears to be trivial
in nature.
Disabling xp_CmdShell... Is it Really a "Best Practice"?
22
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
xp_CmdShell is DISABLED
Observation
Once an attacker has gained access to
the server with "SA" privs, the "attack
software" being used takes less than 3
milliseconds to test its state and
enable it (witnessed during a "Hacking
SQL Server" demonstration).
A "manual attack" (which I did myself)
took only seconds more to enable it.
There was nothing that prevented
enabling it by any login that has "SA"
privs.
Disabling xp_CmdShell... Is it Really a "Best Practice"?
23
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
xp_CmdShell is DISABLED
Q5: Does having xp_CmdShell
disabled provide any security
advantages?
A5: Yes but, again, it's trivial in
nature.
Disabling xp_CmdShell... Is it Really a "Best Practice"?
24
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
xp_CmdShell is DISABLED
Observation
Enabling xp_CmdShell does do some very
minimal logging to trace the event but an
attacker will likely break in either as a system
login (usually "SA"), via some application login,
or as a human login registered on the server.
In any case, it's not at all likely that the
attacker will break in as him/her self so there is
no reliable "conviction" path to correctly
identify the actual perpetrator.
To wit, the logging being done when
xp_CmdShell is enabled appears to be of no use
other than providing written post-mortem
testament that security is bad and that the
server has already been attacked.
Disabling xp_CmdShell... Is it Really a "Best Practice"?
25
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
xp_CmdShell is DISABLED
Q6: Will deleting/renaming either the
xp_CmdShell extended stored
procedure or the related DLL
prevent the use of xp_CmdShell?
A6: No.
Disabling xp_CmdShell... Is it Really a "Best Practice"?
26
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
xp_CmdShell is DISABLED
Observation
I was really surprised at the answer
to this one. During the same
"hackers" dog'n'pony show
previously mentioned, I witnessed
the hacker implementing his own
version of xp_CmdShell, which didn't
even require the use of the DLL that
xp_CmdShell uses. I won't post the
code here but it was incredibly
simple to do.
Disabling xp_CmdShell... Is it Really a "Best Practice"?
27
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
xp_CmdShell is DISABLED
Q7: Can an attacker with “SA” privs
get to the Command System (CMD)
without using xp_CmdShell at all?
A7: Yes
Self deleting job with CmdExec task.
OPENROWSET with “/c CMD”
Note that the OPENROWSET method requires a
simple registry hack using xp_RegWrite that I’m
not going to demonstrate for obvious reasons.
Disabling xp_CmdShell... Is it Really a "Best Practice"?
28
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
xp_CmdShell is DISABLED
Q8: Is there a way to limit the
amount of damage that someone
with "SA" privs can do from SQL
Server at the Command Line
whether it be accidental or
malicious in nature?
A8: Yes.
Disabling xp_CmdShell... Is it Really a "Best Practice"?
29
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
xp_CmdShell is DISABLED
Observation
Limit the privs that the SQL Server Service and the
SQL Agent Service logins have been assigned.
Reference: SQL Server 2012 Security Best Practices
- Operational and Administrative Tasks
Author: Bob Beauchemin, SQLskills
Technical Reviewers: Darmadi Komo, Jack Richins,
Devendra Tiwari
Published: January 2012
Link:
http://download.microsoft.com/download/8/F/A/8FAB
ACD7-803E-40FC-ADF8355E7D218F4C/SQL_Server_2012_Security_Best_Pra
ctice_Whitepaper_Apr2012.docx
There IS another way that we’ll talk about in a
minute but I haven’t tried it, yet.
Disabling xp_CmdShell... Is it Really a "Best Practice"?
30
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
xp_CmdShell is DISABLED
Observation Summary
No one can use xp_CmdShell if it is
disabled but…
“SA” prived logins can turn it on.
ONLY “SA” prived logins can use it.
ONLY the people that can use it can turn
it on.
An attacker that gets in as “SA” doesn’t
have to use xp_CmdShell to cause
extremely grave damage, but could
enable it and use it.
Logging of the xp_CmdShell enabling
event is trivial and does not provide a
clear conviction path.
Disabling xp_CmdShell... Is it Really a "Best Practice"?
31
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
The Master Question
Master Question:
Does disabling xp_CmdShell provide
any layer of extra security?
Answer:
Other than trivial logging, NO, it
does not.
Disabling xp_CmdShell... Is it Really a "Best Practice"?
32
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
Analysis
What If xp_CmdShell is
ENABLED?
xp_CmdShell is ENABLED
Q9: If xp_CmdShell is enabled, who
can use it?
A9: Only logins that have been
granted or implicitly have "SA" privs
or “proxy” logins.
Giving non-”SA” users privs to execute
xp_CmdShell directly is a huge
security problem because of elevated
privs. Don’t do it!
Disabling xp_CmdShell... Is it Really a "Best Practice"?
34
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
xp_CmdShell is ENABLED
Q10: Does enabling xp_CmdShell
change the rules as to who can use it
according to Answer #9?
A10: Yes but it’s a trivial change.
If xp_CmdShell is disabled, the logins
that can turn it on are the same logins
that can use it (those with "SA" privs).
The exception is those logins that have
been granted usage by proxy. Such
logins are unable to enable xp_CmdShell
if it's in a disabled state because they
aren't setup with "SA" privs.
Disabling xp_CmdShell... Is it Really a "Best Practice"?
35
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
xp_CmdShell is ENABLED
Q11: Does enabling xp_CmdShell
offer any substantial security
DISadvantage over it being
disabled?
A11: Apparently not. Anyone with
"SA" privs can use xp_CmdShell
whether it's enabled or not because
it’s a trivial task to re-enable it.
Disabling xp_CmdShell... Is it Really a "Best Practice"?
36
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
xp_CmdShell is ENABLED
Q12: Considering the answers for
questions 1 through 11, are there
any advantages to having
xp_CmdShell disabled as opposed to
enabling it and leaving it enabled?
A12: Apparently not.
Disabling xp_CmdShell... Is it Really a "Best Practice"?
37
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
xp_CmdShell is ENABLED
Observation
Whether xp_CmdShell is enabled or not, anyone with
"SA" privs and ONLY those with "SA" privs can enable
xp_CmdShell and/or use it (provided the proxy mistake
has not been made).
Only company policy would provide "prevention" of its
use by internal users and ONLY if a system were set
up to check if xp_CmdShell is enabled. That would
require some regular audit system to examine the SQL
Server logs AND that the "SA" (person enabling
xp_CmdShell) didn't delete the entry from the log,
which they could very easily do in an undetectable
manner.
Even then and as previously identified, there are
methods to get to the Command Line without it that
aren't logged at all (and that's a key point). All of those
alternate methods are available to every login that has
"SA" privs.
Disabling xp_CmdShell... Is it Really a "Best Practice"?
38
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
The “Negative” Path
Ancillary Question #1:
Does enabling xp_CmdShell and leaving it
enabled provide any security DISadvantage?
Answer:
Apparently not.
Having xp_CmdShell enabled offers no more of a
security DISadvantage than having it disabled. It
would appear that the key to security has nothing
to do with whether xp_CmdShell is enabled,
disabled, or even deleted. It would appear that
the real key is to prevent access to the system
with "SA" privs.
Disabling xp_CmdShell... Is it Really a "Best Practice"?
39
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
Analysis
Other Concerns
Other Concerns
Q13: Are there any DISadvantages to having
xp_CmdShell DISabled?
A13: Yes, there are at least 3 disadvantages.
If company policy mandates that xp_CmdShell
remain disabled, only the honest DBA (assuming
that DBAs have "SA" privs) will be thwarted.
Such honest DBAs will be deprived of a powerful
tool (according to Given #2 above) capable of
making his/her job much easier and more effective.
Because people DO think that having xp_CmdShell
disabled is a security advantage, they may become
more lax in the security measures they take (or
don't take, as a result) no matter what they are.
Disabling xp_CmdShell... Is it Really a "Best Practice"?
41
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
Other Concerns
Q14: Since DBAs could use xp_CmdShell
to elevate their domain privs, should DBAs
be allowed to use xp_CmdShell?
A14: "Yes" with some possible provisions.
For example if the DBAs are trusted at the
SQL Server level but not at the Domain
level, then the SQL Server login and the SQL
Agent login must be throttled back by the
Domain Administrators to prevent such
elevation of privs. SQL Server and SQL
Agent should not be allowed to see more
than what you want a DBA to see.
Disabling xp_CmdShell... Is it Really a "Best Practice"?
42
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
Other Concerns
Q15: Are there better methods to do the
same things that could be done through
the proper and secure use of
xp_CmdShell?
A15: “It Depends” and seems to be either
an emotional, preferential, or resource
driven decision.
For me, considering answers 1 through 14,
the power of the tool, the availability of a
common command language understood by
every DBA, and the fact that it can all be
done through T-SQL (don’t have to setup
other systems), I have to say “NO”.
Disabling xp_CmdShell... Is it Really a "Best Practice"?
43
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
Other Concerns
Q16: What happens if someone is in
the middle of an xp_CmdShell run
and my proc disables xp_CmdShell?
A16: A command to disable
xp_CmdShell will execute
immediately but all instances
currently running will be allowed to
complete.
That’s good for the “Turn it on, use it,
turn it off” method.
Disabling xp_CmdShell... Is it Really a "Best Practice"?
44
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
Policies
Do setting policies like “Prevent
access to the Command Prompt”
prevent usage of xp_CmdShell?
Yes, but only for the unskilled.
Hackers are setup to blow right
through such things.
http://www.securitytube.net/video/653
People with “SA” privs can easily get to
the registry through sp_RegWrite.
Disabling xp_CmdShell... Is it Really a "Best Practice"?
45
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
Which is Better?
Discover What’s Best
Ancillary Question #2:
Which is better?
Disabling it and never using it?
Enabling it only when needed and then
disabling it, again?
Enabling it and leaving it enabled?
Disabling xp_CmdShell... Is it Really a "Best Practice"?
47
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
Which is Better?
Disabling it and never using it?
Provides only trivial logging.
Provides virtually no protection during an “SA”
attack.
Anyone with “SA” privs can enable and use it.
ONLY “SA” privs can enable and use it.
People “think” it provides security when it does
not and may lull them into a false sense of
security.
Only keeps the honest man honest.
Deprives DBAs and Devs of powerful, flexible,
easy to use tools.
Does provide the maximum “Nice Warm
Fuzzies”
Disabling xp_CmdShell... Is it Really a "Best Practice"?
48
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
Which is Better?
Enabling it only when needed and then disabling it,
again?
Provides only trivial logging (along with some nearly
useless “Log Clogging”).
Provides virtually no protection during an “SA” attack.
Anyone with “SA” privs can enable and use it.
ONLY “SA” privs can enable and use it.
People “think” it provides security when it does not and
may lull them into a false sense of security.
Provides DBAs and Devs with powerful, flexible, easy to
use tools.
Have to remember in person and in code to enable/disable
it every time it’s used. This means it might need to be
enabled multiple times in a script, job, or stored proc.
Does provide some “Nice Warm Fuzzies”
Disabling xp_CmdShell... Is it Really a "Best Practice"?
49
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
Which is Better?
Enabling it and leaving it enabled?
Provides no trivial logging (but helps with “Log
Clogging”).
Provides virtually no protection during an “SA”
attack (just like the other 2 options).
Anyone with “SA” privs can enable and use it.
ONLY “SA” privs can enable and use it.
People “think” is it’s a gaping security hole and
are more vigilant and diligent in limiting privs.
Provides DBAs and Devs with powerful, flexible,
easy to use tools.
Never have to remember to enable or disable it
in person, scripts or code.
Biggest disadvantage is “Visceral Fear” instead
of “Nice Warm Fuzzies”
Disabling xp_CmdShell... Is it Really a "Best Practice"?
50
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
To Summarize…
There appears to be no non-trivial
advantage to having xp_CmdShell
disabled.
There appears to be no more of a
security DISadvantage to having
xp_CmdShell enabled than when it’s
disabled.
There are DISadvantages to having
xp_CmdShell disabled.
Disabling xp_CmdShell... Is it Really a "Best Practice"?
51
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
To Answer the Question…
Disabling xp_CmdShell…
Is it Really a “Best Practice”?
Considering the facts discovered
(especially since it does nothing for
security), the answer is “NO”.
Considering that there are more nontrivial disadvantages to having it
disabled than having it enabled, it
would appear that disabling
xp_CmdShell is actually a “Worst
Practice”.
Disabling xp_CmdShell... Is it Really a "Best Practice"?
52
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
Enable Stored Procedures to
Safely Use xp_CmdShell
Without Pulling Your
Hair Out
Easy and Common Problem
Given a Directory Path, List All of
the Files in the Directory.
For example, DIR C:\Temp /b
The reason for such a simple
example is that it allows us to
concentrate on how to properly and
securely get to the Command Line
rather than concentrating on what
we can do once we get there.
Disabling xp_CmdShell... Is it Really a "Best Practice"?
54
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
Overview
1. Setup Proxy User in Windows.
Done only once for the lifetime of the SQL Server.
“Standard” user.
2. Login to SSMS as “Administrator”.
3. Setup CmdShell Proxy Account in SQL Server.
Done only once for the lifetime of the SQL Server.
Turn on xp_CmdShell
Create Login, User, Proxy, and grant priv to xp_CmdShell.
4. Ensure Correct Ownership of DB
Done only once in the lifetime of the DB.
5. Make Proper Stored Procedures to Use
xp_CmdShell.
Super easy to do.
Requires the addition of 4 easy to remember words.
Disabling xp_CmdShell... Is it Really a "Best Practice"?
55
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
Step 1: (skip if already done)
Setup Proxy User in Windows.
Precise instructions will vary by version of
Windows.
1.
2.
3.
4.
5.
6.
Click the Windows “Start”
Button.
Open the “Control Panel”
Open “User Accounts”
(might be under
“Administrative Tools”).
Create a new “Standard”
user.
Create a strong password
for the user and put in safe
place.
Exit the “Control Panel”
Disabling xp_CmdShell... Is it Really a "Best Practice"?
56
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
Step 2: (skip if already done)
Login to SSMS as “Administrator”
1. If you don’t already have
one, create a short-cut
on the Desktop for SSMS
(“Ctrl” drag from
Windows Start menu).
2. Right-click on the shortcut and select “Run as
Administrator”.
3. Login to SSMS as a login
that has “SA” privs.
Disabling xp_CmdShell... Is it Really a "Best Practice"?
57
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
Step 3: (skip if already done)
Setup CmdShell Proxy Account
Substitute the machine or domain name for “VAIO”.
Substitute the proxy user name from Step 1 for “CmdProxyUser”.
Change the GUID to the password of the proxy user from Step 1.
--===== We MUST be in the MASTER database to create the proxy user.
USE [master];
--===== Enable xp_CmdShell
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0; RECONFIGURE;
--===== Create the server login and map that login "user" the Master database
CREATE LOGIN [VAIO\CmdProxyUser] FROM WINDOWS
WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english];
CREATE USER [VAIO\CmdProxyUser] FOR LOGIN [VAIO\CmdProxyUser]
WITH DEFAULT_SCHEMA=[dbo];
--===== Create the proxy account using the user we just added
EXEC sp_xp_cmdshell_proxy_account 'VAIO\CmdProxyUser','6A1E4814-6149-4A31-AF3BB9D60624145F';
--===== Give the proxy user privs to execute xp_CmdShell.
GRANT EXECUTE ON xp_CmdShell to [VAIO\CmdProxyUser];
Disabling xp_CmdShell... Is it Really a "Best Practice"?
58
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
Step 4: (skip if already done)
Ensure Correct Ownership of DB
1. Start SSMS and login with “SA” privs.
2. If the Object Explorer isn’t already
open, press the {f8} key to open it.
3. Expand the databases folder.
4. Right click on the desired database
and select [Properties].
5. Click on [Files].
6. Type “sa” (without the quotes) in the
“Owner” box.
7. Click [OK].
Disabling xp_CmdShell... Is it Really a "Best Practice"?
59
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
Step 5:
Make Proper Stored Procedures
The only difference between this and a
typical stored procedure is 4 little
words (With Execute As Owner).
CREATE PROCEDURE dbo.GetDirInfo
WITH EXECUTE AS OWNER
AS
EXEC xp_cmdshell 'DIR C:\Temp /b‘
;
GRANT EXECUTE ON dbo.GetDirInfo TO PUBLIC;
Disabling xp_CmdShell... Is it Really a "Best Practice"?
60
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
DANGER! - DOS Injection
Whether you’ve decided to use
SQLCLR, xp_CmdShell, or what, YOU
need to be aware of DOS Injection.
It’s why I don’t like SQLCLR for
doing “DOS-like” things because I
don’t have control over such code
like I do xp_CmdShell.
DOS Injection is like SQL Injection
but at the command line and can be
even more deadly.
Disabling xp_CmdShell... Is it Really a "Best Practice"?
61
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
DANGER! - DOS Injection
Example Of BAD Stored Procedure
Simple proc like most people do.
CREATE PROCEDURE dbo.GetDirInfoBadWay
@Path VARCHAR(8000)
AS
DECLARE @CMD VARCHAR(8000);
SELECT @CMD = 'DIR "'+@Path+'" /b';
EXEC xp_CmdShell @Cmd;
GO
EXEC dbo.GetDirInfoBadWay 'C:\Temp'
;
Deadly DOS Injection
EXEC dbo.GetDirInfoBadWay 'C:\">nul & ECHO Bang u r dead & REM ';
Disabling xp_CmdShell... Is it Really a "Best Practice"?
62
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
Injection Proof
Example of Good Stored Procedure
Injection-Proof Proc Works Correctly
CREATE PROCEDURE dbo.GetDirInfoGoodWay
@Path VARCHAR(8000)
AS
DECLARE @CMD VARCHAR(8000);
SELECT @CMD = 'DIR "'+@Path+'" /b'
WHERE @Path NOT LIKE '%[^a-zA-Z0-9:\*. ]%'
AND CHARINDEX('REM ',@Path) = 0;
EXEC xp_CmdShell @Cmd;
GO
EXEC dbo.GetDirInfoGoodWay 'C:\Temp'
;
But Rejects Hacks and Gives Attacker No
Clues.
EXEC dbo.GetDirInfoGoodWay 'C:\">nul & ECHO Bang u r dead & REM ';
Command(s) completed successfully.
Disabling xp_CmdShell... Is it Really a "Best Practice"?
63
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
Quick Review
Quick Review
Discovered that Disabling xp_CmdShell offers no nontrivial benefits to security.
Learned that disabling it does not prevent attacks.
Learned that only limiting who gets “SA” privs prevents
attacks.
Even an attacker can’t use xp_CmdShell if they can’t get in as
“SA”.
An attacker can get to the Command Line many other ways if
they can get in as “SA”. They don’t even need xp_CmdShell.
Learned how to create a “Command Shell Proxy”.
Learned how to use the “proxy” to allow xp_CmdShell to
be safely used in stored procedures.
Learned how to allow individuals to run those procs
without having the ability to run xp_CmdShell directly.
Learned what DOS Injection is and how to prevent it.
Disabling xp_CmdShell might not be a “Best Practice”,
after all.
Disabling xp_CmdShell... Is it Really a "Best Practice"?
65
12 May 2013 © Copyright by Jeff Moden - All Rights Reserved
Q’n’A
Disabling xp_CmdShell…
Is it Really a “Best Practice”?
by Jeff Moden
Thanks for listening!