Atlas now manages SQL Server linked servers and their login mappings as linked_server blocks, and creates the objects that read through a linked server after it.

atlas schema inspect prints the linked servers of a SQL Server instance as linked_server blocks, and atlas migrate diff and atlas schema apply plan their changes. Linked servers belong to the instance, so Atlas manages them only when linked_servers is enabled in the mode "sqlserver" block, and the URLs of the environment cover the whole database (mode=database).

Setup

The examples below run on three SQL Server containers on one Docker network: a warehouse instance, which the linked server reaches by its network alias, and an instance each for the dev database and the target, as a dev database whose instance holds linked servers is not clean. docker "sqlserver" blocks declare them, and the warehouse's init script creates the remote table and a reader login:

atlas.hcl

docker "sqlserver" "warehouse" {
  image = "mcr.microsoft.com/mssql/server:2022-latest"
  args  = ["--network", "script-linked-server", "--network-alias", "warehouse-db"]
  init  = <<-SQL
    CREATE DATABASE [sales];
    CREATE TABLE [sales].[dbo].[orders] ([id] int NOT NULL PRIMARY KEY, [total] decimal(10, 2) NOT NULL);
    INSERT INTO [sales].[dbo].[orders] VALUES (1, 10.50), (2, 20.00);
    CREATE LOGIN [reader] WITH PASSWORD = N'R3ader!pass', CHECK_POLICY = OFF;
    EXECUTE [sales].[sys].[sp_executesql] N'CREATE USER [reader] FOR LOGIN [reader]; GRANT SELECT ON [dbo].[orders] TO [reader];';
  SQL
}
docker "sqlserver" "app" {
  image    = "mcr.microsoft.com/mssql/server:2022-latest"
  database = "app"
  mode     = "database"
  args     = ["--network", "script-linked-server"]
}
env "warehouse" {
  dev = docker.sqlserver.warehouse.url
}
env "app" {
  dev = docker.sqlserver.app.url
}
env "local" {
  url = trimspace(file("target.txt"))
  dev = trimspace(file("dev.txt"))
  migration {
    dir = "file://migrations"
  }
  schema {
    mode "sqlserver" {
      linked_servers = true
    }
  }
}

atlas tool docker starts each container under a name and prints its URL, which the local environment reads:

docker network create script-linked-server
atlas tool docker --env warehouse --name script-linked-server-warehouse
atlas tool docker --env app --name script-linked-server-dev > dev.txt
atlas tool docker --env app --name script-linked-server-target > target.txt

Create a Docker network of your own for the containers, as above, or run them on the default network. Docker accepts network aliases only on networks you create, so on the default network, remove the args of the docker blocks and set the data_source of the linked server to an address at which the dev and target instances reach the warehouse.

Declaring Linked Servers

A linked_server block takes the properties of sp_addlinkedserver, the options of sp_serveroption, such as rpc_out, and login blocks that map local logins to remote ones.

Objects that reach a linked server depend on it, and their HCL declares it in depends_on. Atlas finds the server in four-part names, OPENQUERY, EXECUTE ... AT, and the dynamic SQL an object runs from string literals:

schema.ms.hcl

linked_server "warehouse" {
  provider    = "MSOLEDBSQL"
  data_source = "warehouse-db"
  catalog     = "sales"
  login {
    use_self = true
  }
}
view "order_ids" {
  schema = schema.dbo
  column "id" {
    null = false
    type = int
  }
  as         = "SELECT [id] FROM OPENQUERY([warehouse], 'SELECT id FROM sales.dbo.orders') AS [q]"
  depends_on = [linked_server.warehouse]
}
procedure "count_orders" {
  schema     = schema.dbo
  lang       = SQL
  as         = "EXECUTE ('SELECT COUNT(*) FROM sales.dbo.orders') AT [warehouse];"
  depends_on = [linked_server.warehouse]
}
synonym "orders" {
  schema = schema.dbo
  object = "[warehouse].[sales].[dbo].[orders]"
}

atlas migrate diff creates the server first, as SQL Server fails to create a view or routine that reads through a missing one. The procedures that manage linked servers can't run in a transaction, so the file is atlas:txmode none:

migrations/1.sql (excerpt)

-- atlas:delimiter \nGO
-- atlas:txmode none

-- Create linked server "warehouse"
EXECUTE sp_addlinkedserver @server = N'warehouse', @srvproduct = N'', @provider = N'MSOLEDBSQL', @datasrc = N'warehouse-db', @catalog = N'sales'
GO
-- Create synonym "orders"
CREATE SYNONYM [dbo].[orders] FOR [warehouse].[sales].[dbo].[orders]
GO
-- Create "count_orders" procedure
CREATE PROCEDURE [dbo].[count_orders]
AS
EXECUTE ('SELECT COUNT(*) FROM sales.dbo.orders') AT [warehouse];
GO
-- Create "order_ids" view
CREATE VIEW [dbo].[order_ids] ([id])
AS
SELECT [id] FROM OPENQUERY([warehouse], 'SELECT id FROM sales.dbo.orders') AS [q]
GO

Changing Linked Servers

Options change in place. A new product, provider, data source, location, provider string, or catalog re-creates the server with its login mappings. Here, the data source changes, rpc_out is enabled, and all logins connect as reader:

schema.ms.hcl

linked_server "warehouse" {
  provider    = "MSOLEDBSQL"
  data_source = "warehouse-db,1433"
  catalog     = "sales"
  rpc_out     = true
  login {
    remote_user     = "reader"
    remote_password = var.reader_password
  }
}

migrations/2.sql (password elided)

-- atlas:txmode none

-- Drop linked server "warehouse"
EXECUTE sp_dropserver @server = N'warehouse', @droplogins = 'droplogins';
-- Create linked server "warehouse"
EXECUTE sp_addlinkedserver @server = N'warehouse', @srvproduct = N'', @provider = N'MSOLEDBSQL', @datasrc = N'warehouse-db,1433', @catalog = N'sales';
-- Set option "rpc out" of linked server "warehouse"
EXECUTE sp_serveroption @server = N'warehouse', @optname = N'rpc out', @optvalue = N'true';
-- Map all logins to linked server "warehouse"
-- atlas:sensitive
EXECUTE sp_addlinkedsrvlogin @rmtsrvname = N'warehouse', @useself = N'false', @locallogin = NULL, @rmtuser = N'reader', @rmtpassword = N'...';

The remote password is write-only: Atlas plans the password the HCL declares, in a statement marked as sensitive, but never inspects or compares it. atlas schema inspect prints the mapping without it:

atlas schema inspect

linked_server "warehouse" {
  provider    = "MSOLEDBSQL"
  data_source = "warehouse-db,1433"
  catalog     = "sales"
  rpc_out     = true
  login {
    remote_user = "reader"
  }
}

Dropping Linked Servers

Objects that read through a linked server are dropped before it:

migrations/3.sql (excerpt)

-- Drop "audit_orders" trigger
DROP TRIGGER [dbo].[audit_orders];
-- Drop "order_ids" view
DROP VIEW [dbo].[order_ids];
-- Drop "order_totals" view
DROP VIEW [dbo].[order_totals];
-- Drop "count_orders" procedure
DROP PROCEDURE [dbo].[count_orders];
-- Drop "last_order" procedure
DROP PROCEDURE [dbo].[last_order];
-- Drop linked server "warehouse"
EXECUTE sp_dropserver @server = N'warehouse', @droplogins = 'droplogins';

Limitations

Linked servers belong to the instance. In a plan scoped to one schema, atlas schema apply rejects them and atlas migrate diff leaves them out. The dev database needs an instance of its own: a dev database whose instance holds linked servers is not clean.
atlas migrate down re-creates a login mapping without its remote password, which Atlas never inspects. A mapping that connects as a remote user comes back without it, and the objects that connect through it then fail to be created.
Credentials in a provider string, such as PWD, are kept out of the inspected HCL and are not compared. Plans still set them, in statements marked as sensitive.
Atlas doesn't detect a linked server named only in SQL that an object builds from variables at run time.
Requires Atlas Pro. Run atlas login to start a free trial.

SQL Server: Linked Servers

Setup

Declaring Linked Servers

Changing Linked Servers

Dropping Linked Servers

Limitations