Delen via

sp_addlinkedserver (Transact-SQL)

  • Artikel

van toepassing op:SQL ServerAzure SQL Managed Instance

Hiermee maakt u een gekoppelde server. Een gekoppelde server biedt toegang tot gedistribueerde, heterogene query's voor OLE DB-gegevensbronnen. Nadat een gekoppelde server is gemaakt met behulp van sp_addlinkedserver, kunnen gedistribueerde query's worden uitgevoerd op deze server. Als de gekoppelde server is gedefinieerd als een exemplaar van SQL Server, kunnen externe opgeslagen procedures worden uitgevoerd.

Notitie

Microsoft Entra ID voorheen Azure Active Directory (Azure AD) werd genoemd.

Transact-SQL syntaxisconventies

Syntaxis

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 ]
[ ; ]

Argumenten

[ @server = ] N'server'

De naam van de gekoppelde server die moet worden gemaakt. @server is sysname, zonder standaardinstelling.

[ @srvproduct = ] N'srvproduct'

De productnaam van de OLE DB-gegevensbron die moet worden toegevoegd als een gekoppelde server. @srvproduct is nvarchar(128), met een standaardwaarde van NULL. Als de waarde is SQL Server, @provider, @datasrc, @location, @provstren @catalog hoeft niet te worden opgegeven.

[ @provider = ] N'provider'

De unieke programmatische id (PROGID) van de OLE DB-provider die overeenkomt met deze gegevensbron. De @provider moet uniek zijn voor de opgegeven OLE DB-provider die op de huidige computer is geïnstalleerd. @provider is nvarchar(128), met een standaardwaarde van NULL.

  • In SQL Server 2019 (15.x) en eerdere versies, als @provider wordt weggelaten, wordt SQLNCLI gebruikt. Met SQLNCLI wordt SQL Server omgeleid naar de nieuwste versie van DE OLE DB-provider van SQL Server Native Client. De OLE DB-provider wordt naar verwachting geregistreerd bij de opgegeven PROGID in het register. In plaats van SQLNCLIwordt MSOLEDBSQL aanbevolen.

  • Vanaf SQL Server 2022 (16.x) moet u een providernaam opgeven. MSOLEDBSQL wordt aanbevolen. Als u @providerweglaat, kunt u onverwacht gedrag ervaren.

Belangrijk

De SQL Server Native Client (vaak afgekort SNAC) is verwijderd uit SQL Server 2022 (16.x) en SQL Server Management Studio 19 (SSMS). Zowel de SQL Server Native Client OLE DB-provider (SQLNCLI of SQLNCLI11) als de verouderde Microsoft OLE DB-provider voor SQL Server (SQLOLEDB) worden niet aanbevolen voor nieuwe ontwikkeling. Schakel over naar het nieuwe Microsoft OLE DB-stuurprogramma (MSOLEDBSQL) voor SQL Server.

[ @datasrc = ] N'datasrc'

De naam van de gegevensbron zoals geïnterpreteerd door de OLE DB-provider. @datasrc is nvarchar(4000), met een standaardwaarde van NULL. @datasrc wordt doorgegeven als de eigenschap DBPROP_INIT_DATASOURCE om de OLE DB-provider te initialiseren.

[ @location = ] N'locatie'

De locatie van de database zoals geïnterpreteerd door de OLE DB-provider. @location is nvarchar(4000), met een standaardwaarde van NULL. @location wordt doorgegeven als de eigenschap DBPROP_INIT_LOCATION om de OLE DB-provider te initialiseren.

[ @provstr = ] N'provstr'

De ole DB-providerspecifieke verbindingsreeks die een unieke gegevensbron identificeert. @provstr is nvarchar(4000), met een standaardwaarde van NULL. Het argument provstr- wordt doorgegeven aan IDataInitialize of ingesteld als de eigenschap DBPROP_INIT_PROVIDERSTRING om de OLE DB-provider te initialiseren.

