1. Module 9 Authenticating and Authorizing Users

2. Module Overview • Authenticating Connections to SQL Server • Authorizing Logins to Access Databases • Authorization Across Servers

3. 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

4. Overview of SQL Server Security

5. 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

6. 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 CREATELOGIN [AdventureWorks\Student] FROMWINDOWS WITHDEFAULT_DATABASE=[tempdb], DEFAULT_LANGUAGE=[us_english]; GO CREATELOGIN [AdventureWorks\Salespeople] FROMWINDOWS; GO

7. 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 CREATELOGINSalesManager WITHPASSWORD='Pa$$w0rd', CHECK_POLICY=ON; GO CREATELOGINSalesApp WITHPASSWORD='Pa$$w0rd', CHECK_POLICY=OFF; GO

8. 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

9. Lesson 2: Authorizing Logins to Access Databases • Authorization Overview • Granting Access to Databases • Managing dbo and guest Access

10. 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

11. 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 CREATEUSERSecureUser FORLOGINSecureUser; GO CREATEUSER Student FORLOGIN [AdventureWorks\Student]; GO CREATEUSERHRApp FORLOGINHRUser; GO

12. 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

13. 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

14. Lesson 3: Authorization Across Servers • Typical "Double-Hop" Problem • Impersonation vs. Delegation • Working with Mis-matched Security IDs

15. Typical "Double-Hop" Problem User identity presented to the application 1 Web Server Delegation is not permitted. Identity of the service account used instead of the identity of the user 3 User identity OK because the application process impersonates the user 2 DB Server Database access disallowed because of incorrect identity 4

16. 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

17. 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

18. Demonstration 3A: Working with Mis-matched SID's • In this demonstration, you will see how to correct an issue with mis-matched SIDs.

19. 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 Estimated time: 45minutes

20. 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.

21. 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?

22. Module Review and Takeaways • Review Questions • Best Practices