Linked Server Resource

Synopsis

Manages SQL Server linked servers for distributed queries across SQL Server instances and other OLE DB data sources.

Type

OpenDsc.SqlServer/LinkedServer

Capabilities

• Get
• Set
• Delete
• Export

Properties

Connection properties

serverInstance

SQL Server instance name.

Type: string
Required: Yes
Access: Read/Write
Default value: None

connectUsername

Username for SQL authentication.

Type: string
Required: No
Access: Write-Only
Default value: None

connectPassword

Password for SQL authentication.

Type: string
Required: No
Access: Write-Only
Default value: None

Linked server properties

name

Name of the linked server.

Type: string
Required: No
Access: Read/Write
Default value: None

productName

Product name of OLE DB data source.

Type: string
Required: No
Access: Read/Write
Default value: None

providerName

OLE DB provider name.

Type: string
Required: No
Access: Read/Write
Default value: None

dataSource

OLE DB data source (server name or path).

Type: string
Required: No
Access: Read/Write
Default value: None

location

Location of database for OLE DB provider.

Type: string
Required: No
Access: Read/Write
Default value: None

catalog

Default catalog (database).

Type: string
Required: No
Access: Read/Write
Default value: None

providerString

OLE DB provider connection string.

Type: string
Required: No
Access: Read/Write
Default value: None

Linked server options

dataAccess

Whether data access is enabled.

Type: bool
Required: No
Access: Read/Write
Default value: None

rpc

Whether RPC from the linked server is allowed.

Type: bool
Required: No
Access: Read/Write
Default value: None

rpcOut

Whether RPC out is enabled.

Type: bool
Required: No
Access: Read/Write
Default value: None

useRemoteCollation

Use remote server's collation.

Type: bool
Required: No
Access: Read/Write
Default value: None

collationName

Collation name for character comparisons.

Type: string
Required: No
Access: Read/Write
Default value: None

collationCompatible

Whether collation is compatible.

Type: bool
Required: No
Access: Read/Write
Default value: None

lazySchemaValidation

Use lazy schema validation.

Type: bool
Required: No
Access: Read/Write
Default value: None

connectTimeout

Connection timeout in seconds (min: 0).

Type: int
Required: No
Access: Read/Write
Default value: None

queryTimeout

Query timeout in seconds (min: 0).

Type: int
Required: No
Access: Read/Write
Default value: None

isPromotionofDistributedTransactionsForRPCEnabled

Promote distributed transactions for RPC.

Type: bool
Required: No
Access: Read/Write
Default value: None

Read-only properties

id

Unique identifier.

Type: int
Required: No
Access: Read-Only
Default value: None

dateLastModified

Date last modified.

Type: datetime
Required: No
Access: Read-Only
Default value: None

distributor

Whether it is a distributor.

Type: bool
Required: No
Access: Read-Only
Default value: None

distPublisher

Whether it is a distribution publisher.

Type: bool
Required: No
Access: Read-Only
Default value: None

publisher

Whether it is a publisher.

Type: bool
Required: No
Access: Read-Only
Default value: None

subscriber

Whether it is a subscriber.

Type: bool
Required: No
Access: Read-Only
Default value: None

DSC properties

_exist

Whether the linked server should exist. Defaults to true.

Type: bool
Required: No
Access: Read/Write
Default value: true

Examples

Example 1 — Get a linked server

PowerShellShell

$resourceInput = @'
serverInstance: .
name: RemoteServer
'@

dsc resource get -r OpenDsc.SqlServer/LinkedServer --input $resourceInput

resource_input=$(cat <<'EOF'
serverInstance: .
name: RemoteServer
EOF
)

dsc resource get -r OpenDsc.SqlServer/LinkedServer --input "$resource_input"

Example 2 — Create a linked server

PowerShellShell

$resourceInput = @'
serverInstance: .
name: RemoteServer
providerName: SQLNCLI
dataSource: remote-sql.example.com
rpcOut: true
dataAccess: true
'@

dsc resource set -r OpenDsc.SqlServer/LinkedServer --input $resourceInput

resource_input=$(cat <<'EOF'
serverInstance: .
name: RemoteServer
providerName: SQLNCLI
dataSource: remote-sql.example.com
rpcOut: true
dataAccess: true
EOF
)

dsc resource set -r OpenDsc.SqlServer/LinkedServer --input "$resource_input"

Example 3 — Configuration document

$schema: https://aka.ms/dsc/schemas/v3/bundled/config/document.json
resources:
  - name: Remote SQL linked server
    type: OpenDsc.SqlServer/LinkedServer
    properties:
      serverInstance: "."
      name: RemoteServer
      providerName: SQLNCLI
      dataSource: remote-sql.example.com
      rpcOut: true
      dataAccess: true

Exit codes

Code	Description
0	Success
1	Error
2	Invalid JSON
3	Invalid argument
4	Unauthorized access
5	Invalid operation