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.
Yes your assumption is right, credentials cannot be created for SQL Server logins it can only be Domain users (visible from your SQL Server), and yes you have to pass the password when creating credentials.
Once credentials has been created one or more proxies can use them.
Typically proxies in SQL Server are used to facilitate cross domain processes. A process executing (probably SSIS job etc.) on DomainA\ServerA at run-time will access databases on DomainB\ServerB, The user account running the job on DomainA\ServerA must have access to DomainB\ServerB. Now in this case a proxy on DomainA\ServerA can be used with the credentials of a User, let say UserB from DomainB with access to ServerB etc. The proxy at run-time when reaches to DomainB will provide the credentials for UserB and the process can continue to run.