Sql-server – SQL Server 2016: create credential and add SQL Server Agent proxy for it

sql serversql-server-2016sql-server-agentssis

I'm trying to script a creation of SQL Server identity to be then used to execute SQL Server agent jobs via a proxy.

It looks as though I can only use a Windows account and thence I would have to provide its password in plain text. Seriously? There must be a better way to do this. I need this script to work on my team-mates' machines as well as mine:

USE [msdb]

CREATE LOGIN [proxy_login] WITH PASSWORD=N'passw0rd', 
    DEFAULT_DATABASE=[SSISConfig], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

CREATE CREDENTIAL [my_cred] WITH IDENTITY='proxy_login', SECRET='passw0rd'

EXEC msdb.dbo.sp_add_proxy 
    @proxy_name=N'My_Proxy',
    @credential_name=N'my_cred', 
    @enabled=1

Error:

Msg 14720, Level 16, State 1, Procedure sp_verify_credential_identifiers, Line 69 [Batch Start Line 0]
The operation failed because credential '@credential_name' identity is not a valid Windows account

In ideal world I would like to use the SYSTEM_USER login for the credential without having to supply their password.

Best Answer

The operation failed because credential '@credential_name' identity is not a valid Windows account

That is because you did not use Windows account, but SQL Server account, that is wrong. You just misunderstand SQL Server Agent Proxies

SQL Server Agent proxies use credentials to store information about Windows user accounts. The user specified in the credential must have "Log on as a batch job" permission on the computer on which SQL Server is running.

SQL Server Agent checks subsystem access for a proxy and gives access to the proxy each time the job step runs. If the proxy no longer has access to the subsystem, the job step fails. Otherwise, SQL Server Agent impersonates the user that is specified in the proxy and runs the job step.

In your cited article there is this phrase that you did not read carefully:

A credential is a record that contains the authentication information that is required to connect to a resource outside SQL Server.

That is, CREDENTIAL for Agent is some account to access OS(Windows) resources, so it's Windows account, OS does not know nothing about your SQL Server logins. The permissions to Windows resources are granted to Windows accounts, and one of this account you can use as a proxy for Agent.

Related Question