Delen via

Gekoppelde servers maken (SQL Server Database Engine)

  • Artikel

van toepassing op:SQL ServerAzure SQL Managed Instance

In dit artikel wordt beschreven hoe u een gekoppelde server maakt en toegang krijgt tot gegevens van een andere SQL Server, een beheerd exemplaar van Azure SQL SQL of een andere gegevensbron met behulp van SSMS - of Transact-SQL. Met gekoppelde servers kunnen de SQL Server-database-engine en Azure SQL Managed Instance gegevens lezen uit de externe gegevensbronnen en opdrachten uitvoeren op de externe databaseservers (bijvoorbeeld OLE DB-gegevensbronnen) buiten het exemplaar van SQL Server.

Achtergrond

Gekoppelde servers zijn doorgaans geconfigureerd om de database-engine in staat te stellen een Transact-SQL instructie uit te voeren die tabellen bevat in een ander exemplaar van SQL Server of een ander databaseproduct zoals Oracle. Veel typen gegevensbronnen kunnen worden geconfigureerd als gekoppelde servers, waaronder databaseproviders van derden en Azure Cosmos DB.

Nadat een gekoppelde server is gemaakt, kunnen gedistribueerde query's worden uitgevoerd op deze server en kunnen query's tabellen uit meer dan één gegevensbron samenvoegen. Als de gekoppelde server is gedefinieerd als een exemplaar van SQL Server of een azure SQL Managed Instance, kunnen externe opgeslagen procedures worden uitgevoerd.

De mogelijkheden en vereiste argumenten van de gekoppelde server kunnen aanzienlijk variëren. De voorbeelden in dit artikel bevatten een typisch voorbeeld, maar alle opties worden niet beschreven. Zie sp_addlinkedserver (Transact-SQL)voor meer informatie.

Machtigingen

Wanneer u Transact-SQL instructies gebruikt, vereist ALTER ANY LINKED SERVER machtiging voor de server of lidmaatschap van de setupadmin vaste serverfunctie. Voor het gebruik van Management Studio is CONTROL SERVER machtiging of lidmaatschap van de sysadmin vaste serverfunctie vereist.

Een gekoppelde server maken met SSMS

Maak een gekoppelde server met SSMS met behulp van de volgende procedure:

Het dialoogvenster Nieuwe gekoppelde server openen

In SQL Server Management Studio (SSMS):

  1. Open de Objectverkenner.
  2. Vouw Serverobjecten uit.
  3. Klik met de rechtermuisknop op Gekoppelde Servers.
  4. Selecteer nieuwe gekoppelde server.

De pagina Algemeen bewerken voor de eigenschappen van de gekoppelde server

Typ op de pagina Algemeen in het vak Gekoppelde server de naam van het exemplaar van SQL Server- waarmee u een koppeling maakt.

Notitie

Als het exemplaar van SQL Server- het standaardexemplaren is, voert u de naam in van de computer waarop het exemplaar van SQL Server-wordt gehost. Als de SQL Server- een benoemd exemplaar is, voert u de naam van de computer en de naam van het exemplaar in, zoals Accounting\SQLExpress.

Geef indien nodig het servertype en gerelateerde informatie op:

  • SQL Server
    Identificeer de gekoppelde server als een exemplaar van Microsoft SQL Server of een beheerd exemplaar van Azure SQL. Als u deze methode gebruikt om een gekoppelde server te definiëren, moet de naam die is opgegeven in gekoppelde server de netwerknaam van de server zijn. Alle tabellen die van de server worden opgehaald, zijn ook afkomstig uit de standaarddatabase die is gedefinieerd voor de aanmelding op de gekoppelde server.

  • Andere gegevensbron
    Geef een ander OLE DB-servertype op dan SQL Server. Als u op deze optie klikt, worden de onderstaande opties geactiveerd.

    • Provider
      Selecteer een OLE DB-gegevensbron in de keuzelijst. De OLE DB-provider wordt geregistreerd bij de opgegeven PROGID in het register.

    • Productnaam
      Typ de productnaam van de OLE DB-gegevensbron die u wilt toevoegen als een gekoppelde server.

    • gegevensbron
      Typ de naam van de gegevensbron zoals geïnterpreteerd door de OLE DB-provider. Als u verbinding maakt met een exemplaar van SQL Server, geeft u de naam van het exemplaar op.

    • provider-tekst
      Typ de unieke programmatische id (PROGID) van de OLE DB-provider die overeenkomt met de gegevensbron. Zie sp_addlinkedserver (Transact-SQL)voor voorbeelden van geldige providertekenreeksen.

    • locatie
      Typ de locatie van de database zoals geïnterpreteerd door de OLE DB-provider.

    • Catalogus
      Typ de naam van de catalogus die moet worden gebruikt bij het maken van een verbinding met de OLE DB-provider.

