Keeping the DBA out of the database

2. SESSION CODE: DAT310 Peter Ward @wardy peter@wardyit.com +61 403 177 761 Chief Technical Architect WARDY IT Solutions – www.wardyit.com Keeping the DBA out of the database

3. About Peter Ward • Chief Technical Architect WARDY IT Solutions • Email: peter@wardyit.com • Twitter: @wardy • SQL Server Microsoft Most Valuable Professional (MVP) • Microsoft Virtual Technology Solutions Professional (TSP) • WARDY IT Solutions • Australia’s leading SQL Server specialists • MAPA Data Platform Partner of the Year since 2009

4. Agenda • SQL Server Security Best Practices • Authorising Elevated Privileges • Separation of Duties Framework

5. Unauthorised Database Access

6. (c) 2011 Microsoft. All rights reserved.

7. (c) 2011 Microsoft. All rights reserved.

8. Principals & Securables • Principals • Objects that can be granted permissions to access objects • Securables • Objects to which access can be controlled

9. sysadmin Straw Poll Question Are permissions checked if a login is a member of the sysadmin server role?

10. sysadmin Straw Poll Question Are permissions checked if a login is a member of the sysadmin server role? Answer No, SQL Server will bypass the Permission Check Algorithm if the login is a member of the sysadmin fixed server role

11. sysadmin Fixed Server Role • Do not use the sysadmin role for everyday tasks • Limit role to logins for emergency use or as required • Treat the sysadmin role as a protected role • Grant individual permissions on the server • CONTROL SERVER corresponds to sysadmin

12. CONTROL SERVER • CONTROL SERVER grants full server level permissions and full access to all databases • CONTROL SERVER allows a granular approach to granting and denying access to securables • Limited processes that do not function under CONTROL SERVER

13. CONTROL SERVER example USE [master] GO GRANT CONTROL SERVER TO [Corp\DBA_Team] GO DENY ALTER ANY LOGIN TO [Corp\DBA_Team] GO USE [AdventureWorks2008R2] GO DENY CONTROL ON [HumanResources].[EmployeePayHistory] TO [Corp\DBA_Team] GO

14. sa account • sa account should never be used • sa login is not mapped to an individual and is high privileged • Privileges cannot be reduced • Rename and disable the account Logon - Error: 18456, Severity: 14, State: 7 Login failed for user 'sa'. [CLIENT: 61.129.123.41] Logon - Error: 18456, Severity: 14, State: 7 Login failed for user 'sa'. [CLIENT: 218.24.197.235]

15. Disable and Rename the sa account USE [master] GO ALTER LOGIN sa DISABLE; ALTER LOGIN sa WITH NAME = [SQLsa];

16. db_owner • db_owner is a fixed role in each database • DBO is an automatic member • sysadmins map to DBO • DBO will bypass the permission check algorithm • db_owner similar to a database-scoped sysadmin • Members of db_owner may have granular permissions granted and denied

17. Restricting db_owner USE [AdventureWorks2008R2] GO CREATE USER[CORP\Helpdesk] FOR LOGIN [CORP\HELPDESK] GO EXECsp_addrolemember N'db_owner', N'CORP\Helpdesk' GO DENY ALTER ANY USERTO[CORP\Helpdesk] GO

18. Security Security is assigning permissions as needed and denying permissions were risk has been accessed

19. Granular Server Permissions • SQL Server supports granting permissions at a granular level • Eg. view metadata but not the data • More then 120 granular permissions at the server level • Do not use fixed server-level roles • Determine what permissions are required to accomplish the assigned task and only grant those permissions

20. Demo • Demonstrate why you should only grant those permissions required for an assigned task

21. Auditing • Every installation of SQL Server with have one sysadmin login • Compensating controls are auditing and polices • Need to protect against inside threats • Auditing captures instance and database level events

22. Demo • Demonstrate how to use auditing to track highly privileged users who manage security

24. Elevated Privileges • DBA permissions need to be minimized to limit direct access to SQL Server objects • At a minimum DBA’s should not be in the sysadmin role • Certain tasks required elevated privileges up to and including sysadmin • Temporary access can be granted using EXECUTE AS or signed modules

25. Authorising Elevated Privileges • EXECUTE AS temporarily changes the execute context of the caller to an elevated user • Signed Modules adds the need permissions to the caller without changing the primary identity of the execution context

26. Demo • Demonstrate using EXECUTE AS

27. Signed Modules • Provide a secure and auditable mechanism to enable permissions to execute a specific task • A module may be a Function, Trigger, Assembly or Stored Procedure • Allows security to be maintained whilst allowing a DBA to be responsively empowered • Separation of Duties Framework Simplifies the implementation

28. Demo • Demonstrate how to implement a Signed Module

29. Separation Duties Framework • Signed Modules increase the granularity of SQL Server permissions • SoD Framework designed to simplify the implementation of Signed Modules • Supports multiple tiers of access • Predefined set of processes to manage a restrictive instance and sensitive databases

30. Setting up SoD • Define the roles and tasks • Create folders representing the defined roles • Add .sql files to the folders • Execute the Powershell install script • Place users and groups into the database roles

31. Demo • Demonstrate implementing the SoD Framework

32. Resources • SQL Server Separation of Duties Framework • http://bit.ly/SQLSoD • WARDY IT Solutions SoD Utility • http://bit.ly/WARDYSoD

33. Enrol in Microsoft Virtual Academy Today Why Enroll, other than it being free? The MVA helps improve your IT skill set and advance your career with a free, easy to access training portal that allows you to learn at your own pace, focusing on Microsoft technologies. • What Do I get for enrolment? • Free training to make you become the Cloud-Hero in my Organization • Help mastering your Training Path and get the recognition • Connect with other IT Pros and discuss The Cloud Where do I Enrol? www.microsoftvirtualacademy.com Then tell us what you think. TellTheDean@microsoft.com

34. © 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. (c) 2011 Microsoft. All rights reserved.

35. Resources • www.msteched.com/Australia • Sessions On-Demand & Community • www.microsoft.com/australia/learning • Microsoft Certification & Training Resources • http:// technet.microsoft.com/en-au • Resources for IT Professionals • http://msdn.microsoft.com/en-au • Resources for Developers (c) 2011 Microsoft. All rights reserved.