Linked Servers (BETA) #

Linked Server support was added in 1.43.16. It supports the linked_server and linked_login Resource Blocks.

Resource Block Schema #

linked_server - Some settings cannot be changed on existing servers. All values are optional.
- name (string - optional. If not provided, it defaults to the resource name)
- product (string)
- provider (string)
- data_source (string)
- catalog|database (string) You can use either catalog or database for this field.
- data_access (boolean) This can be altered
- connect_timeout (int) This can be altered
- query_timeout (int) This can be altered
- rpc (boolean) Enables RPC from the remote server and can be altered
- rpc_out (boolean) Enables RPC to the remote server and can be altered
- msdtc (boolean) This maps remote proc transaction promotion and can be altered.
linked_login - The resource name (or identifier) in the resource block isn’t used
- server (string - required)
- login (string) If this is empty (or not present), the resource block defines the default (or wildcard) connection to the remote server. If it is populated, it defines how this login connects to the remote server.
- use_self (boolean) If this is set to true, the user connects to the remote user using their current security context.
- remote_login (string) This defines the login to use on the remove server if not connecting as yourself.
- sqldsc_credential (string) This is the credential name for the password of the remote login.

Linked servers have a number of options that can be changed. But SQL Server doesn’t provide a way to change the linked server itself. So we can’t change the name, product, provider, data source, or catalog. The linked server will need to be dropped and added back.

Linked Servers can be created in one of two ways. The simplest is a direct connection to the linked server. This creates a linked server to a server named Server01 using defaults. All logins use their current security context to connect.

resource "linked_server" "Server01" {}

In this case, we didn’t set a product and it defaulted to SQL Server. That means we don’t need to set the provider, data_source, or catalog.

A more complicated example looks like this:

resource "linked_server" "db-apps" {
    data_source = "db-apps.static.loc"
    provider = "MSOLEDBSQL"
}

The most common providers are MSOLEDBSQL, SQLNCLI, SQLNCLI11 (or other versions of SQLNCLI), and SQLOLEDB. These are listed in SSMS under /Server Objects/Linked Server/Providers. Using SQLNCLI uses the highest version of the native client driver that is installed.
The data_source is the DNS name of the remote server.

Linked Server Logins #

Linked Server Logins are typically specified for one of four scenarios. Note that the Resource Name isn’t used for linked server logins. All of these logins can be remove by setting ensure="absent"

my image

Default (or wildcard) Login using the current security context. This would check the green checkbox above.
```
resource "linked_login" "not_used" {
    server = "TestLinkedServer"
    use_self = true
}
```
Default (or wildcard) Login using a named remote login. This says that all users that don’t have a mapped login, will login to the remote server as otherUser. This is what you see in the dialog box above with the last checkbox at the bottom.
```
resource "linked_login" "not_used" {
    server = "TestLinkedServer"
    remote_login = "otherUser"
    sqldsc_credential = "otherUserCredential"
}
```

Mapped Login that uses a named login. This is the yellow highlight above. User D40\DSCLogin will connect to the remote server as t1.

resource "linked_login" "not_used" {
    server = "TestLinkedServer"
    login = "D40\DSCLogin"
    remote_login = "t1"
    sqldsc_credential = "t1"
}

Mapped Login that keeps the security context. This is the SQLDSCLogin right below the yellow highlight. This login will connect to the remote server as itself.
```
resource "linked_login" "not_used" {
    server = "TestLinkedServer"
    login = "SQLDSCLogin"
    use_self = true
}
```

Linked Server Limitations #

The current release has a number of limitations.

Only the most common options of linked servers can be changed.
SQL Server doesn’t allow some settings to be changed such as the data source and provider. The linked server must be dropped and recreated. This isn’t supported yet.
Currently, a linked_login resource block doesn’t check if the linked server exists before trying to make changes to the linked login.