A proxy account is not allowed for a transact-sql subsystem
When a proxy account is first created, SQL Server Agent verifies that it is linked to a valid set of credentials. Upon each job step execution, SQL Server Agent makes sure the user is still granted access to the proxy account and fails the job if the user does not have access. Underlying credential objects can be manipulated while jobs are running, (such as when updating passwords and changing the security context of the object). One thing to keep in mind is that all jobs linked to the proxy account associated with this credential object will be affected.
Setting Up a Proxy Account
Now that you understand the theory behind proxy accounts, let’s go through the process of creating one. Suppose you need to have a special account for running a CmdExec task whose purpose is to run queries against Analysis Server and generate textual reports.
Under the Proxies folder in Object Explorer, there are multiple subfolders that contain proxies associated with different SQL Server Agent subsystems. There is also a special folder at the end that contains unassigned proxies. Right-clicking on the Proxies folder and selecting New Proxy opens the New Proxy Account dialog.
Tips and Tricks
Make sure you created an appropriate Credential object before attempting to create a proxy account. The Credentials folder is located under the Security folder in Object Explorer. The main requirement for a Credentials object to be used later for proxy account association is to have a valid Windows account identity and password. For most tasks that do not establish connections to other computers across the network, it may be sufficient to create a local computer account instead of a domain account. In this scenario, job steps using such an account run faster because account authentication can occur locally without contacting a domain controller.
For a new proxy account you need to fill in the proxy account name, a valid previously created credential object name, and subsystem associations (see Figure 8-9).
Figure 8-9. Creating a new proxy account.
If you already know which users will be using this proxy account, you can add them at this stage as well by clicking on Principals in the left tree view (See Figure 8-10).
Figure 8-10. Proxy account principal associations.
Now that the proxy account is ready, you can create the job running the CmdExec task (see Figure 8-11). One additional caveat here is the need to preserve full job step output because this is how a report actually gets generated. By default, only the first 1024 characters of any job step output are persisted in the database, and even then most of this space is consumed by statistical information about runtime, execution credentials, and so on.
Figure 8-11. CmdExec task creation.
Following normal job creation procedure, we will concentrate more on CmdExec job step creation. One thing to notice here is the proxy account selection under the Run as option. The drop-down list will be populated with all available proxies associated with the current subsystem to which the user has access.
Click on the Advanced item in the left tree view to get to the job step logging options.
Ciao Fab
