SQL Server Security By Mattias Lind So Qoo

SQL Server Security By Mattias Lind (@So. Qoo. L) 2015 -08 -20 For PASS Security VC

Mattias Lind Senior Microsoft Data Platform & Business Intelligence Architect @Sogeti mattias. lind@sogeti. se MVP on SQL Server Microsoft Certified Trainer blog. mssqlserver. se sqlguru. se @So. Qoo. L

Today’s Content • Authentication • SQL Server Logins & Windows Authentication • Server Roles • Database Users & Roles • Partial Contained Databases • NTLM vs. Kerberos

Authentication • Validate connection • Make sure right users consume • Server level • Database level

SQL Server Logins & Windows Authentication • SQL Server Login Name • Password • Exists in the local instance • Authenticated by SQL Server • Windows User or Group • Reference by SID • Exists in AD or SAM • Approved by SQL Server Connect To Server

Server Roles • Delegates specific administrative control of the server • Set of server fixed • Custom server roles

Database Users & Roles • Database Users approves access to a database • A SQL Server Login are tied to a User • Can be based on a Windows User or Group • Database Roles groups permissions and are associated to Users • A Role is not a group, it’s a Permission Set • Application Roles have a password and can elevate permissions for the session

Partial Contained Databases • Uses a Partial Contained Database User with a password • Inherits permission to connect to server, no need for a Login • Configurables are Server, Database, and Database User