Transcript 6231B_09x

Module 9
Authenticating and
Authorizing Users
Module Overview
• Authenticating Connections to SQL Server
• Authorizing Logins to Access Databases
• Authorization Across Servers
Lesson 1: Authenticating Connections to SQL Server
• Overview of SQL Server Security
• SQL Server Authentication Options
• Managing Windows Logins
• Managing SQL Server Logins and Policies
Overview of SQL Server Security
SQL Server Authentication Options
Authentication is the process of verifying an identity
SQL Server can be configured in two modes:
Windows Authentication
Mixed SQL and Windows
Authentication
Managing Windows Logins
• Create Windows logins using:

Object Explorer option in SSMS

T-SQL CREATE LOGIN statement
• Remove using DROP LOGIN

Returns error if user currently logged on
CREATE LOGIN [AdventureWorks\Student]
FROM WINDOWS
WITH DEFAULT_DATABASE=[tempdb],
DEFAULT_LANGUAGE=[us_english];
GO
CREATE LOGIN [AdventureWorks\Salespeople]
FROM WINDOWS;
GO
Managing SQL Server Logins and Policies
• Create SQL Server logins using:

Object Explorer option in SSMS

T-SQL CREATE LOGIN statement
• Decide if account policy should apply
• Use ALTER LOGIN to:

Reset passwords

Disable/Enable logins
CREATE LOGIN SalesManager
WITH PASSWORD = 'Pa$$w0rd',
CHECK_POLICY = ON;
GO
CREATE LOGIN SalesApp
WITH PASSWORD = 'Pa$$w0rd',
CHECK_POLICY = OFF;
GO
Demonstration 1A: Authenticating Logons and
Logon Tokens
In this demonstration, you will see:
• How to create a Windows login
• How to view the list of existing logins
• How to create a SQL Server login using T-SQL
• How to connect to SQL Server
• How to check the available login tokens
• How to create a SQL Server login using the GUI
• How to create a login with policy disabled
• How to view the existing SQL Server logins and their policy
and expiration check status
Lesson 2: Authorizing Logins to Access Databases
• Authorization Overview
• Granting Access to Databases
• Managing dbo and guest Access
Authorization Overview
• Authentication and Authorization are often confused
• Authentication

Is the verification of the identity of a principal (such as
determining who someone is)
• Authorization

Is the assignment of permissions on a securable to a principal
(such as deciding what a person is permitted to do)

Can be implemented by assigning a principal to a role that
already has permissions

Implemented via GRANT, DENY, or REVOKE statements for
permissions on database objects
Granting Access to Databases
• Logins gain access to databases by the
creation of Database Users
• Create Database Users via:

Object Explorer option in SSMS

T-SQL CREATE LOGIN statement
CREATE USER SecureUser
FOR LOGIN SecureUser;
GO
CREATE USER Student
FOR LOGIN
[AdventureWorks\Student];
GO
CREATE USER HRApp
FOR LOGIN HRUser;
GO
Managing dbo and guest Access
• dbo

The sa login and members of sysadmin role are mapped to
dbo account, along with the database owner
• guest

This user account allows logins without user accounts to
access a database
Demonstration 2A: Authorizing Logins and User
Tokens
• In this demonstration you will see:

How to create database users using T-SQL

How to create database users using the GUI

How to view existing database principals

How to view user tokens
Lesson 3: Authorization Across Servers
• Typical "Double-Hop" Problem
• Impersonation vs. Delegation
• Working with Mis-matched Security IDs
Typical "Double-Hop" Problem
1
User identity presented
to the application
Web Server
3
2
4
Delegation is not
permitted. Identity of
the service account
used instead of the
identity of the user
User identity OK
because the
application process
impersonates the user
Database access
disallowed because of
incorrect identity
DB Server
Impersonation vs. Delegation
• Commonly mistaken for each other
• Impersonation

Ability to impersonate a user on the local machine
• Delegation

Ability to impersonate a user across the network
Working with Mismatched Security IDs
• Logins and Database Users

Both have names

Both have security identifiers called SIDs
• By default, SQL Server Logins receive new SIDs when
created

Occurs even if name and password is identical

SIDs for Windows logins provided by Windows
• Common problem occurs when restoring or attaching
databases from another instance
• Two methods for fixing the problem

Specify SID when creating the login (best option)

Fix the login SID in the database after restore
Demonstration 3A: Working with Mis-matched SID's
• In this demonstration, you will see how to correct an issue
with mis-matched SIDs.
Lab 9: Authenticating and Authorizing Users
• Exercise 1: Create Logins
• Exercise 2: Correct an Application Login Issue
• Exercise 3: Create Database Users
• Challenge Exercise 4: Correct Access to Restored Database
(Only if time permits)
Logon information
Virtual machine
623XB-MIA-SQL
User name
AdventureWorks\Administrator
Password
Pa$$w0rd
Estimated time: 45 minutes
Lab Scenario
You need to configure the security for the Marketing
database prior to the business accessing the system. You
need to configure security so that organizational users are
able to connect to SQL Server but are only to access
resources that they are permitted to access. Most users will
connect using their Windows group credentials. Some users
however will need to use individual Windows logins. An
application requires the use of a SQL Server login.
If you have time there is a problem with the
LanguageDetails database that you should try to solve.
Note: the changes you make will later be migrated to the
production environment. You should use T-SQL commands
to implement the required changes.
Lab Review
• What is a common scenario that requires account policy to
be disabled for a SQL Server login?
• What sort of login needs to be created for users in a
Windows domain if that domain is not trusted by the
domain that the SQL Server is part of?
Module Review and Takeaways
• Review Questions
• Best Practices