February 28, 2024 at 5:08 pm
We have a few SSIS packages/catalogs that are called / scheduled via the SQL Agent.
Recently, we upgraded the SQL Standard version from 2017 to 2019 (and Visual Studio SSIS/SSDT were also updated), which unfortunately caused one of the SSIS packages to fail when calling an external encryption function. Basically, the SSIS package extracts the data into CSV file, then runs gpg4win to encrypt the data.
Here is what we know:
• NT Service\SQLSERVERAGENT was used to run the SQL Agent service.
• That account does have permissions on the applicable Windows directories (it was running before, right?)
• Running the SSIS package manually (BIDS/Visual Studio), it runs fine.
• Running the SSIS package via the SQL Agent appears to fail when trying to do the encryption.
Nothing appears to get logged, the process just "hangs" until we stop it and kill any windows processes.
• There were no proxies setup before the upgrade (nor did we create one now).
• The SSIS target had been set to SQL Server 2017 but, when the upgrade was done, we changed it to SQL Server 2019.
We changed the agent service to use a network adminstrator account and used the encryption interface software to point to the existing certificate (to create an entry in \Users\..\AppData\Roaming\ directory) for the network admin account. Now the SSIS package works correctly when started by the agent, but I am really scratching my head to figure out WHY it stopped working for the SQLSERVERAGENT.
• SQL Server configurations all appear to be the same (it was an upgrade).
• SID for SQLSERVERAGENT is still the same (again, it was an upgrade).
• Windows permissions have not changed.
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
February 29, 2024 at 5:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
February 29, 2024 at 5:12 pm
That does suggest a permissions problem.
How do you know permissions didn't change? Is it because you didn't change any, or because every permission relevant to SQL is configured by group policy?
Did the OS also get upgraded/replaced or was this an inplace upgrade on the same machine?
March 7, 2024 at 12:44 pm
That does suggest a permissions problem.
Yes... that is what I am thinking... but just have not figured out what/how. I posted here as we could not figure this out, so maybe someone had some "under the hood" knowledge that might have helped?
How do you know permissions didn't change? Is it because you didn't change any, or because every permission relevant to SQL is configured by group policy?
We restored a previous copy of the environment to review permissions in SQL Server (which should not have changed, since this was an "in place" upgrade) as well as the permissions of windows accounts. It appears everything is the same as far as we can tell?
Did the OS also get upgraded/replaced or was this an inplace upgrade on the same machine?
No... same OS, this was an inplace upgrade of SQL Server.
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
March 8, 2024 at 6:00 am
When are you using gpg4win, inside the SSIS package or as a different step to encrypt the file?
If the process is inside the package, my assumption, it looks like during SSIS catalog upgrade some of the settings of encryption process might have lost, hence it failed.
=======================================================================
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply