Module 1
SQL Server Security
Module Overview
Authenticating Connections to SQL Server
Authorizing Logins to Connect to Databases
Authorization Across Servers
Partially Contained Databases
Lesson 1: Authenticating Connections to SQL Server
Overview of SQL Server Security
SQL Server Authentication
Azure SQL Database Firewall
Managing Logins and Policies
Demonstration: Authenticating Logins
Overview of SQL Server Security
Securables
Objects to which access must be secured
Principals
Security identities that access securables and perform actions
Permissions
The actions principals can perform on securables
Principal Permissions Securable
Security Hierarchies:
Securables can contain other securables
Principals can contain other principals
Permissions are inherited unless overridden
SQL Server Authentication
Authentication is the process of verifying that an
identity is valid:
Windows authenticationonly principals authenticated
by Windows can connect
SQL Server (mixed) authenticationprincipals
authenticated by Windows or SQL Server can connect
Azure AD authenticationAzure principals are
managed in a single place
Authentication ProtocolsKerberos
Azure SQL Database Firewall
Connection Fails
Users
Windows Azure
Windows Azure Platform
No
SQL Database Firewall
Configuration
Server-Level Rules Tools and Methods
Client IP
address
in
range?
Client IP Database-level
address
in No Firewall Rule for
range? Sales. Yes
Yes
Finance Clients Orders Sales
Azure SQL Database
Managing Logins and Policies
Logins:
Create in SQL Server Management Studio
Create using the CREATE LOGIN statement:
CREATE LOGIN [ADVENTUREWORKS\SalesReps] FROM WINDOWS
WITH DEFAULT_DATABASE =[salesdb];
Create and set security policy for SQL Server Logins:
CREATE LOGIN DanDrayton
WITH PASSWORD = 'Pa$$w0rd', CHECK_POLICY = ON,
DEFAULT_DATABASE = [salesdb];
Disable logins:
ALTER LOGIN DanDrayton DISABLE;
Delete logins:
DROP LOGIN DanDrayton;
Demonstration: Authenticating Logins
In this demonstration, you will see how to:
Set the authentication mode
Create logins
Manage server-level roles
Manage server-level permissions
Lesson 2: Authorizing Logins to Connect to Databases
Granting Access to Databases
Managing dbo and guest Access
Authorizing Logins and User Tokens
Demonstration: Authorizing Logins and User
Tokens
Granting Access to Databases
Logins cannot access a database to which they have
not been granted access
Grant access to a login by creating a database user
for it using SSMS or Transact-SQL
CREATE USER SalesReps
FOR LOGIN [ADVENTUREWORKS\SalesReps];
WITH DEFAULT_SCHEMA = Sales;
CREATE USER DanDrayton
FOR LOGIN DanDrayton;
CREATE USER WebUser
FOR LOGIN [ADVENTUREWORKS\WebAppSvcAcct];
Managing dbo and guest Access
dbo database user:
sa login, members of sysadmin role, and owner of the
database map to the dbo account
guest database user:
Enables logins without user accounts to access a
database
Disabled by default in user databases
Enabled by using the GRANT CONNECT statement
Authorizing Logins and User Tokens
Security Token Service:
Allows a single sign-on for multiple services and applications
Identifies and authorizes login tokens:
SELECT * FROM sys.login_token;
Identifies and authorizes user tokens:
SELECT * FROM sys.user_token;
Demonstration: Authorizing Logins and User Tokens
In this demonstration, you will see how to:
Create a server role
Create a login
Alter server roles
Create a user
View the results
Lesson 3: Authorization Across Servers
Linked Servers Security
Typical "Double-Hop" Problem
Impersonation vs. Delegation
Working with Mismatched Security Identifiers
Demonstration: Working with Mismatched
Security IDs
Linked Servers Security
Authenticated Access to External Data Sources
Link Server Objects:
Provider and Data Sources
Configuration:
Client, Server, Database Server Tiers
Definitions
EXEC sp_addlinkedserver@server='RemoteServer',
@srvproduct='',
@provider='SQLOLEDB',
@datasrc='r:\datasource\RemoteServer';
Security
Typical "Double-Hop" Problem
Client Win Authentication Login for S1 and S2
Application TCP/IP/Named Pipes
Account cannot be delegated
Hop1
Domain: Requires SPN
Server S1 Delegation: On
TCP/IP/Name Pipes
Linked: S2 must be registered on S1
Hop2
Domain: Requires SPN
Server S2 TCP/IP/Name Pipes
Impersonation vs. Delegation
Delegation:
Identity passed to remote servers
Impersonation:
Identity used within a domain
Windows Authentication
S4U
LogonUser API
Impersonate users and logins within a SQL Server
instance using EXECUTE AS
Working with Mismatched Security Identifiers
Orphaned users created by mismatched SIDs
Search using sp_change_users_login
Resolve using sp_change_users_login
Change password with sp_password
Use sp_changedbdowner to restore dbo
Guest account not mapped to a login
Consider Windows authenticated accounts
Demonstration: Working with Mismatched Security IDs
In this demonstration, you will see how to:
Test for orphaned users
Fix broken logins
Show that the logins have been corrected
Lesson 4: Partially Contained Databases
Introduction to Partially Contained Databases
Considerations for Using Partially Contained
Databases
Demonstration: Creating a Partially Contained
Database
Introduction to Partially Contained Databases
Contained databases do not have a hierarchical
dependency on server logins
Use contained databases to:
Move databases between different SQL Server
instances without having to migrate server-level
dependencies
Develop databases when the developer does not know
which instance will ultimately host the database
Enable failover in a high-availability scenario without
having to synchronize server-level logins
Users in a contained database include:
Users mapped to Windows accounts (users or groups)
Users with passwords
Considerations for Using Partially Contained Databases
Benefits:
Migration
Failover, including AlwaysOn Group Availability
Administration
Development
Considerations:
CDC, CT, Replication not allowed
Some Procedure types not supported
Collation
Password Policy, CREATE USER
ALTER DATABASE CURRENT
Connection Strings must be explicit
Cross Database Queries
Demonstration: Creating a Partially Contained Database
In this demonstration, you will see how to:
Check the server instance for containment
Inspect code to enable or disable containment
Create a partially contained database
Lab: Authenticating Users
Exercise 1: Create Logins
Exercise 2: Create Database Users
Exercise 3: Correct Application Login Issues
Exercise 4: Configure Security for Restored
Databases
Logon Information
Virtual machine: 20764B-MIA-SQL
User name: ADVENTUREWORKS\Student
Password: Pa$$w0rd
Estimated Time: 60 minutes
Lab Scenario
Adventure Works Cycles is a global manufacturer,
wholesaler and retailer of cycle products. You are a
database administrator for Adventure Works,
tasked with managing access to the MIA-SQL SQL
Server instance by creating and configuring logins
and database users.
Lab Review
In this lab, you have created server logins and
database users. You have diagnosed issues with a
login, and configured security for a database
restored from another server.
Module Review and Takeaways
In this module, you have learned about the core
concepts of SQL Server security, including logins
and database users. You have learned how to work
with cross-database security, and how to work with
databases that have different levels of containment.