Linked server "Data source name not found and no default driver specified" error

Question

I made a linked server with Microsoft OLE DB Provider for ODBC Drivers:

EXEC master.dbo.sp_addlinkedserver @server = N'TEST', 
                                @srvproduct=N'Microsoft Visual FoxPro Driver', 
                                @provider=N'MSDASQL', 
                                @datasrc=N'TEST'

It worked fine still I used WIndows Authentication Mode, but when I created a login user and switch to SQL AUthentication it did not work.
What changes are needed to work with SQL Authentication?
In providers I have enabled:"Nested queries","Allow inpocess", Level zero only".
The error is:

OLE DB provider "MSDASQL" for linked server "TEST" returned message "[Microsoft][ODBC Driver Manager] Invalid connection string attribute". OLE DB provider "MSDASQL" for linked server "TEST" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "TEST".

Alberto Solano · Accepted Answer · 2013-12-09 13:48:56Z

0

If the connection to the linked server works fine using Windows Authentication, but it doesn't using a SQL Server Authentication, I would have a look at the properties of the linked server and make sure, choosing the tab "Security", that both the authentication modes are enabled:

enter image description here

answered Dec 9, 2013 at 13:48

Alberto Solano

8,2553 gold badges40 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Suraj Singh · Accepted Answer · 2013-12-09 14:03:57Z

0

This error message can occur because of one of these problems.

One of the initialization parameters specified in sp_addlinkedserver (data_source, location, provider_string, or catalog) is incorrect for this provider.

Verify that these parameters defined for the linked server, specified by executing
sp_addlinkedserver, are correct for the given provider.

Check Here also sp_addlinkedsrvlogin
Login and password sent to the provider is invalid.

Verify that there is a valid login and password configured for the linked server and the current SQL Server login through sp_addlinkedsrvlogin.

Go through this Article

UPDATE

During a linked server connection (for example, when it processes a distributed query), the sending server provides a login name and password to connect to the receiving server on its behalf. For this connection to work, you must create a login mapping between the linked servers by using SQL Server system stored procedures. --HERE and HERE

You need to create login mapping for linked server see security for Linked Server

Use sp_addlinkedsrvlogin

Creates or updates a mapping between a login on the local instance of SQL Server and a security account on a remote server.

sp_addlinkedsrvlogin [ @rmtsrvname= ] 'rmtsrvname' 
     [ , [ @useself= ] 'TRUE' | 'FALSE' | 'NULL'] 
     [ , [ @locallogin= ] 'locallogin' ] 
     [ , [ @rmtuser= ] 'rmtuser' ] 
     [ , [ @rmtpassword= ] 'rmtpassword' ]

edited Dec 9, 2013 at 14:03

answered Dec 9, 2013 at 12:22

Suraj Singh

4,0871 gold badge23 silver badges37 bronze badges

2 Comments

Me5 Over a year ago

I think is not a provider problem if it works fine when I switch to Windows authentication mode...or I have to change the provider or something in the linked server connection to work fine in SQL SERVER authentication mode?

Suraj Singh Over a year ago

@Me5 Please check the updated answer, You need to create login mapping.

Collectives™ on Stack Overflow

Linked server "Data source name not found and no default driver specified" error

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related