Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Is there a reason why you aren't using a domain account for SQL services? –  Eric Higgins Apr 2 '12 at 22:07
    
Yes, but I don't know which reason (I guess something with the DTAP cycle and the domain being not everywhere available). Anyway, it's a given environment which I'm not allowed to alter. –  vstrien Apr 4 '12 at 7:41
    
Situation #1 seems to be the answer. I think the error message you're getting is from within the package. In other words, the package is executing, but a task within it requires more permissions than the service account was granted. You may not have to grant administrator-level permissions to the account to make it succeed (grant granular permissions instead, or change the process to not need the permissions). –  Jon Seigel Aug 21 '12 at 16:43
    
Can You add SQL Logins (SQL Authentication) to the remote SQL Server (Server 2)? If You have that option, You can use the SQL Login in the SSIS connection used for destination. That means You will have to use a password to encrypt the package, but it will solve your problem. –  Justicator Sep 13 '12 at 15:32
    
@Justicator: Maybe, as a last resort. But whenever domain logins are possible, I prefer not to use SQL Authentication. –  vstrien Sep 14 '12 at 6:12

1 Answer 1

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.