Wanneer de gekoppelde server wordt gemaakt op basis van de OLE DB-provider van sql Server Native Client, kan het exemplaar worden opgegeven met behulp van het trefwoord SERVER als SERVER=servername\instancename om een specifiek exemplaar van SQL Server op te geven. De servernaam is de naam van de computer waarop SQL Server wordt uitgevoerd en exemplaarnaam de naam is van het specifieke exemplaar van SQL Server waarmee de gebruiker wordt verbonden.

  • Voor toegang tot een gespiegelde database moet een verbindingsreeks de databasenaam bevatten. Deze naam is nodig om failoverpogingen in te schakelen door de gegevenstoegangsprovider. De database kan worden opgegeven in de parameter @provstr of @catalog. Desgewenst kan de verbindingsreeks ook de naam van een failoverpartner opgeven.

  • Als u sp_addlinkedserver uitvoert vanuit een lokale aanmelding of een aanmelding die geen deel uitmaakt van de rol sysadmin, wordt mogelijk de volgende fout weergegeven:

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

    U kunt dit probleem oplossen door de parameter User ID toe te voegen aan uw verbindingsreeks. In het volgende voorbeeld is myUser de gebruikers-id die is doorgegeven aan de verbindingsreeks:

    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'*****'

    Zie Toegang tot de externe server wordt geweigerd omdat er geen aanmeldingstoewijzing bestaatvoor meer informatie.

[ @catalog = ] N'catalogus'

De catalogus die moet worden gebruikt wanneer er verbinding wordt gemaakt met de OLE DB-provider. @catalog is sysname, met een standaardwaarde van NULL. @catalog wordt doorgegeven als de eigenschap DBPROP_INIT_CATALOG om de OLE DB-provider te initialiseren. Wanneer de gekoppelde server is gedefinieerd voor een exemplaar van SQL Server, verwijst de catalogus naar de standaarddatabase waaraan de gekoppelde server is toegewezen.

[ @linkedstyle = ] linkedstyle

Alleen ter informatie geïdentificeerd. Niet ondersteund. Toekomstige compatibiliteit is niet gegarandeerd.

Codewaarden retourneren

0 (geslaagd) of 1 (mislukt).

Resultatenset

Geen.

Opmerkingen

In de volgende tabel ziet u de manieren waarop een gekoppelde server kan worden ingesteld voor gegevensbronnen die toegankelijk zijn via OLE DB. Een gekoppelde server kan meerdere manieren voor een bepaalde gegevensbron worden ingesteld; er kan meer dan één rij zijn voor een gegevensbrontype. Deze tabel bevat ook de sp_addlinkedserver parameterwaarden die moeten worden gebruikt voor het instellen van de gekoppelde server.

Externe OLE DB-gegevensbron OLE DB-provider @srvproduct @provider @datasrc @location @provstr @catalog
SQL Server OLE DB-provider van SQL Server Native Client SQL Server 1 (standaard)
SQL Server OLE DB-provider van SQL Server Native Client SQLNCLI Netwerknaam van SQL Server (voor standaardexemplaren) Databasenaam (optioneel)
SQL Server OLE DB-provider van SQL Server Native Client SQLNCLI servernaam\exemplaarnaam (voor een specifiek exemplaar) Databasenaam (optioneel)
Oracle, versie 8 en hoger Oracle-provider voor OLE DB Enig OraOLEDB.Oracle Alias voor de Oracle-database
Toegang/jet Microsoft OLE DB-provider voor Jet Enig Microsoft.Jet.OLEDB.4.0 Volledig pad van jetdatabasebestand
ODBC-gegevensbron Microsoft OLE DB-provider voor ODBC Enig MSDASQL Systeem-DSN van ODBC-gegevensbron
ODBC-gegevensbron Microsoft OLE DB-provider voor ODBC Enig MSDASQL ODBC-verbindingsreeks
Bestandssysteem Microsoft OLE DB-provider voor indexeringsservice Enig MSIDXS Naam van indexeringsservicecatalogus
Microsoft Excel-spreadsheet Microsoft OLE DB-provider voor Jet Enig Microsoft.Jet.OLEDB.4.0 Volledig pad van Excel-bestand Excel 5.0
IBM Db2-database Microsoft OLE DB-provider voor DB2 Enig DB2OLEDB Raadpleeg de documentatie voor Microsoft OLE DB-provider voor DB2. Catalogusnaam van DB2-database

1 Op deze manier van het instellen van een gekoppelde server moet de naam van de gekoppelde server hetzelfde zijn als de netwerknaam van het externe exemplaar van SQL Server. Gebruik @datasrc om de server op te geven.

2 'Any' geeft aan dat de productnaam van alles kan zijn.

De OLE DB-provider van de SYSTEEMeigen SQL Server-client is de provider die wordt gebruikt met SQL Server als er geen providernaam is opgegeven of als SQL Server is opgegeven als de productnaam. Zelfs als u de naam van de oudere provider opgeeft, WORDT SQLOLEDB gewijzigd in SQLNCLI wanneer deze wordt bewaard in de catalogus.

De parameters @datasrc, @location, @provstren @catalog identificeren de database of databases waaraan de gekoppelde server verwijst. Als een van deze parameters is NULL, is de bijbehorende initialisatie-eigenschap van OLE DB niet ingesteld.

Wanneer u in een geclusterde omgeving bestandsnamen opgeeft die verwijzen naar OLE DB-gegevensbronnen, gebruikt u de naam van de Universal Naming Convention (UNC) of een gedeeld station om de locatie op te geven.

De opgeslagen procedure sp_addlinkedserver kan niet worden uitgevoerd binnen een door de gebruiker gedefinieerde transactie.

Belangrijk

Azure SQL Managed Instance biedt momenteel alleen ondersteuning voor SQL Server, SQL Database en andere met SQL beheerde exemplaren als externe gegevensbronnen.

Belangrijk

Wanneer een gekoppelde server wordt gemaakt met behulp van sp_addlinkedserver, wordt er een standaard zelftoewijzing toegevoegd voor alle lokale aanmeldingen. Voor niet-SQL Server-providers kunnen geverifieerde aanmeldingen van SQL Server toegang krijgen tot de provider onder het SQL Server-serviceaccount. Beheerders moeten overwegen sp_droplinkedsrvlogin <linkedserver_name>, NULL te gebruiken om de globale toewijzing te verwijderen.

Machtigingen

Voor de sp_addlinkedserver-instructie is de machtiging ALTER ANY LINKED SERVER vereist. (Het dialoogvenster SQL Server Management Studio Nieuwe gekoppelde server wordt geïmplementeerd op een manier waarvoor lidmaatschap van de sysadmin vaste serverfunctie is vereist.)

Voorbeelden

Een. De OLE DB-provider van Microsoft SQL Server gebruiken

In het volgende voorbeeld wordt een gekoppelde server met de naam SEATTLESalesgemaakt. De productnaam is SQL Serveren er wordt geen providernaam gebruikt.

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

In het volgende voorbeeld wordt een gekoppelde server gemaakt S1_instance1 op een exemplaar van SQL Server met behulp van het SQL Server OLE DB-stuurprogramma.

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

In het volgende voorbeeld wordt een gekoppelde server gemaakt S1_instance1 op een exemplaar van SQL Server met behulp van de OLE DB-provider van de SYSTEEMeigen SQL Server-client.

Belangrijk

SQL Server Native Client OLE DB-provider (SQLNCLI) blijft afgeschaft en het wordt niet aanbevolen om deze te gebruiken voor nieuwe ontwikkelwerkzaamheden. Gebruik in plaats daarvan het nieuwe Microsoft OLE DB-stuurprogramma voor SQL Server (MSOLEDBSQL) die wordt bijgewerkt met de meest recente serverfuncties.

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

B. De Microsoft OLE DB-provider voor Microsoft Access gebruiken

De Provider Microsoft.Jet.OLEDB.4.0 maakt verbinding met Microsoft Access-databases die de 2002-2003-indeling gebruiken. In het volgende voorbeeld wordt een gekoppelde server met de naam SEATTLE Mktggemaakt.

Notitie

In dit voorbeeld wordt ervan uitgegaan dat zowel Microsoft Access als de voorbeelddatabase Northwind zijn geïnstalleerd en dat de Northwind-database zich bevindt in C:\Msoffice\Access\Samples op dezelfde server als het SQL Server-exemplaar.

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. De Microsoft OLE DB-provider voor ODBC gebruiken met de parameter datasrc

In het volgende voorbeeld wordt een gekoppelde server gemaakt met de naam SEATTLE Payroll die gebruikmaakt van de Microsoft OLE DB-provider voor ODBC (MSDASQL) en de parameter @datasrc.

Notitie

De opgegeven ODBC-gegevensbronnaam moet worden gedefinieerd als systeem-DSN op de server voordat u de gekoppelde server gebruikt.

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

D. De Microsoft OLE DB-provider voor Excel-spreadsheet gebruiken

Als u een gekoppelde serverdefinitie wilt maken met behulp van de Microsoft OLE DB-provider voor Jet voor toegang tot een Excel-spreadsheet in de indeling 1997 - 2003, maakt u eerst een benoemd bereik in Excel door de kolommen en rijen van het Excel-werkblad op te geven die u wilt selecteren. De naam van het bereik kan vervolgens worden verwezen als een tabelnaam in een gedistribueerde query.

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

Als u toegang wilt krijgen tot gegevens uit een Excel-spreadsheet, koppelt u een celbereik aan een naam. De volgende query kan worden gebruikt voor toegang tot het opgegeven benoemde bereik SalesData als een tabel met behulp van de gekoppelde server die eerder is ingesteld.

SELECT *
   FROM ExcelSource...SalesData;
GO

Als SQL Server wordt uitgevoerd onder een domeinaccount dat toegang heeft tot een externe share, kan een UNC-pad worden gebruikt in plaats van een toegewezen station.

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

E. De Microsoft OLE DB-provider voor Jet gebruiken om toegang te krijgen tot een tekstbestand

In het volgende voorbeeld wordt een gekoppelde server gemaakt om rechtstreeks toegang te krijgen tot tekstbestanden, zonder de bestanden als tabellen in een Access-.mdb-bestand te koppelen. De provider is Microsoft.Jet.OLEDB.4.0 en de tekenreeks van de provider is Text.

De gegevensbron is het volledige pad van de map die de tekstbestanden bevat. Een schema.ini-bestand, waarin de structuur van de tekstbestanden wordt beschreven, moet zich in dezelfde map bevinden als de tekstbestanden. Zie de documentatie van de Jet Database Engine voor meer informatie over het maken van een schema.ini-bestand.

Maak eerst een gekoppelde server.

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

Aanmeldingstoewijzingen instellen.

EXEC sp_addlinkedsrvlogin txtsrv, FALSE, Admin, NULL;

De tabellen op de gekoppelde server weergeven.

EXEC sp_tables_ex txtsrv;

Voer een query uit op een van de tabellen, in dit geval file1#txt, met behulp van een vierdelige naam.

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

F. De Microsoft OLE DB-provider voor DB2 gebruiken

In het volgende voorbeeld wordt een gekoppelde server gemaakt met de naam DB2 die gebruikmaakt van de Microsoft OLE DB-provider voor DB2.

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. Een Azure SQL-database toevoegen als een gekoppelde server voor gebruik met gedistribueerde query's in cloud- en on-premises databases

U kunt een Azure SQL-database toevoegen als een gekoppelde server en deze vervolgens gebruiken met gedistribueerde query's die de on-premises en clouddatabases omvatten. Dit is een onderdeel voor hybride databaseoplossingen die on-premises bedrijfsnetwerken en de Azure-cloud omvatten.

Het SQL Server-boxproduct bevat de gedistribueerde queryfunctie, waarmee u query's kunt schrijven om gegevens uit lokale gegevensbronnen en gegevens uit externe bronnen (inclusief gegevens uit niet-SQL Server-gegevensbronnen) te combineren die zijn gedefinieerd als gekoppelde servers. Elke Azure SQL-database (met uitzondering van de master database van de logische server) kan worden toegevoegd als een afzonderlijke gekoppelde server en vervolgens rechtstreeks in uw databasetoepassingen worden gebruikt als elke andere database.

De voordelen van het gebruik van Azure SQL Database zijn beheerbaarheid, hoge beschikbaarheid, schaalbaarheid, werken met een vertrouwd ontwikkelingsmodel en een relationeel gegevensmodel. De vereisten van uw databasetoepassing bepalen hoe deze Azure SQL Database in de cloud zou gebruiken. U kunt al uw gegevens tegelijk verplaatsen naar Azure SQL Database of een deel van uw gegevens geleidelijk verplaatsen terwijl u de resterende gegevens on-premises houdt. Voor een dergelijke hybride databasetoepassing kan Azure SQL Database nu worden toegevoegd als gekoppelde servers en kan de databasetoepassing gedistribueerde query's uitgeven om gegevens uit Azure SQL Database en on-premises gegevensbronnen te combineren.

Hier volgt een voorbeeld waarin wordt uitgelegd hoe u verbinding maakt met een Azure SQL-database met behulp van gedistribueerde query's.

Voeg eerst één Azure SQL-database toe als gekoppelde server, met behulp van sql Server Native Client.

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

Voeg referenties en opties toe aan deze gekoppelde server. Vervang <password> door een geldig wachtwoord.

EXEC sp_addlinkedsrvlogin
  @rmtsrvname = 'LinkedServerName',
  @useself = 'false',
  @rmtuser = 'LoginName',
  @rmtpassword = '<password>';

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

Gebruik nu de gekoppelde server om query's uit te voeren met behulp van vierdelige namen, zelfs om een nieuwe tabel te maken en gegevens in te voegen.

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;

Voer een query uit op de gegevens met behulp van vierdelige namen:

SELECT * FROM LinkedServerName.DatabaseName.SchemaName.TableName;

H. Een gekoppelde Azure SQL Managed Instance-server maken met verificatie van beheerde identiteiten

Notitie

Microsoft Entra ID voorheen Azure Active Directory (Azure AD) werd genoemd.

Als u een gekoppelde server wilt maken met verificatie van beheerde identiteit, voert u de volgende T-SQL uit, waarbij u <managed_instance> vervangt door uw eigen met SQL beheerde instantie. De verificatiemethode gebruikt ActiveDirectoryMSI in de parameter @provstr. U kunt eventueel @locallogin = NULL gebruiken om alle lokale aanmeldingen toe te staan.

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';

Als u verificatie met beheerde identiteiten wilt inschakelen, moet een beheerde identiteit die is toegewezen aan azure SQL Managed Instance worden toegevoegd als aanmelding bij het externe beheerde exemplaar. Zowel door het systeem toegewezen als door de gebruiker toegewezen beheerde identiteiten worden ondersteund.

Als een primaire identiteit is ingesteld, wordt deze gebruikt, anders wordt de door het systeem toegewezen beheerde identiteit gebruikt. Als de beheerde identiteit opnieuw wordt gemaakt met dezelfde naam, moet de aanmelding op het externe exemplaar ook opnieuw worden gemaakt, omdat de nieuwe beheerde identiteitstoepassings-id en de service-principal-SID van SQL Managed Instance niet meer overeenkomen. Als u wilt controleren of deze twee waarden overeenkomen, converteert u SID naar toepassings-id met de volgende query.

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

Ik. Een gekoppelde SQL Managed Instance-server maken met pass-through Microsoft Entra-verificatie

Als u een gekoppelde server wilt maken met passthrough-verificatie, voert u de volgende T-SQL uit, waarbij u <managed_instance> vervangt door uw eigen beheerde SQL-exemplaarserver:

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

Met pass-through-verificatie wordt de beveiligingscontext van de lokale aanmelding overgedragen naar het externe exemplaar. Voor pass-through-verificatie moet de Microsoft Entra-principal worden toegevoegd als aanmelding bij zowel het lokale als externe azure SQL Managed Instance. Beide beheerde exemplaren moeten zich in een serververtrouwensgroep bevinden. Wanneer aan de vereisten wordt voldaan, kan de gebruiker zich aanmelden bij een lokaal exemplaar en een query uitvoeren op het externe exemplaar via het gekoppelde serverobject.

Verwante inhoud