1

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.

1 Answer 1

3

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.

1
  • And if you want a TSQL job step to run as a particular user, Agent will impersonate the job owner, and if the job owner is a sysadmin it can specify an arbitrary user to impersonate. Nov 2, 2017 at 15:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.