1 / 82

Security

Danny Ravid SQL & BI Practice Leader MCA/MCM , Glasshouse Ltd. SQL Server Division Manager ,Hi-Tech College. Microsoft Regional Specialist (AKA : MRD). Security. Agenda. Core Concepts Execution Contexts Module Signing Cryptography Auditing. Endpoints.

tana
Download Presentation

Security

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. Danny Ravid SQL & BI Practice Leader MCA/MCM , Glasshouse Ltd.SQL Server Division Manager ,Hi-Tech College.Microsoft Regional Specialist (AKA : MRD) Security

  2. Agenda • Core Concepts • Execution Contexts • Module Signing • Cryptography • Auditing

  3. Endpoints • An endpoint exposes SQL Server services on network • Exposed Services: • TSQL • SOAP (Web Services) • Service Broker • Database Mirroring • Works over TCP and HTTP transport • Service must be compatible with the transport (TSQL works with TCP only) • TSQL endpoints are created by default • HTTP endpoints deprecated in SQL Server 2008

  4. Default Endpoints

  5. CONNECT Permission • CONNECT SQL permission on SERVER • For all types of services (TSQL, SOAP, …) • Implicitly granted when login is created • CONNECT permission on ENDPOINT • CONNECT on TSQL ENDPOINT is granted to PUBLIC by default • Login enabled or disabled • Login attribute not a real permission • Allows disabling SA and sysadmin

  6. Authentication • Authenticates identity within SQL Server • Endpoint based • Verifies access rights to the server • Establishes primary security context

  7. Authentication Types • SQL Authentication • In-built authentication protocol • Login/password based • Independent of Windows OS authentication • Windows Authentication • Works with passwords, smartcards, thumb readers, etc. • Recommended in most cases

  8. Authentication Modes • Windows only • SQL logins cannot connect • Mixed • SQL logins and Windows logins allowed • Mode can be changed after installation

  9. SQL Authentication • Requires SQL logins • Login handshake encrypted using SSL • Certificate to be used determined at connection • Validates password • Enforces account policy • Account is locked out, must change password • Supports password change on login CREATE LOGIN Alice WITH PASSWORD = ‘…’

  10. Benefits of SQL Authentication • SQL authentication is not un-secure • Used when • No rights to create Windows users • Avoiding delegation double-hop issues • Non-Windows clients • Application logins outside of Windows • Notes • Encrypted when using SNAC • No SID from operating system • Principal is not guaranteed unique

  11. SQL Authentication

  12. Password Complexity • Designed to deter brute force attacks • Password must not contain all or part of the account name • Part of an account name is defined as three or more consecutive alphanumeric characters delimited on both ends by whitespace or some special chars • Min 8 chars, max 128 chars • Contains characters from three of the following four categories: • Latin uppercase letters (A through Z) • Latin lowercase letters (a through z) • Base 10 digits (0 through 9) • Non-alphanumeric characters

  13. Policy Recommendations • Leave CHECK_POLICY on • Set CHECK_EXPIRATION on to avoid old passwords • Set MUST_CHANGE for new logins

  14. Windows Authentication • Uses Windows OS users CREATE LOGIN [REDMOND\SQLTest1] FROM WINDOWS • Secure using Kerberos or NTLM • Windows creates client security token on the server • The Windows token contains • User identity • Group memberships • Windows privileges

  15. Windows Authentication

  16. Benefits of Windows Authentication • Uses standard Kerberos / NTLM protocol • No need to manage logins in SQL Server • Works with SmartCards and other non-password based authentication devices

  17. Login Based on Windows Group Membership • Only possible for Windows Logins • Login is not provisioned in SQL Server • CREATE LOGIN has not be called • Login is a member of Windows Group, provisioned in SQL Server CREATE LOGIN [Bob\SQLUsers] FROM WINDOWS

  18. Database Ownership & Trust • Have distinct owners for databases • Not all owned by “SA” • Minimize owners for each database • Confer trust selectively • Leave CDOC (cross-database ownership chaing) setting off • Migrate usage to selective trust instead

  19. Cross Database Chaining • User token outside the database • Token authenticator vouches for the token • If authenticator trusted, token is honored • If authenticator not trusted, token not honored • Default behaviour • Two alternatives for setting authenticators • Use TRUSTWORTHY setting to have DBO as an authenticator • Use certificates as authenticators • Recommended

  20. Trustworthy Databases • Per database setting • Indicates that a context set in this DB can have the DBO as an authenticator • Only a sysadmin can change the TRUSTWORTHY state for a database • ALTER DATABASE database_nameSET TRUSTWORTHY {ON | OFF}

  21. Recommendations: Trustworthy • Don’t turn TRUSTWORTHY ON unless the DB and all its administrators are truly trustworthy • Monitor TRUSTWORTHY bit changes • Avoid turning TRUSTWORTHY ON a database owned by sysadmin members • For Cross-DB scenarios assign a low privileged dbo • For cross database & server access impersonation consider using signatures

  22. Worst Case Scenario • TRUSTWORTHY ON • dbo is a member of sysadmin role • Result: Privileged users in this DB can become sysadmin themselves.

  23. Execution Context

  24. Application Roles • Password based • Completely contained in DB • No presence outside the DB • By default cannot revert • SQL Server 2005 added sp_unset_approle • Application roles are DB scoped • Token is not trusted in server scope

  25. App Roles - Recommendations • Do not access: • Cross database resources as “guest” • No access to server scoped metadata • Password • Do not hardcode it • Subject to password policy check (complexity) • Use flag 1416 for backwards compatibility only • “guest” access cross database • Limited “guest” access

  26. EXECUTE AS • Permission based • Scoped • User vs. Login • Context impersonation bound to module • Stack based • REVERT • NO REVERT & REVERT WITH COOKIE

  27. Explicit Impersonation • EXECUTE AS LOGIN = ‘login_name’ • Server level impersonation • Requires IMPERSONATE ON LOGIN::<login_name> • EXECUTE AS USER = ‘user_name’ • Database level impersonation • Requires IMPERSONATE ON USER::<user_name> • Stackable – can return to previous • EXECUTE AS • push new execution context frame • REVERT • pop top execution context frame • Active context is determined exclusively by top context frame

  28. EXECUTE AS CALLER • Default behavior • Use the caller’s context • Same as SQL Server 2000 • No IMPERSONATE permission is required

  29. EXECUTE AS Principal • Will execute under the specified principal context • Requires IMPERSONATE on the principal

  30. ORIGINAL_LOGIN • Returns details of the non-impersonated context • Very useful for auditing • Allows controlling behavior based on original login details when EXECUTE AS another principal

  31. EXECUTE AS: Positioning • What it is designed for • Controlled escalation of privileges via modules • Easy to use when all resources are in the same DB • What it is not designed for • Sandboxing against an attack • Can be used as a defence in depth.

  32. Users without Logins • CREATE USER Someuser WITHOUT LOGIN • No access to outside databases • To switch to user context use EXECUTE AS USER = ‘Someuser’ • Requires IMPERSONATE permission on Someuser • Better alternative to application role • Orphan users / mismatched SIDs are similar • Use sp_change_users_login to map them to logins • ALTER USER … WITH LOGIN since 2005 SP2

  33. Credentials • Stores authentication information needed to access resources outside SQL Server • Most contain a Windows username and password • Permits access to Windows resources for SQL Server logins • One credential can be mapped to multiple logins • One login can only be mapped to a single credential • Mapped using CREATE/ALTER LOGIN • sys.credentials

  34. Proxy Accounts • Allow subsystems to make use of credentials for external access • Specify proxy name, credential name and description • Must create the credential first • Assign created proxy to appropriate subsystems

  35. SETUSER • Deprecated–> do not use • Only for backwards compatibility • Limited to sysadmin & DBO only • DBO access is restricted • Requires high privileges • No stack on impersonated context • Revert via SETUSER call • NO REVERT

  36. Impersonation • Consider IMPERSONATE a privileged permission • Impersonator >= Impersonated • AUTHENTICATE permission is highly privileged permission • Can escalate to DBO • Consider AUTHENTICATE SERVER as powerful as sysadmin!

  37. Separation of Duties • Module encapsulation can be done using • Ownership chaining • EXECUTE AS • Code signing • Always place a security check inside the module • Don’t rely on EXECUTE permission on the module

  38. Execution Context: Recommendations • Set context on modules (don’t let default) • Use EXECUTE AS instead of SETUSER • Use WITH NO REVERT/COOKIE instead of App Roles

  39. Cryptography • Cryptography is the science of keeping secrets • Encryption is the process of obscuring information to make it unreadable without special knowledge • Plain text -> Cipher Text -> Plain Text

  40. Historical Note – Caesar’s Shift Cipher • 2000 years ago…

  41. Historical Note – Enigma machine • Famous electro-mechanical encryption device used to encrypt and decrypt messages

  42. Symmetric keys • Every encryption method has an encryption algorithm and decryption algorithm. When both algorithms depend on the same key, its known as symmetric key encryption. Encryption Symmetric Key 4428-6823-7821-2358 0x0088840517080E4FA2… Decryption

  43. Key Distribution • Biggest challenge has been management of keys • How do I send you the key that I will use to encrypt data that I send you? • Chicken/Egg problem

  44. Asymmetric Keys • Keys are mathematically related • Contains public and private key • Computationally infeasible (today) to derive one from the other Encryption with Public Key Asymmetric Key 4428-6823-7821-2358 Decryption with Private Key

  45. Hashes • One-way function only • Fingerprint of data • Cannot derive the data from the hash • Proves the integrity of the data • Recipient recalculates hash and compare values

  46. Salt • Random number added to the encryption key or to a password to protect them from disclosure • Also known as “Initialization Vector” • Without salt, a value encrypted twice will have same ciphertext • Critical to also avoid known text in known location

  47. Algorithms (a.k.a Cipher) • Magic recipe for scrambling data is the algorithm • Most algorithms are mind-numbingly complex mathematical equations • Many algorithms used • Not all as useful as others • SQL Server uses Microsoft CSP limited to algorithmsavailable in operating system

  48. Digital Signatures • Digitally sign modules • Two roles of the signing certificate: • Secondary Identity • Extend the execution context • Authenticator • Vouch for the EXECUTE AS context defined in the module definition NOTE: Signature can act as secondary identity and authenticator simultaneously

  49. Digital Envelopes • To send you encrypted data, I encrypt the data with your public key • You use your private key to decrypt • I know that only you can read it • SSL works like this

More Related