Sql-server – Running an SSIS package owned by a domain user from SQL Server running on a local service account

sql serverssis

I want to run an SSIS package containing Transfer SQL Server Objects tasks. The involved servers are on the same domain, but the SQL Server services are running on local service accounts. So the environment looks like this:

Domain

Server 1

  • SQL Server running on local account
  • On filesystem: SSIS package
  • In SQL Server Agent: a job

Server 2

  • SQL Server running on local account

To be able to log on to both servers, I've created a domain account to be used as service account. When I use this domain account to log on to Server 1, and then execute the package from file system, every step succeeds. However, when I try to add the job to SQL Server I run into one of the following problems:

Situation 1. Job owner: local account; run SSIS step as proxy to the domain account. When I set the job owner to a local account, but run the job as a proxy to the domain account, the job itself will successfully execute, but the package throws errors like

Execution failed with the following error: "The directory
'LocalApplicationData' does not exist.".

This error can be fixed by creating a login with Administrator-rights for the domain user on Server 1, but this is obviously not a desirable solution. Adding the account to one of the SQL Server agent / DTS groups doesn't work either.

Situation 2. Job owner: domain account; run SSIS step as a proxy to the domain account. When I set both the job owner and the 'run as user' for the step to the domain account, the job won't start at all, with the following error:

Unable to determine if the owner (Domain\Domain user) of job Job
name has server access (reason: Could not obtain information about
Windows NT group/user 'Domain\Domain user', error code 0x5. [SQLSTATE
42000] (Error 15404)).

I believe the last error is because SQL Server runs on a local account and therfor cannot look at which rights domain accounts have.

What is the right way to make the job run? Situation 2 feels cleaner to me, but seems impossible because SQL Server runs on a local account. Situation 1 would work too, but giving a domain user administrative rights on my SQL Server is not going to happen.

UPDATE:

@JonSeigel and @Mr.Brownstone:

It seems plausible that this is issue is because of the lack of permissions. However, the error is about the non-existence 'LocalApplicationData' – one of the folders which is generated for each account. I've already logged in to the server with the credentials under which the package runs, (hereby creating a profile directory) and tried several combinations of permissions for the profile directory. Even when manually granting almost all permissions on this specific directory, I get the error mentioned above.

While doing some more research, I ran into a forum thread at http://www.sqlservercentral.com/Forums/Topic391332-148-1.aspx#bm391441 which is quite similar – without solution either though.

Best Answer

My personal opinion is that option #1 is the way to go. But I see no need for you to have to grant the domain account local administrator access. It seems to me that it requires access to certain folders and files and so you could grant the domain user access to only the resources that it needs to run the package successfully. This can be done through the file/folder properties dialog box and select the security tab - there should be no need to set it for every file and folder as you could set the permissions of the parent directory and set them to override child properties.

I hope this helps you.

Related Question