servers to UDB via IBMDADB2, access denied

A linked server definition in MSSQL 2000, can succcessfully authenticates
with a UDB database v8.1.0.61 on a SUN box, but then gets access denied,
eventhough the account being used has DBADM authority. However, I can
successfully authenticate and query the same UDB databases from a DOS DB2
command prompt on the same windows 2003 server.
See example below and results:
On the windoes 2003 server...
set nocount on
EXEC sp_addlinkedserver
@.server = 'srvr1833',
@.srvproduct = 'aeplm148',
@.provider = 'IBMDADB2',
@.datasrc = 'aeplm148',
@.location = 'srvr1833.dbms.gdomain.com:18330',
@.catalog = 'aeplm148'
go
EXEC sp_addlinkedsrvlogin
@.rmtsrvname = 'srvr1833',
@.useself = false,
@.rmtuser = 'User1',
@.rmtpassword = 'Password1'
go
EXEC sp_serveroption
@.server = 'srvr1833',
@.optname = 'data access',
@.optvalue = 'true'
go
SELECT *
FROM srvr1833.aepcclm148.epl.mline
Results:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'IBMDADB2' reported an error. Access denied.
OLE DB error trace [OLE/DB Provider 'IBMDADB2' IUnknown::QueryInterface
returned 0x80070005: Access denied.].
Yet on a DOS command line using db2...
db2 => connect to aeplm148 user User1 using Password1
Database Connection Information
Database server = DB2/SUN 8.1.6
SQL authorization ID = DGIEPL
Local database alias = AEPLM148
db2 => list database directory
System Database Directory
Number of entries in the directory = 1
Database 1 entry:
Database alias = AEPLM148
Database name = AEPLM148
Node name = XX
Database release level = a.00
Comment =
Directory entry type = Remote
Catalog database partition number = -1
Alternate server hostname =
Alternate server port number =
db2 => list node directory show detail
Node Directory
Number of entries in the directory = 1
Node 1 entry:
Node name = XX
Comment =
Directory entry type = LOCAL
Protocol = TCPIP
Hostname = srvr1833.dbms.gdomain.com
Service name = 18330
Remote instance name =
System =
Operating system type = None
db2 => select * from aeplm148.epl.mline fetch first 2 rows only
I_PLANT_PL I_STDY I_PRES_LINE I_PRES_TYP N_LINE_NAME
L_PLAN I_LOAD_TYP I_AUTOM_TYP I_PRES_LINE_SIZE I_PRES_MECHNM_TYP
L_
TCH_PL_LINE_TO_PLT_LINE Q_SHUT_HGT Q_NMBR_OF_STATNS I_UPD_TID D_UPD_LAST
X_COMN
More results followed but not pasted...
Can anybody offer some suggestions?
I've run out of ideas.
I know the linked server is authenticating properly because when I put in a
bad password, the error message returned says, bad password or userid.
One would assume that the problem is on the UDB server only. Yet I can
access the data from the Windows 2003 server using the UDB client in a DOS
prompt.
Both techniques authenticate properly. Only the linked server gets access
denied.
The driver version on the Windows 2003 Server is DB2 v8.1.12.99A clue was found.
This will work when you remote desktop to the server and log in as either
the administrators account to the account in which the database engine is
running under. But it will not work with any other account, even if that
account is part of the administrators group.
What is different about the "Administrator" account from an a account that
is simply a member of the Administor"s" group?
The account used by the sqlserver engine is also part of the administrators
group.
"DBA449" wrote:

> A linked server definition in MSSQL 2000, can succcessfully authenticates
> with a UDB database v8.1.0.61 on a SUN box, but then gets access denied,
> eventhough the account being used has DBADM authority. However, I can
> successfully authenticate and query the same UDB databases from a DOS DB2
> command prompt on the same windows 2003 server.
> See example below and results:
> On the windoes 2003 server...
> set nocount on
> EXEC sp_addlinkedserver
> @.server = 'srvr1833',
> @.srvproduct = 'aeplm148',
> @.provider = 'IBMDADB2',
> @.datasrc = 'aeplm148',
> @.location = 'srvr1833.dbms.gdomain.com:18330',
> @.catalog = 'aeplm148'
> go
> EXEC sp_addlinkedsrvlogin
> @.rmtsrvname = 'srvr1833',
> @.useself = false,
> @.rmtuser = 'User1',
> @.rmtpassword = 'Password1'
> go
> EXEC sp_serveroption
> @.server = 'srvr1833',
> @.optname = 'data access',
> @.optvalue = 'true'
> go
> SELECT *
> FROM srvr1833.aepcclm148.epl.mline
> Results:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'IBMDADB2' reported an error. Access denied.
> OLE DB error trace [OLE/DB Provider 'IBMDADB2' IUnknown::QueryInterfac
e
> returned 0x80070005: Access denied.].
>
> Yet on a DOS command line using db2...
>
> DB2 => connect to aeplm148 user User1 using Password1
> Database Connection Information
> Database server = DB2/SUN 8.1.6
> SQL authorization ID = DGIEPL
> Local database alias = AEPLM148
> DB2 => list database directory
> System Database Directory
> Number of entries in the directory = 1
> Database 1 entry:
> Database alias = AEPLM148
> Database name = AEPLM148
> Node name = XX
> Database release level = a.00
> Comment =
> Directory entry type = Remote
> Catalog database partition number = -1
> Alternate server hostname =
> Alternate server port number =
> DB2 => list node directory show detail
> Node Directory
> Number of entries in the directory = 1
> Node 1 entry:
> Node name = XX
> Comment =
> Directory entry type = LOCAL
> Protocol = TCPIP
> Hostname = srvr1833.dbms.gdomain.com
> Service name = 18330
> Remote instance name =
> System =
> Operating system type = None
> DB2 => select * from aeplm148.epl.mline fetch first 2 rows only
> I_PLANT_PL I_STDY I_PRES_LINE I_PRES_TYP N_LINE_NAME
> L_PLAN I_LOAD_TYP I_AUTOM_TYP I_PRES_LINE_SIZE I_PRES_MECHNM_T
YP
> L_
> TCH_PL_LINE_TO_PLT_LINE Q_SHUT_HGT Q_NMBR_OF_STATNS I_UPD_TID D_UPD_LAST
> X_COMN
> More results followed but not pasted...
> Can anybody offer some suggestions?
> I've run out of ideas.
> I know the linked server is authenticating properly because when I put in
a
> bad password, the error message returned says, bad password or userid.
> One would assume that the problem is on the UDB server only. Yet I can
> access the data from the Windows 2003 server using the UDB client in a DOS
> prompt.
> Both techniques authenticate properly. Only the linked server gets access
> denied.
> The driver version on the Windows 2003 Server is DB2 v8.1.12.99
>sql

