Better Together: Secure SQL Server on Secure Windows

1. Required Slide SESSION CODE: DAT304 Better Together: Secure SQL Server on Secure Windows Al Comeau SQL Server Security Lead Microsoft Corporation

2. Goals • Investigate Security from a different perspective • Intersection between SQL Server and Windows • Cover some familiar ground, but look further “under the hood” • Provide some hints and tips you can bring back with you and (hopefully) make use of

3. AGENDA • Setup Install • Service Configuration • Access Control • Authentication • Auditing • User Account Control (UAC) and Impact on SQL Server

4. SQL SERVER SETUP INSTALL • Feature Selection • Product File Installation • Binaries are installed • Log/Data files instantiated • Registry Keys created and populated • Service Configuration • Service Account • Startup Configuration • Access Control • Resources protected through strong ACL’s to: • NT Administrators • SQL Server Service Principals

5. SQL SERVER SERVICES CONFIGURATION • SQL Server Service Accounts • User Specified Service Account • Some Services Default To Pre-determined Account • Startup Configuration - Services are configured in the following modes: • Automatic • Manual • Disabled • Service SID • New Service Principal in Windows Vista and above • Access granted to Service SID to access OS and SQL resources

6. SQL SERVER and SERVICE SID • Service SID • New Service Principalintroduced in Windows Vista, Windows Server 2008 and above • Least privilege Principal to access and protect resources • Provide Service Isolation and Defense in depth • Reduce damage potential • Windows Service Control Manager derives a SID from normalized service name • E.g. NT Service\Service Name • SCM adds service SID to process token S-1-5-80-XXXXX-YYYYY • SQL Server usage of Service SID • Service SID is enabled for SQL Server services at service configuration • Privileges are granted to Service SID at service configuration

7. SQL SERVER SERVICES WITH per SERVICE SID

8. SQL SERVER ACCESS CONTROL • Depending on deployment configuration, SQL Server uses NT service group or Service SID to access resources • NT service group • Created locally at setup install for each SQL Server service • Group membership contains SQL Server service account or Service SID • Service privileges granted to the service group • Use as a indirection for access control • Service SID • Provide single consistent access control behavior • Simplify service account configuration • Simplify service account change • SQL Server Engine and SQL Server Agent Service SID are provisioned as Login in the Sysadmin Server role

9. SQL Server Service Account SQL SERVER ACCESS CONTROL • SQL Server 2005 File System and Registry Permissions Domain Account Or Built In Accounts Local Windows Group SQL Server sysadmin role • SQL Server 2008 File System and Registry Permissions Local Windows Group Domain Account Or Built In Accounts SQL Server sysadmin role Windows XP Windows Server 2003 Start/Stop and Off box permissions? Local Windows Group File System and Registry Permissions Domain Account Or Built In Accounts Windows Vista Windows Server 2008 SQL Server sysadmin role NT Service\Service Name

10. SQL SERVER ACCESS CONTROL BEHAVIOR

11. SQL SERVER SERVICE PRINCIPAL PROVISIONING

12. SQL SERVER AUTHENTICATION • Windows Authentication default • OS and SQL resources accessed using Windows token • Single sign on • Simplified administration • No password management • Leverage Windows Password policy to enforce password compliance • Complexity • Expiration • Lockout enforcement • Protect conversations and credentials in transit • Windows principal provisioned as login inside SQL Server • Login token constructed from Windows

13. SQL SERVER LOGIN PROVISIONING • Logins provisioned as SQL Administrators (Sysadmin): • Principals with highly elevated privileges • “SA” built-in login • Disabled for Windows Authentication Mode • Enabled for Mixed Authentication Mode • Windows principal provisioned @Setup install • Local System • SQL Server Engine Service Account or Service SID • SQL Server Agent Service Account or Service SID • NT Adminsare not provisioned inside SQL Server by default and thereby provides Separation and Isolation between NT Admin & SQL Admin

14. SQL SERVER IMPERSONATION • Impersonate Windows user to access OS and SQL resources • Windows user must have access to the resources explicitly – no Elevation of Privilege opportunity • Impersonate SQL Service principal [context] where SQL Login is a highly privilege elevated login • SQL Service principal must have access to the resources explicitly

15. SQL SERVER AUDITING • Windows Event Log to record SQL Server events like Login Failure, SPN registration, Authentication details etc. • Application Log • Security Log • Use Security Log for better separation and stronger repudiation

16. USER ACCOUNT CONTROL (UAC) AND SQL SERVER • UAC is a new feature on Windows Vista and above • UAC allow users to perform common tasks as non-administrators • Running with least privilege helps protect the system • UAC is ON by default • UAC Impact on SQL Server 2005 • SQL Connectivity • SQL Server provision Built-In\Administrators group to Sysadmin server role • When an NT admin makes a request to connect to SQL Server 2005 on Vista, the connection attempt fails • The connection token does not include administrator privileges and so the SQL instance does not recognize it a valid login • Solution Do not rely on Built-In\Administrators login provisioning. Explicitly provision Windows principal as login

17. USER ACCOUNT CONTROL (UAC) AND SQL SERVER • UAC Impact on SQL Server 2008 • SQL Server 2008 setup install require NT admin to specify windows principal to provision to the Sysadmin server role • When provisioned principal makes a request to connect to SQL Server 2008 on Vista, the connection succeeds • SQL Server Applications • SQL Server categorized its applications into two categories – Admin and Non-admin • The applications that take admin action on the machine and there by required admin privileges are marked [manifested] to elevate on Vista and above • The applications that do not take admin action on the machine are not marked to elevate

18. Questions?

19. Required Slide Track PMs will supply the content for this slide, which will be inserted during the final scrub. Track Resources • SQL Server 2008 R2 Books Online • SQL Server Security Portal • SQL Server Security Forum • SQL Server and User Account Control (UAC)

20. Required Slide Speakers, please list the Breakout Sessions, Interactive Sessions, Labs and Demo Stations that are related to your session. Related Content • DAT302 - Achieving Compliance with Microsoft SQL Server 2008

21. Required Slide Track PMs will supply the content for this slide, which will be inserted during the final scrub. DAT Track Scratch 2 Win • Find the DAT Track Surface Table in the Yellow Section of the TLC • Try your luck to win a Zune HD • Simply scratch the game pieces on the DAT Track Surface Table and Match 3 Zune HDs to win

22. Required Slide Resources Learning • Sessions On-Demand & Community • Microsoft Certification & Training Resources www.microsoft.com/teched www.microsoft.com/learning • Resources for IT Professionals • Resources for Developers • http://microsoft.com/technet • http://microsoft.com/msdn

23. Required Slide Complete an evaluation on CommNet and enter to win!

24. Sign up for Tech·Ed 2011 and save $500 starting June 8 – June 31st http://northamerica.msteched.com/registration You can also register at the North America 2011 kiosk located at registrationJoin us in Atlanta next year

25. © 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.

26. Required Slide