Create Linked Server for Remote SQL Server

I'll show you how to create linked server for remote access in sql server 2008 r2.

if we have two servers on different pc's on a LAN, say server1 and server2. if we have to get data of server 1 on server2 we have to create a linked server on server2.

First we'll create a remote login on server1 then a linked server on server2.

For remote login on server1

1. Open sql server1 and expand security tab.
2. Right click on Logins and select Newlogin.
3. In new window enter name of login and select SQL server authentication wrote password and uncheck UER MUST CHANGE PASSWORD option and click ok
   
   
   
   
4. Now Expand your database and then expand security tab. Now right click on Users and select add new user.
   
   
   
   
5. Now click on ok button. You remote login created. Dont forget to turn off firewall.

Crete linked server2

1. Open SQL Server2 and expand server objects.
2. Right click on Linked server and select new .a new window will appear.
3. Enter name of you server1 in linked server box. here our server name is server1
   
   
   
   
4. Now click on security tab and click on add button below box. In locallogin option in box enter your local server name here our local server is SERVER2 . In Remote user option enter Login name we created in Server1. Here we created it with name aims and in next box write password of that user.
   
   
   
   
5. In Server Option Tab change RPC and RPC OUt to TRUE.
6. Click on ok