question

LouisDG-1517 avatar image
0 Votes"
LouisDG-1517 asked ErlandSommarskog answered

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

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-general
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

What is on the other end? That is, what data source are you accessing? Which ODBC driver?

What more exactly does "I've set up the proper credential mapping (windows authentication) for the appropriate users." mean? Which commands did you run where?

0 Votes 0 ·

The linked server is towards a hana CF server (HDBODBC driver). The users have been specifically mapped in the security settings of the linked server to use the proper credentials. As mentioned, the linked server works fine on the machine where it's defined.

0 Votes 0 ·

You will have to bear with me: I am completely unfamiliar with Hana CF and HDBODBC.

The users have been specifically mapped in the security settings of the linked server to use the proper credentials.

So this was done with sp_addlinkedsrvlogin? Can you show an example? Please don't show a screenshot, but use the Script button up to the left to script the commands for the linked server. Replace all sensitive names with dummy names.

As mentioned, the linked server works fine on the machine where it's defined.

So if users log on directly to the machine where SQL Server runs, they can run queries? But if they connect with SSMS (or whatever) from a different machine and connect to SQL Server, they get this error?

Sorry for asking questions, but I want to be sure that I understand exactly what the configuration is.




0 Votes 0 ·
Show more comments
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered LouisDG-1517 commented

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.




· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Cathy,

Thanks for your reply. You can find error message below:

47941-image.png

Indeed, exactly as your screenshot. See script for linked server creation below:


47933-image.png


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.


0 Votes 0 ·
image.png (97.6 KiB)
image.png (454.1 KiB)

Hi @LouisDG_1517,

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 still have some confuse about this statement. Did you mean if you want to use NOMIAN\user1 ( on server 1) query link server( on server 2) it will work only when NOMIAN\user1 login is created on server 2? Could you please description this more clearly, thanks for your understanding.

Best regards,
Cathy

0 Votes 0 ·

Hi Cathy,

  • Machine A: Define linked server connection to machine C

  • Machine B: Log on to the SQL server that's on machine A (via SSMS)

=> If user is logged in on machine A -> linked server works on machine B
=> If user is not logged in on machine A -> linked server does not work anymore on machine B

(So you really need to be logged on to both machines for the linked server to work on a machine other than the one where we defined the linked server).

Thanks!

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered LouisDG-1517 commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

It's a different server, where we connect to the SQL server using SSIS (for instance). And another server, where we host an application. So I think you understand everything perfectly, it's just a strange situation.

Yes, indeed, that's what I would expect too... That's why we're very confused.


0 Votes 0 ·
DavidBrowne-9516 avatar image
0 Votes"
DavidBrowne-9516 answered

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.