SSIS package 'Unexpected Termination' status when running from SSISDB catalog
-
We are trying to run SSIS package to read Access Database (.mdb) files using Native OLE DB\Microsoft Office 12 Access Database Engine OLE DB Provider in the Source Connection Manager. This package ran successfully from Visual Studio 2019 and also from SSISDB catalog in lower end servers.
However, executing the package in higher environments resulted in 'Unexpected Termination' status in SSISDB catalog without any error message. Tried changing different properties available in Source Connection Manager but all resulted in the same 'Unexpected Termination'.
Debugging it with help of DBA, we found out that the package executes successfully if the executor/caller of the SSIS package has admin privileges(like the DBA) in the server. As it turns out the successful execution in lower environments was also because of the admin privileges the executor had in the server. Revoking the admin privilege in lower environment(UAT) also seemed to show the same status.
Is the admin privilege an absolute necessity to run packages with ACE OLE DB providers? Or are there any missing areas we need to check?
Any answers or ideas would be very helpful.
Thanks.
-
you likely need to grant read/write permissions on the SQL Agent and SQL Instance accounts temp folder to the accounts executing the packages. ACE driver reads/writes files there while processing and that access is required.
-
We did try changing the temp folder and executing the package. Still no luck.
-
Just to clarify, when you say "admin privilege," you're granting the login in question sysadmin? Further, it sounds like you're running the SSIS package from the SSIS Catalog and not in an SQL Agent job, is that correct?
In our environment, we use the catalog and the only additional instance-level privilege the SSIS developers have is the bulkadmin role. They do (and this is another issue entirely) also have db_owner of the database(s) they work with.
Perhaps try granting the executor bulkadmin to start.
-
Yes, 'admin privilege' referring to another user with sysadmin - (a DBA in our organization) executing the package. Also, he tried it with SQL Agent job with SQL Server agent user and it successfully executed.
I guess i have the bulkadmin role as other packages in the same catalog executes successfully but the one with Microsoft ACE OLE DB 12 and 16 drivers are not executing as expected.
-
when a user with sysadmin executes the package from the catalog SQL Server will use its own account instead to execute - this will likely bypass some of the security issues.
SQL Agent works differently from the execution from the catalog (or using one of the supplied SSIS Catalog SP's) in that the authentication token is generated locally (on the server) for the SQL Account or the SQL Proxy used.
this has several implications - one of them is that, where needed, security on the temp folder is not for the user executing the package, but for a different user which can yield different results than those expected.
Same applies if the package is attempting to access a file on a network share - this will also fail with authentication issues (and ACE driver will not fail with an authentication error but with a "weird" error most of the times. To be more clear the authentication token SQL Server uses from the user executing the package STOPS within the sql server itself and is not "sent" to the destination server (rather a "nt\anonymous login" is passed).
Worthwhile noting that SSIS Catalog does NOT work with kerberos delegated authentication which would potentially be required for the execution of the packages.
so if you would be so kind as to tell us where are the files your package is using (locally mapped drive or network) that will help clarify the issue - and if network then not likely for you to have a solution if packages need to be executed outside a SQL Agent job.
-
Little correction on my previous comment: It seems like we need remote admin privilege on the server where SSIS catalog is running and not the 'sysadmin' privilege on SQL Server instance to execute the package successfully.
Our current setup:
1. SSIS packages are deployed to SSISDB catalog of SQL Server instance running on a server (let's say Server1)
2. Our files are located in a different network drive.
3. We execute the DTExec command from another server (Server2) pointing to the SSIS packages in Server1.
This setup is working for all other file types (csv, txt, etc.) but facing error for Access DB (.mdb) files.
Also, using SQL Agent Job with a different proxy user (windows authentication) did not work as well however using SQL Agent Job with SQL Server Agent user did work.
Found this on the download page of Access Database Engine 2016 Redistributable. Specifically, the 4th point. Does this place any restriction:
-
that wording from the ACE page is just that - words. there are no restrictions within the software as I use it on our servers.
but have a look at https://stackoverflow.com/questions/23523953/empty-excel-file-permissions-issue-ssis-excel-destination-buffers-large-record for folder permissions.
this assuming that role SSIS_ADMIN is not enough to solve the issue.
-
Found this on the download page of Access Database Engine 2016 Redistributable.
OK, so that answers another question I had, and it's a good thing. Now for the challenging question. Do you have the x86 or the x64 Redistributable on the server in question
AND
What version of Access or the Redistributable is installed on the workstations that are attempting to run the package in the Catalog AND what version is on the workstation of whoever developed the Package?
I ask because we've had problems when the developer had x86 Access and we'd put the x64 Redistributable on the server. Possibly the exact same error you're getting (it's been a while)
-
Initially we had x64 version of Access. But we tried installing x86 version along with it but did not work that way as well.
Also, as the answer in above Stackoverflow link, giving the executing user access to "C:\Users\Default" folder in the server did not work.
-
Yes, despite some rather "hacky" solutions, you can't have the x86 and x64 Redistributables installed on the same system. Ideally, you have the one that matches what's on the developers' workstations, where the SSIS package is being built.
- jasona.work wrote:
Yes, despite some rather "hacky" solutions, you can't have the x86 and x64 Redistributables installed on the same system. Ideally, you have the one that matches what's on the developers' workstations, where the SSIS package is being built.
Yes you can. In fact, before 64-bit VS was released, it was pretty much a necessity, given that VS ran in 32-bit mode and DTExec ran (by default) in 64-bit mode.
But it is a cumbersome process.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel - jasona.work wrote:
Yes, despite some rather "hacky" solutions, you can't have the x86 and x64 Redistributables installed on the same system. Ideally, you have the one that matches what's on the developers' workstations, where the SSIS package is being built.
with that I totally disagree - server should have 64 bit version - and workstation should have the one matching the VS version you have (32 bit if lower than VS 22 (first to be 64 bit).
what one needs to ensure is that the SSIS package in the server runs with the correct bitness (64 bit with very very few exceptions (cases where a driver MUST be used that does not have a 64 bit version))
And going back to the original problem - if it works when user has full admin rights then the issue is NOT bitness, but permissions of some kind - issue is finding out which permission and how to fix it.
can be folder permissions, dcom permissions and a few others
- frederico_fonseca wrote:
with that I totally disagree - server should have 64 bit version - and workstation should have the one matching the VS version you have (32 bit if lower than VS 22 (first to be 64 bit).
Knowing that a package will execute in 64-bit mode on the server, is it not preferable to have it execute in 64-bit mode while debugging on a client machine? That's why, before 64-bit VS, it made sense to have both 32- and 64-bit versions installed. Also, I'd rather not be bothered clicking on the 'Use 32-bit Runtime' debug option.
I agree with your other points.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply