SQL Server Cannot initialize the data source object of OLE DB provider MSDASQL for linked server

LouisDG 1 Reputation point
2020-12-12T11:53:25.713+00:00

Hi all,

I've created a linked server on SQL machine 1. Connection works fine, querying works fine.
I've set up the proper credential mapping (windows authentication) for the appropriate users.
When trying to use the linked server from different machines, the linked server connection only works when the user is logged in on the original machine. If not, I get the standard error "SQL Server Cannot initialize the data source object of OLE DB provider MSDASQL for linked server".
-> The users are admin on both machines we're trying to connect from, as well as the machine where the linked server is defined.

-> They have the permission "Impersonate a client after authentication" as well as "Create global objects"

-> Running SSMS as admin does not work (and even then, we need to be able to run the query from a frontend client, without SSMS).

What am I missing? Why does this only work when the user is logged in? When we log out again, it stops working and is consistent across different users and machines.

Thanks,

Louis

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,718 questions

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. CathyJi-MSFT 21,096 Reputation points Microsoft Vendor
    2020-12-14T06:09:08.03+00:00

    Hi @LouisDG_1517,

    > SQL Server Cannot initialize the data source object of OLE DB provider MSDASQL for linked server"

    This is a general error message, could you please share us more error message about this issue from SQL server error log. The related error message are around the statement that you offered.

    >I've set up the proper credential mapping (windows authentication) for the appropriate users.

    Did you mean map windows account to remote login( login on linked server) as below screenshot?

    47782-screenshot-2020-12-14-135816.jpg

    If not, please share us the screenshot as I offered above or share us the command that you created linked server.

    You can also check your steps follow below blog and MS document to find if you missing some steps.

    How to create and configure a linked server in SQL Server Management Studio

    To create a linked server by using Transact-SQL

    > the linked server connection only works when the user is logged in on the original machine

    We need to use the account that mapped to the remote login( the login is on linked server) to login in the SQL server instance. Then we can query the linked server.

    Best regards,
    Cathy

    If the response is helpful, please click "Accept Answer" and upvote it, thank you.

  2. Erland Sommarskog 101K Reputation points MVP
    2020-12-14T22:28:41.037+00:00

    Note that the server does work on a different machine for these users, but only when that same user is also logged in on the original machine where we defined the linked server at the same time.

    I am still not sure that I have understood what you are really saying here.

    What exactly is "different machine" here? A different computer that runs a different instance of SQL Server? Or just a client from where the users run an application or SSMS?

    The login mapping you have setup looks pretty normal to me, so it should work no matter whether users are connecting to SQL Server directly from the machine where SQL Server is installed or from a remote computer. However, I am not sure that you are talking about that situation, because you describe it in a way that is a little unusual to me.

  3. David Browne 111 Reputation points Microsoft Employee
    2020-12-14T23:12:22.087+00:00

    What am I missing? Why does this only work when the user is logged in? When we log out again, it stops working and is consistent across different users and machines.

    It's probably caused by the ODBC driver itself. Neither the MSDASQL OleDb provider or SQL Server Linked Server would behave differently based on whether the user is logged on.

    By analogy the Office connectivity components assume that the current user has the HKCU registry hive loaded, which is typically not the case for server applications.

    You might be able to work around this, and improve the stability of your SQL Server by hosting the ODBC driver in a seperate SSIS process. Possibly using the SSIS Streaming Destination.

    0 comments
  4. Erland Sommarskog 101K Reputation points MVP
    2020-12-15T22:17:42.867+00:00

    I don't think I have an answer, but we can note that since the login mapping is set up with a login specific to the data service, it cannot matter for authentication to succeed whether the Windows users is logged in one the server or not. That mapping happens inside SQL Server, and it does not matter if the data source is SQL Server or Hana CF.

    So, as David says, it has to be something with the ODBC driver. Apparently, it can only be loaded if the user is already on the machine. Which could be because of registry access as David suggests.

    Here is a test you could make. Create an SQL Login, call it HanaCFtest. Set up a server mapping for HanaCFTest for this user. Give one of these users IMPERSONATE permission on this login. Then let this user run 

    EXECUTE AS LOGIN = 'HanaCFTest'
go
-- Some operation on the linked server.
go
REVERT

    Try this both locally and remotely.

    Talking with the vendor can of course also be an option.

    0 comments