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
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.