Docstoc

SQL Server: Security

Document Sample
SQL Server: Security Powered By Docstoc
					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?
 • Limit discussion to database engine


            Learn More @ http://www.learnnowonline.com
               Copyright © by Application Developers Training Company
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


                Learn More @ http://www.learnnowonline.com
                   Copyright © by Application Developers Training Company
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

                Learn More @ http://www.learnnowonline.com
                   Copyright © by Application Developers Training Company
Contained Database Authentication
 Connection                                                           Matching     Yes                             Yes
  Request                                                              user in                    Password
                                                                      database                     match?
                                                                          ?

                                            SQL Server



                                                                                     *
                                                                      No                          No

    Initial    Yes       Initial      Yes          Authent-                                                   No          Permis-   Yes
   catalog              catalog                     ication                      Authentication                           sion in
  specified?          contained?                     type?                           failure                             database
                                                                                                                             ?

                                                                                                  No
  No                  No                      Windows

                                                                                                  Matching
                                                                      Matching     Yes            principal        Yes
                                                                      login or                        in
                                                                       group?                     database
                                                                                                       ?

                                                                      No



                      Server-level                                                                              Database
                     authentication                                                                           authentication




                           Learn More @ http://www.learnnowonline.com
                                   Copyright © by Application Developers Training Company
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




             Learn More @ http://www.learnnowonline.com
                Copyright © by Application Developers Training Company
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


                Learn More @ http://www.learnnowonline.com
                   Copyright © by Application Developers Training Company
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

              Learn More @ http://www.learnnowonline.com
                 Copyright © by Application Developers Training Company
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
        records
      • MAX_FILES option on CREATE SERVER AUDIT
      • Blocks and rolls back operations until clear files


               Learn More @ http://www.learnnowonline.com
                  Copyright © by Application Developers Training Company
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)




              Learn More @ http://www.learnnowonline.com
                 Copyright © by Application Developers Training Company
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




             Learn More @ http://www.learnnowonline.com
                Copyright © by Application Developers Training Company
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 @ http://www.learnnowonline.com
                Copyright © by Application Developers Training Company
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




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

				
DOCUMENT INFO
Shared By:
Stats:
views:13
posted:10/8/2012
language:English
pages:16
Description: Explore some of the best new security features in SQL Server.