When I use the "test connection" for my linked server from SQL Server 2005 to DB2 on IBM - the connection works. But when I try to access the Linked Server from the SqlClient - I'm getting the "Create create and instance of OLD DB Provider MSDASQL for linked server" error. Here is the stack trace. Any help on this would be greatly appreciated.
System.Data.SqlClient.SqlException was caught
Message="Cannot create an instance of OLE DB provider \"MSDASQL\" for linked server \"JOBCOST\"."
Source=".Net SqlClient Data Provider"
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
Are you using SSPI authentication for SQL Client -> SQL Server and SQL Server -> Linked SQL Server connections?
If this is the case then you're facing an identity double-hop scenario. Basically, you pass a security conext from the client machine to SQL Server and want it to impersonate this security context to create an instance of another client (MSDASQL) to connect to the linked SQL Server. This is not going to work because double-hops are forbidden by default due to a high security risk.