New-DbaLinkedServer

Want to see the source code for this command? Check out New-DbaLinkedServer on GitHub.
Want to see the Bill Of Health for this command? Check out New-DbaLinkedServer.

Synopsis

Creates a new linked server connection to remote SQL Server instances or heterogeneous data sources.

Description

Creates a new linked server on a SQL Server instance, allowing you to query remote databases and heterogeneous data sources as if they were local tables. This replaces the need to manually configure linked servers through SSMS or T-SQL scripts, while providing consistent security context management for unmapped logins. The function uses SMO to create the linked server definition and automatically configures the default security mapping based on your specified security context.

Syntax

New-DbaLinkedServer
    [[-SqlInstance] <DbaInstanceParameter[]>]
    [[-SqlCredential] <PSCredential>]
    [[-LinkedServer] <String>]
    [[-ServerProduct] <String>]
    [[-Provider] <String>]
    [[-DataSource] <String>]
    [[-Location] <String>]
    [[-ProviderString] <String>]
    [[-Catalog] <String>]
    [[-SecurityContext] <String>]
    [[-SecurityContextRemoteUser] <String>]
    [[-SecurityContextRemoteUserPassword] <SecureString>]
    [[-InputObject] <Server[]>]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> New-DbaLinkedServer -SqlInstance sql01 -LinkedServer linkedServer1 -ServerProduct mssql -Provider sqlncli -DataSource sql02

Creates a new linked server named linkedServer1 on the sql01 instance. The link is via the SQL Native Client and is connected to the sql02 instance.

Example: 2

PS C:\> Connect-DbaInstance -SqlInstance sql01 | New-DbaLinkedServer -LinkedServer linkedServer1 -ServerProduct mssql -Provider sqlncli -DataSource sql02

Creates a new linked server named linkedServer1 on the sql01 instance. The link is via the SQL Native Client and is connected to the sql02 instance. The sql01 instance is passed in via pipeline.

Example: 3

PS C:\> New-DbaLinkedServer -SqlInstance sql01 -LinkedServer linkedServer1 -ServerProduct mssql -Provider sqlncli -DataSource sql02 -SecurityContext CurrentSecurityContext

Creates a new linked server named linkedServer1 on the sql01 instance. The link is via the SQL Native Client and is connected to the sql02 instance. Connections with logins that are not explicitly
mapped to the remote server will use the current login's security context.

Optional Parameters

-SqlInstance

The target SQL Server instance or instances. This can be a collection and receive pipeline input to allow the function
to be executed against multiple SQL Server instances.

-SqlCredential

Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported.
For MFA support, please use Connect-DbaInstance.

-LinkedServer

Specifies the name for the new linked server object in SQL Server. This name must be unique on the SQL Server instance.
Use a descriptive name that identifies the remote data source or its purpose to help other DBAs understand the connection.

-ServerProduct

Specifies the product name of the remote data source as it appears in sys.servers.product column.
Common values include 'SQL Server' for SQL Server instances, 'Oracle' for Oracle databases, or custom names for other OLE DB data sources.
This is primarily used for documentation and identification purposes in system views.

-Provider

Specifies the OLE DB provider used to connect to the remote data source.
Common providers include 'SQLNCLI' or 'MSOLEDBSQL' for SQL Server, 'OraOLEDB.Oracle' for Oracle, or 'Microsoft.ACE.OLEDB.12.0' for Access databases.
The provider must be installed and registered on the SQL Server instance.

-DataSource

Specifies the network name or connection string for the remote data source.
For SQL Server instances, this is typically the server name or server\instance format.
For other data sources, this could be a TNS name for Oracle or a file path for file-based sources.

-Location

Specifies the physical location information for the data source, typically used for documentation purposes.
This parameter is rarely required for most linked server configurations and is primarily used with specific OLE DB providers.
Leave empty unless specifically required by your data source provider.

-ProviderString

Specifies additional connection properties passed directly to the OLE DB provider.
Use this for provider-specific settings like connection pooling, timeouts, or authentication options that cannot be set through other parameters.
The format and available options depend on the specific OLE DB provider being used.

-Catalog

Specifies the default database or catalog name to use when connecting to the remote data source.
For SQL Server linked servers, this sets the default database context for queries that don't specify a database name.
This is equivalent to the 'Initial Catalog' connection property in connection strings.

-SecurityContext

Specifies the security context option found on the SSMS Security tab of the linked server. This is a separate configuration from the mapping of a local login to a remote login. It specifies the
connection behavior for a login that is not explicitly mapped. 'NoConnection' means that a connection will not be made. 'WithoutSecurityContext' means the connection will be made without using a
security context. 'CurrentSecurityContext' means the connection will be made using the login's current security context. 'SpecifiedSecurityContext' means the specified username and password will be
used. The default value is 'WithoutSecurityContext'. For more details see the Microsoft documentation for sp_addlinkedsrvlogin and also review the SSMS Security tab of the linked server.

-SecurityContextRemoteUser

Specifies the remote login name. This param is used when SecurityContext is set to SpecifiedSecurityContext. To map a local login to a remote login use New-DbaLinkedServerLogin.

-SecurityContextRemoteUserPassword

Specifies the remote login password. This param is used when SecurityContext is set to SpecifiedSecurityContext. To map a local login to a remote login use New-DbaLinkedServerLogin. NOTE: passwords
are sent to the SQL Server instance in plain text. Check with your security administrator before using this parameter.

-InputObject

Accepts SQL Server SMO objects from the pipeline, typically from Connect-DbaInstance.
This allows you to create linked servers on multiple instances by piping server connections to this function.
Use this when you need to create the same linked server configuration across multiple SQL Server instances.

-EnableException

By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.

-WhatIf

Shows what would happen if the command were to run. No actions are actually performed.

-Confirm

Prompts you for confirmation before executing any changing operations within the command.