Troubleshooting Permission Issues with CREDENITIALS

I keep hearing this story and similar variations…

”On a dark and stormy night, I developed a SQL Server Integration Services (SSIS) package.  It worked wonderfully on my desktop development environment.  All the files were properly accessed and the data was processed in a magnificent matter.”

”But suddenly I deployed the SSIS package to production environment, or sometimes even the test environment first.  And BAM! the SQL Agent job I created to execute the package would fail.”

”I laughed and cried and checked all of the settings and permissions and everything is identical to the development environment.  I just don’t know what is going wrong.  Help me Obi-Won, you’re my only hope.”

The first thing I do any time I hear this story is to tell the developer that I think it’s really weird to actually say “open parenthesis” and “close parenthesis”.  But, right after that I check the execution log for the package.  The execution log will usually have all this verbiage around it not being able to this, that, or the other thing with some external resource that the SSIS package is referencing.

But the developer had stated that everything is identical to the development environment but this, of course, doesn’t mean that the SQL Agent account is actually the same between the two environments.  And this is where the problem often lies.  In the development environment the package was executed by a power user, the developer, that could access anything the developer wanted.  In nice and secure SQL environments, the SQL Agent service account will have minimal permissions within SQL Server and no permissions outside of the server that it doesn’t require.

New package.  New requirements.  New permissions.  New proxy.

As I mentioned, the account executing the SSIS package has different between the environments.  What if I could run the SSIS package on the production environment with the SAME permissions I ran the package with in the development environment.  SQL Server 2005 and 2008 allows for this through the use of CREDENTIALS.  Books Online defines CREDENTIALS as such:

A credential is a record that contains the authentication information (credentials) required to connect to a resource outside SQL Server. This information is used internally by SQL Server. Most credentials contain a Windows user name and password.

The information stored in a credential enables a user who has connected to SQL Server by way of SQL Server Authentication to access resources outside the server instance. When the external resource is Windows, the user is authenticated as the Windows user specified in the credential. A single credential can be mapped to multiple SQL Server logins. However, a SQL Server login can be mapped to only one credential.

To execute a SQL Agent job under the developers account the following four things need to be done:

1. Create the CREDENTIAL using CREATE CREDENTIAL.

-- Create a credential with the account DomainUser and its password
USE [master]
GO
CREATE CREDENTIAL MyCredential WITH IDENTITY = N'DomainUser', SECRET = N'Password'
GO

2. Create a proxy that references the CREDENTIAL using msdb.dbo.sp_add_proxy.

-- Create a proxy and assign the credential to it
USE [msdb]
GO
EXEC msdb.dbo.sp_add_proxy @proxy_name=N'MySSISProxy',@credential_name=N'MyCredential',@enabled=1
GO

3. Grant the proxy access to the SSIS subsystem using msdb.dbo.sp_grant_proxy_to_subsystem.

-- Grant proxy access to the 'SSIS package execution' subsystem, aka #11
USE [msdb]
GO
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'MySSISProxy', @subsystem_id=11
GO

4. Assign the Run As value for the SQL Agent Job Step to the proxy.

Often by going through these steps I’m able to show that the package that runs in the developer’s development environment under his login will also run in the production environment under his login.   Now identical really means identical and through either adding permissions to the SQL Agent service account or, even better, creating a CREDENTIAL with the appropriate permissions that package can be executed as intended.

Nice and Secure Environments

I should note that using the developer’s login is a temporary troubleshooting technique.  The developer’s login should not be used on an ongoing basis for the SQL Agent job.  This will open up a whole host of issues such as password resets and proxy accounts that stop working with developers logins are disabled.

And as I mentioned above, some people like nice and secure SQL Agent environments.  I am a big fan of those as well.  CREDENTIALS are a great tool that should be used to keep SQL Agent secure.  By limiting the scope of the SQL Agent service accounts, SQL Agent can be prevented from accessing resources and data that it shouldn’t normally access.  I’m not going to get too much into security best practices around this other than to say if restricting SQL Agent permissions isn’t a current consideration you should read the SQL Server 2005 Security Best Practices – Operational and Administrative Tasks white paper.