ten security tips for sql server - mssqltips · idera sql compliance manager improve any sql server...
TRANSCRIPT
Ten Security Tips for SQL
Server
Andy Warren
http://sqlandy.com
http://linkedin.com/in/sqlandy
Idera SQL Compliance ManagerImprove any SQL Server Audit
Audit Sensitive Data - see who did what, when, where, and how
Track and Detect - monitor and alert on suspicious activity
Satisfy Audits - for PCI, HIPAA, FERPA and SOX requirements
Generate Reports - 25 built-in reports to validate SQL Server audit trails
Minimize Overhead - light data collection agent minimizes server impact
www.idera.com
About Me
• DBA, Consultant, Writer
• Served on the PASS Board
• Co-founder of SQLSaturday
• Former President of oPASS
• Founding principal in
SQLServerCentral.com
3
Goals For Today
• Get you thinking about security!
4
Agenda Page
• What does compliance want and why?
• What about the DBA?
• And the hacker?
• What works and what is security theater?
• 10 security tips
• Additional ideas and reading
• Q&A
5
Understanding Compliance
• Technical, but not as technical as you
• Places high value on best practices
• Places high value on vulnerability scans
• Wants to make sure the obvious has been
done
• Higher level, more holistic view
6
What Does Compliance Want?
• Least privilege
• Granular permissions
• Lots of logging/auditing
• Change control
• Usable alerts
• Process. Process. Process. Sustainability
7
What Does the DBA Want?
• Crisp and clear patterns for securing
instances and databases
• Reasonable belief that they are “doing
enough”
• Support from the business and compliance
on enforcing the rules
• Not wind up on the evening news due to a
breach
8
The hacker!
• Wants? Access of course
• Starts on tertiary systems that “aren’t
important” and works their way in to the
core
• Looking for clues, exploits common gaps
• Tries hard to erase footprints
9
Good or Theater?
• In the SQL space most things we do are
reasonable, but here is a test:
– Enable C2 Auditing
– Require Windows Authentication Only
– Disable xp_cmdshell
10
The Caution Statement!
• These are basic tips, but still worth doing
and checking
• There are always exceptions
• Even a small change can break things (but
are also easy to undo)
11
#1 – Disable The Guest Account
• Start with something easy and obvious by
making sure there is no anonymous
access allowed in any of your user
databases, plus model
• Not as easy as just removing it from the
instance
12
#2 – No Grants to Public
• Auditors want to see explicit grants to well
defined roles (ideally that map to a
business role or an application role)
• Our goal is to deny information by default,
grant as requested/approved. Public =
bad!
13
#3 – Remove BuiltIn\Admins
• Not required for SQL to have this
• If admins require access, add a domain
group as a login (even if it has to be a
sysadmin member)
• Will require some testing, but it’s not a
major change
14
#4 – Audit AND Alert on Login Failed
• Typically only audit failed logins
• Failures should be relatively rare
• Lots and lots of options for alerting
– Leverage SQL Agent, alerts, etc
– External monitor
• Nothing more important than to investigate
these, this is your best chance to catch an
attack in the early stages
15
#5 – Rename the SA Login
• Obvious target for a hacker
• Rename, then create a fake SA that
becomes the bait
16
#6 – Disable The Browser Service
• Or at least disable advertising
• Denying knowledge slows the attack down
• Surprisingly effective
17
#7 – Change the Default Port
• Hackers know to try 1433
• Any other port takes longer to find, more
chance we detect them looking
• Make sure your scanner knows the correct
port to scan on!
18
#8 – Grant Perms to Roles Only
• More secure? No. And Yes.
• Roles are packaging, they play a big part
in managing effectively AND in the annual
(or more) access recertification
• Adding someone to a role is a simple
change with not much chance of error
• Arguably much riskier to do a bunch of
grants for a new user
19
#9 – Remove Unused Logins
• Ideally logins are groups or service
accounts that require little maintenance
• Once a year (or more) review is required
– Check for orphaned users while you’re at it!
20
#10 – Vulnerability Scans
• Scanners (Rapid7, Qualys, etc) may not
catch everything, but they catch a lot
• Run often! Especially after any patching,
upgrade, deployment
• Not all issues can be fixed immediately –
prioritize
• Fix, then rescan to confirm
21
Summary
• Security is complicated
• Hardening is first (and evolving) step
• Process is incredibly important, one slip
can let an attacker through
• Monitoring and alerting can be home
grown, but easier with tools
• Easy to have a gap that gets overlooked
(it’s always been that way)
22
Resources
• Securing SQL Server by Denny Cherry
• Microsoft SQL Server 2012 Security
Cookbook by Rudi Bruchez
• SQL Server Security Checklist by Tibor
Nagy
• Nexpose
23
Questions and Wrap-up
24