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

Question

We created one Linked Server inside SQL server 2012R2, which is used to connect to another non SQL database installed on another Windows 2012R2 server, the Linked Server can be tested successfully when using Windows account connect to SQL Server, while it always failed when using SQL login account connect to the SQL Server. The error message is:

The OLE DB provider "MSDASQL" for linked server "IP21" reported an error. The provider did not give any information about the error.
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "IP21". (Microsoft SQL Server, Error: 7399)

When we tried to create one "Local server login to remote server login mapping" or use the alternative options at the bottom of the Security window, the attempt all failed with the above error raised.

However, the similar Linked server works fine on another SQL server, not sure why.

Anybody met with this similar problem before? Or do you have any idea that can help shoot the problem?

Answer 1

Hi @Li, Treen ,

Anybody met with this similar problem before? Or do you have any idea that can help shoot the problem?

I have not met this similar problem. However, I have some ideas for it.

For SQL Server Authentication, a login with the exact same name and password must exist on the remote server. It means you need to create the same user name and password on your two servers if you use one sql server authentication.

So test on my side : create linked server in different server with SQL Server Authentication. (I restarted all the services on these two servers with same account doamin\Administrator)

--on server CLIENT\SQL2017DB  
  
select * from test_dac.dbo.T_Test  
  
use test_dac  
go  
--1.create new login  
CREATE LOGIN test0924 WITH PASSWORD = 'Password01!';  
--2.create a new user in database test  
use test_dac  
go  
  
create user test0924 for login test0924  
  
GRANT select ON DATABASE::test_dac TO test0924;  
GO  

 --on server VERSIONTEST\SQLLOGIN2017  
    use master  
    go  
    --1.create new login  
    CREATE LOGIN test0924 WITH PASSWORD = 'Password01!';  
    --2.create a new user in database test  
    create user test0924 for login test0924  
--create link server by ssms or t_sql  

--3.check   
select * from TESTLINK.test_dac.dbo.T_Test  

More information: create-linked-servers-sql-server-database-engine

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

Hi @Li, Treen ,

Do you notice this in this doc. : create-linked-servers-sql-server-database-engine
On the Security page, specify the security context that will be used when the original SQL Server connects to the linked server.

In a domain environment where users are connecting by using their domain logins, selecting Be made using the login's current security context is often the best choice.

When users connect to the original SQL Server by using a SQL Server login, the best choice is often to select By using this security context, and then providing the necessary credentials to authenticate at the linked server.

So for your case, if you want to use one Windows Account, please chooest option: Be made using the login's current security context

test on my side:
windowsaccount:

sqlaccount

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 3

Hi @Li, Treen ,

Is the reply helpful?

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.