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.