oPASS – March 8, 2012

1. The Dirty Business of Auditing oPASS – March 8, 2012 Auditing SQL Server (2000 – 2008R2) K. Brian Kelley MCSE, CISA, Security+, MVP-SQL Server

2. My Background • Database Administrator / Architect • Infrastructure and security architect • Incident response team lead • Certified Information Systems Auditor (CISA) • SQL Server security columnist / blogger • Co-Author of: • How to Cheat at Securing SQL Server 2005 (Syngress) • Professional SQL Server 2008 Administration (Wrox) • Introduction to SQL Server (Texas Publishing)

3. Contact Information • Mail: kbriankelley@acm.org • Twitter: @kbriankelley • Blogs: • SQL Server Central • http://gkdba.wordpress.com/

4. Agenda for Tonight • Why auditors can’t audit SQL Server: “Tag, you’re It” • SQL Server Surface Area • Server Level Auditing • Database Level Auditing

5. Information Disclosure Issue • SQL Server 2000 – Access to DB, you can audit • But so can anyone… • Catch-22 • SQL Server 2005+, you must have permissions to object. • Recommendation: Automate the auditing. Use service account with proper permissions.

6. Surface Area – From Remote • Quest Discovery Wizard • SQL Ping • MS Assessment and Planning (MAP) tool • nmap • General scanner – Qualys, Nessus

7. Surface Area – On the Server • SQL Server 2000: • SQL Server Server Network Utility • SQL Server 2005 only: • SQL Server Surface Area Configuration • SQL Server 2005 and above: • SQL Server Configuration Manager

8. What to Look For • What network protocols • What ports SQL Server is listening on • Whether remote connections are allowed

9. Server Level Concerns • SQL Server 2000 and above • SQL Server 2005 and above

10. All Versions • Logins • SQL Server logins • Windows users • Windows groups • Server Roles

11. What to Look For • Windows users (not service accounts) • A lot of SQL Server logins • Members of: • sysadmin • securityadmin • serveradmin • Processadmin • Use of sa or sysadmin level accounts

12. SQL Server 2005 and above • Server level securables • DAC (remote) • OLE automation • SQL Mail • xp_cmdshell • Password policy enforcement • Impersonation of Logins

13. Visualizing Securables

14. What to Look For (2005+) • Everything in all versions list • CONTROL permission at Server level • IMPERSONATE of sa or sysadmin logins • SQL logins without full password policy enforcement: • No enforcement at all • Password never expires

15. Database Level Concerns • SQL Server 2000 and above • SQL Server 2005 and above

16. All Versions • How database users map to server logins • Use of guest user (except system DBs) • Database Owner (maps as dbo) • Members of database roles: • db_owner • db_ddladmin • db_securityadmin • Database level permissions (CREATE)

17. SQL Server 2005+ • Permissions at database securable level • Permissions at schema securable level • Encryption key escrow

18. What to Look For • Use of database owner by application • Use of db_owner by application • End users with too many rights • Developers in the following roles in prod: • db_owner • db_ddladmin • db_securityadmin

19. Questions & Answers