De pagina Beveiliging bewerken voor de eigenschappen van de gekoppelde server

Geef op de pagina Security de beveiligingscontext op die wordt gebruikt wanneer het oorspronkelijke exemplaar verbinding maakt met de gekoppelde server. Er zijn twee strategieën om hier te configureren die alleen of gecombineerd kunnen worden gebruikt. De eerste is het toewijzen van aanmeldingen van de lokale server aan de externe server en de tweede is hoe de gekoppelde server aanmeldingen moet behandelen die niet zijn toegewezen.

Loginkoppelingen toevoegen

U kunt eventueel opgeven hoe specifieke lokale serveraanmeldingsgegevens worden geverifieerd met behulp van de gekoppelde server.

Herhaal onder het proces van lokale serveraanmelding naar aanmelding op afstandvoor elke login die u wilt koppelen:

  1. Selecteer toevoegen.

  2. Geef een lokale loginop.

    Geef de lokale aanmelding op die verbinding kan maken met de gekoppelde server. De lokale aanmelding kan een aanmelding zijn met behulp van SQL Server-verificatie of een Windows-verificatieaanmelding. Het gebruik van een Windows-groep of een ingesloten databasegebruiker wordt niet ondersteund. Gebruik deze lijst om de verbinding met specifieke aanmeldingen te beperken of om toe te staan dat sommige aanmeldingen verbinding maken als een andere aanmelding.

    Notitie

    Veelvoorkomende problemen met gekoppelde servers die Gebruikmaken van Windows-verificatie naar een extern SQL Server-exemplaar, ontstaan door problemen met SPN's (Service Principal Names). Zie SPN-ondersteuning (Service Principal Name) in clientverbindingenvoor meer informatie. Microsoft Kerberos Configuration Manager voor SQL Server is een diagnostisch hulpprogramma dat helpt bij het oplossen van kerberos-gerelateerde verbindingsproblemen met SQL Server. Zie Microsoft Kerberos Configuration Manager voor SQL Servervoor meer informatie.

  3. Selecteer imiteren (optioneel).

    Geef de gebruikersnaam en het wachtwoord van de lokale aanmelding door aan de gekoppelde server. Voor SQL Server-verificatie moet een aanmelding met exact dezelfde naam en hetzelfde wachtwoord bestaan op de externe server. Voor Windows-aanmeldingen moet de aanmelding een geldige aanmelding op de gekoppelde server zijn.

    Als u imitatie wilt gebruiken, moet de configuratie voldoen aan de vereiste voor delegering.

  4. Specificeer een externe gebruiker als u geen impersonatie gebruikt.

    Gebruik de externe gebruiker om de gebruiker toe te wijzen die is gedefinieerd in de lokale login . De externe gebruiker moet een SQL Server-authenticatie-login op de externe server zijn.

  5. Geef een extern wachtwoord op als u geen imitatie gebruikt.

    • Geef het wachtwoord van de externe gebruiker op.

  6. Selecteer verwijderen om desgewenst een bestaande lokale aanmelding te verwijderen.

De standaardbeveiligingscontext opgeven voor aanmeldingen die ontbreken op de toewijzingslijst

In een domeinomgeving waar gebruikers verbinding maken met behulp van hun domeinaanmelding, selecteert u Worden gemaakt met behulp van de huidige beveiligingscontext van de aanmelding vaak de beste keuze is. Wanneer gebruikers verbinding maken met de oorspronkelijke SQL Server- met behulp van een SQL Server- aanmelding, is de beste keuze vaak om te selecteren door gebruik te maken van deze beveiligingscontexten vervolgens de benodigde referenties te verstrekken voor authenticatie bij de gekoppelde server.

Selecteer een van de volgende opties:

  • niet gemaakt worden
    Er wordt geen verbinding gemaakt voor aanmeldingen die niet zijn gedefinieerd in de lijst.

  • Worden gemaakt zonder gebruik te maken van een beveiligingscontext
    Er wordt een verbinding gemaakt zonder een beveiligingscontext te gebruiken voor aanmeldingen die niet in de lijst zijn gedefinieerd.

  • worden gemaakt met behulp van de huidige beveiligingscontext van het inloggen
    Er wordt een verbinding gemaakt met behulp van de huidige beveiligingscontext van de aanmelding voor aanmeldingen die niet in de lijst zijn gedefinieerd. Als u verbinding hebt met de lokale server met behulp van Windows-verificatie, worden uw Windows-referenties gebruikt om verbinding te maken met de externe server. Als verbinding is gemaakt met de lokale server met behulp van SQL Server-verificatie, worden aanmeldingsnaam en wachtwoord gebruikt om verbinding te maken met de externe server. In dit geval moet er een aanmelding met exact dezelfde naam en hetzelfde wachtwoord bestaan op de externe server.

  • Worden gedaan met behulp van deze beveiligingscontext
    Er wordt een verbinding gemaakt met behulp van de aanmelding en het wachtwoord die zijn opgegeven in de vakken Externe aanmelding en Met wachtwoord voor aanmeldingen die niet in de lijst zijn gedefinieerd. De externe aanmelding moet een SQL Server-verificatieaanmelding zijn op de externe server.

    Voorzichtigheid

    Als een gekoppelde server is geconfigureerd met de optie om te worden gebruikt met deze beveiligingscontext, kan elke gebruiker op het exemplaar toegang krijgen tot de externe gekoppelde server met behulp van deze context. Dit kan de onbedoelde kans hebben op misbruik of schadelijke interne toegang. De geauthenticeerde SQL-externe aanmelding aan de verbonden server moet de minimaal benodigde machtigingen op de externe server krijgen om het principe van het minste recht te garanderen en de kwetsbaarheid voor aanvallen te verminderen.

De pagina Serveropties bewerken in eigenschappen van gekoppelde servers (optioneel)

