how to create a linked server using SQL Server Management Studio (SSMS) and T-SQL.

SQL Tech

How to create a linked server using SQL Server Management Studio (SSMS) and T-SQL.

Naren 0Comment

To Creating a linked server in SQL Server allows you to access external data sources such as other SQL Server instances, or other OLE DB data sources(e.g MYSQL,ORACLE,PLSQL etc). Here is a step-by-step guide on.

1.Using SQL Server Management Studio (SSMS):

  • Open SQL Server Management Studio (SSMS).
  • Connect to the SQL Server instance where you want to create the linked server.
  • In the Object Explorer, expand the Server Objects .
  • Right-click on Linked Servers and select New Linked Server.
  • In the New Linked Server dialog box:
  • Linked server: Enter the name of the linked server.
  • Server type: Select SQL Server or Other data source depending on the type of external data source.
  • Data Source: Enter the name or network address of the SQL Server instance you want to link to.

For Other Data Source:

  • Provider: Select the appropriate OLE DB provider.
  • Product name: Enter the name of the product.
  • Data source: Enter the network name of the external data source.
  • Provider string: Optional connection string specific to the provider.
  • Location: Optional location of the data.
  • Catalog: The initial database to connect to.

Security Page:

  • Configure the security settings for the linked server.
  • You can choose to impersonate a login or specify remote login and password.
  • Click OK to create the linked server.
  • Right click on newly created linked server and test connection if success then connection created sucessfully else failed.

2.Using T-SQL:

Here is an example T-SQL script to create a linked server:

Linked server configuration:
EXEC sp_addlinkedserver
@server = 'test',
@srvproduct = '',
@provider = 'provider name',
@datasrc = 'Remote server name or ip address';

Linked server security configuration:
EXEC sp_addlinkedsrvlogin 
    @rmtsrvname = 'test', 
    @useself = 'false', 
    @locallogin = NULL, 
    @rmtuser = 'sa', 
    @rmtpassword = '*****';
Verify linked server:
SELECT * FROM sys.servers WHERE name = 'test';

Notes:

  • Ensure the appropriate OLE DB(32 bit or 64 bit) provider is installed and configured on the SQL Server instance ..
  • For security reasons, consider creating a SQL Server login with minimal privileges for use with the linked server.
  • Test the linked server connection to ensure it works correctly(Right click and test connection).

By following these steps, you can create and configure a linked server in SQL Server, allowing you to query and interact with external data sources directly from your SQL Server session.