Do I have to be admin?

Often, I see people reporting “zzz fails if I’m not admin”. Do I have to be machine admin to use SSIS?

Well, absolutely no. Most SSIS tasks can be performed without being admin at all. Actually, I develop on Vista machine and I very rarely need to elevate to admin status.

Then why would you get errors if you are not admins? Most often you don’t have access to whatever data source or package location you use. E.g. if you load a package from SQL Server, you need to have access to this SQL Server. Often people automatically get access because they are local admins, but for other users, access should be configured explicitly.

Same is true for database and file connections at package execution time: if package tries to access something, the user who runs the package has to have an access to this resource (assuming package is using integrated authentication). So if your package fails when run as non-admin, the first step to do is turn on SSIS or console logging and see what error you get – this will point to the resource for which you need permissions.

Do I ever have to be machine administrator when working with SSIS? Yes, there are several situations when admin is needed.

  • First, when you look at the running packages in SSMS, you’ll only see your own packages if you are non-admin. If you need to see all packages, you need to be machine admin (unfortunately, it is controlled by admin membership – would be better to have specialized role for this).

  • Second, if you are developing custom task or transform in managed code, you’ll need to install it to GAC – this requires machine admin as well.

  • When developing in native code, this is usually simpler – you register the task once as admin; later you can compile it as regular user.

Comments (1)

  1. Mohit Nayyar says:

    Hi Michael

    I think we also need administrator permission if we are running our packages out of process (ExecuteOutOfProcess=TRUE) in master-child scenario.

    Initially I was running everything in-process (Master-Child ExecuteOutOfProcess=FALSE) but then due to some issue, we change this setting and things started failing.

    I was getting this error [Error 0x80070005 while loading package file "PackageName.dtsx". Access is denied] after changing ExecuteOutOfProcess=TRUE for Execute Package Task in Master package.

    Important point here is that I was getting this error only for child packages and NOT for master package.

    Then we just added our ETL account (SQL Agent is using this account) to administrator group and upon machine restart things worked.

    So I thought to run DTSHost.exe we have to be the administrator, or it could be more to do with starting a new process, which requires administrative rights.

    – Mohit Nayyar