CREATE Credential Secret Storage

I am hoping to use SQL Server Agent 2k5 to run jobs in the security context of another account. I have successfully done this, so I know it works.

My question is: what mechanism does SQL 2k5 use to encrypt the secret that I enter when setting up the credentials for the job account using the CREATE CREDENTIAL statement? Is the secret protected using DPAPI? If so, what precautions must I take if I when managing the SQL Server Agent service account?

Many thanks, Kevin

The secret part of the credential is encrypted by the service master key (SMK). The SMK itself is protected by DPAPI using the SQL Server service account credentials. Changing the Agent service account shouldn't have any impact on the credentials, but changing the SQL Server service account will have an impact. If you change the service account manually, you will end up with a SMK that cannot be decrypted (in newer SQL Server builds we are mitigating this problem to some extent). My strong advice is to always have available an up-to-date backup of the SMK. This way, you can always restore the SMK if it becomes undecryptable. Losing the SMK is equivalent with losing all your encrypted data, so you should be extra careful about keeping backups of the SMK.

As a side note, SMK backups store the SMK encrypted with a password using 3DES and on Windows 2003 we enforce the password policy strength settings as we do for SQL Server logins.

For some additional information on the SMK, you can also look at the following:

http://blogs.msdn.com/lcris/archive/2005/07/08/437048.aspx

Thanks
Laurentiu

|||The credential secret is protected by service master key (see CREATE CREDENTIAL in BOL for details) which is in turm protected by DPAPI.

However encrypted credential secred as well as encrypted service master key is persisted in master database. Thus anybody who runs under the same windows account as SQLServer (i.e. NETWORK SERVICE by default) have an access to it. There is no published or unpublished interface to read these secrets, but this data can be eventually obtained from master.mdf file or even by implementing xp on a live server.

In short your job account credentials are protected by one or more ACLs granted to account under which SQLServer runs. It will be a good practice to run SQLServer under a unique account, so that no other machine task or service is using it. In that case only NT box admin has access to it. But this is normal, since any secret on local machine is available to NTBox admin.