Microsoft SQL Server 2008 R2 Security Overview

1. Microsoft SQL Server 2008 R2Security Overview Name Title Microsoft Corp.

2. Agenda Introduction SQL Server 2005 Security Recap Security in SQL Server 2008 R2 Demo Compliance and Certifications

3. Business Challenges Data reliability is a growing concern for many enterprises Data misuse and detection/privacy violation • Insider threat – 70% of attacks come from inside the firewall* • Identity theft • Industrial espionage • Government espionage Regulations like PCI and HIPAA mandate strict requirements for data security, data privacy and data integrity *Source: Forrester, March 2009

4. Business Needs Ensure reliability, confidentiality, availability and integrity of data Demonstrate that good security practices are being followed in the database environment Provide a history of detailed auditing data for use by internal/external auditors

5. Insights into Database Vulnerabilities   Fewer vulnerabilities translates to less time spent patching servers and inherently more secure databases SQL Server continues to lead in lowest security patches across the major DBMS vendors Notes: Update as of 12/31/2010 inclusive. Chart counts NIST CVE – Reported Software Flaws by “published” date, utilizing the NIST NVD. SQL Server Query = Vendor Name: Microsoft Corporation; Product Names: sql_server, sql_server, sql_server_desktop_engine, sql_server_express_edition, sql_server_reporting services, sql_srvsql_srv_desktop_engine; Oracle Query = Vendor Name: Oracle; Product Name: ‘Any’, all CVEs where “Vulnerable software and versions” lists a database product; DB2 Query = Vendor Name: IBM; Product Names: db2, db2_content_manager, db2_content_manager_toolkit, db2_server, db2_universal_database; MySQL Query = Vendor Name: mysql, mysql-ocaml, mysql_auction, mysql_eventum, mysql_quick_admin, mysqldumper, mysqlnewsengine; Product Name: ‘Any’

6. SQL Server 2005 Security Recap

7. SQL Server 2005 Security Recap CONTROL ACCESS PROTECT DATA ENSURE COMPLIANCE Built-in encryption User-schema separation Capture and audit DDL activities Granular permission control Key management Password policy enforcement Catalog security Encrypted log-in credentials

8. SQL Server 2005 Security Recap Password policy enforcement Customer challenges Security feature Capture and audit DDL activities Weak passwords Built-in encryption User-schema separation Capture and audit DDL activities PROTECT DATA Built-in encryption Key management Lack of audit information Granular permission control Key management Password policy enforcement Catalog security Data confidentiality CONTROL ACCESS Catalog security Encrypted log-in credentials User-schema separation Metadata protection Execution context Module signing Schema level permission Encrypted log-in credentials Connection end-points Granular permission to execute statements in a module ENSURE COMPLIANCE Protect access to the DB

9. SQL Server 2008 Security Enhancements

10. SQL Server 2008 Investments CONTROL ACCESS PROTECT DATA ENSURE COMPLIANCE Authentication enhancements Transparent Data Encryption Policy-based Management Extensible Key Management User-schema separation SQL Server Audit Built-in encryption Granular permission control Capture and audit DDL activities Key management Encrypted log-in credentials Password policy enforcement Catalog security

11. Protect Data • Transparent Data Encryption • Extensible Key Management CONTROL ACCESS PROTECT DATA ENSURE COMPLIANCE Transparent Data Encryption Authentication enhancements Policy-based Management Extensible Key Management User-schema separation SQL Server Audit Built-in encryption Granular permission control Capture and audit DDL activities Key management Encrypted log-in credentials Password policy enforcement Catalog security

12. Data Protection Investments • PROTECT DATA Database security is a growing concern for many enterprises Recent regulations have mandated strict requirements for data security, data privacy and data integrity SQL Server 2005 limitations SQL Server 2008 • Encryption required application changes • Encryption keys not separate from data • Extensible Key Management (EKM) • Transparent Data Encryption (TDE)

13. PROTECT DATA Extensible Key Management (EKM) Key storage, management and encryption done by HSM module HSM SQL EKM key is a proxy to HSM key SQL EKM Provider DLL SQL EKM Provider DLL implements SQLEKM interface, calls into HSM module SQL EKM Key (HSM key proxy) Data SQL Server

14. “Defense in depth” makes unauthorized access to data harder by storing encryption keys away from the data May facilitate separation of duties between DBA and data owner Uses HSM for encryption and decryption which may result in performance gains Enables centralized key management across organization • PROTECT DATA Benefits of using EKM …SQL Server 2008 helps CareGroup comply with HIPPA data encryption requirements… SQL Server 2008 delivers an excellent solution… by supporting third-party key management and hardware security module products.—CareGroup Case Study Evidence

