Connect to MySQL database from SQL server

First of all you need to download the MySQL connection / ODBC driver.

The ODBC driver can be download free of charge from the followinglocation:
http://www.mysql.com/products/connector/odbc/

Folow the simple installation instructions.

In Microsoft SQL Server Management Studio – expand the database tree on the left hand side of the window – expand server obects – expand linked servers

Right-click linked servers – click New Linked Server…

Linked server: enter a name for the connection – this can be anything.
Server type: select Other data source
Provider: select Microsoft OLE DB Provider for ODBC Drivers
Product name: This is the name of the MySQL database connecting to

Provider String: This MUST be entered exactly as it appears below, omitting server, database, user and password with your own details.

Driver={MySQL ODBC 3.51 driver}
;Server=SERVER_IP;DataBase=DATABASE;Root=3306;UID=USER;
PWD=PASSWORD

For Example:
Driver={MySQL ODBC 3.51 driver}
;Server=123.456.789.10;DataBase=MY_DATABASE;Root=3306;
UID=ADMIN;PWD=pas32w0rd

Select the Security tab on the right
select Be made using this security cointext towards the bottomof the screen.
Enter a valid user name and password to cennect to the MySAL server.

e.g.
Remote login: Admin
with password: pass32w0rd

Select the Server Options tab on the right
Set the details as follows:

Collation Compatible: FALSE
Data Access: TRUE
Rpc TRUE
Rpc Out TRUE
Use Remote Collation TRUE
Connection Timeout 0
Query Timeout 0

Click OK to instantiate the linked server.

Advertisements