Muokkaa

Jaa

Create linked servers (SQL Server Database Engine)

  • Artikkeli

Applies to: SQL Server Azure SQL Managed Instance

This article shows how to create a linked server and access data from another SQL Server, an Azure SQL Managed Instance, or another data source by using SQL Server Management Studio (SSMS) or Transact-SQL. Linked servers enable the SQL Server database engine and Azure SQL Managed Instance to read data from the remote data sources and execute commands against the remote database servers (for example, OLE DB data sources) outside of the instance of SQL Server.

Background

Linked servers are typically configured to enable the database engine to execute a Transact-SQL statement that includes tables in another instance of SQL Server, or another database product such as Oracle. Many types of data sources can be configured as linked servers, including third-party database providers and Azure Cosmos DB.

After a linked server is created, distributed queries can be run against this server, and queries can join tables from more than one data source. If the linked server is defined as an instance of SQL Server or an Azure SQL Managed Instance, remote stored procedures can be executed.

The capabilities and required arguments of the linked server can vary significantly. The examples in this article provide a typical example but all options aren't described. For more information, see sp_addlinkedserver (Transact-SQL).

Permissions

When using Transact-SQL statements, requires ALTER ANY LINKED SERVER permission on the server or membership in the setupadmin fixed server role. When using Management Studio requires CONTROL SERVER permission or membership in the sysadmin fixed server role.

Create a linked server with SSMS

Create a linked server with SSMS using the following procedure:

Open the New Linked Server dialog

In SQL Server Management Studio (SSMS):

  1. Open Object Explorer.
  2. Expand Server Objects.
  3. Right-click Linked Servers.
  4. Select New Linked Server.

Edit the General page for the linked server properties

On the General page, in the Linked server box, type the name of the instance of SQL Server that you're linking to.

Note

If the instance of SQL Server is the default instance, enter the name of the computer that hosts the instance of SQL Server. If the SQL Server is a named instance, enter the name of the computer and the name of the instance, such as Accounting\SQLExpress.

Specify the Server type and related information if needed:

  • SQL Server
    Identify the linked server as an instance of Microsoft SQL Server or an Azure SQL Managed Instance. If you use this method of defining a linked server, the name specified in Linked server must be the network name of the server. Also, any tables retrieved from the server are from the default database defined for the login on the linked server.

  • Other data source
    Specify an OLE DB server type other than SQL Server. Clicking this option activates the options below it.

    • Provider
      Select an OLE DB data source from the list box. The OLE DB provider is registered with the given PROGID in the registry.

    • Product name
      Type the product name of the OLE DB data source to add as a linked server.

    • Data source
      Type the name of the data source as interpreted by the OLE DB provider. If you're connecting to an instance of SQL Server, provide the instance name.

    • Provider string
      Type the unique programmatic identifier (PROGID) of the OLE DB provider that corresponds to the data source. For examples of valid provider strings, see sp_addlinkedserver (Transact-SQL).

    • Location
      Type the location of the database as interpreted by the OLE DB provider.

    • Catalog
      Type the name of the catalog to use when making a connection to the OLE DB provider.

Edit the Security page for the linked server properties

On the Security page, specify the security context that is used when the original instance connects to the linked server. There are two strategies to configure here that can be used alone or combined. The first is to map logins from the local server to the remote server, and the second is how the linked server should treat logins that aren't mapped.

Add login mappings

You can optionally specify how specific local server logins authenticate using the linked server.

Under Local server login to remote server login mappings, repeat the following process for each login you would like to map:

  1. Select Add.

  2. Specify a Local login.

    Specify the local login that can connect to the linked server. The local login can be either a login using SQL Server authentication or a Windows authentication login. Using a Windows group or a contained database user isn't supported. Use this list to restrict the connection to specific logins, or to allow some logins to connect as a different login.

    Note

    Common issues with linked servers using Windows authentication to a remote SQL Server instance arise from issues with service principal names (SPNs). For more information, see Service Principal Name (SPN) Support in Client Connections. Microsoft Kerberos Configuration Manager for SQL Server is a diagnostic tool that helps troubleshoot Kerberos related connectivity issues with SQL Server. For more information, see Microsoft Kerberos Configuration Manager for SQL Server.

  3. Select Impersonate (optional).

    Pass the username and password from the local login to the linked server. For SQL Server authentication, a login with the exact same name and password must exist on the remote server. For Windows logins, the login must be a valid login on the linked server.

    To use impersonation, the configuration must meet the requirement for delegation.

  4. Specify a Remote User if you aren't using impersonation.

    Use the remote user to map user defined in Local login. The Remote User must be a SQL Server authentication login on the remote server.

  5. Specify a Remote Password if you aren't using impersonation.

    • Specify the password of the Remote User.

  6. Select Remove to remove an existing local login, if desired.

