sp_addlinkedserver (Transact-SQL)

Gilt für: SQL Server Azure SQL Managed Instance

Erstellt einen Verbindungsserver. Ein verknüpfter Server bietet Zugriff auf verteilte, heterogene Abfragen für OLE DB-Datenquellen. Nachdem ein verknüpfter Server mithilfe sp_addlinkedservereines verknüpften Servers erstellt wurde, können verteilte Abfragen für diesen Server ausgeführt werden. Wenn der verknüpfte Server als Instanz von SQL Server definiert ist, können remote gespeicherte Prozeduren ausgeführt werden.

Transact-SQL-Syntaxkonventionen

Syntax

sp_addlinkedserver
    [ @server = ] N'server'
    [ , [ @srvproduct = ] N'srvproduct' ]
    [ , [ @provider = ] N'provider' ]
    [ , [ @datasrc = ] N'datasrc' ]
    [ , [ @location = ] N'location' ]
    [ , [ @provstr = ] N'provstr' ]
    [ , [ @catalog = ] N'catalog' ]
    [ , [ @linkedstyle = ] linkedstyle ]
[ ; ]

Argumente

[ @server = ] N'server'

Der Name des zu erstellenden verknüpften Servers. @server ist "sysname" ohne Standard.

[ @srvproduct = ] N'srvproduct'

Der Produktname der OLE DB-Datenquelle, die als verknüpfter Server hinzugefügt werden soll. @srvproduct ist nvarchar(128), mit einem Standardwert von NULL. Wenn der Wert SQL Serverist , @provider, @datasrc, @location, @provstr und @catalog nicht angegeben werden müssen.

[ @provider = ] N'provider'

Der eindeutige programmgesteuerte Bezeichner (PROGID) des OLE DB-Anbieters, der dieser Datenquelle entspricht. Die @provider muss für den angegebenen OLE DB-Anbieter eindeutig sein, der auf dem aktuellen Computer installiert ist. @provider ist nvarchar(128), mit einem Standardwert von NULL.

• In SQL Server 2019 (15.x) und früheren Versionen wird verwendet, wenn @provider nicht angegeben SQLNCLI wird. Die Verwendung SQLNCLI leitet SQL Server an die neueste Version des OLE DB-Anbieters des SQL Server Native Client um. Es wird vorausgesetzt, dass der OLE DB-Anbieter mit der angegebenen PROGID in der Registrierung registriert ist. Anstelle von SQLNCLI, MSOLEDBSQL wird empfohlen.

• Ab SQL Server 2022 (16.x) müssen Sie einen Anbieternamen angeben. MSOLEDBSQL wird empfohlen. Wenn Sie @provider weglassen, können Unerwartetes Verhalten auftreten.

[ @datasrc = ] N'datasrc'

Der Name der Datenquelle, wie vom OLE DB-Anbieter interpretiert. @datasrc ist nvarchar(4000), mit einem Standardwert von NULL. @datasrc wird als DBPROP_INIT_DATASOURCE Eigenschaft übergeben, um den OLE DB-Anbieter zu initialisieren.

[ @location = ] N'location'

Der Speicherort der Datenbank, wie vom OLE DB-Anbieter interpretiert. @location ist nvarchar(4000), mit einem Standardwert von NULL. @location wird als DBPROP_INIT_LOCATION Eigenschaft übergeben, um den OLE DB-Anbieter zu initialisieren.

[ @provstr = ] N'provstr'

Der anbieterspezifische OLE DB-Verbindungszeichenfolge, der eine eindeutige Datenquelle identifiziert. @provstr ist nvarchar(4000), mit einem Standardwert von NULL. 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 OLE DB-Anbieter des SQL Server Native Client 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 spezifischen Instanz von SQL Server, mit der der Benutzer verbunden wird.

• 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 @provstr - oder @catalog-Parameter angegeben werden. Optional kann in der Verbindungszeichenfolge auch ein Failoverpartnername angegeben werden.

• Wenn Sie aus einer lokalen Anmeldung oder einer Anmeldung ausgeführt werden sp_addlinkedserver , die nicht Teil der Sysadmin-Rolle ist, wird möglicherweise die folgende Fehlermeldung angezeigt:

  Access to the remote server is denied because no login-mapping exists.
  

  Um dieses Problem zu beheben, fügen Sie den User ID Parameter zu Ihrem Verbindungszeichenfolge hinzu. Im folgenden Beispiel myUser wird die Benutzer-ID an die Verbindungszeichenfolge übergeben:

  EXEC master.dbo.sp_addlinkedserver @server = N'LinkServerName',
      @provider = N'SQLNCLI',
      @srvproduct = 'MS SQL Server',
      @provstr = N'SERVER=serverName\InstanceName;User ID=myUser'
  
  EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LinkServerName',
      @locallogin = NULL,
      @useself = N'False',
      @rmtuser = N'myUser',
      @rmtpassword = N'*****'
  

  Weitere Informationen finden Sie unter Zugriff auf den Remoteserver, da keine Anmeldezuordnung vorhanden ist.

[ @catalog = ] N'catalog'

Der Katalog, der verwendet werden soll, wenn eine Verbindung mit dem OLE DB-Anbieter hergestellt wird. @catalog ist "sysname" mit der Standardeinstellung "NULL. @catalog 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 ist, bezieht sich der Katalog auf die Standarddatenbank, der der verknüpfte Server zugeordnet ist.

[ @linkedstyle = ] linkedstyle

Nur für Informationszwecke identifiziert. Wird nicht unterstützt. Zukünftige Kompatibilität wird nicht sichergestellt.

Rückgabecodewerte

0 (erfolgreich) oder 1 Fehler.

Resultset

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.

¹ Auf diese Weise erzwingt das Einrichten eines verknüpften Servers den Namen des verknüpften Servers mit dem Netzwerknamen der Remoteinstanz von SQL Server. Verwenden Sie @datasrc , um den Server anzugeben.

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

Der OLE DB-Anbieter von SQL Server Native Client ist der Anbieter, der mit SQL Server verwendet wird, wenn kein Anbietername angegeben wird oder SQL Server als Produktname angegeben wird. Auch wenn Sie den älteren Anbieternamen , SQLOLEDB, angeben, ändert es sich in SQLNCLI, wenn er im Katalog beibehalten wird.

Die Parameter @datasrc, @location, @provstr und @catalog identifizieren die Datenbank oder Datenbanken, auf die der verknüpfte Server verweist. Wenn einer dieser Parameter lautet NULL, 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.

Berechtigungen

Für die sp_addlinkedserver Anweisung ist die ALTER ANY LINKED SERVER Berechtigung erforderlich. (Das SQL Server Management Studio Das Dialogfeld "Neuer verknüpfter Server " wird auf eine Weise implementiert, die eine Mitgliedschaft in der festen Serverrolle "sysadmin " erfordert.)

Beispiele

A. Verwenden des OLE DB-Anbieters von Microsoft SQL Server

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 mithilfe des SQL Server OLE DB-Treibers ein verknüpfter Server S1_instance1 in einer SQL Server-Instanz von SQL Server erstellt.

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

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

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.

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 datasrc Parameter

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

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-Kalkulationstabelle

Zum Erstellen einer verknüpften Serverdefinition mithilfe des Microsoft OLE DB-Anbieters für Jet für den Zugriff auf eine Excel-Tabelle im Format 1997 - 2003 erstellen Sie zuerst einen benannten Bereich in Excel, indem Sie die Spalten und Zeilen des zu markierenden 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 anstelle eines zugeordneten Laufwerks ein UNC-Pfad 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 für den 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 Datenbank-Engine-Dokumentation.

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;

Listen Sie die Tabellen auf dem verknüpften Server auf.

EXEC sp_tables_ex txtsrv;

Fragen Sie eine der Tabellen in diesem Fall file1#txtmit einem vierteiligen Namen ab.

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

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

Im folgenden Beispiel wird ein verknüpfter Server erstelltDB2, der den Microsoft OLE DB-Anbieter für 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üpften 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-Feldprodukt enthält das Feature für verteilte Abfragen, 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 (mit Ausnahme der Datenbank des master logischen Servers) kann als einzelner verknüpfter Server hinzugefügt und dann direkt in Ihren Datenbankanwendungen wie jede andere Datenbank verwendet werden.

