How to add a Linked Server?

To add a linked server using SQL Server Management Studio(SSMS), open the server where you want to create a link from in object explorer.

Step 1: In SSMS -> object explorer -> server -> server objects ->  Linked servers(right click on the linked server folder and select "New Linked Server")

then click on that New Linked Server option, it will display as follows.

Step 2: The New Linked Server dialog will appears as follows

Step 3: For "Server Type" make sure that "Other Data Source" is selected.

Step 4: Provider-- Select "Microsoft OLE DB Provider for SQL Server".

Step 5: Product Name--SQLSERVER

Step 6: Data source-- Type the actual server name and instance name (SERVERNAME\INSTANCENAME).

Step 7: Provider String-- Blank.

Step 8: catalog--Optional.

Step 9: Click the security and click on "Be made using the login’s current security context", so that it provides more secure.

Step 10: Click on "Server Options" -> in right pane change RPC & RPC Out to "True" as follows.

Step 11: Click OK, and the New Linked Server is created.