How to Create a Linked Server in AWS RDS for SQL Server


AWS RDS for SQL Server is a managed database service that simplifies administration tasks such as backups, patching, and high availability. However, this managed model also introduces certain restrictions — including limitations around administrative permissions.

One common challenge is creating a linked server. In a standard on-premises or EC2-hosted SQL Server environment, linked servers can be created directly from SQL Server Management Studio (SSMS). In AWS RDS for SQL Server, this UI-based approach is not available.

This article explains how to create and use linked servers in AWS RDS for SQL Server using supported stored procedures. You will also learn how to query and update remote tables once the linked server is configured.

Understanding Linked Server Limitations in AWS RDS

When you attempt to create a linked server using SSMS against an AWS RDS for SQL Server instance, you may encounter errors such as:

  • 'The requested dialog cannot be displayed.'
  • 'You must be a member of the sysadmin role to perform this operation.'

These errors occur because AWS RDS does not grant full sysadmin privileges. Instead of using the SSMS wizard, AWS provides supported system stored procedures to manage linked servers.

Scenario 1: Linking from On-Premises or EC2 SQL Server to RDS SQL Server

If you are creating a linked server from an on-premises or EC2-hosted SQL Server to AWS RDS for SQL Server, the process is straightforward.

In this case, you can use the standard SSMS workflow:

  1. Open SSMS and connect to your local or EC2 SQL Server instance.
  2. Navigate to Server Objects → Linked Servers.
  3. Right-click and select New Linked Server.
  4. Specify the RDS endpoint as the data source.
  5. Configure security using your RDS SQL Server credentials.

After creation, the RDS databases and tables appear as a linked server, allowing you to query them as if they were local.

Scenario 2: Linking from AWS RDS SQL Server to Another SQL Server

Creating a linked server from AWS RDS SQL Server requires executing stored procedures directly, rather than using the SSMS UI.

AWS supports sp_addlinkedserver and sp_addlinkedsrvlogin for this purpose.

Create the Linked Server

EXEC master.dbo.sp_addlinkedserver
    @server = 'localdb_link',
    @srvproduct = '',
    @provider = 'SQLNCLI',
    @datasrc = 'your-target-server-hostname';

Configure Login Mapping

EXEC master.dbo.sp_addlinkedsrvlogin
    @rmtsrvname = 'localdb_link',
    @useself = 'FALSE',
    @locallogin = NULL,
    @rmtuser = 'admin',
    @rmtpassword = 'your_password';
GO

Once these commands complete successfully, the remote SQL Server appears as a linked server within your RDS instance.

Querying and Updating Data Through the Linked Server

When querying tables through a linked server, be sure to fully qualify the object name using the following format:

LinkedServerName.DatabaseName.SchemaName.TableName

Example SELECT

SELECT *
FROM localdb_link.SalesDB.dbo.Orders;

Example UPDATE

UPDATE localdb_link.SalesDB.dbo.Orders
SET Status = 'Processed'
WHERE OrderID = 1001;

As long as permissions are configured correctly, you can perform SELECT, INSERT, UPDATE, and DELETE operations across linked servers.

Extending Linked Servers with SaaS and Cloud Data

While this article focuses on SQL Server-to-SQL Server connectivity, many organizations want to expose SaaS or cloud application data through linked servers as well.

Using tools such as CData ODBC Drivers or CData Connect AI, you can surface data from applications like Salesforce, Dynamics 365, or cloud APIs as SQL-accessible sources. These can then be linked into SQL Server — including AWS RDS — using the same linked server concepts described above.

Power SQL Server with Live Cloud and SaaS Data

If your use case extends beyond SQL Server-to-SQL Server integration, CData enables SQL Server and AWS RDS to query live data from hundreds of SaaS applications and cloud services using standard SQL.

Learn more about CData ODBC Drivers for exposing external data as relational tables, or explore how CData Connect AI provides secure, governed, real-time data access for analytics and AI workloads.

In this article

Related articles