Specify the default security context for logins not present in the mapping list

In a domain environment where users are connecting by using their domain logins, selecting Be made using the login's current security context is often the best choice. When users connect to the original SQL Server by using a SQL Server login, the best choice is often to select By using this security context, and then providing the necessary credentials to authenticate at the linked server.

Select one of the following options:

  • Not be made
    A connection won't be made for logins not defined in the list.

  • Be made without using a security context
    A connection will be made without using a security context for logins not defined in the list.

  • Be made using the login's current security context
    A connection will be made using the current security context of the login for logins not defined in the list. If connected to the local server using Windows Authentication, your Windows credentials will be used to connect to the remote server. If connected to the local server using SQL Server Authentication, login name and password will be used to connect to the remote server. In this case, a login with the exact same name and password must exist on the remote server.

  • Be made using this security context
    A connection will be made using the login and password specified in the Remote login and With password boxes for logins not defined in the list. The remote login must be a SQL Server Authentication login on the remote server.

    Caution

    If a linked server is configured with option Be made using this security context, any user on the instance can access the remote linked server using this context. This may have the unintended potential for abuse or malicious internal access. The SQL authenticated Remote login provided to the linked server should be granted minimum necessary permissions on the remote server, to ensure a principle of least privilege and to reduce attack surface.

Edit the Server Options page in linked server properties (optional)

To view or specify server options, select the Server Options page. You can edit any of the following options:

  • Collation Compatible
    Affects Distributed Query execution against linked servers. If this option is set to true, SQL Server assumes that all characters in the linked server are compatible with the local server, with regard to character set and collation sequence (or sort order). This enables SQL Server to send comparisons on character columns to the provider. If this option isn't set, SQL Server always evaluates comparisons on character columns locally.

    This option should be set only if it's certain that the data source corresponding to the linked server has the same character set and sort order as the local server.

  • Data Access
    Enables and disables a linked server for distributed query access.

  • RPC
    Enables remote procedure calls (RPC) from the specified server.

  • RPC Out
    Enables RPC to the specified server.

  • Use Remote Collation
    Determines whether the collation of a remote column or of a local server will be used.

    If true, the collation of remote columns is used for SQL Server data sources, and the collation specified in collation name is used for non-SQL Server data sources.

    If false, distributed queries will always use the default collation of the local server, while collation name and the collation of remote columns are ignored. The default is false.

  • Collation Name
    Specifies the name of the collation used by the remote data source if use remote collation is true and the data source isn't a SQL Server data source. The name must be one of the collations supported by SQL Server.

    Use this option when accessing an OLE DB data source other than SQL Server, but whose collation matches one of the SQL Server collations.

    The linked server must support a single collation to be used for all columns in that server. Don't set this option if the linked server supports multiple collations within a single data source, or if the linked server's collation can't be determined to match one of the SQL Server collations.

  • Connection Timeout
    Time-out value in seconds for connecting to a linked server.

    If 0, use the sp_configure default remote login timeout option value.

  • Query Timeout
    Time-out value in seconds for queries against a linked server.

    If 0, use the sp_configure default remote query timeout option value.

  • Enable Promotion of Distributed Transactions
    Use this option to protect the actions of a server-to-server procedure through a Microsoft Distributed Transaction Coordinator (MS DTC) transaction. When this option is TRUE, calling a remote stored procedure starts a distributed transaction and enlists the transaction with MS DTC. For more information, see sp_serveroption (Transact-SQL).

Save the linked server

Select OK.

View or edit linked server provider options in SSMS

