Sql – Linked server “Data source name not found and no default driver specified” error

linked-serversqlsql server

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".

Best Answer

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

Related Topic