MSDASQL - Unexpected Failure when Connecting ODBC Driver

Question

I was trying to link my localhost SQL server to a remote MySQL server.

I have configured a ODBC connection to the remote server and tested that the connection is successful.

However, when I try to link the ODBC to my local server, the linked server failed the connection test for unknown reasons.

It is important to note that I have tried to do the exact same things on two of my colleagues' computers, and the connection worked on the two computers.

Answer 1

Seems like you are trying to use a DSN. That has to be a system DSN for things to work. If it ever works with a DSN - as I am not a friend of DSNs, I've never tested that.

I did this: I went to https://dev.mysql.com/downloads/connector/odbc/ and downloaded the 64-bit version of the driver and installed it. After some experimenting I was able to run this successfully against my Azure Flexible MySQL Server:

EXEC sp_addlinkedserver MYSQL, '', 'MSDASQL', 
      @provstr = 'Driver={MySQL ODBC 8.0 Unicode Driver};User=xyzxyz;Password=TopSecret;Server=zzzzz.mysql.database.azure.com;Database=slasketti'
go
SELECT * FROM MYSQL.slasketti..nisselille
go
SELECT * FROM   OPENQUERY(MYSQL, 'SELECT * FROM INFORMATION_SCHEMA.COLLATIONS')
go
EXEC sp_dropserver MYSQL

Answer 2

Hi @Chiu Tung Lau

Please check that the steps to create a linked server to MySQL from SQL Server are correct: https://www.sqlshack.com/how-to-create-and-configure-a-linked-server-to-connect-to-mysql-in-sql-server-management-studio/.

Also, I found a thread with a similar error to yours that maybe you can use as a reference: https://learn.microsoft.com/en-us/answers/questions/105916/cannot-initialize-the-data-source-object-of-ole-db.

Best regards,

Aniya

Answer 3

Hi Chiu Tung Lau •,

Welcome to Microsoft Q&A forum and thanks for using Azure Services.

As I understand, you are getting when connecting local SQL Server with MySQL Server.

It works for others but for you it is going into error.

Could you please check k the connection settings, including the server name, port number, and authentication credentials if they correctly entered.

Check Firewall is not blocking connection to server.

Also try below if helps:

In the Microsoft SQL Server studio Navigate to Server Objects > Linked Server > Providers and double-click MicrosoftOLE DB Provider for ODBC drivers.

Check 'Allow inprocess' ( Check the box) SaveTry to check permissions too.

Check permissions too as below:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'(my_host)' IDENTIFIED BY '(my_password)';

Reset the mysql permissions cache, FLUSH PRIVILEGES.

Let us know if these options suggested above helped. If not, we can further investigate. Thank you