Tuesday, July 17, 2012

Creating SQL Credentials for Network Service account

It’s fairly normal in production environments to find the SQL Server configured to disallow use of the SQL Agent account for the execution of certain types of job steps: SSIS packages and CmdExec for example. Instead you have to configure an explicit SQL Agent proxy, which requires first storing credentials within SQL’s credential store.

For domain accounts this is fairly straightforward, but if you attempt to add credentials from one of the ‘virtual accounts’ (such as Network Service), you’ll get the following error: “The secret stored in the password field is blank”

image

The solution is (eventually) obvious: add the credential using TSQL (or SMO), and avoid the UI validation:

USE [master]
GO

CREATE CREDENTIAL [Network Service] WITH IDENTITY = N'NT AUTHORITY\NETWORK SERVICE'
GO

et, voila:

image

1 comment:

Anonymous said...

But when you create a proxy using the credential you specified and set a job step to run as that proxy you get the following error:
"Unable to start execution of step 1 (reason: Error authenticating proxy NT AUTHORITY\NETWORK SERVICE, system error: Logon failure: unknown user name or bad password.). The step failed."

It seems without a password you cannot specify an identity for a credential.

Popular Posts