Kousik Chatterjee's Blog

Q. What is Linked Server?
Ans: Linked Servers allows you to connect to other database instances on the same server or on another machine or remote servers. It allows SQL Server to execute SQL scripts against OLE DB data sources on remote servers using OLE DB providers. The remote servers can be SQL Server, Oracle, MySQL etc. which means those databases that support OLE DB can be used for linking servers.

Scenario:

MySQL Server IP: 192.168.72.220
-- DB_Name: reportsdb
-- DB_User: reports
-- DB_Pass: redhat

Windows Server 2012 R2 IP: 192.168.72.226
-- Install MSSQL Server 2012 with SSMS
-- Download & Install ODBC Driver for MySQL to Connect from SQL Server.
 Link: http://cdn.mysql.com//Downloads/Connector-ODBC/5.3/mysql-connector-odbc-5.3.4-winx64.msi
  
Step: 1. Create Database & User on MySQL Server (192.168.72.220):

mysql> create database reportsdb;
mysql> grant all privileges on reportsdb.* to reports@'localhost' identified by 'redhat';
mysql> grant all privileges on reportsdb.* to reports@'%' identified by 'redhat';
mysql> flush privileges;

mysql> use reportsdb;
mysql> create table report1(name char(20));
mysql> insert into report1 values('koushik');
mysql> insert into report1 values('koushik1');

mysql> show tables;
+-------------------+
| Tables_in_report1  |
+-------------------+
| reports1                 |
+-------------------+
1 row in set (0.00 sec)

mysql> select * from reports1;

+-----------+
| name         |
+-----------+
| koushik1    |
| koushik      |
+-----------+
2 rows in set (0.00 sec)

mysql> flush privileges;
mysql> exit


Step: 2. Download & Install ODBC Driver for MySQL to Connect from SQL Server :

Link: https://dev.mysql.com/downloads/connector/odbc/5.1.html

For Windows Server 2008/2012 :
Link: http://cdn.mysql.com//Downloads/Connector-ODBC/5.3/mysql-connector-odbc-5.3.4-winx64.msi

Step 3. MySQL ODBC Connector Configuration :

Open Run --> odbcad32.exe --> System DNS (tab) & Click on Add -> Select MySQL ODBC 5.3 Driver --> Finish -> On MYSQL Connector Provide the Details of the Database that you are Trying to Connect.

Data Source Name: REPORTSMYSQL
DESCRIPTION: REPORTSMYSQL
TCP/IP SERVER: 192.168.72.220
USER: reposts
Password: redhat
Database: reportsdb

-- Test. (If Everything is Good it'll Showing Message: "Connection Success") --> Click "OK".
--> Click "OK" --> Again Click "OK"

Note: MySQL ODBC Connector Configuration has been done.

Method 1 :

Step: 3. Configure Linked Server :

-- Run the Following Query in SQL Server (SSMS). Change the Following Field :

1. DRIVER, 2.SERVER, 3.DATABASE, 4.USER, & 5. PASSWORD.

/****** Object: LinkedServer [REPORTSMYSQL] Script Date: 02/08/2016 16:34:21 ******/

EXEC master.dbo.sp_addlinkedserver @server = N'REPORTSMYSQL', @srvproduct=N'REPORTSMYSQL', @provider=N'MSDASQL', @provstr=N'DRIVER={MySQL ODBC 5.3 Driver}; SERVER=192.168.72.220;DATABASE=reportsdb; USER=reports; PASSWORD=redhat;option=3'

/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'REPORTSMYSQL',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,
@rmtpassword=NULL
GO

EXEC master.dbo.sp_serveroption @server=N'REPORTSMYSQL', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'REPORTSMYSQL', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'REPORTSMYSQL', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'REPORTSMYSQL', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'REPORTSMYSQL', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'REPORTSMYSQL', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'REPORTSMYSQL', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'REPORTSMYSQL', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'REPORTSMYSQL', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'REPORTSMYSQL', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'REPORTSMYSQL', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'REPORTSMYSQL', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'REPORTSMYSQL', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

-- Execute.

or Method 2 :

-- Login to SSMS Console.
-- Expand "Server Object" -->  Right Click on "Linked Servers" --> Click on "New Linked Server" --> Then Do the Following (For Example) :

Linked Server: REPORTSMYSQL
Server Type: Other Data Source
Provider: Microsoft OLE DB Provider for ODBC Drivers
Product Name: REPORTSMYSQL
Data Source: REPORTSMYSQL
Provider String: DRIVER={MySQL ODBC 5.3 Driver};SERVER=192.168.72.220;PORT=3306;DATABASE=reportsdb; USER=reports; PASSWORD=redhat;option=3;

--> Click on "OK"

Step: 4. Verify the Connection :

Connect to SSMS --> Server Objects --> Linked Server --> TESTMYSQL.

Thanks For Visiting on My Blog, For More Tutorials Keep Visiting My Blog