How to update a credential when underlying account’s password is changed?


We use SQL Server credential for multiple reasons mainly to create proxy accounts. However if the password for the account (which is being used by credential) is changed, then whole system fails.

For example, a job (which executes a SSIS using a proxy account) can fail with below error when the proxy account fails because the actual active directory account’s password is changed but credential is not updated:

Message:

Unable to start execution of step 1 (reason: Error authenticating proxy REDQUEEN\sqluser1, system error: The user name or password is incorrect.).  The step failed.


Note: Here the underlying account is REDQUEEN\sqluser1

Please use below code to update credential and then the proxy also will start working:

USE [master]
GO
ALTER CREDENTIAL [Credential Name] WITH IDENTITY = N'Account Name',
SECRET = N'Correct Password'
GO

Example:

USE [master]
GO
ALTER CREDENTIAL [Cre_SSIS] WITH IDENTITY = N'REDQUEEN\sqluser1',
SECRET = N'*******'
GO

Comments