10

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.

Improve this question
5
  • Is there a reason why you aren't using a domain account for SQL services?
    – Eric Higgins
    Apr 2, 2012 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, 2012 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, 2012 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.
    – Roi Gavish
    Sep 13, 2012 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, 2012 at 6:12

1 Answer 1

Reset to default
5

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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