Skip to content
July 19, 2013 / Francisco Castro

SQL Server Job Error: Error processing a SSAS database inside a SSIS Package

Usually when you deploy your dtsx (SSIS package extension) package you have already tested it and it is just fine. But when you try to execute the same dtsx package on a SQL Server job, sometimes it doesn’t work. Why?!

One possible error description: Either the user, NT AUTHORITY\NETWORK SERVICE, does not have access to the ‘databasename’, or the database does not exist.

Problem: When you execute the dtsx package on development enviroment or by some other tool (dtexec or dtexecui) the user executing the package is your user (the one you are logged in). On the other hand, when executing the package on a job, the user executing the package is the agent user. This user don’t have permissions for some actions like processing the cube ou acessing the cube datasource.

Solution: Create a proxy account that executes the job under your user privileges.

The solution  is very easy and everyone can do it in less than 5 minutes, you just need to do these simple steps:

  1. Create a credential in sql server – A credential is a record containing the authentication information needed to connect to a resource outside of SQL Server. Most credentials consist of a Windows login name and password.
    • Under Security folder on your instance, right click on folder CredentialsPost2Img1
    • Select option “New Credential..” and a new window will appear
    • Give a name to your credential (example: MyCredential), a user (mydomain\myuser) and a password. The user shoud be your own user or a user with all necessary privileges.
    • Select ok, and your Credential is created.
  2. Create a proxy Account – Microsoft SQL Server Agent proxy accounts define a security context in which a job step can run.
    • Under SQL Server Agent, open “Proxies” folder, right click on SSIS Package ExecutionPost2Img2
    • Select option “New Proxy…”
    • Give a name to your proxy, select your credential (created on step 1) and select the appropriate subsystems, in my particular case i have selected SSIS and SSAS related (see image below)Post2Img3
    • Press “Ok” button and your proxy is created.
  3. Use your proxy account to run the problematic job step
    • Open your job
    • Edit your step
    • On “Run as:” option select your proxy account.

    Post2Img4

More info:

Microsoft documentation to create a proxy –  http://msdn.microsoft.com/en-us/library/ms190698%28v=sql.105%29.aspx

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: