Connecting to Sphinx from sql server linked server

Tested with Manticore, but should work with Sphinx too:

EXEC master.dbo.sp_addlinkedserver @server = N'Search',
@srvproduct=N'manticore', @provider=N'MSDASQL', @datasrc=N'manticore',
@provstr=N'Driver={MySQL ODBC 8.0 ANSI Driver};
Server=127.0.0.1;Port=9306,charset=UTF8;User=;Password=;'

manticore is System DSN name set in ODBC Administrator.

In sphinx.conf you need to set mysql_version_string to 5.1.1 (or anything greater than 4.1.1) to replace the actual daemon version. Otherwise the ODBC driver will refuse to work with the searchd daemon (because sees server version as being too old).

Update based on comments

The issue was solved after installing the latest Sphinx release 3.1.1. There was some bug related to MySQL client in release 3.0.3.

Did you check in SQL Server Management Studio under "Server Objects > Linked Servers > Providers" if your MSDASQL Provider is listed here and is installed properly?

Also you could try to open the properties for this provider and check the "allow inprocess" option there and check again if you can connect.

You also might want to check this link for further things you can try:

https://support.microsoft.com/en-us/help/2450479/you-get-7399-and-7300-error-messages-when-accessing-a-linked-server