1 / 30

Security in SQL Server 2008

Security in SQL Server 2008. Vinod Kumar Technology Evangelist - Microsoft http://blogs.sqlxml.org/vinodkumar http://www.ExtremeExperts.com. Session Objectives And Takeaways. Session Objective(s): Describe what applications can do to help increase data security

fayre
Download Presentation

Security in SQL Server 2008

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Security in SQL Server 2008 Vinod Kumar Technology Evangelist - Microsoft http://blogs.sqlxml.org/vinodkumar http://www.ExtremeExperts.com

  2. Session Objectives And Takeaways • Session Objective(s): • Describe what applications can do to help increase data security • Discuss encryption, authentication, permissions, and SQL injection • Understand that Security is an important consideration for application as well as the server • Know what is available in SQL Server and how it can help customers achieve security objectives

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

  4. Data Protection

  5. 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

  6. Data EncryptionSQL Server 2005 Support • 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 • DES, TRIPLE_DES, RC2, RC4, RC4_128, DESX, AES (128, 192, or 256)

  7. 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

  8. 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

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

  10. Authentication • Windows Auth is preferable to SQL Auth

  11. 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

  12. Application Role Separation and Permissions

  13. 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

  14. Ownership chaining • Be aware of ownership chaining

  15. 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)

  16. Module Signing (cont) 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

  17. Execution ContextLogin and User Token Token Primary Identity SQL or Windows user name Secondary Identity Roles and Windows group memberships, including public Secondary Identity Certificate Signed modules Authenticator Cross-DB impersonated context

  18. Execution ContextBest Practices • Controlled escalation of privileges • DB scoped: EXECUTE AS and App Roles • Cross-DB scoped: Certificates • Avoid using dynamic SQL under an escalated context • Do not use use CDOC and SETUSER • Avoid allowing guest access on user DBs

  19. SQL Injection

  20. 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.

  21. SQL Injection T-SQL example CREATE PROC sp_SqlInjectionDemo( @ColumnValuevarchar(100) ) AS DECLARE @cmdnvarchar(max) SET @cmd = N'SELECT * FROM [test].[Demo] WHERE data = ''' + @ColumnValue + '''' print @cmd -- For demonstration purposes EXEC( @cmd ) Go

  22. SQL Injection ASP example ‘‘ Execute a SQL command strCmd = " N'SELECT * FROM [test].[Demo] WHERE data = '" & columnValue & "'" SetobjCommand.ActiveConnection = objConn objCommand.CommandText = strCmd objCommand.CommandType = adCmdText SetobjRS = objCommand.Execute()

  23. SQL Injection Example - attacker's side • T-SQL: EXECsp_SqlInjectionDemo 'abc''; SELECT * FROM sys.objects where name like ''sys%' go • ASP:

  24. 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

  25. 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

  26. SQL InjectionAdditional resources • SQL Server Security Blog • SQL injection (BOL) • Preventing SQL injection in ASP • Giving SQL injection the respect it deserves • Raul Garcia’s blog

  27. demo

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

  29. Feedback / QnA • Your Feedback is Important! Please take a few moments to fill out our online feedback form at: << Feedback URL – Ask your organizer for this in advance>> For detailed feedback, use the form at http://www.connectwithlife.co.in/vtd/helpdesk.aspx Or email us at vtd@microsoft.com • Use the Question Manager on LiveMeeting to ask your questions now!

More Related