Binaryworld - How to add/configure a new Linked server using T-SQL ... [ SQL Server -> Linked Server ]

Here is the T-SQL script to add linked server using sp_addlinkedserver SP. Once you add linked server you can add login using sp_addlinkedsrvlogin. By default current login is mapped with linked server means remote machine must have exact same login with same password in order to get access to the linked server but this is not the case most of time so you need step [3].

Click here to copy the following block

USE MASTER
GO

--////////////////////////////////
--//[1] Create a linked server
--////////////////////////////////
Exec sp_addlinkedserver
@server='webdb', --//Logical name given to the linked server.
@srvproduct='', --//optional . Just for description
@provider='SQLOLEDB', --//OLEDB Provider name, check BOL for more providers
@datasrc='AMD64', --//actual remote server name
@catalog='Northwind' --//default database for this linked server

GO
--////////////////////////////////
--//[2] Enable/disable some options
--////////////////////////////////
Exec sp_serveroption 'webdb', 'data access', 'true' --Enables and disables a linked server for distributed query access
Exec sp_serveroption 'webdb', 'rpc', 'true' --//Enables RPC from the given server.
Exec sp_serveroption 'webdb', 'rpc out', 'true' --//Enables RPC to the given server (required to call SP using Linked Server).
Exec sp_serveroption 'webdb', 'collation compatible', 'true'

GO

--////////////////////////////////
--//[3] Add linked server login
--////////////////////////////////
Exec sp_addlinkedsrvlogin
@useself='false', --//false means we are going to use remote login/password
--//true means use local login/password to connect to remote machine (If local login/password does not match on remote machine then will fail)

@rmtsrvname='webdb', --//Exising Linked server name
@rmtuser='remote_login' , --//remote login
@rmtpassword='pass_of_remote_login' --//remote login's password

Now you can try to run the following query to test your SQL linked server

Click here to copy the following block

SELECT * FROM OPENQUERY(webdb, 'SELECT * FROM Orders')