Als u serveropties wilt weergeven of opgeven, selecteert u de pagina Serveropties. U kunt een van de volgende opties bewerken:

  • Sortering Compatibel
    Beïnvloedt de uitvoering van gedistribueerde query's op gekoppelde servers. Als deze optie-instelling is ingesteld op true, gaat SQL Server ervan uit dat alle karakters in de gekoppelde server compatibel zijn met de lokale server, met betrekking tot tekenset en collatievolgorde (of sorteervolgorde). Hierdoor kan SQL Server vergelijkingen op tekenkolommen naar de provider verzenden. Als deze optie niet is ingesteld, evalueert SQL Server altijd lokaal vergelijkingen op tekenkolommen.

    Deze optie moet alleen worden ingesteld als het zeker is dat de gegevensbron die overeenkomt met de gekoppelde server dezelfde tekenset en sorteervolgorde heeft als de lokale server.

  • Gegevens Toegang
    Hiermee schakelt u een gekoppelde server in of uit voor gedistribueerde querytoegang.

  • RPC
    Hiermee schakelt u externe procedure-aanroepen (RPC) van de opgegeven server in.

  • RPC Uit
    Hiermee schakelt u RPC in op de opgegeven server.

  • externe sortering gebruiken
    Bepaalt of de collatie van een externe kolom of van een lokale server wordt gebruikt.

    Indien waar, wordt de sortering van externe kolommen gebruikt voor SQL Server-gegevensbronnen en wordt de sortering die is opgegeven in de sorteringsnaam gebruikt voor niet-SQL Server-gegevensbronnen.

    Als dit onwaar is, worden gedistribueerde query's altijd de standaardsortering van de lokale server gebruikt, terwijl de sorteringsnaam en de sortering van externe kolommen worden genegeerd. De standaardwaarde is onwaar.

  • sorteringsnaam
    Hiermee geeft u de naam op van de sortering die door de externe gegevensbron wordt gebruikt als externe sortering waar is en de gegevensbron geen SQL Server-gegevensbron is. De naam moet een van de sorteringen zijn die worden ondersteund door SQL Server.

    Gebruik deze optie bij het openen van een andere OLE DB-gegevensbron dan SQL Server, maar waarvan de sortering overeenkomt met een van de SQL Server-sorteringen.

    De gekoppelde server moet één sortering ondersteunen die moet worden gebruikt voor alle kolommen op die server. Stel deze optie niet in als de gekoppelde server meerdere sorteringen binnen één gegevensbron ondersteunt, of als de sortering van de gekoppelde server niet kan worden bepaald aan een van de SQL Server-sorteringen.

  • Verbindingstijd is verlopen
    Time-outwaarde in seconden voor het maken van verbinding met een gekoppelde server.

    Als dit 0 is, gebruikt u de sp_configure standaard time-out voor externe aanmelding als optiewaarde.

  • Query-timeout
    Time-outwaarde in seconden voor query's op een gekoppelde server.

    Als 0, gebruikt u de sp_configure standaardtime-out voor externe query optiewaarde.

  • promotie van gedistribueerde transacties inschakelen
    Gebruik deze optie om de acties van een server-naar-serverprocedure te beveiligen via een MS DTC-transactie (Microsoft Distributed Transaction Coordinator). Als deze optie WAAR is, start het aanroepen van een op afstand opgeslagen procedure een gedistribueerde transactie en registreert de transactie bij MS DTC. Zie sp_serveroption (Transact-SQL)voor meer informatie.

De gekoppelde server opslaan

Selecteer OK-.

Opties voor gekoppelde serverproviders weergeven of bewerken in SSMS

Alle providers hebben niet dezelfde opties beschikbaar. Sommige typen gegevens bevatten bijvoorbeeld indexen die beschikbaar zijn en sommige mogelijk niet. Gebruik dit dialoogvenster om SQL Server inzicht te geven in de mogelijkheden van de provider. SQL Server installeert enkele algemene gegevensproviders, maar wanneer het product de gegevenswijzigingen aanlevert, biedt de provider die door SQL Server is geïnstalleerd mogelijk geen ondersteuning voor alle nieuwste functies. De beste informatiebron over de mogelijkheden van het product dat de gegevens levert, is de documentatie voor dat product.
Om de gekoppelde server Providersopties pagina in SSMS te openen:

  1. Open de Objectverkenner.
  2. Breid serverobjecten uit.
  3. Vouw de gekoppelde servers uit .
  4. Vouw Providersuit.
  5. Klik met de rechtermuisknop op een aanbieder en selecteer Eigenschappen.

Provideropties worden op de volgende manier gedefinieerd:

  • dynamische parameter
    Geeft aan dat de provider de '?' parametermarkeringssyntaxis toestaat voor geparameteriseerde query's. Stel deze optie alleen in als de provider de interface ICommandWithParameters ondersteunt en een '?' ondersteunt als parametermarkering. Als u deze optie instelt, kan SQL Server geparameteriseerde query's uitvoeren op de provider. De mogelijkheid om geparameteriseerde query's uit te voeren op de provider kan leiden tot betere prestaties voor bepaalde query's.

  • Geneste queries
    Geeft aan dat de provider geneste SELECT instructies in de FROM-clausule toestaat. Als u deze optie instelt, kan SQL Server bepaalde query's delegeren aan de provider waarvoor nesting van SELECT-instructies in de FROM-component is vereist.

  • niveau nul alleen
    Alleen OLE DB-interfaces op niveau 0 worden aangeroepen voor de provider.

  • toestaan

    Met SQL Server kan de provider worden geïnstantieerd als een in-process server. Wanneer deze optie niet is ingesteld, is het standaardgedrag het instantiëren van de provider buiten het SQL Server-proces. Door de provider buiten het SQL Server-proces te instantiëren, wordt het SQL Server-proces beschermd tegen fouten in de provider. Wanneer de provider buiten het SQL Server-proces wordt geïnstantieerd, zijn updates of invoegingen die verwijzen naar lange kolommen (zoalstekst, ntextof afbeelding) niet toegestaan.

  • Niet-getransacteerde updates
    SQL Server staat updates toe, zelfs als ITransactionLocal- niet beschikbaar is. Als deze optie is ingeschakeld, kunnen updates voor de provider niet worden hersteld, omdat de provider geen ondersteuning biedt voor transacties.

  • Index als toegangspad
    SQL Server probeert indexen van de provider te gebruiken om gegevens op te halen. Indexen worden standaard alleen gebruikt voor metagegevens en worden nooit geopend

  • ad-hoctoegang niet
    SQL Server staat ad-hoc toegang via de functies OPENROWSET en OPENDATASOURCE naar de OLE DB-provider niet toe. Als deze optie niet is ingesteld, staat SQL Server ook geen ad-hoctoegang toe.

  • ondersteunt de 'LIKE'-operator
    Geeft aan dat de provider query's ondersteunt met behulp van het LIKE-sleutelwoord.

Een gekoppelde server maken met Transact-SQL

Als u een gekoppelde server wilt maken met behulp van Transact-SQL, gebruikt u de sp_addlinkedserver (Transact-SQL), CREATE LOGIN (Transact-SQL)en sp_addlinkedsrvlogin (Transact-SQL) instructies.

In dit voorbeeld wordt een gekoppelde server gemaakt met een ander exemplaar van SQL Server met behulp van Transact-SQL:

  1. Voer in Query-editor de volgende Transact-SQL opdracht in om een koppeling te maken naar een exemplaar van SQL Server met de naam SRVR002\ACCTG:

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

  2. Voer de volgende code uit om de gekoppelde server te configureren voor het gebruik van de domeinreferenties van de aanmelding die gebruikmaakt van de gekoppelde server.

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

Opvolgen: Stappen die moeten worden uitgevoerd nadat u een gekoppelde server hebt gemaakt

Met de volgende stappen kunt u een gekoppelde server valideren.

De gekoppelde server testen

Overweeg een van de volgende twee benaderingen om de verificatie van een gekoppelde server te testen in uw huidige beveiligingscontext.

  • Als u de mogelijkheid wilt testen om verbinding te maken met een gekoppelde server in SSMS, bladert u naar de gekoppelde server in Objectverkenner, klikt u met de rechtermuisknop op de gekoppelde server en selecteert u vervolgens Verbinding testen.

  • Als u de mogelijkheid wilt testen om verbinding te maken met een gekoppelde server in T-SQL, voert u een eenvoudige SELECT-instructie uit, bijvoorbeeld om basisgegevens van de databasecatalogus op te halen. In dit voorbeeld worden de namen van de databases op de gekoppelde server geretourneerd.

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

Tabellen samenvoegen vanaf een gekoppelde server

Gebruik vierdelige namen om te verwijzen naar een object op een gekoppelde server. Voer de volgende code uit om een lijst met alle aanmeldingen op de lokale server en de bijbehorende aanmeldingen op de gekoppelde server te retourneren.

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

Wanneer NULL wordt geretourneerd voor de gekoppelde serveraanmelding, geeft dit aan dat de aanmelding niet bestaat op de gekoppelde server. Deze aanmeldingen kunnen de gekoppelde server niet gebruiken, tenzij de gekoppelde server is geconfigureerd om een andere beveiligingscontext door te geven of de gekoppelde server anonieme verbindingen accepteert.

Gekoppelde servers met Azure SQL Managed Instance

Als u Azure SQL Managed Instance gebruikt, raadpleegt u de volgende voorbeelden uit sp_addlinkedserver (Transact-SQL):

Volgende stappen

Meer informatie over het beheren van gekoppelde servers in deze artikelen: