SSMS error - Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server

Question

Hi Cathy,

I have a very similar problem as LouisDG_1517 :

Machine A: Define linked server connection to machine/cluster C

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

      Option 1. If user is logged in on machine A -> linked server works on machine B in accessing Hive DB running on Cluster C  

      Option 2. If user is not logged in on machine A -> linked server does not work anymore on machine B  

For me, it does not matter whether user logged in into both A or B,

The other new thing in my case is , Machine C has kerberos secured Hive DB and I did setup MIT kerberos on Machine A

Thanks!

Answer 1

Hi @shashi das ,

I think your login account is not ##MS_policyEventProcess. Such as below screenshot in my environment, I using contoso\administrator account to connect to SQL server, then i choose the contoso\administrator account as below screenshot. Please check your environment again.

This scenario, in which one computer connects to another computer to connect to a third computer, is called a double hop. Did you configure security account delegation for your login account? Please refer to the MS document Configuring Linked Servers for Delegation to get more detail steps.

---

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

Answer 2

I think this is more an issue with the Cloudera driver. I have no idea what the "security package" may be, but it certainly is nothing in SQL Server.

I would recommend that you first start to get an ODBC application to connect to Cloudera. (You could use the Odbc provider in .NET for instance.) Next step would be to use ab OLE DB application so that you go through MSDASQL. (OleDb Client in .NET can be used for this.)

When you have this going, you can try SQL Server. Or trying running these applications from xp_cmdshell.

Debuging a linked server like this is just too many parts at the same time.

Answer 3

Hi @CathyJi-MSFT

Thanks for your response.

I realize little mistake in my original post. I am clarifying both the options again :

Option 1. If user is logged in on machine A -> SSMS tool using the linked server works well in accessing Hive DB, running on Cluster C

Option 2. If user is logged in on machine B -> linked server does not work anymore on machine B

Answering your questions:

Q1. Connecting to server A on server B, then using linked server to query data from server C ? The process failed and cause above error?
My Ans - YES, absolutely right. that was the error

Q2. Which login account that you are using .....
My Ans - I use my_user_id to login both server A and Server B. Please find the attachment for the security setting of linked server

Q3. Did you mean the connection about SQL server are using Kerberous authentication?
My Ans - YES. I am trying to access Hive DB of a kerberized cluster C. As per option 1, it works well.

Answer 4

Hi @CathyJi-MSFT

Thanks for pinpointing the security area. I tried changing to my_domain\my_user_id as well as domain\service_id but it ONLY works on the server machine A and never works on Machine B.

Other information is, with the default security settings, I am able to query non hadoop DBs (Say Oracle) in Machine B using a linked server defined on Server A.

So the extra thing in my current problem is the kerberos security, the service_id.KEYTAB file and the service_id@PRINCIPAL

Answer 5

Any more suggestions @CathyJi-MSFT