January 14, 2011 at 6:35 am
I'm facing a strange issue and I'm totally lost.
I set up a linked server to a DB2 database on iSeries (AS/400) using HitOLEDB provider loaded out of process. I'm doing this beacause the provider is a bit unstable and I don't want to crash the DB engine loading it with "allowinprocess".
Everything works fine when I connect with SQL authentication.
With windows authentication I had to tweak COM security settings for MSDAINITIALIZE and now I can read and write the linked server's tables.
In order to enable distributed transactions, I also had to change security settings for MSDTC, SCM and CLUSSVC and enable Authenticated Users for Generic Read access.
When I try to start a distributed transaction under Windows Authentication, I get this error:
Msg 7391, Level 16, State 2, Line 9
The operation could not be performed because OLE DB provider HiTOLEDB400
for linked server XXXX was unable to begin a distributed transaction.
I also attached the MSDTC trace for this transaction, but it doesn't seem to contain meaningful information.
The Event Log doesn't contain significant entries.
I also tried tracing sqlserver.exe process with Process Monitor, but it doesn't log any failures.
Specs:
SQL Server 2005 SP3 x64
Windows 2003 R2 Clustered
HitOLEDB/400 x64
Any help would be greatly appreciated.
-- Gianluca Sartori
January 14, 2011 at 6:53 am
Question for you, Does the SQL that you are trying to execute have a BEGIN and END Transaction statements?
-Roy
January 14, 2011 at 7:01 am
Thank you for responding, Roy.
Here's my test code:
SET XACT_ABORT ON
BEGIN DISTRIBUTED TRANSACTION
--Do some stuff locally
CREATE TABLE #Test(i int, h char(1))
INSERT INTO #Test VALUES (1, '1')
--Do some stuff remotely
UPDATE
OPENQUERY(LINKEDSERVERNAME, 'SELECT * FROM SomeRemoteTable')
SET SomeField = '2'
ROLLBACK
SET XACT_ABORT OFF
-- Gianluca Sartori
January 14, 2011 at 7:06 am
I was searching for this error and found this particular forum where someone had almost the same issue. Maybe you could check it out.
-Roy
January 14, 2011 at 7:11 am
Does this need an END as Roy suggests?
GE
January 14, 2011 at 7:19 am
Thank you Roy and Greg.
Unfortunately this does not solve my issue. Basically, that thread says "don't use distributed transactions", but this is exactly what I'm trying to do.
Please note that with SQL authentication everything works fine!!!! It boggles the mind...
-- Gianluca Sartori
January 14, 2011 at 7:27 am
Gianluca Sartori (1/14/2011)
Thank you Roy and Greg.Unfortunately this does not solve my issue. Basically, that thread says "don't use distributed transactions", but this is exactly what I'm trying to do.
Please note that with SQL authentication everything works fine!!!! It boggles the mind...
That it works with SQL auth and not with AD auth makes me think it's a security issue. I don't know how DTC works on AS/400, but in Windows, it's a service and it needs to be accessed with the right credentials. Is it possible the credentials aren't getting passed over correctly for DTC on the target? Or the DTC service on the Windows machine doesn't have auth on the AS/400 machine?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 14, 2011 at 7:32 am
IIRC, the following drivers work for distributed transactions on the i:
MTS (Microsoft Transaction Server)
System i Access ODBC
IBMDASQL OLE
IBM DB2 .NET
Do you have access to the IBM drivers? I think they would be your best bet. I have not used the HiT driver. I have had success setting up a linked server using the ODBC driver. The problem with the ODBC driver is that you must send the user id and password in clear text. What I did was set up a user id with very limited capabilities than can not log into the machine.
Here's a link to the IBM website:
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/index.jsp
Type "distributed transactions" into the search box in the left pane.
Tom
January 14, 2011 at 7:51 am
OCTom (1/14/2011)
IIRC, the following drivers work for distributed transactions on the i:MTS (Microsoft Transaction Server)
System i Access ODBC
IBMDASQL OLE
IBM DB2 .NET
Do you have access to the IBM drivers? I think they would be your best bet. I have not used the HiT driver. I have had success setting up a linked server using the ODBC driver. The problem with the ODBC driver is that you must send the user id and password in clear text. What I did was set up a user id with very limited capabilities than can not log into the machine.
Here's a link to the IBM website:
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/index.jsp
Type "distributed transactions" into the search box in the left pane.
Tom
Thank you, Tom. Hit driver is much faster than MS driver, this is why we're using it.
I could change to MS provider, but we have LOTS of code that runs on the linked server, I don't want to change provider and test everything again...
-- Gianluca Sartori
January 14, 2011 at 7:55 am
GSquared (1/14/2011)
That it works with SQL auth and not with AD auth makes me think it's a security issue. I don't know how DTC works on AS/400, but in Windows, it's a service and it needs to be accessed with the right credentials. Is it possible the credentials aren't getting passed over correctly for DTC on the target? Or the DTC service on the Windows machine doesn't have auth on the AS/400 machine?
You're right, AFAIK.
What boggles me is that adding the windows user to local admins doesn't solve the issue. (It's something I don't want to do, anyway).
When working with SQL auth, DTC is invoked with the sql service account and everything works fine. Go figure.
-- Gianluca Sartori
January 15, 2011 at 8:43 am
What's your MSDTC security configuration.
I think you db2 as400 uses xa transactions.
Did you enable XA transactions with MSDTC ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 18, 2011 at 9:26 am
ALZDBA (1/15/2011)
What's your MSDTC security configuration.I think you db2 as400 uses xa transactions.
Did you enable XA transactions with MSDTC ?
Yes, I enabled XA transactions and everything else.
What puzzles me is that SQL authentication makes everything work like a charm. I downloaded a newer version of the provider, let's see what happens.
I'll keep you updated.
-- Gianluca Sartori
February 9, 2011 at 9:20 am
Hi everyone.
Here's how things evolved meanwhile:
1) It's not a distributed transaction issue. It was initially, then I configured MSDTC correctly and it started giving the same error for a different problem.
2) Updates don't work, but only for OPENQUERY syntax. 4-part name syntax works, EXECUTE ... AT works.
3) Everything works fine when the users that issues the statement is part of the Administrators group.
We contacted the provider manufacturer, but they're not able (or willing) to help. They say it's a permission issue on the operating system side (quite true) and we should call Microsoft.
Microsoft controlled our server configuration and they say everything is set up correctly. They're analyzing a dump file for DllHost.exe and we hope they will find where the problem lies.
If I ever get out of this situation, I promise I will publish a blog post to sum up all the information I collected during this troubleshooting.
-- Gianluca Sartori
February 9, 2011 at 12:16 pm
that auth remark rings a bell.
If I recall correct our DB2 (udb gateway for z/os) admins granted read on the IBM folder (and subsequent folders) to authenticated users.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 10, 2011 at 12:56 am
ALZDBA (2/9/2011)
that auth remark rings a bell.If I recall correct our DB2 (udb gateway for z/os) admins granted read on the IBM folder (and subsequent folders) to authenticated users.
Interesting note, Johan. Which provider are you using?
-- Gianluca Sartori
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply