sp_addlinkedserver (Transact-SQL)

Gilt für: SQL Server (alle unterstützten Versionen) Azure SQL Managed Instance

Erstellt einen Verbindungsserver. Ein Verbindungsserver ermöglicht den Zugriff auf verteilte, heterogene Abfragen für OLE DB-Datenquellen. Nach der Erstellung eines verknüpften Servers mithilfe sp_addlinkedservervon verteilten Abfragen kann dieser Server ausgeführt werden. Wenn der verknüpfte Server als Instanz von SQL Server definiert ist, können remote gespeicherte Prozeduren ausgeführt werden.

ThemenlinksymbolTransact-SQL-Syntaxkonventionen

Syntax

sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ]   
     [ , [ @provider= ] 'provider_name' ]  
     [ , [ @datasrc= ] 'data_source' ]   
     [ , [ @location= ] 'location' ]   
     [ , [ @provstr= ] 'provider_string' ]   
     [ , [ @catalog= ] 'catalog' ]   

Argumente

[ @server = ] 'server'

Der Name des zu erstellenden Verbindungsservers. Der Argumentserver ist sysname, ohne Standard.

[ @srvproduct = ] 'product_name'

Der Produktname der OLE DB-Datenquelle, die als Verbindungsserver hinzugefügt werden soll. Der Wert product_name ist "nvarchar(128)" mit einem Standardwert von NULL. Wenn der Wert SQL Server ist, müssen provider_name, data_source, Standort, provider_string und Katalog nicht angegeben werden.

[ @provider = ] 'provider_name'

Der eindeutige Programmbezeichner (Programmatic Identifier, PROGID) des OLE DB-Anbieters, der dieser Datenquelle entspricht. Die provider_name muss für den angegebenen OLE DB-Anbieter eindeutig sein, der auf dem aktuellen Computer installiert ist. Der Wert provider_name ist nvarchar(128), wobei null standardmäßig null ist; Wenn provider_name jedoch nicht angegeben wird, wird SQLNCLI verwendet.

Hinweis

Die Verwendung von SQLNCLI bewirkt eine Umleitung von SQL Server zur neuesten Version des SQL Server Native Client OLE DB-Anbieters. Es wird vorausgesetzt, dass der OLE DB-Anbieter mit der angegebenen PROGID in der Registrierung registriert ist.

Wichtig

Der vorherige Microsoft OLE DB-Anbieter für SQL Server (SQLOLEDB) und SQL Server Native Client OLE DB-Anbieter (SQLNCLI) bleiben als veraltet markiert und sollten nicht mehr für neue Bereitstellungen verwendet werden. Verwenden Sie stattdessen den neuen Microsoft OLE DB-Treiber für SQL Server (MSOLEDBSQL), der mit den aktuellsten Serverfeatures aktualisiert wird.

[ @datasrc = ] 'data_source'

Ist der Name der Datenquelle, wie vom OLE DB-Anbieter interpretiert. Der Wert data_source ist nvarchar(4000). data_source wird als DBPROP_INIT_DATASOURCE-Eigenschaft übergeben, um den OLE DB-Anbieter zu initialisieren.

[ @location = ] 'location'

Der Speicherort der Datenbank im vom OLE DB-Anbieter unterstützten Format. Der Wertspeicherort ist nvarchar(4000), wobei der Standardwert null ist. Der Argumentspeicherort wird als DBPROP_INIT_LOCATION-Eigenschaft übergeben, um den OLE DB-Anbieter zu initialisieren.

[ @provstr = ] 'provider_string'

Die für den OLE DB-Anbieter spezifische Verbindungszeichenfolge, die eine eindeutige Datenquelle identifiziert. Der Wert provider_string ist nvarchar(4000), wobei der Standardwert NULL ist. Das Argument provstr wird entweder an IDataInitialize übergeben oder als DBPROP_INIT_PROVIDERSTRING-Eigenschaft festgelegt, um den OLE DB-Anbieter zu initialisieren.

Wenn der verknüpfte Server mit dem SQL Server Native Client OLE DB-Anbieter erstellt wird, kann die Instanz mithilfe des SERVER-Schlüsselworts angegeben werden, um SERVER=servername\\instancename eine bestimmte Instanz von SQL Server anzugeben. Der Servername ist der Name des Computers, auf dem SQL Server ausgeführt wird, und der Instanzname ist der Name der bestimmten Instanz von SQL Server, mit der der Benutzer verbunden wird.

Hinweis

Der Zugriff auf eine gespiegelte Datenbank ist nur dann möglich, wenn eine Verbindungszeichenfolge den Datenbanknamen enthält. Dieser Name ist notwendig, um Failoverversuche des Datenzugriffsanbieters zu ermöglichen. Die Datenbank kann im parameter @provstr oder @catalog angegeben werden. Optional kann in der Verbindungszeichenfolge auch ein Failoverpartnername angegeben werden.

[ @catalog = ] 'Katalog'

Ist der Katalog, der verwendet werden soll, wenn eine Verbindung mit dem OLE DB-Anbieter hergestellt wird. Der Wertkatalog ist sysname, mit einem Standardwert von NULL. Der Argumentkatalog wird als DBPROP_INIT_CATALOG-Eigenschaft übergeben, um den OLE DB-Anbieter zu initialisieren. Wenn der verknüpfte Server für eine Instanz von SQL Server definiert wird, verweist der Katalog auf die Standarddatenbank, der der verknüpfte Server zugeordnet ist.

Rückgabecodewerte

„0“ (erfolgreich) oder „1“ (fehlerhaft)

Resultsets

Keine.

Hinweise

Die folgende Tabelle zeigt die Einrichtungsmöglichkeiten eines Verbindungsservers für Datenquellen, auf die über OLE DB zugegriffen werden kann. Für die Einrichtung eines Verbindungsservers für eine bestimmte Datenquelle gibt es mehrere Möglichkeiten; für die einzelnen Datenquellentypen sind möglicherweise mehrere Zeilen vorhanden. In dieser Tabelle sind auch die Parameterwerte aufgeführt, die sp_addlinkedserver zum Einrichten des verknüpften Servers verwendet werden sollen.

OLE DB-Remotedatenquelle OLE DB-Anbieter product_name provider_name data_source location provider_string catalog
SQL Server Microsoft SQL Server Native Client OLE DB-Anbieter SQL Server 1 (Standard)
SQL Server Microsoft SQL Server Native Client OLE DB-Anbieter SQLNCLI Netzwerkname von SQL Server (für Standardinstanz) Datenbankname (optional)
SQL Server Microsoft SQL Server Native Client OLE DB-Anbieter SQLNCLI Servername\Instanzname (für bestimmte Instanzen) Datenbankname (optional)
Oracle, Version 8 und höher Oracle-Anbieter für OLE DB Any OraOLEDB.Oracle Alias für die Oracle-Datenbank
Access/Jet Microsoft OLE DB-Anbieter für Jet Any Microsoft.Jet.OLEDB.4.0 Vollständiger Pfad der Jet-Datenbankdatei
ODBC-Datenquelle (ODBC data source) Microsoft OLE DB-Anbieter für ODBC Any MSDASQL System-DSN der ODBC-Datenquelle
ODBC-Datenquelle (ODBC data source) Microsoft OLE DB-Anbieter für ODBC Any MSDASQL ODBC-Verbindungszeichenfolge
Dateisystem Microsoft OLE DB-Anbieter für den Indexierungsdienst Any MSIDXS Katalogname von Indexdienstleistung
Microsoft Excel-Kalkulationstabelle Microsoft OLE DB-Anbieter für Jet Any Microsoft.Jet.OLEDB.4.0 Vollständiger Pfad der Excel-Datei Excel 5.0
IBM DB2-Datenbank Microsoft OLE DB-Anbieter für DB2 Any DB2OLEDB Siehe Microsoft OLE DB-Anbieter für DB2 Dokumentation. Katalogname der DB2-Datenbank

1 Durch die Einrichtung eines verknüpften Servers ist der Name des verknüpften Servers identisch mit dem Netzwerknamen der Remoteinstanz von SQL Server. Verwenden Sie data_source , um den Server anzugeben.

2 "Any" gibt an, dass der Produktname alles sein kann.

Der Microsoft SQL Server Native Client OLE DB-Anbieter ist der Anbieter, der mit SQL Server verwendet wird, wenn kein Anbietername angegeben wird oder SQL Server als Produktname angegeben wird. Selbst wenn Sie den älteren Anbieternamen, SQLOLEDB, angeben, wird er beim persistenten Speichern im Katalog in SQLNCLI geändert.

Die data_source, Speicherort, provider_string und Katalogparameter identifizieren die Datenbank oder Datenbanken, auf die der verknüpfte Server verweist. Falls einer dieser Parameter den Wert NULL hat, wird die entsprechende OLE DB-Initialisierungseigenschaft nicht festgelegt.

Verwenden Sie in einer Clusterumgebung, wenn Sie Dateinamen angeben, um auf OLE DB-Datenquellen zu verweisen, den UNC-Namen (Universal Naming Convention) oder ein freigegebenes Laufwerk, um den Speicherort anzugeben.

Die gespeicherte Prozedur sp_addlinkedserver kann nicht innerhalb einer benutzerdefinierten Transaktion ausgeführt werden.

Wichtig

Azure SQL Managed Instance unterstützt derzeit nur SQL Server, SQL-Datenbank und andere SQL Managed Instance als Remotedatenquellen.

Wichtig

Wenn ein verknüpfter Server mithilfe sp_addlinkedservererstellt wird, wird für alle lokalen Anmeldungen eine standardmäßige Selbstzuordnung hinzugefügt. Für nicht SQL Server Anbieter können SQL Server authentifizierte Anmeldungen möglicherweise zugriff auf den Anbieter unter dem SQL Server-Dienstkonto erhalten. Administratoren sollten eventuell sp_droplinkedsrvlogin <linkedserver_name>, NULL verwenden, um die globale Zuordnung zu entfernen.

Berechtigungen

Die sp_addlinkedserver Anweisung erfordert die BERECHTIGUNG ALTER ANY LINKED SERVER . (Das Dialogfeld SQL Server Management Studio Neuen verknüpften Server wird auf eine Weise implementiert, die die Mitgliedschaft in der sysadmin Fixed-Serverrolle erfordert.)

Beispiele

A. Verwenden des Microsoft SQL Server OLE DB-Anbieters

Im folgenden Beispiel wird der Verbindungsserver SEATTLESales erstellt. Der Produktname lautet SQL Server, und es wird kein Anbietername verwendet.

USE master;  
GO  
EXEC sp_addlinkedserver   
   N'SEATTLESales',  
   N'SQL Server';  
GO  

Im folgenden Beispiel wird ein verknüpfter Server S1_instance1 in einer Instanz von SQL Server mithilfe des SQL Server OLE DB-Treibers erstellt.

EXEC sp_addlinkedserver     
   @server=N'S1_instance1',   
   @srvproduct=N'',  
   @provider=N'MSOLEDBSQL',   
   @datasrc=N'S1\instance1';  

Im folgenden Beispiel wird ein verknüpfter Server S1_instance1 in einer Instanz von SQL Server mithilfe des SQL Server Native Client OLE DB-Anbieters erstellt.

Wichtig

Der SQL Server Native Client OLE DB-Anbieter (SQLNCLI) bleibt als veraltet markiert und sollte nicht mehr für neue Bereitstellungen verwendet werden. Verwenden Sie stattdessen den neuen Microsoft OLE DB-Treiber für SQL Server (MSOLEDBSQL), der mit den aktuellsten Serverfeatures aktualisiert wird.

EXEC sp_addlinkedserver     
   @server=N'S1_instance1',   
   @srvproduct=N'',  
   @provider=N'SQLNCLI',   
   @datasrc=N'S1\instance1';  

B. Verwenden des Microsoft OLE DB-Anbieters für Microsoft Access

Der Microsoft.Jet.OLEDB.4.0-Anbieter stellt eine Verbindung mit Microsoft Access-Datenbanken her, die das 2002-2003-Format verwenden. Im folgenden Beispiel wird der Verbindungsserver SEATTLE Mktg erstellt.

Hinweis

In diesem Beispiel wird davon ausgegangen, dass sowohl Microsoft Access als auch die Beispieldatenbank Northwind installiert sind und dass sich die Northwind Datenbank in C:\Msoffice\Access\Samples auf demselben Server wie die SQL Server-Instanz befindet.

EXEC sp_addlinkedserver   
   @server = N'SEATTLE Mktg',   
   @provider = N'Microsoft.Jet.OLEDB.4.0',   
   @srvproduct = N'OLE DB Provider for Jet',  
   @datasrc = N'C:\MSOffice\Access\Samples\Northwind.mdb';  
GO  

C. Verwenden des Microsoft OLE DB-Anbieters für ODBC mit dem parameter data_source

Im folgenden Beispiel wird ein verknüpfter Server erstellt SEATTLE Payroll , der den Microsoft OLE DB-Anbieter für ODBC (MSDASQL) und den data_source-Parameter verwendet.

Hinweis

Der angegebene ODBC-Datenquellenname muss vor der Verwendung des Verbindungsservers auf dem Server als System-DSN definiert werden.

EXEC sp_addlinkedserver   
   @server = N'SEATTLE Payroll',   
   @srvproduct = N'',  
   @provider = N'MSDASQL',   
   @datasrc = N'LocalServer';  
GO  

D: Verwenden des Microsoft OLE DB-Anbieters für Excel-Tabellentabelle

Um eine verknüpfte Serverdefinition mithilfe des Microsoft OLE DB-Anbieters für Jet zu erstellen, um auf eine Excel-Tabelle im Format 1997 - 2003 zuzugreifen, erstellen Sie zunächst einen benannten Bereich in Excel, indem Sie die Spalten und Zeilen des zu auswählenden Excel-Arbeitsblatts angeben. Auf den Namen des Bereichs kann dann als Tabellenname in einer verteilten Abfrage verwiesen werden.

EXEC sp_addlinkedserver 'ExcelSource',  
   'Jet 4.0',  
   'Microsoft.Jet.OLEDB.4.0',  
   'c:\MyData\DistExcl.xls',  
   NULL,  
   'Excel 5.0';  
GO  

Um auf Daten zugreifen zu können, die sich in einer Excel-Kalkulationstabelle befinden, ordnen Sie einem Zellenbereich einen Namen zu. Die folgende Abfrage kann für den Zugriff auf den angegebenen benannten Bereich SalesData als Tabelle mithilfe des zuvor eingerichteten Verbindungsservers verwendet werden.

SELECT *  
   FROM ExcelSource...SalesData;  
GO  

Wenn SQL Server unter einem Domänenkonto ausgeführt wird, das Zugriff auf eine Remotefreigabe hat, kann ein UNC-Pfad anstelle eines zugeordneten Laufwerks verwendet werden.

EXEC sp_addlinkedserver 'ExcelShare',  
   'Jet 4.0',  
   'Microsoft.Jet.OLEDB.4.0',  
   '\\MyServer\MyShare\Spreadsheets\DistExcl.xls',  
   NULL,  
   'Excel 5.0';  

E. Verwenden des Microsoft OLE DB-Anbieters für Jet zum Zugriff auf eine Textdatei

Im folgenden Beispiel wird ein Verbindungsserver für den direkten Zugriff auf Textdateien erstellt, ohne die Dateien als Tabellen in einer MDB-Datei von Microsoft Access zu verknüpfen. Der Anbieter ist Microsoft.Jet.OLEDB.4.0, und die Anbieterzeichenfolge lautet Text.

Die Datenquelle ist der vollständige Pfad des Verzeichnisses mit den Textdateien. Eine Datei namens schema.ini, die die Struktur der Textdateien beschreibt, muss im selben Verzeichnis wie die Textdateien vorhanden sein. Weitere Informationen zum Erstellen einer schema.ini-Datei finden Sie in der Jet-Datenbankmoduldokumentation.

Erstellen Sie zunächst einen verknüpften Server.

EXEC sp_addlinkedserver txtsrv, N'Jet 4.0',   
   N'Microsoft.Jet.OLEDB.4.0',  
   N'c:\data\distqry',  
   NULL,  
   N'Text';  

Einrichten von Anmeldezuordnungen.

EXEC sp_addlinkedsrvlogin txtsrv, FALSE, Admin, NULL;  

Listet die Tabellen auf dem verknüpften Server auf.

EXEC sp_tables_ex txtsrv;  

Abfragen einer der Tabellen in diesem Fall file1#txtmithilfe eines vierteiligen Namens.

SELECT * FROM txtsrv...[file1#txt];  

F. Verwenden des Microsoft OLE DB-Anbieter für DB2

Im folgenden Beispiel wird der Verbindungsserver DB2 erstellt, der Microsoft OLE DB Provider for DB2 verwendet.

EXEC sp_addlinkedserver  
   @server=N'DB2',  
   @srvproduct=N'Microsoft OLE DB Provider for DB2',  
   @catalog=N'DB2',  
   @provider=N'DB2OLEDB',  
   @provstr=N'Initial Catalog=PUBS;  
       Data Source=DB2;  
       HostCCSID=1252;  
       Network Address=XYZ;  
       Network Port=50000;  
       Package Collection=admin;  
       Default Schema=admin;';  

G. Hinzufügen einer Azure SQL Datenbank als verknüpfter Server für die Verwendung mit verteilten Abfragen in Cloud- und lokalen Datenbanken

Sie können eine Azure SQL Datenbank als verknüpfter Server hinzufügen und dann mit verteilten Abfragen verwenden, die die lokalen und Clouddatenbanken umfassen. Dies ist eine Komponente für Datenbankhybridlösungen, die lokale Unternehmensnetzwerke und die Azure-Cloud umfassen.

Das SQL Server Box-Produkt enthält das verteilte Abfragefeature, mit dem Sie Abfragen schreiben können, um Daten aus lokalen Datenquellen und Daten aus Remotequellen (einschließlich Daten aus nicht-SQL Server Datenquellen) zu kombinieren, die als verknüpfte Server definiert sind. Jede Azure SQL Datenbank (außer der Datenbank des logischen Servers) kann als einzelner verknüpfter Server master hinzugefügt und dann direkt in Ihren Datenbankanwendungen als andere Datenbank verwendet werden.

Die Vorteile der Verwendung Azure SQL Datenbank umfassen Die Verwaltung, hohe Verfügbarkeit, Skalierbarkeit, Arbeiten mit einem vertrauten Entwicklungsmodell und ein relationales Datenmodell. Die Anforderungen Ihrer Datenbankanwendung bestimmen, wie sie Azure SQL Datenbank in der Cloud verwenden würde. Sie können alle Daten gleichzeitig in Azure SQL Datenbank verschieben oder einige Ihrer Daten schrittweise verschieben, während die verbleibenden Daten lokal bleiben. Für eine solche Hybriddatenbankanwendung kann Azure SQL Datenbank jetzt als verknüpfte Server hinzugefügt werden, und die Datenbankanwendung kann verteilte Abfragen stellen, um Daten aus Azure SQL Datenbank und lokalen Datenquellen zu kombinieren.

Nachfolgend finden Sie ein einfaches Beispiel, das erläutert, wie Sie eine Verbindung mit einer Azure SQL Datenbank herstellen, indem Sie verteilte Abfragen verwenden.

Fügen Sie zunächst eine Azure SQL Datenbank als verknüpfter Server hinzu, indem Sie SQL Server Native Client verwenden.

EXEC sp_addlinkedserver  
  @server='LinkedServerName', 
  @srvproduct='',       
  @provider='sqlncli', 
  @datasrc='ServerName.database.windows.net',   
  @location='',  
  @provstr='',  
  @catalog='DatabaseName'; 

Fügen Sie Anmeldeinformationen und Optionen zu diesem verknüpften Server hinzu.

EXEC sp_addlinkedsrvlogin  
  @rmtsrvname = 'LinkedServerName',  
  @useself = 'false',  
  @rmtuser = 'LoginName',
  @rmtpassword = 'myPassword';

EXEC sp_serveroption 'LinkedServerName', 'rpc out', true;  

Verwenden Sie nun den verknüpften Server, um Abfragen mit vierteiligen Namen auszuführen, auch um eine neue Tabelle zu erstellen und Daten einzufügen.

EXEC ('CREATE TABLE SchemaName.TableName(col1 int not null CONSTRAINT PK_col1 PRIMARY KEY CLUSTERED (col1) )') at LinkedServerName;  
EXEC ('INSERT INTO SchemaName.TableName VALUES(1),(2),(3)') at LinkedServerName; 

Abfragen der Daten mit vierteiligen Namen:

SELECT * FROM LinkedServerName.DatabaseName.SchemaName.TableName; 

H. Erstellen von SQL Managed Instance-Verbindungsservern mit Azure AD-Authentifizierung für verwaltete Identitäten

Führen Sie zum Erstellen eines verknüpften Servers mit verwalteter Identitätsauthentifizierung die folgende T-SQL-Datei aus. Die Authentifizierungsmethode verwendet ActiveDirectoryMSI im @provstr Parameter. Berücksichtigen Sie optional die Verwendung @locallogin = NULL aller lokalen Anmeldungen.

EXEC master.dbo.sp_addlinkedserver
@server     = N'MyLinkedServer',
@srvproduct = N'',
@provider   = N'MSOLEDBSQL',
@provstr    = N'Server=mi.35e5bd1a0e9b.database.windows.net,1433;Authentication=ActiveDirectoryMSI;';

EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'MyLinkedServer',
@useself    = N'False',
@locallogin = N'user1@domain1.com';  

Wenn Azure SQL Managed Instance verwaltete Identität (früher als verwaltete Dienstidentität bezeichnet) als Anmeldung zu einer remote verwalteten Instanz hinzugefügt wird, ist die Managed Identity-Authentifizierung mit verknüpftem Server möglich, der wie im vorherigen Beispiel erstellt wurde. Sowohl system zugewiesene als auch benutzer zugewiesene verwaltete Identitäten werden unterstützt.

Wenn die primäre Identität festgelegt ist, wird sie verwendet, andernfalls wird die verwaltete Identität des Systems verwendet. Wenn die verwaltete Identität mit demselben Namen neu erstellt wird, muss auch die Anmeldung bei der Remoteinstanz neu erstellt werden, da die neue ID der verwalteten Identität und verwaltete Instanz Dienstprinzipal-SID nicht mehr übereinstimmen. Um die übereinstimmung dieser beiden Werte zu überprüfen, konvertieren Sie SID in Anwendungs-ID mit der folgenden Abfrage.

SELECT convert(uniqueidentifier, sid) as AADApplicationID
FROM sys.server_principals
WHERE name = '<managed_instance_name>';

I. Erstellen von SQL Managed Instance-Verbindungsservern mit Passthrough-Azure AD-Authentifizierung

Führen Sie zum Erstellen eines verknüpften Servers mit Pass-Through-Authentifizierung nach T-SQL aus.

EXEC master.dbo.sp_addlinkedserver
@server     = N'MyLinkedServer',
@srvproduct = N'',
@provider   = N'MSOLEDBSQL',
@datasrc    = N'mi.35e5bd1a0e9b.database.windows.net,1433';

Bei der Pass-Through-Authentifizierung wird der Sicherheitskontext der lokalen Anmeldung an eine Remoteinstanz übertragen. Die Pass-Through-Authentifizierung erfordert, dass der AAD-Prinzipal sowohl für lokale als auch remote Azure SQL Managed Instance hinzugefügt werden soll. Beide verwaltete Instanzen müssen sich in einer Serververtrauenswürdigen Gruppe befinden. Wenn die Anforderungen erfüllt sind, kann sich der Benutzer bei einer lokalen Instanz anmelden und die Remoteinstanz über das verknüpfte Serverobjekt abfragen.

Weitere Informationen