Linked Server failure for IBM OLE DB Providers on SQLServer2005 X64 Cluster

I've Created Linked servers to IBM AS400 machines using the IBMDA400 and IBMSQL400 OleDb providers. If I failover, or take off-line and bring back on-line the SQL Resource Group, I get this Error:

Msg 7340, Level 16, State 4, Line 1
Cannot create a column accessor for OLE DB provider "IBMDA400" for linked server "ATCOAIRC".

Solution:

- Browse in the Management Studio to the linked Server, request the properties, and close this window again everytime SQLServer Service starts ;-)

Or, if you don't want to do this manually - Duhhh...

- USE master;
GO

EXEC sp_configure 'show advanced option', '1';
RECONFIGURE

EXEC sp_configure 'scan for startup procs', '1';
RECONFIGURE
GO

CREATE PROC usp_enum_oledb_providers
AS

exec sp_enum_oledb_providers

GO

exec sp_procoption N'usp_enum_oledb_providers', 'startup', 'on'


Note: I didn't have this problem with SQLServer or Oracle Linked Servers.

IBM Ole DB error in SQLServer 2005 on x64

Error:

Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "IBMDA400" for linked server "(null)" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 2
Cannot get the column information from OLE DB provider "IBMDA400" for linked server "(null)".

Or

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
--> An exception occured while executing a Transact statement or batch.
--> Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB Provider "IBMDA400" for linked server.



Solution:

1) Make sure on your SQL Server 2005 x64 (64 bit) machine that "Allow inprocess" is checked for the linked server provider (IBMDASQL in my case).
--> In SQL Mgmt Studio, connect to the SQL Server Database Engine and go to Server Objects->Linked Server->Providers in the Object Explorer.
--> Right-click on IBMDASQL and select Properties. Check the "Allow inprocess" box in the Provider Options dialog that comes up.

2) Enable Ole Automation Procedures;

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

fn_my_permissions

Returns a list of the permissions effectively granted to the principal on a securable.

Examples:

A. Listing effective permissions on the server
The following example returns a list of the effective permissions of the caller on the server.

SELECT * FROM fn_my_permissions(NULL, 'SERVER');
GO

B. Listing effective permissions on the database
The following example returns a list of the effective permissions of the caller on the AdventureWorks database.

USE AdventureWorks;
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
GO

C. Listing effective permissions on a view
The following example returns a list of the effective permissions of the caller on the vIndividualCustomer view in the Sales schema of the AdventureWorks database.

USE AdventureWorks;
SELECT * FROM fn_my_permissions('Sales.vIndividualCustomer', 'OBJECT')
ORDER BY subentity_name, permission_name ;
GO

D. Listing effective permissions of another user
The following example returns a list of the effective permissions of database user Wanida on the Employee table in the HumanResources schema of the AdventureWorks database. The caller requires IMPERSONATE permission on user Wanida.

EXECUTE AS USER = 'Wanida';
SELECT * FROM fn_my_permissions('HumanResources.Employee', 'OBJECT')
ORDER BY subentity_name, permission_name ;
REVERT;
GO

E. Listing effective permissions on a certificate
The following example returns a list of the effective permissions of the caller on a certificate named Shipping47 in the current database.

SELECT * FROM fn_my_permissions('Shipping47', 'CERTIFICATE');
GO

F. Listing effective permissions on an XML Schema Collection
The following example returns a list of the effective permissions of the caller on an XML Schema Collection named ProductDescriptionSchemaCollection in the AdventureWorks database.

USE AdventureWorks;
SELECT * FROM fn_my_permissions('ProductDescriptionSchemaCollection',
'XML SCHEMA COLLECTION');
GO

G. Listing effective permissions on a database user
The following example returns a list of the effective permissions of the caller on a user named MalikAr in the current database.

SELECT * FROM fn_my_permissions('MalikAr', 'USER');
GO

H. Listing effective permissions of another login
The following example returns a list of the effective permissions of SQL Server login WanidaBenshoof on the Employee table in the HumanResources schema of the AdventureWorks database. The caller requires IMPERSONATE permission on SQL Server login WanidaBenshoof.

EXECUTE AS LOGIN = 'WanidaBenshoof';
SELECT * FROM fn_my_permissions('AdventureWorks.HumanResources.Employee', 'OBJECT')
ORDER BY subentity_name, permission_name ;
REVERT;
GO

Windows Vista and Office 2007 Compatibility

SQL Server Service Pack 2 or greater is needed to be fully compatible with Windows Vista and Microsoft Office 2007.