SQL Server Security 101 How did you get

SQL Server Security 101 How did you get in here, and Who said you could do that? Presented by: Pat Phelan DBA Mentor at Involta, LLC.

Presenter confessions • This is a “ 101” presentation, it is not a deep dive into internals! • Wikipedia citations • This presentation cites Wikipedia • Academia doesn’t support those citations • Wikipedia makes definitions and additional information freely accessible • Wikipedia makes most of those available in many human languages. • Feedback request • This presentation is evolving • Constructive comments and suggestions are welcome!

Session Overview • We will cover (built into this presentation) • We might cover (based on audience interest) • AD/Windows basics • SQL Server Logins • Database users • Permissions • SQL 2000 -2012 security • Pre-SQL 2000 security • Principle of least privilege • Other information sources • Non-SQL Server kinds of security

AD/Windows features used by SQL • AD - Active Directory • Often includes a number suffix that ties AD to a Windows Server version • Directory of Windows Objects • • Computers Users Groups Printers

Kerberos • https: //en. wikipedia. org/wiki/Kerberos_(protocol) • AD security component since Windows 2000 • AD uses a Kerberos subset • Kerberos provides authentication of client and server • Kerberos encourages encrypted communication for all traffic • Kerberos requires encrypted communication for inital identification

SPN - Service Principal Names • Associates multiple AD/Windows objects within Kerberos • Computer address (usually Ethernet IP address and port) • Service name • AD/Windows account for this SPN • SETSPN. EXE • Requires Domain Administrator privilege to run • Lists and modifies SPNs • FCI (SQL Server Failover Cluster Instance) notes • SPNs are why FCIs are so much easier for Domain Adminis to install! • Default security doesn’t permit non-Domain Admin to install FCI

SID – Windows Security Identifier • Always authoritative • if an object has a SID, then the SID defines the object’s identity • Defined to be variable length • Usually a GUID under the covers

Names in Windows • Names are easy and decorative for security communication. • Names are for humans, rarely used inside of Windows • Names come in many varieties • • Object usually have multiple names “Friendly” name Canonocial name Fully qualified name • Applications can usually find a SID from a name

Windows Security-speak • People usually use names • Windows almost always uses SIDs • SPNs are critically important for services to access AD • AD is Window’s authoritative source for security information • Users • Computers • Groups

SQL Logins • Logins authenticate a connection to the server • Logins divide into two types… At least a dozen ways! • • Server login Contained Login SQL Authenticated Login Windows Authenticated Login

Contained SQL Logins • Especially important in Daa. S, such as Azure or Cosmos DB • Highly scalable • Only database user association • No server level permissions • Can not be sysadmin • Can not create new databases • Limited access to other databases • No cross database joins • Limited access to system databases (master, msdb)

SQL Server Logins • Can have server level permissions (such as sysadmin) • Can access multiple databases • Cross database joins • System databases (master and msdb) • Can stop the SQL Server • Windows authenticated users can have Service level permissions too

Database User • No Authentication • Inherits identity from Login • Nearly all object permissions are granted to Users, not Logins

Authentication Types • SQL Authentication • Login name • Password • Windows Authentication • Kerberos ticket grants SID access • AD/Windows User • AD/Windows Group

Security notes • Login returned by SUser_SName() • User returned by User_Name() • Impersonation • SETUSER • EXECUTE AS

EXECUTE xp_logininfo • Relies on SPN and Kerberos for AD/Windows information • My definitive answer for “What access does <blank> have to this SQL Server? ” • Parameters • @option = ‘all’ • Enumerates all of the access paths that a given login can use to get access as a result set • @option = ‘members’ • Enumerates the members of an AD/Windows Group as a result set!

SQL Server permissions • Great presenters can cover SQL permissions in about 30 hours • I’m not that great • We only have an hour • Let’s give it a shot anyway! • Full discussion starts at: • https: //docs. microsoft. com/en-us/sql/relationaldatabases/security/permissions-database-engine • Pretty SQL Security poster PDF at: • http: //go. microsoft. com/fwlink/? Link. Id=229142

SQL Permission related commands • GRANT • DENY • REVOKE Give permission Forbid ever* getting permission Drop previous GRANT or DENY • *Note that there is a specific exception where an identity is: • Denied access to a table • Granted access to a column within that table

How Identity (Login or User) matches work • Exact match for your identity • Match for a SQL Role that includes your identity • Recursive • Match for an AD/Windows Group that includes your AD User • Usually recursive

How permissions are checked • If the Identity is a sysadmin, it has permission • If the Identity owns any container (database, schema, etc. ), it has permission • If the Identity is DENIED, it can’t get permission • There can be an exception for a GRANT on a column created after a DENY on the associated object. • If the Identity is GRANTED permission, it has permission • By default, no permission (except sysadmin and owner)

Database Permissions • Keyword Permission to: • EXECUTE • SELECT • INSERT • UPDATE • DELETE Execute code (function or stored procedure) Retrieve information Add new data (usually rows) to an object Change data in an object Remove data from an object

Historical SQL Server permissions • Sys. Admin • Security. Admin • Server. Admin • others Ability to do anything that SQL can do Ability to add logins and grant permissions “Power User” able to manage much of SQL Server

Modern SQL Server permissions • Sys. Admin still exists, almost unchanged • Much more granular permissions • • 237 in SQL 2017 / Azure 230 in SQL 2016 219 in SQL 2014 214 in SQL 2012

Evaluation Request • Please fill out the evaluation forms • If you have specific comments or questions, please email me • Comments and questions are how I learn to present better. If you want me to give better presentations, you need to give me feedback! • No comment is small or insignificant. I read them all. • Thanks!

SQL Security 101 How did you get in here, and who said that you could do that? • Quad City PASS Local User Group • 2018 -02 -21 • Davenport, Iowa • http: //qcpass. sqlpass. org • Pat Phelan • DBA Mentor at Involta, LLC. • Pat@Yet. Another. SQL. com • Twitter: @Yet. Another. SQL Edit