All providers don't have the same options available. For example, some types of data have indexes available and some might not. Use this dialog box to help SQL Server understand the capabilities of the provider. SQL Server installs some common data providers, however when the product providing the data changes, the provider installed by SQL Server might not support all the newest features. The best source of information about the capabilities of the product providing the data is the documentation for that product.
To open the linked server Providers Options page in SSMS:

  1. Open Object Explorer.
  2. Expand Server Objects.
  3. Expand Linked Servers.
  4. Expand Providers.
  5. Right-click a provider and select Properties.

Provider options are defined as follows:

  • Dynamic parameter
    Indicates that the provider allows '?' parameter marker syntax for parameterized queries. Set this option only if the provider supports the ICommandWithParameters interface and supports a '?' as the parameter marker. Setting this option allows SQL Server to execute parameterized queries against the provider. The ability to execute parameterized queries against the provider can result in better performance for certain queries.

  • Nested queries
    Indicates that the provider allows nested SELECT statements in the FROM clause. Setting this option allows SQL Server to delegate certain queries to the provider that require nesting SELECT statements in the FROM clause.

  • Level zero only
    Only level 0 OLE DB interfaces are invoked against the provider.

  • Allow inprocess

    SQL Server allows the provider to be instantiated as an in-process server. When this option isn't set, the default behavior is to instantiate the provider outside the SQL Server process. Instantiating the provider outside the SQL Server process protects the SQL Server process from errors in the provider. When the provider is instantiated outside the SQL Server process, updates or inserts referencing long columns (text, ntext, or image) aren't allowed.

  • Non transacted updates
    SQL Server allows updates, even if ITransactionLocal isn't available. If this option is enabled, updates against the provider aren't recoverable, because the provider doesn't support transactions.

  • Index as access path
    SQL Server attempts to use indexes of the provider to fetch data. By default, indexes are used only for metadata and are never opened

  • Disallow ad hoc access
    SQL Server doesn't allow ad hoc access through the OPENROWSET and OPENDATASOURCE functions against the OLE DB provider. When this option isn't set, SQL Server also doesn't allow ad hoc access.

  • Supports 'Like' operator
    Indicates that the provider supports queries using the LIKE key word.

Create a linked server with Transact-SQL

To create a linked server by using Transact-SQL, use the sp_addlinkedserver (Transact-SQL), CREATE LOGIN (Transact-SQL), and sp_addlinkedsrvlogin (Transact-SQL) statements.

This example creates a linked server to another instance of SQL Server using Transact-SQL:

  1. In Query Editor, enter the following Transact-SQL command to link to an instance of SQL Server named SRVR002\ACCTG:

    USE [master]  
GO  
EXEC master.dbo.sp_addlinkedserver   
    @server = N'SRVR002\ACCTG',   
    @srvproduct=N'SQL Server';  
GO

  2. Execute the following code to configure the linked server to use the domain credentials of the login that is using the linked server.

    EXEC master.dbo.sp_addlinkedsrvlogin   
    @rmtsrvname = N'SRVR002\ACCTG',   
    @locallogin = NULL ,   
    @useself = N'True';  
GO

Follow Up: Steps to take after you create a linked server

The following steps help you validate a linked server.

Test the linked server

Considering either of the following two approaches to test a linked server's authentication in your current security context.

  • To test the ability to connect to a linked server in SSMS, browse to the linked server in Object Explorer, right-click the linked server and then select Test Connection.

  • To test the ability to connect to a linked server in T-SQL, execute a simple SELECT statement, for example, to retrieve basic database catalog information. This example returns the names of the databases on the linked server.

    SELECT name FROM [SRVR002\ACCTG].master.sys.databases;  
GO

Join tables from a linked server

Use four-part names to refer to an object on a linked server. Execute the following code to return a list of all logins on the local server and their matching logins on the linked server.

SELECT local.name AS LocalLogins, linked.name AS LinkedLogins  
FROM master.sys.server_principals AS local  
LEFT JOIN [SRVR002\ACCTG].master.sys.server_principals AS linked  
     ON local.name = linked.name;  
GO

When NULL is returned for the linked server login, it indicates that the login doesn't exist on the linked server. These logins won't be able to use the linked server unless the linked server is configured to pass a different security context or the linked server accepts anonymous connections.

Linked servers with Azure SQL Managed Instance

If you're using Azure SQL Managed Instance, see the following examples from sp_addlinkedserver (Transact-SQL):

Next steps

Learn more about managing linked servers in these articles: