SQL Server Security Basics
Learn More @ http://www.learnnowonline.com
Copyright by Application Developers Training Company
Objectives
Understand potential data threats and how SQL Servers design protects against them Learn about SQL Server and Windows integrated authentication See how SQL Server provides an authorization system to control access to data and objects
Learn More @ http://www.learnnowonline.com
Copyright by Application Developers Training Company
Agenda
Security Overview Authentication Authorization
Learn More @ http://www.learnnowonline.com
Copyright by Application Developers Training Company
Security Overview
Relational data is a tempting target for attackers SQL Server 2008 provides plenty of features to secure your data and server
Need to understand the threats
Match countermeasures to the threats
Learn More @ http://www.learnnowonline.com
Copyright by Application Developers Training Company
The Threats
Identifying threats is a critical first step
Type of data will probably influence security measures
Sometimes the best way to protect data is to never put it in a database Typical threats
Theft of data Data vandalism Protecting data integrity Illegal storage
Understand threats to protect against them
Learn More @ http://www.learnnowonline.com
Copyright by Application Developers Training Company
Security Design Philosophy
Trustworthy Computing memo, 2002 Four pillars of security design
Secure by design Secure by default Secure in deployment Secure through communications
Its just secure
Implications throughout the product SQL Server is reasonably secure out of the box Your job is to keep it secure
Learn More @ http://www.learnnowonline.com
Copyright by Application Developers Training Company
The Two Stages of Security
Similar to Windows security
Authentication: who are you? Authorization: now that we know who you are,
what can you do?
Learn More @ http://www.learnnowonline.com
Copyright by Application Developers Training Company
Key SQL Server Security Terms
Authentication Authorization Group Impersonation Login Permission Principal Privilege Role User
Learn More @ http://www.learnnowonline.com
Copyright by Application Developers Training Company
Agenda
Security Overview Authentication Authorization
Learn More @ http://www.learnnowonline.com
Copyright by Application Developers Training Company
Authentication
Process of verifying that a principal is who or what it claims to be
SQL Server has to uniquely identify principals in order to
authorize
Two paths to authentication
Windows authentication SQL Server authentication
Authentication modes
Mixed Mode Authentication Windows Only Authentication Mode
Learn More @ http://www.learnnowonline.com
Copyright by Application Developers Training Company
Windows Integrated Authentication
SQL Server assumes a trust relationship with Windows Server
Windows does the heavy lifting for authentication The SQL Server checks permissions on the principal
Advantages
Single user login Auditing features Simplified login management Password policies
Changes only take effect when user connects
Learn More @ http://www.learnnowonline.com
Copyright by Application Developers Training Company
Configuring SQL Server Security Settings
Select either when install or later Settings apply to all databases and server objects in an instance of SQL Server Changing modes after installation may or may not cause problems
Windows to Mixed
Mixed to Windows
Learn More @ http://www.learnnowonline.com
Copyright by Application Developers Training Company
SQL Server Authentication
Client applications must provide login credentials as part of connection string Logins stored in SQL Server Windows authentication stronger
But must use SQL Server authentication with old
versions of Windows, non-Windows systems
Learn More @ http://www.learnnowonline.com
Copyright by Application Developers Training Company
Windows and SQL Server Logins
SQL Server logins are not stored in Windows
Disabled if you select Windows authentication
Mixed mode is much more flexible
But less secure
Learn More @ http://www.learnnowonline.com
Copyright by Application Developers Training Company
Beware of the sa Login
System administrator login Mapped to sysadmin fixed server role Conveys full system administrator privileges Cannot modify or delete Must use a strong password! Use only as access of last resort NEVER use sa for database access through client applications
Learn More @ http://www.learnnowonline.com
Copyright by Application Developers Training Company
Password Policy and Enforcement
Before SQL Server 2005, no enforcement of passwords for SQL Server logins
No minimum strength No expiration policy
SQL Server now hooks into Windows password policy
Windows Server 2003, Vista, and later versions NetValidatePasswordPolicy API method
Learn More @ http://www.learnnowonline.com
Copyright by Application Developers Training Company
Contained Databases
Not a security feature per se
But introduces a new authentication scheme
Solves problem of moving databases
Past: move database plus external dependencies Contained databases solves associated problems
Learn More @ http://www.learnnowonline.com
Copyright by Application Developers Training Company
Contained Databases Authentication
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, doesnt fall back to duplicate login, if any
Learn More @ http://www.learnnowonline.com
Copyright by Application Developers Training Company
Contained Databases Authentication
Connection Request Matching user in database ?
Yes
Password match?
Yes
SQL Server No
Initial catalog specified?
Yes
Initial catalog contained?
Yes
Authentication type?
Authentication failure No
Matching principal in database ?
No No
Permission in database ?
Yes
No
No
Windows
Matching login or group?
Yes
Yes
No
Server-level authentication
Database authentication
Learn More @ http://www.learnnowonline.com
Copyright by Application Developers Training Company
Agenda
Security Overview Authentication Authorization
Learn More @ http://www.learnnowonline.com
Copyright by Application Developers Training Company
Authorization
Principals: user or process allowed to access securable objects Securables: protected resource Permissions: type of access
Learn More @ http://www.learnnowonline.com
Copyright by Application Developers Training Company
Principals
Windows-level principals
Windows Domain Login Windows Group Windows Local Login
SQL Server-level principals
SQL Server Login SQL Server Login mapped to a certificate SQL Server login mapped to a Windows login SQL Server Login mapped to an asymmetric key Application Role Database Role Database User Database User mapped to a certificate Database User mapped to a Windows login Database User mapped to an asymmetric key Public Role
Learn More @ http://www.learnnowonline.com
Copyright by Application Developers Training Company
Database-level principals
Principals
Scope of a principal determines scope of permission Principal can be a login, user, or role
Roles are analogous to Windows groups
Users in role inherit roles permissions Simplify security management
Types of roles
Fixed server roles User-defined server roles Fixed database roles User-defined database roles
Learn More @ http://www.learnnowonline.com
Copyright by Application Developers Training Company
Fixed Server Roles
Cannot alter, even to add new ones, except to add logins to a role Server roles
System administrator Bulk insert administrator Database creator Disk administrator Process administrator Server administrator Setup administrator Security administrator
Learn More @ http://www.learnnowonline.com
Copyright by Application Developers Training Company
User-Defined Server Roles
Long awaited security feature
Long have had user-defined database roles But nothing at the server level
Used to be, only way to grant some permissions was through a fixed server role SQL Server 2012 solves these problems
Learn More @ http://www.learnnowonline.com
Copyright by Application Developers Training Company
Fixed Database Roles
Control authorization within a database Configure each database individually Database roles
db_accessadmin db_backupoperator db_datareader db_datawriter db_ddladmin db_denydatareader db_denydatawriter db_owner db_securityadmin
Learn More @ http://www.learnnowonline.com
Copyright by Application Developers Training Company
The Public Role
Every database user assigned to this role Be very careful about granting permissions Normally restrict permissions for this role
Learn More @ http://www.learnnowonline.com
Copyright by Application Developers Training Company
The dbo (Database Owner) Role
Mapped to sysadmin fixed server role Not related to db_owner role
Learn More @ http://www.learnnowonline.com
Copyright by Application Developers Training Company
User-Defined Database Roles
Standard role Application role
Learn More @ http://www.learnnowonline.com
Copyright by Application Developers Training Company
Securable Objects
Protected resource that you can control access to Physical object or action
Learn More @ http://www.learnnowonline.com
Copyright by Application Developers Training Company
Securable Objects
Server Database Endpoint Remote Binding Route Server Role SQL Server Login
Database Application Role Assembly Asymmetric Key Certificate Database user Fixed Database Role Full-Text Catalog Message Type Schema Service Service Contract Symmetric Key
Schema Default Function Procedure Query Stats Queue Rule Synonym Table Trigger Type View XML Schema Collection
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 about SQL Server on SlideShare
A Tour of SQL Server
Learn More @ http://www.learnnowonline.com
Copyright by Application Developers Training Company
