SQL Server 2005 Security Enhancements

1. SQL Server 2005 Security Enhancements Dr Greg Low Senior Consultant Readify greg.low@readify.net

2. Original version of this material was DAT330 TechEd 2004 presented by Girish Chander

3. Agenda • Yukon Security Features • Authentication • Endpoint Based Authentication • Password Policy Enforcement • Strengthening SQL Authentication • Authorization • User-Schema Separation • Module Execution Context • Granular Permissions Control • Catalog Security • Crypto support • Data Encryption

4. Payloads TDS SOAP SSB Database Mirroring Endpoint-Based Authentication • Endpoint: • Point of entry into an instance • Binds transport protocol to payload Transport Protocol Named pipes Shared Memory TCP HTTP VIA • For HTTP • Off by default, Endpoints need to be explicitly created • No permissions on endpoint by default

5. Endpoint-Based Authentication (Cont) • For Other transports • Default endpoint created for every enabled transport at start up • CONNECT permissions granted to authenticated logins • Permissions can be denied on a per endpoint basis • Advantages • Transport/payload based access management • Controlled within SQL Server • Ability to start/stop/disable individual endpoints

6. Connect to the SQL Server computer Establish login credentials; Authorize against EP Establish a database context Verify permissions for all actions SQL Server Security Model Network connection request Login authentication request to SQL Server Switch to a database and authorize access Attempt to perform some action

7. Password Policy Enforcement • Enforcement of the following on logins • Password strength • Password expiration • Account lockouts • Follows local Windows password policy • Supports consistent enterprise wide policy • Approach • New password policy check API in Windows Server 2003 • Called during Authentication, password set and reset • On Win2K server • API not available • Only supports SQL server native password complexity

8. Password Policy For Logins • Secure by default • Policy checked for all logins by default • Can be configured on a per login basis • Admin options on logins • Turn ON/OFF Password policy checks • Turn ON/OFF Password expiration checks • Requires Password Policy to be ON • Gives ‘password never expires’ ability • Ability to force password change on first login • Present in the sys.sql_logins catalog view ***

9. Password Policy For Logins • During Authentication • Account lockout in case of brute force attack • Password age restrictions – if expiration set • Password Set/Change • Password complexity enforced and history checked • Client side support • Allows password change on login, similar to Windows • Ability to disable logins quickly

10. Password Policy Enforcements

11. Strengthening SQL Authentication • Stronger channel for authentication protocol for standard SQL logins • Channel is encrypted using SQL generated certificates • No SSL certificate loading required • Similar to current SSL implementation • Default mechanism for standard SQL logins for Yukon clients talking to Yukon server • Seamless to applications; No application change required • Support for old style authentication for down level clients

12. Agenda • Yukon Security Features • Authentication • Endpoint Based Authentication • Password Policy Enforcement • Strengthening SQL Authentication • Authorization • User-Schema Separation • Module Execution Context • Granular Permissions Control • Catalog Security • Crypto support • Data Encryption

13. User-Schema Separation • Separation of principals and schemas • Principal • Entities against whom objects are secured • Live in the sys.database_principals view • Schema • Container of objects; 3rd part of 4 part name • Live in the sys.schemas view

14. User-Schema Separation (Cont) • Notion of default schema • Property of User or Application role • Used in name resolution; Look up algorithm for objects • Maintained in sys.database_principals • New DDL for user and schemas • CREATE/ALTER/DROP for USER, ROLE, and SCHEMA • Dropping user does not require application re-write

15. Table Function View Stored Proc User-Schema SeparationThe Solution User 2 Owned by contained in Schema

16. Select * from foo • S1. foo • Dbo.foo User-Schema SeparationThe Solution User1 Default Schema S1 User2 User3

17. Default Schema • Used for name resolution purposes • Not all users need to own schemas • Gives the ability to share out name resolution look up across many users • DBO schema need not be the only shared schema, from name lookup perspective

18. Default Schema (Cont) • Why is this useful? • To create objects guaranteed to hit name lookups from every context, objects need not be created in DBO schema • By having these contexts point to another schema as the default schema • Allowing creation of objects in DBO schema could pose security risks through ownership chaining • This can be mitigated using ‘lower privileged’ schemas as the default schema • ‘lower privileged’: Owned by a lower privileged account

19. Approle1 User1 Role1 Schema1 Schema2 SP1 Fn1 Tab1 User-Schema Separation Database • Database can contain multiple schemas • Each schema has an owning principal – user or role • Each user has a default schema for name resolution • Most database objects live in schemas • Object creation inside schema requires CREATE permission and ALTER or CONTROL permission on the schema • Example: Creation of table in schema requires CREATE TABLE permission and ownership of schema or ALTER or CONTROL on schema • Ownership chaining still based on owners not schemas Hasdefaultschema Owns Owns Owns Schema3

20. User-Schema Separation

21. User2.Proc1 User1.T1 User 3 User1.T1 User2.Proc1 Execution Context SQL 2000 Execute Perms checked for User3 Select Perms checked for User3 User 3 User1.T1 User1.Proc1 Execute Perms checked for User3 NO Perms checked for User3 Yukon ‘Execute AS ‘X’ ’ Execute Perms checked for User3 Select Perms checked for‘X’. Not for user3

22. Module Execution Context • Ability to choose execution context of modules • Module: Stored procs, functions, triggers • No need to rely on ownership chaining to get ‘upfront permission check’ behavior • Ownership chaining rules still apply however

23. Module Execution Context (Cont) • Permissions checked against current execution context • Unlike Ownership chaining, applies to DDL as well • Option available for dynamic SQL as well • Alternative to the absence of ownership chaining • Execution context maintained in the sys.sql_modules catalog view

24. Contexts • Execute AS CALLER • Statements execute as immediate calling context • Default behavior, Similar to SQL Server 2000 • Use when caller’s permission needs to be checked, Or ownership chaining will suffice • Execute AS ‘UserName’ • Statements execute as the username specified • Impersonate permission required on user specified

25. Contexts • Execute AS SELF • Statements execute as the person specifying the execute as clause for the module • May be useful in application scenarios where calling context may change • Execute AS OWNER • Statements execute as the current owner of the module • Impersonate privileges on owner required, at setting time • On ownership change, context is new owner

26. Using Execute As To Create Permission Buckets • Scenario • Database Admin wants to delegate the ability to truncate a set of tables each night • Problem • Truncate is not a grantable permission • Closest covering permission is ALTER, but that is too high and gives other ability

27. Using Execute As To Create Permission Buckets (Solution) • Solution: Execute As to the rescue • Create a Proc that truncates the table • Mark it to execute as a user with ALTER permissions • Grant execute permission to the target user • Result • You’ve just made Truncate a grantable permission!

28. Module Execution Context

29. Granular Permissions Control • More permissions – at multiple scopes • Server, Database, Schema, Object, Principal • Principle of least privileges • Assign only required permissions to perform an action • Granular permissions enable that • Many new permission verbs added for granular control • SQL 2000 fixed roles still supported • Catalog Views • Database permissions live in sys.database_permissions view • Server permissions live in sys.server_permissions view

30. Permission States • Three permission states • Grant gives a right • Deny explicitly denies a right • Revoke takes away an existing grant or deny Grant - Revoke Revoke [deny] + DENY Grant Deny

31. General Permissions Scheme • Grantee • Server level permissions grantable to logins • Database level permissions grantable to users/db roles/application roles • Securable • Entity to be secured • Example: Tables, assemblies, databases, server, etc… • Same permission can be at multiple scopes • Example CONTROL on schema level and CONTROL on table within a schema • DENY at any level always take precedence

32. General Permissions Scheme • Most securables have the following permissions • CONTROL: Owner like permissions • ALTER: Ability to change the properties of the securable. Also grants the ability to CREATE/DROP/ALTER sub entities • Example: ALTER permission on a schema allows altering the name of the schema; And also altering tables, views etc. within schema

33. General Permissions Scheme (Cont) • ALTER ANY ‘X’: Ability to alter any object of type X • Example: ALTER ANY ASSEMBLY allows altering any assembly in the database • Take Ownership: Grants the ability to take ownership of an object

34. New permissionsFurther Examples • Concern: I want to delegate auditing power without giving access to the entire server • Solution: ALTER TRACE • New server level permission. Sysadmin no longer required to run profiler • Concern: There is db_datareader and db_datawriter, but I want a db_procexecutor equivalent • Solution: EXECUTE (higher scope--database or schema) • Can execute any proc/function/assembly in database, or schema (if schema scoped) • Equivalent SELECT, INSERT, UPDATE, DELETE permissions introduced as well

35. New permissionsFurther Examples • Concern: I want to be able to selectively impersonate another user, without having full database, or instance privileges • Solution: IMPERSONATE (for login and user) • No longer require sysadmin or DBO for impersonating user/login

36. Catalog Security • System tables implemented as views: Catalog views • Metadata is secured by default • Minimal permissions to public • Catalog views are row level secured • Need to be owner or have some permission on object to see it in catalog view • SA can see everything in server • DBO can see everything in database • New permission to allow viewing of metadata • VIEW DEFINITON • Applicable at object level, schema level, database, and server level

37. Agenda • Yukon Security Features • Authentication • Endpoint Based Authentication • Password Policy Enforcement • Strengthening SQL Authentication • Authorization • User-Schema Separation • Module Execution Context • Granular Permissions Control • Catalog Security • Crypto support • Data Encryption

38. Encryption Support Inside DB • Encrypt and Decrypt built-ins for encryption support • Encryption built-insEncrypts cleartext and returns ciphertext *** • EncryptByKey() • EncryptByCert() • EncryptByPassphrase() • Decryption built-insDoes the reverse • Symmetric Keys and Certificates • Used for encryption/decryption • Entities inside SQL Server • Support for creation and storage in SQL • Symmetric keys and private keys always stored encrypted in SQL

39. Symmetric Keys • Entities inside the database • CREATE SYMMETRIC KEY DDL • Used in the built-ins for encryption and decryption • Keys are always stored encrypted or in ‘closed’ state • Usage of Key requires ‘opening’ the key • OPEN KEY DDL • Involves decrypting the key • Knowledge of mechanism used to encrypt the key required • Once ‘open’, keys can be used until they are ‘closed’ or sessions is terminated • All keys are encrypted in memory • Catalog views • Sys.symmetric_keys • Sys.Open_keys

40. Certificates • Entities inside the database • CREATE CERTIFICATE DDL • Can be loaded into SQL Server • Private key is optional • Required if signing, decryption, or authentication is needed • Can be created in SQL Server • SQL server generates the certificates • Support for ‘dumping’ certificates and private keys • Private keys always stored encrypted • Required for • Service Broker: Authentication and message integrity and secrecy • Encryption support • Securing SQL authentication natively • Module Signing

41. Encryption Layers

42. Database Master Key • A Key that is specific to the database • Used to secure certificate private keys inside the database • Explicitly created by owner of the database • Support for regeneration and recovery • ALTER MASTER KEY DDL • DUMP/LOAD MASTER KEY DDL • Always secured using user password • Stored in the database • Can also be secured using Service Master Key • Used when SQL server does key management • Does not rely on user password to get to key • Stored in the sysdatabases table

43. Service Master Key • Key that is specific to the instance • Used to secure system data • Linked server passwords • Connection strings • Database Master keys • Mapped account credentials • Created upon install • Support for regeneration and recovery • ALTER SERVICE MASTER KEY DDL • DUMP/LOAD SERVICE MASTER KEY DDL • Secured using DPAPI • The service accounts credentials

44. Encrypting Data

45. The SQL Server 2005 University Masters • Fully accredited, masters level degree from Charles Sturt University • SQL Server 2005 MCDBA certification included as an integral component • Graduates eligible for full professional level membership of the Australian Computing Society • Part time study delivered via Distance Education • Qualifies for Government FEE-HELP program • Places available for experienced applicants without previous qualifications First intake commences September 2005Register your interest atwww.itmasters.info

46. Technical Readiness Resources • Microsoft SQL Server 2005 Workshops • 1 or 2 day hands on workshops on each of : • Database Infrastructure • Database Development • Business Intelligence • Tour 1 Starts Mid May • Tour 2 has been added due to popular demand – starts late August

47. Event: Microsoft SQL Server 2005 Workshops • Microsoft SQL Server 2005 Database Infrastructure & ScalabilityPresented by Brent Challis from DDLS this workshop covers Installation and upgrade options, Management tools, Architecture, Security enhancements, Scalability and performance enhancements and Maintenance enhancements. • Microsoft SQL Server 2005 DevelopmentThis intensive workshop led by Greg Low from Readify, this workshop covers the developer-related enhancements in SQL Server 2005, with a focus on T-SQL enhancements, CLR Integration, Security enhancements and ADO.NET enhancements. • Microsoft SQL Server 2005 Business IntelligencePresented by Peter Myers from Tenix Connections, this workshop covers the building of Analysis Services 2005 databases, cubes, dimensions, & aggregations, enhancements made to MDX, the new Integration Services 2005 (formerly DTS), the new data mining algorithms of Analysis Services 2005 and creating reports from Analysis Services cubes. http://www.microsoft.com/australia/events/sql2005/

48. Microsoft SQL Server 2005 Workshops - Dates http://www.microsoft.com/australia/events/sql2005/

49. Thanks for listening! greg.low@readify.net

50. © 2002 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.