none
Cannot create an instance of OLE DB provider MSDASQL for linked server

    Question

  •  

    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"

    ErrorCode=-2146232060

    Class=16

    LineNumber=1

    Number=7302

    Procedure=""

    Server="LGMDEV01"

    State=1

    StackTrace:

    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)

    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

    at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()

    at System.Data.SqlClient.SqlDataReader.get_MetaData()

    at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

    at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)

    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)

    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

    at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

    at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)

    at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)

    at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

    at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

    at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)

    at ForecastService.GetJobPeriods(Guid TransactionID, String JobCode, NetworkCredential JdeCredentials) in c:\ForecastingService\App_Code\ForecastService.cs:line 657

    Monday, September 22, 2008 8:24 PM

All replies

  • 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.

     

    Check this out: http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx

     

    It is recommended to use SQL Server authentication for the linked server to nail down all requests to a specific user with read-only permissions for specific tables.
    Wednesday, September 24, 2008 5:37 PM