1. Danny Ravid SQL & BI Practice Leader MCA/MCM , Glasshouse Ltd.SQL Server Division Manager ,Hi-Tech College.Microsoft Regional Specialist (AKA : MRD) Security

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

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

5. Default Endpoints

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

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

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

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

10. 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 = ‘…’

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

12. SQL Authentication

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

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

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

16. Windows Authentication

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

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

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

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

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

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

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

24. Execution Context

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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