This article provides an example of creating a linked server in SQL Server using Transact-SQL. Using T-SQL enables you to create the linked server without relying on a graphical user interface (such as SSMS).

Syntax

To create a linked server using T-SQL, use the sp_addlinkedserver system stored procedure.

The official syntax goes like this:

sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ]   
     [ , [ @provider= ] 'provider_name' ]  
     [ , [ @datasrc= ] 'data_source' ]   
     [ , [ @location= ] 'location' ]   
     [ , [ @provstr= ] 'provider_string' ]   
     [ , [ @catalog= ] 'catalog' ]

Most of the arguments are optional, but you will need to provide the linked server name.

You’ll also probably want to specify the data source or location of the linked server, and perhaps a provider name. The catalog argument allows you to specify a default database to which the linked server is mapped. Check out the official Microsoft documentation for an explanation of each of these arguments.

Example – Create the Linked Server

To create a linked server using T-SQL, execute the sp_addlinkedserver stored procedure while passing in the name of the linked server as well as its source.

Here’s an example of creating a linked server:

EXEC sp_addlinkedserver 
    @server=N'Homer', 
    @srvproduct=N'', 
    @provider=N'MSOLEDBSQL', 
    @datasrc=N'172.17.0.2,1433',
    @catalog='Music';

In this case, the linked server name is “Homer” and I specify the IP address of the server, followed by the TCP port (in my case it’s actually a Docker container on the same machine). Change the server name and IP address/port as required. I also specify a default database called “Music”.

This example uses MSOLEDBSQL as the provider name, but you can use whichever provider name is applicable in your situation. In my case, I’m linking to another instance of SQL Server.

Note that MSOLEDBSQL is the recommended provider for SQL Server. If you’ve previously used SQLOLEDB or SQLNCLI, both of these are now deprecated. Microsoft decided to undeprecate OLE DB and release it in 2018.

If you were to link to Oracle you might use OraOLEDB.Oracle, for MS Access you might use Microsoft.Jet.OLEDB.4.0 (for Access the 2002-2003 formats) or Microsoft.ACE.OLEDB.12.0 (for the 2007 format). The official documentation has a table of parameters to use for different scenarios.

Test the Linked Server

Once you’ve added the linked server, you can use sp_testlinkedserver to test it:

EXEC sp_testlinkedserver Homer;

Result (if successful):

Commands completed successfully.

If you get a “Login failed” error, chances are you don’t have a corresponding login on the remote server. You’ll need to have a corresponding login with the same credentials as the one on the local server.

The way it works is, when you first add the linked server, a default mapping between all logins on the local server and remote logins on the linked server is automatically created. SQL Server uses the credentials of the local login when connecting to the linked server on behalf of the login. If your local login doesn’t have a corresponding login on the remote server, then you’ll get a “Login failed” error.

Add a Login for the Linked Server

If you don’t want the linked server to use your own login, you can specify a different login for it to use. As long as the remote server has a corresponding login with the same credentials, you’ll be good to go. Obviously, you’ll need to ensure that the remote account has the appropriate permissions to do what you need it to do.

Here’s an example of adding a SQL Server login for the linked server.

EXEC sp_addlinkedsrvlogin 
    @rmtsrvname=N'Homer', 
    @useself=N'FALSE', 
    @locallogin=NULL, 
    @rmtuser=N'Marge', 
    @rmtpassword=N'BigStrong#Passw0rd';

Run this on the local server after you’ve created the linked server. You’ll need a corresponding login with the same credentials on the actual (remote) linked server.

This adds a login called “Marge” for the linked server called “Homer”. As long as the remote server has a login with the same credentials, the local server will be able to log in to the linked server.

See Add a Linked Server Login in SQL Server for an example of adding a corresponding login on the remote server.

Drop a Linked Server

Here’s an example of dropping the linked server called “Homer” and all associated logins.

EXEC sp_dropserver 'Homer', 'droplogins';

The droplogins argument is optional, but if you don’t specify this when dropping a linked server that has associated remote and linked server login entries, or is configured as a replication publisher, an error message is returned.

See Remove a Linked Server using T-SQL for more examples of dropping a linked server.

Run a Distributed Query against the Linked Server

Now that we’ve created the linked server, here’s an example of running a distributed query against it:

SELECT * 
FROM Homer.Music.dbo.Artists;

The only difference between this and a local query is that you need to add the linked server name to the FROM clause.

Like this:

LinkedServer.Database.Schema.Table

Alternatively, you can use OPENQUERY() to run a pass-through query:

SELECT * 
FROM OPENQUERY(
    Homer, 
    'SELECT * FROM Music.dbo.Artists;'
    );