February 22, 2016 at 2:08 pm
I have a newly built SQL 2014 cluster which has me at my wit's end when trying to execute SSIS packages with "transactions required".
Namely, I receive the following error when executing the SSIS package from dtexecui.exe or as a part of a job step:
"The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D027 "MSDTC was unable to read its configuration information.". The DTC transaction failed to start. This could occur because the MSDTC Service is not running. End Error DTExec: The package execution returned DTSER_FAILURE (1)."
I googled until my eyes bled and tried just about everything I found. Made sure that:
- Clustered DTC resource resolves via DNS
- Under Security Settings for the clustered DTC, DTC network is enabled, remote clients are enabled, transaction manager communication Inbound and Outbound are enabled.
- Testing distributed transactions from SSMS works:
begin distributed tran
select * from [linked_server].master.sys.sysdatabases
commit
This test correctly returns the remote server's list of databases and I see the committed and total transaction count increment in the Transaction Statistics under the Clustered DTC section of Dcomcnfg.exe. I use one of the instances on the same cluster as the remote linked server for the purposes of this test.
I'm honestly stumped. Ready to provide any further info needed to resolve this. Please help!
February 23, 2016 at 1:19 am
See if this helps : http://www.mofeel.net/1232-microsoft-public-sqlserver-reportingsvcs/7217.aspx
"Final note: If I put the user (DOMAIN\UserSSIS) in the
BUILTIN\Administrators groups on each node of the cluster, then the job works
just fine. But this workaround is not acceptable for us and we try not to be
forced to use it.
"
February 23, 2016 at 9:20 am
So that seems to have worked (to be precise, adding SQL Server Agent's account to Local Admins on all cluster nodes is what worked - the post is slightly unlcear). That constitutes a bit of a hole in my security set up though. Any way to do it without it?
April 15, 2016 at 1:17 am
I have a similar problem, and adding the SQL Server Agent user to builtin\administrators on all clusters did not solve the problem. I still cannot execute my SSIS package from SQL Agent Jobs.
I can execute the package directly from my SSIS catalogue though (with my administrator login).
Any idea what to do next?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply