Security

1. Security David Frommer Principal Architect Business Intelligence Microsoft Partner of the Year2005 & 2007

2. Agenda • Principals, Securables and Permissions • User Schema Separation • Password Policies • Credentials • Proxy Accounts • Impersonation • Column Level Encryption

3. Principals, Securables and Permissions • The new security model for SQL Server 2005 • Principal • Individuals, groups, and processes that can request SQL Server resources. • Logins, Users, Roles, etc • Securable • A Securable is a resource that can be secured • Tables, Views, Endpoints, etc

4. Principals, Securables and Permissions • Permission • Permissions grant principals access to securables • Grant a user Execute rights to a Stored Procedure, etc • SQL 2005 introduces new permissions like Control, Alter Any and Impersonate • Permissions work in hierarchies

5. Principals, Securables and Permissions

6. Agenda • Principals, Securables and Permissions • User Schema Separation • Password Policies • Credentials • Proxy Accounts • Impersonation • Column Level Encryption

7. User Schema SeparationOverview • What is a schema? • A collection of database objects that form a namespace • SQL 2000 • Server.Database.Owner.Object • SQL 2005 • Server.Database.Schema.Object

8. User Schema SeparationBenefits • Dropping database users is greatly simplified • Multiple users can own a schema through roles or windows groups. • Multiple users can share a default schema • Developers and applications can own and share objects in a specific schema instead of in DBO • Permissions can be managed at the schema level instead of the object level

9. User Schema Separation • During an upgrade, SQL Server will create a schema for every user in the database. • Create schemas for applications and avoid continued use of the DBO schema

10. Agenda • Principals, Securables and Permissions • User Schema Separation • Password Policies • Credentials • Proxy Accounts • Impersonation • Column Level Encryption

11. Password Policies • You can now use windows password policies for SQL accounts (note that SQL accounts cannot have a different policy than the Windows accounts) • Password expiration rules • Windows Server 2003 or higher • Enforcement can be decided on a per-login basis • This feature is not enforced by default. Logins upgraded from SQL 2000 will not have this turned on.

12. Agenda • Principals, Securables and Permissions • User Schema Separation • Password Policies • Credentials • Proxy Accounts • Impersonation • Column Level Encryption

13. CredentialsOverview • A credential is a record that contains the authentication information required to connect to a resource outside of SQL Server • Generally it maps to a Windows login • SQL Server logins can be mapped to credentials • A login maps to one credential but a single credential can map to many logins

14. CredentialsBenefits • Giving SQL Server accounts access to OS resources • Creating SQL Agent proxies • Giving applications access to other SQL services (SSAS, SSRS, SSIS)

15. Agenda • Principals, Securables and Permissions • User Schema Separation • Password Policies • Credentials • Proxy Accounts • Impersonation • Column Level Encryption

16. Agent Proxy Accounts • Defines the security context for a job step • SQL 2000 – only one proxy account available for all jobs. Generally this account had very high levels of privileges • SQL 2005 – Many proxy accounts that can have limited access to certain subsystems and principals can be assigned rights to use particular proxies. • The list of available proxies will be filtered by the type of job step and proxies to which the user has access. • During upgrade the old proxy account is changed to an UpgradeProxyAccount with access to the subsystems that were explicitly used.

17. SQL Login Proxy Credential Principal Windows Login Agent Proxy Accounts SQLAgentUser Role Give Access Grant Logon as Batch

18. Agent Proxy AccountsSubsystems • ActiveX Script • Operating System • Replication Distributor • Replication Merge • Replication Queue Reader • Replication Snapshot • Replication Transaction-Log Reader • Analysis Services Command • Analysis Services Query • SSIS Package Execution

19. Agenda • Principals, Securables and Permissions • User Schema Separation • Password Policies • Credentials • Proxy Accounts • Impersonation • Column Level Encryption

20. Impersonation • Run under the security context of another principal • EXECUTE AS CALLER • EXECUTE AS user_name • EXECUTE AS SELF (Creator) • EXECUTE AS OWNER

21. Agenda • Principals, Securables and Permissions • User Schema Separation • Password Policies • Credentials • Proxy Accounts • Impersonation • Column Level Encryption

22. EncryptionOverview • Final security barrier for sensitive data is typically data encryption • Encryption increases processor load and consumes storage space • Encryption requires key management • Symmetric encryption: • Is fast • Uses one key • Does not provide nonrepudiation

23. EncryptionTypes • Asymmetric encryption: • Uses a key pair • Is slower than symmetric encryption • Provides confidentiality and nonrepudiation • Hybrid encryption: • Takes advantage of the speed of symmetric encryption and the increased security of asymmetric encryption

24. EncryptionDiagram

25. EncryptionBest Practices • Key management is critical to an encryption framework • Key generation • Key usage • Key backup • Key regeneration

26. EncryptionBest Practices • Limit the use of encryption to sensitive data • Consider performance effect of encryption • Consider whether an external source requires access to encrypted data • Consider increased size of ciphertext over plaintext