SQL Server Denali Security

2. SQL Server Denali Security

3. The SQL Server 2008 R2 Security Story

4. SQL Server 2008 R2 Security Recap Security features Transparent Data Encryption Customer challenges Protect data-at-rest Extensible Key Managements PROTECT DATA Data/Key separation Kerberos authentication enhancements Use strong authentication CONTROL ACCESS SQL Server Audit Change Data Capture Monitor all activity Policy-Based Management Detect non-compliant configurations Common Criteria Certification (EAL4+) ENSURE COMPLIANCE Industry Certification

5. SQL Server Denali Security Transparent Data Encryption Customer challenges Crypto Enhancements Security features Protect data-at-rest Extensible Key Managements PROTECT DATA Data/Key separation Kerberos authentication enhancements Contained Database Authentication Default Schema for Groups User-Defined Server Roles Use strong authentication CONTROL ACCESS Change Data Capture User-Defined Audit Audit Resilience Audit in all SKUs SQL Server Audit Audit Filtering T-SQL Stack Info Monitor all activity Policy-Based Management Detect non-compliant configurations Common Criteria Certification (EAL4+) ENSURE COMPLIANCE Industry Certification

6. User-Defined Server Roles • What: Allow creation of new roles for login with server permissions • Problem: Administrators has too many privileges • Benefits: Increase flexibility, manageability, and better compliance in data access • Configurable server roles (vs. fixed): securable class, permission set can change as needed • Server-level principal: Administrator define server groups (container), add logins, add/deny permissions • Lock down administrators privilege (better separation of duties) • Avoid using sysadmin (sa) for DBA • Triggers can be used for custom response when add/remove logins to user-defined server roles DBARole CONTROL SERVER ALTER ANY LOGIN

7. Default Schema for Groups • What: Can assign default schema to a group (no. 1 customer request) • Problem: Users and schemas are implicitly created when Windows Groups are used in development • Benefits: Decrease complexity of database schema management • Schema tied to group rather than individual users • Prevent errors of assigning schema to the wrong users • Prevent unnecessary implicit user and schema creation • Reduce chances of wrong schema used in queries when not using 2 part-names ([app].[table1] versus [table1] where schema is ambiguous) Default schema = schema1 Group1

8. Contained Database Authentication • What: Users can be authenticated and given access to a database without a login • Problem: • Deployment errors when forgot to create logins (e.g. DB Mirroring) • Don’t know which logins tied to which applications • Benefits: Simplify deployment of database applications and increase manageability of database access • Easier to deploy applications from dev.->test->production (self-contained). • Tightly scope security boundary. Don’t need to create unnecessary login (harder to track) • Better separation between administrators and users (Logins -> Administration, Users -> Applications). • Reduces the number of needed logins and the chance of orphaned, or unused logins DB Instance User DB Users

9. Audit Resilience • What: Audits automatically recover from many temporary file and network issues • Problem: Hard choice for customers (either miss audit or stop SQL Server) • Benefits: Better recovery, uptime, isolation, and manageability • Self healing audit process for temporary file or network issues • Audits can prevent audited operations when the audit is in a failed state rather than take down entire SQL Server instance • Before: • Write failures may silently lose Audit records • Use ON_FAILURE = SHUTDOWN • Now: • Automatically recover from most file or network errors • Added “ON_FAILURE = FAIL_OPERATION” • Added “MAX_FILES” option • Administrators can debug audit on the specific operation and reliably connect using Dedicated Administrator Connection (DAC) even when Audit is preventing all operations

10. Audit on all SKUs • What: Basic SQL Audit features are available on all SKUs of SQL Server • Problem: • No SQL Audit on lower SKUs (need SQL trace). • Admin requires to learn SQL Trace and SQL Audit • Benefits: Audit standardization, better performance, and features • Available for all SKUs. No need SQL Trace • SQL Audit has better features (multiple audits, multiple targets, persist state, DDL, Triggers for DDL, UI, Autostart, more resilience options) • Better performance than SQL Trace • Server Audit Specs only (including XEvents). DB Audit Specs in Enterprise SKU. All SQL Server SKUs

11. User-Defined Audit & Audit Filtering • What: • Application code can write custom events into the audit log • Audit events can be filtered prior to write to the audit log much like they could in SQL Trace • Problem: Fixed set of audit information only • Benefits: Better flexibility • Customizable audit information • Selective. Remove unnecessary events from auditing exec sp_audit_write 1234, 1, N‘Hello World’ @user_defined_event_id @succeeded @user_defined_info Audit Log

12. T-SQL Stack Information • What: • Additional information for business logic information • Problem: Hard to audit 3-tier applications (middle tier using single account to access database) • Benefits: Better auditing • Able to report front-end user information • Able to view detailed information about execution stack exec hr.viewsalary select salary from hr.payroll hr.viewsalary hr.payroll Audit Log

13. Crypto Enhancements • Create certificate from bytes • 4K certificates supported for import • SMK/DMK default to AES256 • Key backups encrypted with AES256 • SHA2 (256 and 512) support • Password hashes use SHA512 • RC4 deprecated

14. © 2011 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.