SQL Server: Security by LearnNowOnline

SQL Server: Security

Learn More @ http://www.learnnowonline.com Copyright ÂŠ by Application Developers Training Company

Security in SQL Server 2012 • Rebuilt security from the ground up in SQL Server 2005 • New version builds on that new foundation

• Will explore some of the best new security features • Are they enough to make an upgrade mandatory?

What’s New in Security? • Not a lot, but it’s good • Security Management • Default schema for groups • User-defined server roles • Authentication • Contained databases • Data Protection • Encryption enhancements • Auditing • Lots of goodies for DBAs Learn More @ http://www.learnnowonline.com Copyright © by Application Developers Training Company

Security Management • Default schema for groups • Fixes a big hole • Avoids creating implicit schemas • Less chance of wrong schema in queries

• Default schema for users solved a problem • But caused others • Allowing them for groups solves it • Create both using CREATE/ALTER USER statement Learn More @ http://www.learnnowonline.com Copyright © by Application Developers Training Company

Security Management • User-defined server roles • Server-level principal • Grant server-level permissions • Mix of GRANT and DENY permissions • T-SQL: CREATE/ALTER/DROP SERVER ROLE • Can only assign server-level permissions • Permissions needed • CREATE SERVER ROLE permission • IMPERSONATE on the server principal for logins • ALTER for server roles used as the server principal • Others if you assign server role ownership

Authentication • Contained databases • • • •

Can create a SQL user with a password Windows user in database Not associated with a login Authenticate against contained database • Get a token for that database only • Security boundary is tightly scoped

• If authentication fails at database, doesn’t fall back

to duplicate login, if any

Contained Database Authentication Connection Request

Matching user in database?

Yes

Password match?

SQL Server No Initial catalog specified?

Yes

Initial catalog contained?

Authentication type?

No No

Authentication failure

Permission in database?

Windows Matching login or group?

Yes

Matching principal in database?

Yes

Server-level authentication

Learn More @ http://www.learnnowonline.com Copyright ÂŠ by Application Developers Training Company

Database authentication

Yes

Contained Database Issues • Security issues you have to be careful about • Accessing other databases using the guest account • Duplicate logins • Increased access when containment status changes • Attaching a contained database • Offline password attacks

Data Protection • Cryptography Enhancements • HASHBYTES supports SHA2 256 and 512 bits • Passwords hashed with SHA512 • RC4 is deprecated, supported only when compatibility set

to 90 or 100. Don’t use it! • Maximum certificate key length increased to 4,096 from 3,456 • Service and database master keys now use AES instead of Triple-DES (for backups too) • FROM BINARY option on CREATE CERTIFICATE Learn More @ http://www.learnnowonline.com Copyright © by Application Developers Training Company

Auditing • Basic server auditing supported in all SQL Server editions! • Database auditing only in Enterprise, Datacenter,

Developer, and Evaluation editions • No longer need to rely on SQLTrace • • • •

Multiple audits and targets Better performance Persist state Audit resilience

Auditing • Resilient to failure • Can recover from failure to write to the log • From most file or network errors • Wee bit better than shutdown on failure! • FAIL_OPERATION option for the ON_FAILURE event

in CREATE SERVER AUDIT

• If problem with audit initiation at startup, server

instance won’t start • Can force a start with the –f option

Auditing • Cap files without rollover • Formerly could have either: • Indeterminate number of log files • Rollover after predefined number of files • Now can cap without rolling over • Control amount of information without losing audit

Auditing • User-defined audit events • Write what you want to the audit log • sp_audit_write • @user_defined_event_id • @succeeded • @user_defined_info (custom string)

Auditing • Filter audit events • Built on extended events • Pretty fine control over what gets written • Use the WHERE clause on the CREATE SERVER

AUDIT statement

Auditing • T-SQL stack frame information • Determine if query from stored procedure or

directly from application • See the nested frame for the query

Learn More! • This is an excerpt from a larger course. Visit www.learnnowonline.com for the full details! • Learn more about SQL Server on SlideShare:  SQL 2012: Development & Programming