Microsoft SQL Server 2008: Developing Secure Applications

1. BB37 Microsoft SQL Server 2008: Developing Secure Applications  Il-Sung Lee Senior Program Manager Microsoft Corporation

2. demo Insecure Web Application

3. Agenda • Why Do Applications Need to Care? • Data Protection • Application Role Separation and Permissions • SQL Injection

4. Why Do Applications Need To Care? • Data security is not complete without application involvement • SQL injection is now one of the most common type of attack on the web • Applications control or influence: • Encryption • Authentication • Permissions / Role Separation • Vulnerability to SQL Injection

5. Data Protection

6. Data Encryption • Why consider encryption? • Additional layer of security • Required by some regulatory compliance laws • In SQL Server 2000, vendor support required • Since SQL Server 2005 • Built-in support for data encryption • Support for key management • Encryption additions in SQL Server 2008 • Transparent Data Encryption • Extensible Key Management

7. Data EncryptionCell-level Encryption • Encryption and Decryption built-ins • DDL for creation of Symmetric Keys, Asymmetric Keys, and Certificates • Symmetric Keys and Private Keys are always stored encrypted • Securing the Keys themselves • Based on user passwords • Automatic, using SQL Server key management • Choice of algorithms • Including DES, TRIPLE_DES, AES (128, 192, or 256)

8. Data EncryptionBest Practices • Encrypt only necessary data • Use symmetric encryption • Plan carefully • Key management is very important • Understand changes to existing code needed • Consider key size and algorithm on CPU

9. Channel Encryption • Support for full SSL Encryption since SQL Server 2000 • Clients: MDAC 2.6 or later • Force encryption from client or server • Login packet encryption • Used regardless of encryption settings • Supported since 2000 • Self-generated certificates avail since 2005

10. Channel EncryptionBest Practices • Enable channel encryption whenever possible and tolerable • Provision a certificate on the server • Force encryption from the client

11. Authentication • Windows Auth is preferable to SQL Auth

12. AuthenticationEnhancement in 2008 • SQL Server 2005 • Kerberos possible with TCP/IP connections only • SPN must be registered with AD • SQL Server 2008 • Kerberos available with ALL protocols • SPN may be specified in connection string (OLEDB/ODBC) • Kerberos possible without SPN registered in AD

13. Application Role Separation and Permissions

14. Permission Strategy • Follow principal of least privilege! • Avoid using sysadmin/sa and db_owner/dbo • Grant required perms to normal login • Never use the dbo schema • User-schema separation • Applications should have own schema • Consider multiple schemas • Leverage Flexible Database Roles • Facilitates role separation • Consider Auditing user activity

15. Ownership Chaining • Be aware of ownership chaining

16. Module Signing • Need ALTER ANY LOGIN server permission to ALTER LOGIN • Need to GRANT ALTER ANY LOGIN TO Alice? – No! ALTER LOGIN Bob ENABLE Alice (non privileged login)

17. Module Signing SP_ENABLE_LOGIN • Alice has permission to call SP • SP run under Alice’s context but with elevated privilege • SP protected against tampering ALTER LOGIN Bob ENABLE ALTER ANY LOGIN Alice (non privileged login) Cert_login

18. SQL Injection

19. SQL InjectionIntroduction • SQL Injection is an attack where malicious code is inserted into strings and later passed to SQL Server for parsing and execution. • SQL injection is one of the most common attacks. • It can affect T-SQL code as well as code generated outside SQL such as ASP, ASP .Net, managed code, native code, etc.

20. demo Fixing SQL Injection

21. SQL InjectionStrategies to protect against SQL injection • Validate Input against a white-list • Use parameterized SQL queries • Use Type-Safe SqlParameter in .Net • Use parameterized SPs • Least-privilege Principle • Least privileged principal for web services • Escape special characters • Escape quotes with quotename/replace • Escape wildcards in LIKE statements • Validate buffer length to avoid truncation

22. SQL InjectionTools • Microsoft Source Code Analyzer for SQL injection • Aid in SQL injection detection for ASP code • July CTP: http://www.microsoft.com/downloads/details.aspx?FamilyId=58A7C46E-A599-4FCB-9AB4-A4334146B6BA&displaylang=en • Requirements: • OS: XP SP2, Windows 2003 SP1, Windows Vista or Windows 2008 • .Net Framework 2.0

23. demo Source Code Analyzer for SQL Injection

24. Protecting Your Data • Consider encryption for protecting sensitive data • Carefully think about permissions • Maximize role separation • Always be mindful of SQL Injections

25. Additional Information • SQL Server Security blog: • http://blogs.msdn.com/sqlsecurity/ • Raul’s blog: • http://blogs.msdn.com/raulga/archive/tags/sql+injection/default.aspx • BOL - Security Considerations for Databases and Database Applications: • http://msdn.microsoft.com/en-us/library/ms187648.aspx • SQL Injection (BOL): • http://msdn.microsoft.com/en-us/library/ms161953.aspx • Preventing SQL Injections in ASP: • http://msdn.microsoft.com/en-us/library/cc676512.aspx

26. Evals & Recordings Please fill out your evaluation for this session at: This session will be available as a recording at: www.microsoftpdc.com

27. Q&A Please use the microphones provided

28. © 2008 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.