Die Vorteile der Verwendung von Azure SQL-Datenbank umfassen Verwaltbarkeit, 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 Ihre 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 können Azure SQL-Datenbank jetzt als verknüpfte Server hinzugefügt werden, und die Datenbankanwendung kann verteilte Abfragen ausgeben, um Daten aus Azure SQL-Datenbank und lokalen Datenquellen zu kombinieren.

Nachfolgend finden Sie ein Beispiel, in dem erläutert wird, wie Sie mithilfe verteilter Abfragen eine Verbindung mit einer Azure SQL-Datenbank herstellen.

Fügen Sie zunächst eine Azure SQL-Datenbank als verknüpften Server mithilfe des SQL Server Native Client hinzu.

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

Fügen Sie diesem verknüpften Server Anmeldeinformationen und Optionen 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 Azure SQL verwaltete Instanz verknüpften Server mit verwalteter Identitätsauthentifizierung

Um einen verknüpften Server mit verwalteter Identitätsauthentifizierung zu erstellen, führen Sie den folgenden T-SQL-Code aus, der durch Ihre eigene verwaltete SQL-Instanz ersetzt <managed_instance> wird. Die Authentifizierungsmethode wird im parameter @provstr verwendetActiveDirectoryMSI. Ziehen Sie optional die Verwendung in @locallogin = NULL Betracht, um alle lokalen Anmeldungen zuzulassen.

EXEC master.dbo.sp_addlinkedserver
    @server = N'MyLinkedServer',
    @srvproduct = N'',
    @provider = N'MSOLEDBSQL',
    @provstr = N'Server=<mi_name>.<dns_zone>.database.windows.net,1433;Authentication=ActiveDirectoryMSI;';

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

Um die Authentifizierung mit verwalteten Identitäten zu aktivieren, muss eine dem Azure SQL-verwaltete Instanz zugewiesene verwaltete Identität als Anmeldung zur remoteverwalteten Instanz hinzugefügt werden. Es werden sowohl systemseitig als auch benutzerseitig zugewiesene verwaltete Identitäten unterstützt.

Wenn eine primäre Identität festgelegt ist, wird sie verwendet, andernfalls wird die vom System zugewiesene verwaltete Identität verwendet. Wenn die verwaltete Identität mit demselben Namen neu erstellt wird, muss auch die Anmeldung in der Remoteinstanz neu erstellt werden, da die neue ID der verwalteten Identitätsanwendung und sql verwaltete Instanz Dienstprinzipal-SID nicht mehr übereinstimmen. Um zu überprüfen, ob diese beiden Werte übereinstimmen, konvertieren Sie die SID mit der folgenden Abfrage in anwendungs-ID.

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

I. Erstellen von SQL Managed Instance-Verbindungsservern mit Passthrough-Microsoft Entra-Authentifizierung

Führen Sie zum Erstellen eines verknüpften Servers mit Pass-Through-Authentifizierung den folgenden T-SQL-Code aus, und ersetzen <managed_instance> Sie dabei durch Ihren eigenen SERVER mit verwalteter SQL-Instanz:

EXEC master.dbo.sp_addlinkedserver
    @server = N'MyLinkedServer',
    @srvproduct = N'',
    @provider = N'MSOLEDBSQL',
    @datasrc = N'<mi_name>.<dns_zone>.database.windows.net,1433';

Bei der Pass-Through-Authentifizierung wird der Sicherheitskontext der lokalen Anmeldung an die Remoteinstanz übertragen. Die Pass-Through-Authentifizierung erfordert, dass der Microsoft Entra-Prinzipal sowohl für die lokale als auch für die Remote-Azure SQL-verwaltete Instanz als Anmeldung hinzugefügt wird. Beide verwalteten Instanzen müssen sich in einer Serververtrauensgruppe 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.

Zugehöriger Inhalt

• Gespeicherte Prozeduren für verteilte Abfragen (Transact-SQL)
• sp_addlinkedsrvlogin (Transact-SQL)
• sp_addserver (Transact-SQL)
• sp_dropserver (Transact-SQL)
• sp_serveroption (Transact-SQL)
• sp_setnetname (Transact-SQL)
• Gespeicherte Systemprozeduren (Transact-SQL)
• Systemtabellen (Transact-SQL)