Not able to create MS SQL Server Linked server

Question

I am trying to create MS SQL Server Linked server to connect web MYSQL .

I have given access to my static IP address in "Remote MYSQL"..

I have choosen following option in MS SQL Server Linked server

Provider: Microsoft OLE DB Provider for ODBC Drivers

Product name: MySQL

Data Source: MySQL ( I don't know what I should give here hence I have given MysQL)

Provider String:

DRIVER={MySQL ODBC 5.5 Driver};SERVER=Shared IP address Mention in CPanel;PORT=3306;DATABASE=database name; USER=user;PASSWORD=password;

When I try to connect using above obtion I am getting error as "Not able to connect"

When executed below command in my CMD telnet "Shared IP address Mention in CPanel" 3306

I am getting following message:

5.5.5-10.0.21-MariaDBm9ï~hoqmMv?á)xF4oU

Kindly help me how to solve this issue?

http://www.ideaexcursion.com/2009/02/25/howto-setup-sql-server-linked-server-to-mysql/ — sandeep rawat, Aug 30 '16 at 13:49
I am getting following error "cannot initialize the data source object of ole db provider msdasql for linked server". — cse_king2, Aug 31 '16 at 06:05
you dns server is not correctly configured ... test your dns or user do not have permission on temp folder — sandeep rawat, Aug 31 '16 at 06:11
http://stackoverflow.com/questions/1362108/cannot-initialize-the-data-source-object-of-ole-db-provider-msdasql-for-linked — sandeep rawat, Aug 31 '16 at 06:13
I have tested my connection in "System DSN" it works fine but only in linked server it showing error. — cse_king2, Aug 31 '16 at 06:42
I have installed latest version "Connector/ODBC 5.1.13" in my client system — cse_king2, Aug 31 '16 at 06:44
have u checked permission on temp folder permission http://stackoverflow.com/questions/1362108/cannot-initialize-the-data-source-object-of-ole-db-provider-msdasql-for-linked — sandeep rawat, Aug 31 '16 at 06:44
I am using Windows 8 system in client meachine for which "temp" folder I need to change permission? — cse_king2, Aug 31 '16 at 06:48
My Temp folder have full access in security Tab. But still I am not able to connect in Linkedserver — cse_king2, Aug 31 '16 at 07:21
I have given full permission to Temp folder still issue not got resolved. I have attached screen shot of folder permission and sent to your email id. — cse_king2, Sep 01 '16 at 05:58
Hi Sandeep, Thanks for your great help. Additionally I have given provider string values like (SERVER=IPaddress;PORT=3306;DATABASE=database_name;USER=database_user;PASSWORD=database_password;" along with product name & source name while creating linked server. Now it works without any problem. — cse_king2, Sep 01 '16 at 06:18

score 0 · Accepted Answer · answered Sep 01 '16 at 06:57

0

Here are step by step to create linked server to mysql .

you have to make sure that sql server service account having full permission on temp location or it should be part of admin group.

answered Sep 01 '16 at 06:57

sandeep rawat

4,797
1
18
36

Not able to create MS SQL Server Linked server

1 Answers1