server with IBM DB2 UDB for iSeries IDMDASQL OLE Provider Errors

Hello Everyone

I’m having problems retrieving data from IBM DB2 AS/400 linked server

I have managed to connect a linked server using the IBM DB2 UDB for iSeries "IBMDASQL" OLE DB Provider.

But when I run a query to the database I get only the field names and the following errors:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "IBMDASQL" for linked server "iseries" reported an error. Access denied.

Msg 7301, Level 16, State 2, Line 1

Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB provider "IBMDASQL" for linked server "iseries".

My Server is Standard 64bit edition on cluster with CTP 2 installed .

I don’t have the right to use Microsoft OLE DB provider …..?

Thank you.

Firstly check to correct the Access Denied error and ensure the user you are using to COnnect to IBM DB2 should have access.

Also check this http://www-128.ibm.com/developerworks/forums/dw_thread.jsp?forum=292&thread=143817&cat=5 link on IBM forums.

|||

Hello Satya

Thank you for your time.

I had my DB2 user tested with a non cluster 32bit installation.

I already posted my case to mentioned IBM link.

My problem is that I can’t use a Local system account for my 64bit clustered environment.

After executing a simple select query against the linked server I get an empty result set at the result tab with all table fields included and the errors

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "IBMDASQL" for linked server "GRATHD1" reported an error. Access denied.

Msg 7301, Level 16, State 2, Line 1

Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB provider "IBMDASQL" for linked server "GRATHD1".

at the messages tab.

Thank you

|||

I would like to know how you setup the link. I have tried repeatedly to get a link for AS/400 to work, but can't seem to find the proper combination of parameters. Searching the internet has provided no help to me.

Thanks

|||

Hello Lee

I'm posting the script produced by my linked server

Notice that if you set the SQL Server service account to "local system" the linked server works.

/****** Object: LinkedServer [GRATHD1] Script Date: 01/30/2007 11:15:54 ******/
EXEC master.dbo.sp_addlinkedserver @.server = N'GRATHD1', @.srvproduct=N'IBM DB2 UDB for GRATHD1', @.provider=N'IBMDASQL', @.datasrc=N'GRATHD1'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname=N'GRATHD1',@.useself=N'False',@.locallogin=NULL,@.rmtuser=N'as400username',@.rmtpassword='########'
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname=N'GRATHD1',@.useself=N'False',@.locallogin=N'DomainName\UserName',@.rmtuser=N'AS400username',@.rmtpassword='########'

GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'collation compatible', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'data access', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'dist', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'pub', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'rpc', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'rpc out', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'sub', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'connect timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'collation name', @.optvalue=null
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'lazy schema validation', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'query timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'use remote collation', @.optvalue=N'true'

|||

you musto add the options

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'Ole Automation Procedures', 1;

GO

RECONFIGURE;

GO

|||

PS to the Microsoft people

When the server is running under the local system account and not a domain user this option is not necessary.

server to UDB

I'm trying to set up a linked server from sql server 2000 to udb 8.1.14. My selects to the UDB table work fine. Inserts/updates deletes fail. The insert returns - OLE DB error trace [OLE/DB Provider 'IBMDADB2' IRowsetChange::InsertRow returned 0x80040e53: The provider does not support the necessary method.]. Here's the insert format - INSERT OPENQUERY(TESTUDB3, 'SELECT * FROM TEST.T1') VALUES( '1','1','1','1','1'). Is this the correct format? Does the IBM provider allows updates?What happens when you try

insert into TESTUDB3.TEST.T1
VALUES ( '1','1','1','1','1')|||Same error.