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

Popular Posts