Module 14 Configuring Security for SQL Server Agent

1. Module 14 Configuring Security for SQL Server Agent

2. Module Overview • Understanding SQL Server Agent Security • Configuring Credentials • Configuring Proxy Accounts

3. Lesson 1: Understanding SQL Server Agent Security • Overview of SQL Server Agent Security • SQL Server Agent Roles • Discussion: SQL Server Agent Job Dependencies • Assigning Security Contexts to Agent Job Steps • SQL Server Agent Security Troubleshooting • Demonstration 1A: Assigning a Security Context to Job Steps

4. Overview of SQL Server Agent Security • Network permissions are determined by the service account: • Built-in accounts such as Local and Network Service • Windows domain accounts • Account used to execute jobs must connect to: • SQL Server instance for T-SQL Job Steps • Windows and network resources for other job types • Proxy Accounts can be used It is important to make sure that each SQL Server Agent job step runs in an appropriate security context.

5. SQL Server Agent Roles • sysadmin fixed role members can administer SQL Server Agent • Fixed database roles in the msdb control access for other users

6. Discussion: SQL Server Agent Job Dependencies • What SQL Server resources would SQL Server Agent Jobs potentially depend upon? • What resources outside of SQL Server might SQL Server Agent jobs depend upon? • What identity is needed for accessing the external resources?

7. Assigning Security Contexts to Agent Job Steps • T-SQL job steps: • SQL Server Agent impersonates the owner of the Job • If the owner is a member of the sysadminfixed server role, the step runs under the SQL Server Agent service account • Members of the sysadmin fixed server role can also specify a different user • Other job step types: • Members of sysadmin fixed role can use SQL Server Agent account (default) • Proxy Accounts are used to define the credentials to use

8. SQL Server Agent Security Troubleshooting • Check: • That the job is running • The security account that the job is executing under • SQL Server Agent Service Account or Proxy • SQL User for T-SQL Job Steps • The permissions for the account • Check tasks the job is performing • Review job step history

9. Demonstration 1A: Assigning a Security Context to Job Steps • In this demonstration, you will see: • How to view the identity that a job step was executed under • How to change the security context for T-SQL job steps • How to troubleshoot a job step

10. Lesson 2: Configuring Credentials • Overview of Credentials • Configuring Credentials • Managing Credentials • Demonstration 2A: Configuring Credentials

11. Overview of Credentials • Credentials are: • Required for access to external resources • Password encrypted using the service master key • Credentials are used to: • Provide a Windows identity for SQL Server Logins that need to access external resources • Provide identities for Proxy Accounts that are used in SQL Server Agent Job Steps Credentials are SQL Server objects that store authentication information.

12. Configuring Credentials • Created using T-SQL or SSMS • Stored in master database with secret encrypted using the service master key USEmaster; GO CREATECREDENTIALAgent_Export WITHIDENTITY=N'VIENNA\Agent_Export', SECRET=N'Pa$$w0rd'; GO

13. Managing Credentials • Credentials can be listed by querying the sys.credentials system view • Credentials are modified using ALTER CREDENTIAL • Both the identity and the secret are always altered • Credentials are removed via DROP CREDENTIAL SELECT*FROMsys.credentials; GO ALTERCREDENTIALAgent_Export WITHIDENTITY=N'VIENNA\Agent_Export', SECRET=N'NewPa$$w0rd'; GO DROPCREDENTIALAgent_Export; GO

14. Demonstration 2A: Configuring Credentials • In this demonstration you will see: • How to create a job that copies a file • How to create a credential using T-SQL

15. Lesson 3: Configuring Proxy Accounts • Overview of Proxy Accounts • Working with Built-in Proxy Accounts • Managing Proxy Accounts • Demonstration 3A: Configuring Proxy Accounts

16. Overview of Proxy Accounts Proxy Accounts provide SQL Server Agent with access to Microsoft Windows security credentials • Created using SSMS or dbo.sp_add_proxy procedurein msdb • Can always be used by sysadmin fixed server role members • Can be used with permission by: • SQL Login • msdb or server role

17. Working with Built-in Proxy Accounts • Proxies are defined for specific SQL Server Subsystems • Proxies can be used by one or more subsystems • Provides limited security for specific functions • Each job step type is associated to specific subsystem, except T-SQL

18. Managing Proxy Accounts • Proxies are defined in msdb • Configuration information can be accessed through system tables in msdb • sysproxies, sysproxylogin, sysproxyloginsubsystem, syssubsystems USEmsdb; GO SELECT p.name asProxyName, c.name asCredentialName, p.descriptionasProxyDescription FROMdbo.sysproxiesAS p INNERJOINsys.credentialsAS c ONp.credential_id=c.credential_id;

19. Demonstration 3A: Configuring Proxy Accounts • In this demonstration, you will see: • How to define a Proxy Account • How to use a Proxy Account • How to view Proxy Accounts and their properties using T-SQL

20. Lab 14: Configuring Security for SQL Server Agent • Exercise 1: Troubleshoot job execution failure • Exercise 2: Resolve the security issue • Challenge Exercise 3: Perform further troubleshooting (Only if time permits) Logon information Estimated time: 45minutes

21. Lab Scenario You have deployed a job that extracts details of prospects that have not been contacted recently. You have also scheduled the job to run before each of the two marketing planning meetings that occur each week. The marketing team has deployed new functionality in Promote application to improve the planning processes. Rather than having the job scheduled, it is necessary for the Promote application to execute the job on demand. The Promote application connects as a SQL login called PromoteApp. One of the other DBAs Terry Adams has attempted to configure SQL Server so that the PromoteApp login can execute the job. However he is unable to resolve why the job still will not run. In this lab you need to troubleshoot and resolve the problem.

22. Lab Review • What do you need to do when the password for a credential expires? • Can credentials use external encryption providers?

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