Ibmda400 Ole Db Provider
When creating a Linked Server to our AS400 using the IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider, we are running into an issue.When trying to browse table or run queries using a Domain Admin account, there is no issue. When running it under my account (also a Domain Admin) I am getting: Error 7399: OLE DB provider 'IBMDA400' reported an error. Access denied.The accounts used are also local administrators on the SQL 2000 server and assigned System Administrator role in the SQL Login Properties.We were using an ODBC connection to perform functions such as select queries without any problems, but when we tried to start updating tables from an ASP page, we began having problems. Some tables would update and others returned errors.I do not care which we use, so if anyone can provide some insight as to how to correct either issue it would be appreciated.Thanks. It is a valid AS400 login and it doesn't make sense that one account can perform the task, but others with all the same security both network, local machine, and SQL cannot.Here is the error when trying to execute a query using my login:Server: Msg 7399, Level 16, State 1, Line 1OLE DB provider 'IBMDA400' reported an error. Access denied.OLE DB error trace OLE/DB Provider 'IBMDA400' IUnknown::QueryInterface returned 0x80070005: Access denied.When I use the actual Domain Admin account, I cannot view the tables, but can run queries and get results.We as I mentioned before, have a linked server using an ODBC connection to the same AS400 using the same account and have no problems running select queries from any account, but ran into problems when trying to update a couple of tables on the AS400.Thanks.
I understand totally. I am not the DBA nor am I an AS400 person. I am the LAN Admin and got in the middle of this because it does appear to be security and I also agree that it is on the AS400 side, but since we are using the same AS400 account to access it with all the Windows accounts, it doesn't make sense that one Domain Admin account can perform the task and two others cannot since they are set up identically on the SQL server.I will look into the link you sent to see if I can seea nything there.Thanks for your help.I may be back.
Resolving The ProblemThis document describes a resolution to an 'Error 7302 Could not create an instance of OLE DB provider IBMDASQL' being received when attempting to create a linked server in SQL Server.ResolutionWhen using Microsoft SQL Server and distributed queries with the IBM OLE DB Providers supplied with iSeries Access for Windows, the Allow InProcess option must be enabled. This option is required because SQL Server passes the proper authentication across the remote procedure call only when the OLE DB Provider is configured for Allow InProcess (Microsoft Corporation 2007).Allow InProcessSQL Server allows the OLE DB provider to be instantiated as an in-process server. The default behavior is to instantiate the OLE DB provider outside the SQL Server process. Instantiating the provider outside the SQL Server process protects the SQL Server process from errors in the OLE DB provider. SQL Server requires an in-process server for handling specific types of data including long columns, text, and image data.
The OLE DB Provider for DB2 does not currently support the DB2 Large Object (LOB) types.This option may be enabled or disabled for the OLE DB Provider for DB2; however, this option is normally unnecessary when using SQL Server 2000.' (Microsoft Corporation 2007)Setting the Allow InProcess Provider OptionSQL Server 2000When creating a new linked server (under Microsoft SQL Servers - SQL Server Group - SQL Server Database - Security - Linked Servers - Right-click on Linked Servers, and select New Linked Server), select the Provider Options button below the OLE DB provider name.
Ibmda400 Ole Db Provider Connection String
Check the Allow InProcess option to enable the property.SQL Server 2005The Allow InProcess option must be set on the specific provider before the linked server is created. You can add the linked server provider option under SQL Server Database - Server Objects - Linked Servers - Providers - Right-click on a provider, and select Properties.
Finally, check the Allow InProcess option to enable the property.References'Distributed Query Support Using the OLE DB Provider for DB2.' Microsoft Corporation.
24 April 2007.//msdn2.microsoft.com/en-us/library/ms943674.aspx.