15. PROTECT DATA EKM Key Hierarchy in SQL Server HSM Symmetric key Asymmetric key EKM Symmetric key EKM Asymmetric key SQL Server Data Data Native Symmetric key TDE DEK key

16. PROTECT DATA Transparent Data Encryption (TDE) Certificate required to attach database files or restore a backup Encryption/decryption at database level SQL Server 2008 DEK DEK is encrypted with: • Certificate • Key residing in a Hardware Security Module (HSM) Encrypted data page Client Application

17. Encrypt the entire database on the disk to protect against lost or stolen disks or backup media Does not increase database size and has minimal performance impact Does not require application changes Applications do not need to explicitly encrypt/decrypt data Backups are automatically encrypted and unusable without key • PROTECT DATA Advantages of using TDE Protects against direct access to database files, data at rest • “With SQL Server 2008 we have transparent encryption, so we can easily enforce the encryption of the information in the database itself without making any changes on the application side.” • — AvadShammout, Lead Technical Database Administrator, CareGroup HealthCare System Evidence

18. PROTECT DATA TDE – Key Hierarchy DPAPI encrypts Service Master Key • Operating System Level Data Protection API (DPAPI) Service Master Key encrypts Database Master Key • SQL Server 2008Instance Level • Service Master Key • SQL Server 2008Master Database Password • Database Master Key Database Master Key encrypts Certificate In Master Database • Certificate • SQL Server 2008Master Database • Database Encryption Key Certificate encrypts Database Encryption Key • SQL Server 2008User Database

19. Demo: Enabling TDE

20. Control Access • Authentication enhancements CONTROL ACCESS PROTECT DATA ENSURE COMPLIANCE Transparent Data Encryption Authentication enhancements Policy-based Management Extensible Key Management User-schema separation SQL Server Audit Built-in encryption Granular permission control Capture and audit DDL activities Key management Encrypted log-in credentials Password policy enforcement Catalog security

21. CONTROL ACCESS Authentication Enhancements SQL Server 2005 limitations SQL Server 2008 enhancements • Kerberos available with ALL protocols • SPN may be specified in connection string (OLEDB/ODBC) • Kerberos possible without SPN registered in AD • Kerberos possible with TCP/IP connections only • SPN must be registered with AD

22. CONTROL ACCESS Authentication Enhancements Why specify an SPN in the connection? SPN composed using 2 insecure sources Attacker could force NTLM to be used, authentication failures, or redirect connections to rogue servers DNS SQL Browser SPN = MSSQLSvc/<FQDN>:<Port>

23. Ensure Compliance • Policy-based Management • SQL Server Audit CONTROL ACCESS PROTECT DATA ENSURE COMPLIANCE Transparent Data Encryption Authentication enhancements Policy-based Management Extensible Key Management User-schema separation SQL Server Audit Built-in encryption Granular permission control Capture and audit DDL activities Key management Encrypted log-in credentials Password policy enforcement Catalog security

24. Automate surface area configuration Ensure compliance with configuration policies for servers, databases, and database objects across the enterprise Reduce your exposure to security threats by using the new Surface Area facet to control active services and features • ENSURE COMPLIANCE Policy-Based Management

25. ENSURE COMPLIANCE Policy-Based ManagementDefining Policies Conditions Policies Targets Facets Categories

26. ENSURE COMPLIANCE Policy-Based ManagementPolicy checking and governance Facets Conditions • Define aspects of system configuration • Physical properties that relate to settings • Logical properties that encapsulate business rules • Capture the declarative intent (desired state) • Simplify compliance enforcement

27. ENSURE COMPLIANCE Auditing Database Activity SQL Server 2005 SQL Server 2008 enhancements • SQL Server Audit • SQL Trace • DDL/DML Triggers • Third-party tools to read transaction logs • No management tools support

28. ENSURE COMPLIANCE SQL Server Audit Create an Audit object to automatically log actions to: Audit now a 1st Class Server Object • Native DDL for Audit configuration and management • Security support • File • Windows Application Log • Windows Security Log Ability to define granular Audit Actions of Users or Roles on DB objects

29. Track reads, writes, and other events to Windows Application Log and Windows Security Log Detect misuse of permissions early on to limit possible damage More granular audits for flexibility Built into the database engine Simple configuration using SQL Server Management Studio • ENSURE COMPLIANCE Benefits of SQL Server Audit Faster performance than SQLTrace • “The enhanced auditing tools in SQL Server 2008 enable us to track all changes to tables and other data elements in our system.” • —AvadShammout, Lead Technical Database Administrator, CareGroup HealthCare System Evidence

30. ENSURE COMPLIANCE Audit Specifications Database action groups Server action groups Server and database audit specifications for • Server config changes, login/logoff, role membership change, etc. • Schema object access, database role membership change, database object access, database config change • Pre-defined action groups • Individual action filters

31. Audit Specifications • File system • File • Audit • Security Event Log • Application Event Log 0..1 DB audit specification per database per Audit object 0..1 Server audit specification per Audit object • Server Audit Specification • Database Audit Specification • Server Audit Action • Server Audit Action • Server Audit Action • Server Audit Action • Database Audit Action • Server Audit Action • Database Audit Action • Database Audit Action • Database Audit Action • Database Audit Action • CREATE SERVER AUDIT SPECIFICATION SvrAC • TO SERVER AUDIT PCI_Audit •     ADD (FAILED_LOGIN_GROUP); • CREATE DATABASE AUDIT SPECIFICATION AuditAC • TO SERVER AUDIT PCI_Audit •     ADD (SELECT ON Customers BY public)

32. Demo: SQL Server Audit and Policy-Based Management

33. Compliance and Certifications Common Criteria Certified HIPAA and PCI Compliance Evaluated

34. The Common Criteria was designed by a group of nations to improve the availability of security-enhanced IT products, help users evaluate IT products for purchase, and contribute to consumer confidence in IT product security. — SQL Server Books Online World-Class Security Evaluations Key Criteria Common Criteria Certification Requirement for many governments, industries, and enterprise customers Security functions: Access control, audit, management, identification & authentication, session handling and memory management SQL Server 2008 Enterprise achieved Common Criteria (CC) compliance at EAL1+ (Evaluation Assurance Level), EAL4+ is in progress and recognized by the US government Assurance components: Functional specs and high level design plus independent vulnerability testing Represents the third time for CC compliance and the first time for a 64-bit version of SQL Server Environment: CC certified OS (Windows Server) and admin roles R2 is built on the SQL Server 2008 foundation and brings forward the security benefits with minimal changes to the core engine Evidence

35. SQL Server for HIPAA Compliance HIPAA Details SQL Server Support Take advantage of SQL Server 2008 capabilities to help meet database-related compliance requirements Health Information Portability and Accountability Act (HIPAA) governs health information privacy, security, organizational identifiers, and overall administrative practices Technical features can support HIPAA requirements like role-based access, strong user authentication, encryption, and event logging HIPAA has 5 major components, SQL Server can help support the Security Rule; ensuring protected health information (PHI) SQL Server supports HIPAA areas: Access controls, Data integrity & encryption, Communications security, and Audit & compliance SQL Server features can promote the consistency of deployed technical controls and enable effective monitoring over time • Whitepaper: “Supporting HIPAA Compliance with Microsoft SQL Server 2008,” Authored by Information Security Center of Expertise at Jefferson Wells International, Inc, a leading Risk Advisory and Security Compliance services organization. Evidence

36. SQL Server for PCI Compliance PCI Details SQL Server Support Take advantage of SQL Server 2008 capabilities to help meet database-related compliance requirements Payment Card Industry (PCI) Data Security Standard (DSS) is a worldwide security standard created by the Payment Card Industry Security Council SQL Server can be deployed to meet the database server requirements and should always be considered by personnel in cardholder environments Technical features can support PCI requirements like TDE, EKM, SQL Server Audit, and Policy-Based Management SQL Server supports PCI areas: Vendor-supplied defaults, protect stored data, encrypt data transmission, restrict access to data, assign unique IDs to persons with access, and monitor all access to data Automated implementation of key SQL Server 2008 features help enable customers to achieve PCI compliance and standardized security controls • Whitepaper: “Deploying SQL Server 2008 Based on Payment Card Industry Data Security Standards (PCI DSS),” Authored by certified audit firm, Parente Randolph (now ParenteBeard). Evidence

37. Q & A

38. © 2010 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

39. Appendix

40. Reduced Surface Area Configuration Efforts made in reducing surface area include: • Some features are off by default(except when you perform an upgrade • Granular permissions on SQL engineand SQL Server Agent • Users need VIEW DEFINITION permissionsto see metadata that they do not own New in SQL Server 2008, Surface Area Configurations are handled by automated policy-based framework to help ensure compliance across the enterprise

41. Authentication Features Ability to disable a login Password Policy Enforcement Endpoint Based Authentication NTLM and Kerberos for Windows logins Default Secure channel for standard SQL logins • Password complexity, Password expiration, Account lockout • Common policy across the network for windows and SQL • Granular control to turn on/off policy/expiration per login • Useful if login is compromised or user is fired • Ability to choose which users connect over which protocols • Single Sign On • Constrained delegation with Win2K3 (Granular control) • No admin step required to get secure (secure by default)

42. SQL Server 2008Authorization • Principle of least privileges Rich access control model • Granular permissions • Choice of appropriate scope(database, schema, object, sub-object) • Role Based Access control • Application module based access control • Minimizing application impact for user management • Both data (above) and metadata • Ease of security management