Module 5 Designing Security for SQL Server 2008

1. Module 5 Designing Security for SQL Server 2008

2. Module Overview • Exploring Security in SQL Server 2008 • Implementing Identity and Access Control • Guidelines for Secure Development in SQL Server 2008 • Guidelines for Secure Deployment of SQL Server 2008 • Guidelines for Secure Operations

3. Lesson 1: Exploring Security in SQL Server 2008 • Overview of Securing SQL Server 2008 • SQL Server 2008 Security Changes • SQL Injection Attacks • SQL Server 2008 Regulatory Compliance • Discussion: Preventing SQL Injection Attacks

4. Overview of Securing SQL Server 2008 Principals and Database Object Security Platform and Network Security Application Security Principals and Database Object Security Platform and Network Security Application Security • Reduce the SQL Server surface area by configuring set of permission of securable • Enhance object and connection security by creating certificates • Write secure client applications • Limit physical access to the server, hardware components and backup media • Apply all updates and upgrades to the operating system after you test them with the database applications • Restrict access to operating system files

5. SQL Server 2008 Security Changes Major security changes in SQL Server 2008 are: Windows Local Group Changes Surface Control Tools Changes Kerberos Authentication ü ü ü

6. SQL Injection Attacks To reduce the scope for injection attacks: Avoid assumptions about the data size, type, or content ü Var ShipCity; ShipCity = Request.form ("ShipCity"); var sql = SELECT * FROM OrdersTable WHERE ShipCity = 'Redmond' Test the size and data type of input ü Accept only expected values of string variables ü Validate XML data with the related XML schema ü Avoid building T-SQL statements directly from user input ü Use stored procedures to validate user input ü Validate all data in multitiered environments ü Implement multiple layers of validation ü Avoid concatenating unvalidated user input ü Avoid accepting strings such as AUX and CLOCK$ ü

7. SQL Server 2008 Regulatory Compliance You must adhere to the following acts, regulations, and certifications to implement effective security mechanisms: • Sarbanes-Oxley Act (SOX) • Gramm-Leach-Bliley Act (GLBA) • Health Insurance Portability and Accountability Act (HIPAA) • FDA Title 21 CFR Part 11 • California Senate Bill 1386 • Common Criteria

8. Discussion: Preventing SQL Injection Attacks • Describe SQL injection attack. • What measures can you take to minimize the risk of SQL injection attacks? • Can you completely prevent SQL injection attacks?

9. Lesson 2: Implementing Identity and Access Control • Considerations for Using Principals • Considerations for Using Roles • Considerations for Using Securables • Considerations for Using Permissions • Discussion: Using a Loginless User Account

