Connecting to Sphinx from sql server linked server

Question

I am trying to connect to Sphinx from SQL Server Management Studio as a Linked server. I tried the following query:

EXEC master.dbo.sp_addlinkedserver @server=N'SPHINX_SEARCH', @srvproduct=N'', @provider=N'MSDASQL', @provstr=N'Driver={MySQL ODBC 8.0 ANSI Driver};Server=127.0.0.1;Port=9306,charset=UTF8;User=;Password=;OPTION=3'
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SPHINX', @useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO

And i am getting the following exception:

Title: Microsoft SQL Server Management Studio

The test connection to the linked server failed.

Additional information

The OLE DB providcer "MSDASQL" for linked server "SPHINX_SEARCH" 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 "SPHINX_SEARCH". (Microsoft SQL Server, Error: 7399).

While connecting to Sphinx from MySQL is working via the following command:

mysql -h 127.0.0.1 -P 9306

Any suggestions?

---

Sphinx version: 3.0.3, SQL server version: 2014, MySQL version: 5.6

Isomerous

try this: "Be made using login current security context" 3rd option in Linked sever properties and set @useself is to "true" – Moynihan

@Moynihan still not working! – Isomerous

Answer 1

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.

Roxieroxine

There is no system DSN defined in the ODBC administrator, also connection cannot be established to the port 9306 (from ODBC administrator). Only connection can be made using a command prompt console – Isomerous

Note that i can create a System DSN to connect to the mysql engine port (3306), but not for Sphinx (9306) – Isomerous

Unknown MySQL Error – Isomerous

try latest version. There was some bug related to mysql 8 client. – Roxieroxine

The issue was solved after installing the latest Sphinx release 3.1.1. And sorry because the bounty was awarded by the system to the answer before that i tried you suggestion. – Isomerous

Answer 2

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

Olibanum

MSDASQL is installed properly and allow inprocess is already checked. – Isomerous