How to create linked server to connect remote Sql Server

Creating linked server using SSMS(GUI) & TSQL in MS Sql Server 2008 connecting remote database.

 SQL   
 

Posted: April 13, 2017. | By: mustafa

How to create linked server & get remote sql backup

  1. Add linked server using GUI
  2. Add linked server using Tsql

 

Add linked server using GUI

Let us start by opening MS Sql Server 2008 & connect to local sql instance.

In object explorer, expand tree view of the sql server now select Server Objects click on plus button to expand tree view & Right click on Linked Servers and choose New Linked Server.

 

New Linked Server dialog box is displayed, enter the linked server name. Then under the server type choose Other data source option.

Provide Settings:

  1. Provider Name - Choose SQL Server Native Client 11.0 from the dropdown list.
  2. Product Name - you can enter SQLSSERVER.
  3. Data Source - add remote Sql Server instance.
  4. Catalog - you can define database name optional.

 NewLinkedServer.png, SQL, SQL,

On the left side of the pane select Security page. Under the heading "For a login not defined in the list above, connections will" choose last option - Be made using this security context.

Enter remote sql server username and password you use to login.

 LinkedServerOptions.png, SQL, SQL,   

Now select Server Options page. In the server options Data Access is selected true by default, suppose we just want to create linked server but we need not to access data on remote server for now or may be at some later time we can set this to False. However we can always modify settings afterwards.

Other options:

RPC - RPC allows stored procedure call from the linked server.

RPC Out - whereas RPC Out allows stored procedure calls to go out to the linked server.

Connection Timeout - set connect timeout if your connection to sql server is slow.

And finally when your task is completed disable rpc option as a security precaution.

Click OK to create the linked server.

Now to verify the linked server again go to Object Explorer and refresh node Linked Server, you can find newly created linked server. Expand to see the databases and tables on the server.

 

Add linked server using Tsql

Create linked server to remote database using Transact-Sql.

EXECUTE master.dbo.sp_addlinkedserver
 @server = N'LinkedServer_Name',
 @srvproduct = N'SQLServer',
 @provider = N'SQLNCLI', 
 @datasrc = N'IP_Address', -- Or Sql Server Instance name.
 @catalog = N'DB Name';
Go

EXECUTE master.dbo.sp_addlinkedsrvlogin
 @rmtsrvname = N'LinkedServer_Name',
 @useself = N'False',
 @rmtuser = N'UserName',
 @rmtpassword = N'Password';
GO

After linked server is created successfully we are going to access or query database using linked server. Using sql select command with linked server as 4 part notation.

[LinkedServer].[Database Name].[Schema].[Objectname]

select * from LnkServer_Name.Db_Name.[dbo].table_Name;

Also to access any stored procedure residing on the remote server we need to enable remote procedure call. To enable rpc out option for the linked server run the following sql command.

execute sp_serveroption @server='LinkedServer_Name', @optname='rpc out', @optvalue='True'

 


related posts

Back to top