10. Considerations for Using Principals • The SQL Server sa login is a server-level principal • Every database user belongs to the public database role • Every database includes INFORMATION_SCHEMA and sys entities • Server principals with names within double hash (##) marks are only for internal use • The guest user account cannot be dropped, but can be disabled by revoking its CONNECT permission • Application roles help control user access to specific data

11. Considerations for Using Roles Every database user belongs to the public database role ü Members of the db_ssisadmin role and the dc_admin role may be able to elevate their privileges to sysadmin ü By default, the BUILTIN\Administrator group is not included in the SQL Server 2008 sysadmin fixed server role ü The Windows groups created for use by the SQL Server service are not included in the sysadmin fixed server role Flexible database roles are not added as members of fixed roles ü ü

12. Considerations for Using Securables Schemas hold objects, such as tables and views. Every securable in a specific schema must have a unique name. Schema File access permissions are set during operations, such as creating, attaching, modifying to add a new file, backing up, or restoring databases. Data Security Ownership chaining enables managing access to multiple objects by setting permissions on one object. Ownership Chains Securables are the resources to which the SQL Server Database Engine authorization system regulates access.

13. Considerations for Using Permissions Principals and Securables Catalog Views sys.server_permissions Windows Level sys.database_permissions Server Level Database Level Permissions for Fixed Server Roles Permissions for Fixed Database Roles

14. Discussion: Using a Loginless User Account Describe a loginless user account. What are the benefits of having a loginless user?

15. Lesson 3: Guidelines for Secure Development in SQL Server 2008 Guidelines for Execution Context Using Module Signing Guidelines for Context Switching Demonstration: How To Control Execution Context by Using the EXECUTE AS Clause Guidelines for Impersonation

16. Guidelines for Execution Context • Execution context is represented by security tokens. A security token contains: • One server or database principal as the primary identity • One or more principals as secondary identities • Zero or more authenticators • Privileges and permissions of the primary and secondary identities User Security Token Login Security Token • Valid only for a specific database • Contains the primary and secondary identities against which database-level permissions are checked • Valid across the instance of SQL Server • Contains the primary and secondary identities against which server-level permissions and database-level permissions are checked

17. Using Module Signing Verifying data </1010> </1010> Signing data </1010> Private key Signer </1010> Verifier Public key

18. Guidelines for Context Switching Explicit Server-Level Explicit Database-Level Implicit Explicit Server-Level Explicit Database-Level Implicit • The EXECUTE AS USER = 'user_name' statement is used to switch execution context at the databse level • The user name must exist as a principal in sys.database_principals • The caller must have IMPERSONATE permissions on the specified user name • The EXECUTE AS LOGIN = 'login_name' statement is used to switch execution context at the server level • The login name must be visible as a principal in sys.server_principals • The statement caller must have IMPERSONATE permission on the specified login name • The execution context of a module can be implicitly changed by specifying a user or login name in an EXECUTE AS clause in the module definition • The user that the module is impersonating needs to have permissions on the objects accessed by the module

19. Demonstration: How To Control Execution Context by Using the EXECUTE AS Clause In this demonstration, you will see how to: Use EXECUTE AS to switch execution context

20. Guidelines for Impersonation SQL Server Impersonation Within a Domain SQL Server Instance SQL Service Provider Windows User Advantages of the EXECUTE AS statement vs. SETUSER statement • Server or database principals other than sa or dbo can call EXECUTE AS • The scope of impersonation is explicitly defined in the EXECUTE AS statement • With EXECUTE AS, the impersonation remains in effect until the session is dropped • You can create an execution context stack by calling the EXECUTE AS statement multiple times across multiple principals

21. Lesson 4: Guidelines for Secure Deployment of SQL Server 2008 • Guidelines for Using Authentication Modes • Features of SQL Server Configuration Manager • Discussion: Using Policy-Based Management

22. Guidelines for Using Authentication Modes During setup, you must select an authentication mode for the Database Engine Mixed Mode Authentication Windows Authentication Mode • Windows authentication is the default authentication mode • Windows Authentication mode enables Windows authentication and disables SQL Server authentication • SQL Server validates user credentials by using a Windows principal token • A connection made by using Windows authentication is called a trusted connection • Kerberos security protocol in Windows authenticationenforces password policy • Mixed mode authentication enables both Windows authentication and SQL Server authentication • It requires a strong password for the built-in SQL Server system administrator account named sa. The sa account connects by using SQL Server authentication

23. Features of SQL Server Configuration Manager Protocols, Connection, and Startup Options: • Use the SQL Server Services area to start components and configure theautomatic starting options • Use the SQL Server Network Configuration area to enable connection protocols andconnection options Enabling and Disabling Features: • Use facets in SQL Server Management Studio to configure enabling and disabling features • Use Policy-based Management to check the configuration of a facet Command-prompt Options: • Use the Invoke-PolicyEvaluation SQL Server PowerShell cmdlet to invoke Surface Area Configuration Policies SOAP and Service Broker Endpoints: • Use Policy-based Management to turn endpoints off • Use CREATE ENDPOINT and ALTER ENDPOINT to create and alter the properties of endpoints

24. Discussion: Using Policy-Based Management Discuss how policy-based management can be used to regularly inspect and verify that the specific security requirements are actively in place.

25. Lesson 5: Guidelines for Secure Operations Guidelines for Creating a Password Policy SQL Server Cryptography Architecture Using SQL Certificates and Asymmetric Keys Guidelines for Using SQL Server Encryption Cell-Level Encryption Demonstration: How To Enable TDE Discussion: Securing Data by Using Encryption

26. Guidelines for Creating a Password Policy Password complexity Password expiration Policy enforcement Use password complexity to prevent brute force attacks Use password expiration to manage the lifespan of a password Configure password policy enforcement separately for each SQL Server login

27. SQL Server Cryptography Architecture Service Master Key Database Master Key Asymmetric Key Certificate Symmetric Key

28. Using SQL Certificates and Asymmetric Keys SQL Certificates Asymmetric Keys Use certificates and asymmetric keys for asymmetric encryption • Asymmetric keys are used for securing symmetric keys, limited data encryption, and digitally signing database objects • Asymmetric keys can be imported from strong name key files, but they cannot be exported • Asymmetric keys do not have expiry options • Asymmetric keys cannot encrypt connections • You can use certificates as containers for asymmetric keys and to sign code modules • You can use externally generated certificates or certificates generated by SQL Server • You can specify properties for the certificates while creating them

29. Demonstration: How To Enable TDE In this demonstration, you will see how to: Enable TDE by using the ALTER DATABASE statement

30. Discussion: Securing Data by Using Encryption How do you decide about what data to be secured by encryption? Why would you secure an entire database by encryption? What is data at rest?

31. Lab 5: Designing Security for SQL Server 2008 Virtual machine NYC-SQL1 Administrator User name Password Pa$$w0rd • Exercise 1: Designing Secure Development • Exercise 2: Implementing Secure Operations • Exercise 3: Enabling Database Encryption Logon Information Estimated time: 60 minutes

32. Lab Scenario You are a lead database designer at QuantamCorp. You are working on the Human Resources Vacation and Sick Leave Enhancement (HR VASE) project that is designed to enhance the current HR system of your organization. This system is based on the QuantamCorp sample database in SQL Server 2008. The main goals of the HR VASE project are as follows: • Provide managers with current and historical information about employee vacation and sick leave. • Grant view rights to individual employees to view their vacation and sick leave balances. • Provide permission to selected employees in the HR department to view and update the vacation and sick leave details of employees. • Grant the HR manager with the view and update rights to all the data. • Ensure that the application compiles with local regulations by verifying that the sensitive information is protected and unauthorized access to the application is prohibited. You are required to design a security access control policy and examine the various aspects of secure development and operation in the SQL Server database system.

33. Lab Review How can schema help to improve management of database security? Does configuration of TDE for a database ensure encryption of the associated FILESTREAM data? How do you restore an encrypted database if the certificate that protects the database encryption key is unavailable? How will you configure the client application to utilize TDE?

34. Module Review and Takeaways Review Questions Real-World Issues and Scenarios