How to create linked server in SQL Server using TSQL ?

What is linked server?
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.

First create a user in mysql which will have permission for atleast select command.

In mysql Server:-
mysql> grant select ON `koopkrachtdb `.* TO 'koopreport'@'192.168.2.100' identified by 'Rghdwf4324Fvxg';
Query OK, 0 rows affected (0.00 sec)



In SQL Server:-
Change the fields accordingly to your server settings.



/****** Object:  LinkedServer [KOOPKRATCHMYSQL]    Script Date: 11/04/2016 08:13:54 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'KOOPKRATCHMYSQL', @srvproduct=N'MySQL', @provider=N'MSDASQL', @provstr=N'DRIVER={MySQL ODBC 5.3 ANSI Driver}; SERVER=52.71.55.125;DATABASE=koopkrachtdb; USER=koopreport; PASSWORD=Rghdwf4324Fvxg;option=3'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'KOOPKRATCHMYSQL',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

GO

EXEC master.dbo.sp_serveroption @server=N'KOOPKRATCHMYSQL', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'KOOPKRATCHMYSQL', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'KOOPKRATCHMYSQL', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'KOOPKRATCHMYSQL', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'KOOPKRATCHMYSQL', @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'KOOPKRATCHMYSQL', @optname=N'rpc out', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'KOOPKRATCHMYSQL', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'KOOPKRATCHMYSQL', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'KOOPKRATCHMYSQL', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'KOOPKRATCHMYSQL', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'KOOPKRATCHMYSQL', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'KOOPKRATCHMYSQL', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'KOOPKRATCHMYSQL', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO