July 30, 2016 at 2:10 pm
I've done some work for a client over the past 2 years, developing numerous SSIS pkgs. for the client.
The packages have all been running perfectly fine week to week until about a month ago. The client's network was hit with some type of malware / ransomware and highly compromised their network. The had about a week's worth of down time with their third-party IT services firm cleaning the network, trying to restore backed-up files, etc.
Fast forward to a week later when I was told that their SQL Server machine didn't seem to be affected and that I could resume work on the current project I was working on... I determined that the several SQL Server Agent Jobs that run each week to call various SSIS jobs were now failing, whereas they'd all been working without issue prior to the malware/ransomware attack.
Their 3rd party IT services firm understandably did all sorts of work to restore the integrity of their network, and along the way, apparently reset / rebuilt some user accounts and had to re-establish various permissions for these accounts.
We *thought* the user account rebuilding/etc. may have restricted permissions to where the accts. used for calling the automated SQL Server Agent Jobs now didn't have sufficient permissions to run the job nor call the respective SSIS packages.
The IT firm relaxed permissions for a couple of accounts in question and I found that the jobs would at least start to run at that point, so my suspicion was partially correct.
So now with all of the SSIS packages, I'm getting errors all seemingly related to an inability by the packages to read/write to numerous Excel spreadsheets. All of the SSIS packages do different things but basically they all query a few SQL Server databases, & write the recordsets obtained from SQL Server to empty Excel spreadsheet templates.
The SQL Server machine doesn't have Microsoft Office installed, so I've had the Microsoft Access Database Engine 2010 Redistributable (32-bit) installed on the machine for 2 years and it HAD been serving us fine as far as allowing the SSIS pkgs. to read/write to the Excel spreadsheets with ease.
I'm thinking with all of the malware/ransomware issues experienced by the client, and whatever work had to be done by the 3rd party IT services firm to get their network back in a stable, working order, something else has been done that's unfortunately still causing the SSIS packages to fail.
Here's the collection of error msgs. written to a log by just one of the SSIS packages:
Error: 2016-07-29 22:03:14.98
Code: 0xC0202009
Source: Package Connection manager "ExcelDestinationClientContactInfo"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft Access Database Engine" Hresult: 0x80004005 Description: "Could not find installable ISAM.".
End Error
Error: 2016-07-29 22:03:14.98
Code: 0xC020801C
Source: Create Excel Spreadsheet of Client Contact Info for Employees Needing RMD Process Begun OLE DB Destination [36]
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "ExcelDestinationClientContactInfo" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
End Error
Error: 2016-07-29 22:03:14.98
Code: 0xC0047017
Source: Create Excel Spreadsheet of Client Contact Info for Employees Needing RMD Process Begun SSIS.Pipeline
Description: OLE DB Destination failed validation and returned error code 0xC020801C.
End Error
Error: 2016-07-29 22:03:14.98
Code: 0xC004700C
Source: Create Excel Spreadsheet of Client Contact Info for Employees Needing RMD Process Begun SSIS.Pipeline
Description: One or more component failed validation.
End Error
Error: 2016-07-29 22:03:14.98
Code: 0xC0024107
Source: Create Excel Spreadsheet of Client Contact Info for Employees Needing RMD Process Begun
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Is is possible that some permissions dealing with the Microsoft Access Database Engine 2010 Redistributable (32-bit) are "out of whack" following the malware/ransomware issues? That's all I can think of, as earlier parts of the SSIS pkgs. are running fine until we get to the part where data is attempted to be written to the Excel spreadsheets or read from various spreadsheets, and then the error msgs. shown above in bold are reported. These same type of Excel read/write and/or connection acquisition error msgs. are being reported for all long-standing packages that used to run completely seamlessly prior to the malware/ransomeware issues and the follow-up work by the 3rd party IT services firm.
This (below) might be a solution as far as permissions that are suddenly no longer present for the Microsoft Access Database Engine Redistributable 2010 (32-bit), but I'm not sure. Just dumbfounded since everything was working perfectly, the issues described above hit the client's network, and now none of the SSIS pkgs. execute.
Basically, you go to
Control Panel > Administrative Tools > Component Services
then expand
Component Services > Computers > My Computer > DCOM Config
find
MSDAINITIALIZE
go to
Properties > Security > Launch and Activation Permissions
click on
Customize > Edit...
add your login name or "Everyone" if you prefer
tick ALL the "allow" boxes for the new user / group
and hit OK on both pages
Now see if your OpenRowSet / OpenDataSource command works
Any thoughts or recommendations are highly appreciated.
July 31, 2016 at 4:56 pm
Were the SQL Agent Jobs modified in any way? Did you double-check that the job step properties for the SSIS package have the package marked to run in 32-bit mode? That would be my first guess; is that something modified the job definition and now it is running in 64-bit mode and unable to access any 32-bit drivers/providers.
Joie Andrew
"Since 1982"
July 31, 2016 at 7:04 pm
The SQL Server Agent Jobs weren't modified in any way, and they're still set as they were previously to run in 32-bit mode.
Appreciate your suggestion though.
August 1, 2016 at 4:29 am
It's difficult to isolate the impact of a week's worth of changes. And security can be challenging.
I suggest building one of more small SSIS packages that access Excel like your production packages. If you read Excel, build a package that reads a value from one cell and logs it. If you write to Excel, build another small package that writes the date and time to a spreadsheet.
Deploy these packages to Production and schedule them. You're not done until they run in Prod scheduled. In this way you're isolating potential issues, and they should present symptoms you can remedy.
Hope this helps,
Andy
Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics
August 1, 2016 at 8:51 am
Thanks for the suggestions, Andy. That's what I'll have to shoot for if we can't get the issues resolved as they stand at present... writing some very simplistic pkgs. that do very basic read/write operations to Excel, see if they'll run in production, and move on from there.
By the way, I attended your SSIS session at the SQL Saturday event in Murfreesboro, TN this past January and thoroughly enjoyed your presentation.
🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply