Tuesday, August 20, 2013

Linked Servers in SQL Server

I am always seeing that people are struggling while creating linked server in SQL Server based on their access on target server. I have tried to show some sample way to create linked server. Below are list of cases.
1) Linked server from SQL Server to another SQL Server using SQL Login
a) Go to SQL Server Management Studio –> Connect SQL Server –> Server Objects –> Linked Servers –> Right Click New Linked Server
image
b) Enter target server name in Linked Server. Select source type as SQL Server.
image
c) Go to Security and select “Be made using this security context”. Enter user name in Remote login and password in “With password”.
image
d) click Ok.
image
e) Execute below query to check if linked server is working.
Query: Select * From LinkedServerName.DatabaseName.sys.objects
2) Linked server from SQL Server to Self using SQL Login.
a) Go to SQL Server Management Studio –> Connect SQL Server –> Server Objects –> Linked Servers –> Right Click New Linked Server
image
b) Enter linked server name (e.g. SELF) in Linked Server. Select source type as Other data source. Select “Microsoft OLE DB Provider for SQL Server as Provider”. Enter target server name in Data Source. Enter database name in Catalog.
image
c) Go to Security and select “Be made using this security context”. Enter user name in Remote login and password in “With password”.
image

No comments:

Post a Comment