Partager via

Créer des serveurs liés (moteur de base de données SQL Server)

  • Article

S’applique à : SQL Server Azure SQL Managed Instance

Cet article explique comment créer un serveur lié et accéder aux données à partir d’un autre serveur SQL Server, d’une instance managée Azure SQL ou d’une autre source de données en utilisant SQL Server Management Studio (SSMS) ou Transact-SQL. Les serveurs liés permettent au moteur de base de données SQL Server et à Azure SQL Managed Instance de lire des données à partir des sources de données distantes et d’exécuter des commandes sur les serveurs de base de données distants (par exemple, des sources de données OLE DB) en dehors de l’instance de SQL Server.

Background

En général, les serveurs liés sont configurés pour permettre au moteur de base de données d’exécuter une instruction Transact-SQL qui inclut des tables situées dans une autre instance de SQL Server ou un autre produit de base de données comme Oracle. Bien d’autres types de sources de données peuvent être configurés comme serveurs liés, notamment des fournisseurs de bases de données tiers et Azure Cosmos DB.

Une fois qu'un serveur lié a été créé, il est possible d'exécuter des requêtes distribuées sur ce serveur, et les requêtes peuvent joindre des tables de plusieurs sources de données. Si le serveur lié est défini comme une instance de SQL Server ou une instance managée Azure SQL, des procédures stockées distantes peuvent être exécutées.

Les fonctions et les arguments requis du serveur lié peuvent considérablement varier. Cet article fournit un exemple classique, mais toutes les options ne sont pas décrites. Pour plus d’informations, consultez sp_addlinkedserver (Transact-SQL).

autorisations

Quand vous utilisez des instructions Transact-SQL , vous avez besoin de l’autorisation ALTER ANY LINKED SERVER sur le serveur ou de l’appartenance au rôle serveur fixe setupadmin . Quand vous utilisez Management Studio, vous avez besoin de l’autorisation CONTROL SERVER ou de l’appartenance au rôle serveur fixe administrateur système.

Créer un serveur lié avec SSMS

Effectuez la procédure suivante pour créer un serveur lié avec SSMS :

Ouvrir la boîte de dialogue Nouveau serveur lié

Dans SQL Server Management Studio (SSMS) :

  1. Ouvrez l' Explorateur d'objets.
  2. Développez Objets serveur.
  3. Cliquez avec le bouton droit sur Serveurs liés.
  4. Sélectionnez Nouveau serveur lié.

Modifier les propriétés du serveur lié dans la page Général

Sur la page Général, dans la zone Serveur lié, tapez le nom de l'instance de SQL Server associée au lien.

Remarque

Si l'instance de SQL Server est l'instance par défaut, entrez le nom de l'ordinateur qui héberge l'instance de SQL Server. Si l’instance de SQL Server est une instance nommée, entrez le nom de l’ordinateur et le nom de l’instance, par exemple Accounting\SQLExpress.

Spécifiez le Type de serveur et les informations connexes si nécessaire :

  • SQL Server
    Identifiez le serveur lié comme une instance de Microsoft SQL Server ou une instance managée Azure SQL. Si vous utilisez cette méthode pour définir un serveur lié, le nom spécifié dans Serveur lié doit correspondre au nom réseau du serveur. En outre, toutes les tables extraites à partir du serveur appartiennent à la base de données par défaut définie pour la connexion d'accès au serveur lié.

  • Autre source de données
    Spécifiez un type de serveur OLE DB autre que SQL Server. Cliquer sur cette option active les options situées au-dessous.

    • Fournisseur
      Sélectionnez une source de données OLE DB dans la zone de liste. Le fournisseur OLE DB est inscrit avec le PROGID donné dans le Registre.

    • Nom du produit
      Tapez le nom de produit de la source de données OLE DB à ajouter en tant que serveur lié.

    • Source de données
      Tapez le nom de la source de données tel qu'il est interprété par le fournisseur OLE DB. Si vous vous connectez à une instance de SQL Server, spécifiez le nom d'instance.

    • Chaîne du fournisseur
      Tapez l'identificateur de programme unique (PROGID) du fournisseur OLE DB qui correspond à la source de données. Pour obtenir des exemples de chaînes de fournisseur valides, consultez sp_addlinkedserver (Transact-SQL).

    • Lieu
      Tapez l'emplacement de la base de données tel qu'il est interprété par le fournisseur OLE DB.

    • Catalogue
      Tapez le nom du catalogue à utiliser lors de la connexion au fournisseur OLE DB.

Modifier les propriétés du serveur lié dans la page Sécurité

Dans la page Sécurité, spécifiez le contexte de sécurité qui est utilisé quand l’instance d’origine se connecte au serveur lié. Deux stratégies sont disponibles ici, que vous pouvez configurer pour une utilisation individuelle ou combinée. La première consiste à mapper les connexions du serveur local au serveur distant, et la seconde définit comment le serveur lié doit traiter les connexions qui ne sont pas mappées.

Ajouter des mappages de connexion

Vous pouvez éventuellement spécifier la façon dont certaines connexions du serveur local doivent s’authentifier avec le serveur lié.

Sous Mappages de connexions entre le serveur local et le serveur distant, répétez la procédure suivante pour chaque connexion que vous souhaitez mapper :

  1. Sélectionnez Ajouter.

  2. Spécifiez une Connexion locale.

    Affiche la connexion locale qui peut se connecter au serveur lié. La connexion locale peut être une connexion utilisant l'authentification SQL Server ou une connexion utilisant l'authentification Windows. L’utilisation d’un groupe Windows ou d’un utilisateur de base de données autonome n’est pas prise en charge. Utilisez cette liste pour restreindre la connexion à des connexions spécifiques ou pour autoriser certaines connexions à se connecter sous une connexion différente.

    Remarque

    Les problèmes courants avec les serveurs liés utilisant l’authentification Windows auprès d’une instance distante de SQL Server découlent de problèmes affectant les noms de principal du service (SPN). Pour plus d’informations, consultez Prise en charge des noms de principal du service (SPN) dans les connexions clientes. Microsoft pour SQL Server est un outil de diagnostic qui permet de dépanner les problèmes de connexion que rencontre Kerberos avec SQL Server. Pour plus d'informations, consultez Gestionnaire de configuration de Microsoft Kerberos pour SQL Server.

  3. Sélectionnez Emprunter l’identité (facultatif).

    Permet de transmettre le nom d'utilisateur et le mot de passe de la connexion locale au serveur lié. Pour l'authentification SQL Server, une connexion possédant un nom et un mot de passe identiques doit exister sur le serveur distant. Pour les connexions Windows, la connexion doit être une connexion valide sur le serveur lié.

    Pour utiliser l'emprunt d'identité, la configuration doit se conformer aux exigences relatives à la délégation.

  4. Spécifiez un Utilisateur distant si vous n’utilisez pas l’emprunt d’identité.

    Utilisez l’utilisateur distant pour mapper l’utilisateur défini dans Connexion locale. L'utilisateur distant doit être une connexion utilisant l'authentification SQL Server sur le serveur distant.

  5. Spécifiez un Mot de passe distant si vous n’utilisez pas l’emprunt d’identité.

    • Spécifie le mot de passe de l'utilisateur distant.

  6. Si vous le souhaitez, sélectionnez Supprimer pour supprimer une connexion locale existante.

Spécifier le contexte de sécurité par défaut pour les connexions ne figurant pas dans la liste de mappage

Dans un environnement de domaine où les utilisateurs se connectent à l’aide de leurs connexions de domaine, la sélection de Seront effectuées dans le contexte de sécurité de la connexion actuelle est souvent le meilleur choix. Lorsque les utilisateurs se connectent au SQL Server d'origine en utilisant un compte de connexion SQL Server , le meilleur choix est souvent de sélectionner Seront effectuées dans ce contexte de sécurité, puis de fournir les informations d'identification nécessaires pour l'authentification sur le serveur lié.

Sélectionnez l’une des options suivantes :

  • Ne seront pas effectuées
    Une connexion ne sera pas établie pour les connexions non définies dans la liste.

  • Seront effectuées sans contexte de sécurité
    Une connexion sera établie sans utiliser de contexte de sécurité pour les connexions non définies dans la liste.

  • Seront effectuées dans le contexte de sécurité de la connexion actuelle
    Une connexion sera établie en utilisant le contexte de sécurité actuel de la connexion pour les connexions non définies dans la liste. Si vous vous connectez au serveur local en utilisant l’authentification Windows, vos informations d’identification Windows seront utilisées pour vos connexions au serveur distant. Si vous vous connectez au serveur local en utilisant l'authentification SQL Server, le nom et le mot de passe de connexion seront utilisés pour vos connexions au serveur distant. Dans ce cas, une connexion ayant exactement les mêmes nom et mot de passe doit exister sur le serveur distant.

  • Seront effectuées dans ce contexte de sécurité
    Une connexion sera établie en utilisant la connexion et le mot de passe définis dans les zones Ouverture de session à distance et Avec le mot de passe pour les connexions non définies dans la liste. L'ouverture de session à distance doit être une connexion utilisant l'authentification SQL Server sur le serveur distant.

    Attention

    Si un serveur lié est configuré avec l’option Be made using this security context (Créé avec ce contexte de sécurité), tout utilisateur sur l’instance peut accéder au serveur lié à distance avec ce contexte. Cela peut présenter un risque involontaire d’abus ou d’accès interne malveillant. La connexion à distance authentifiée SQL fournie au serveur lié doit disposer d’autorisations minimales nécessaires sur le serveur distant, afin de garantir un principe de privilège minimum et de réduire la surface d’attaque.

Modifier les propriétés du serveur lié dans la page Options du serveur (facultatif)

Pour consulter ou spécifier les options du serveur, sélectionnez la page Options du serveur. Vous pouvez modifier les options suivantes :

  • Compatible avec le classement
    Concerne l'exécution des requêtes distribuées sur les serveurs liés. Si la valeur de cette option est vraie, SQL Server considère que tous les caractères du serveur lié sont compatibles avec le serveur local, en matière de jeu de caractères et de séquence de classement (ou ordre de tri). SQL Server peut alors envoyer au fournisseur des comparaisons sur les colonnes de caractères. Si cette option n'est pas activée, SQL Server compare toujours les colonnes de caractères en local.

    Cette option ne doit être définie que si la source de données correspondant au serveur lié possède le même jeu de caractères et respecte le même ordre de tri que le serveur local.

  • Accès aux données
    Active ou désactive un serveur lié pour l'accès des requêtes distribuées.

  • RPC
    Active les appels de procédure distante (RPC) à partir du serveur spécifié.

  • Sortie RPC
    Active l'appel de procédure à distance (RPC) à destination du serveur spécifié.

  • Utiliser le classement distant
    Détermine si le classement d'une colonne distante ou d'un serveur local doit être utilisé.

    Si la valeur est true, le classement des colonnes distantes est utilisé pour les sources de données SQL Server, tandis que le classement spécifié dans collation name (Nom du classement) est utilisé pour les sources de données autres que SQL Server.

    Si la valeur est false, les requêtes distribuées utilisent toujours le classement par défaut du serveur local, tandis que collation name et le classement des colonnes distantes sont ignorés. La valeur par défaut est false.

  • Nom du classement
    Spécifie le nom du classement utilisé par la source de données distante si use remote collation (Utiliser le classement distant) a la valeur true et si la source de données n'est pas une source de données SQL Server. Le nom doit être l'un des classements pris en charge par SQL Server.

    Utilisez cette option lors d'un accès à une source de données OLE DB autre que SQL Server, mais dont le classement correspond à l'un des classements de SQL Server.

    Le serveur lié doit prendre en charge un classement unique utilisable pour toutes les colonnes du serveur. Ne définissez pas cette option si le serveur lié prend en charge plusieurs classements dans une source de données unique ou si le classement du serveur lié ne correspond peut-être pas à l'un des classements de SQL Server.

  • Délai de connexion
    Valeur du délai d'expiration (en secondes) de la connexion à un serveur lié.

    Si la valeur est 0, utilisez la valeur de l’option Délai d’attente de la connexion distante par défaut de sp_configure.

  • Délai de requête
    Valeur du délai d'expiration, en secondes, des requêtes par rapport à un serveur lié.

    Si la valeur est 0, utilisez la valeur de l’option Délai d’attente de la requête distante par défaut de sp_configure.

  • Activer la promotion des transactions distribuées
    Utilisez cette option pour protéger les actions d'une procédure de serveur à serveur par le biais d'une transaction MS DTC (Microsoft Distributed Transaction Coordinator). Lorsque cette option a la valeur TRUE, l'appel d'une procédure stockée distante démarre une transaction distribuée et inscrit la transaction avec MS DTC. Pour plus d’informations, consultez sp_serveroption (Transact-SQL).

Enregistrer le serveur lié

Cliquez sur OK.

Consulter ou modifier les options du fournisseur de serveur lié dans SSMS

Tous les fournisseurs n'offrent pas les mêmes options. Par exemple, certains types de données proposent des index et d'autres pas. Utilisez cette boîte de dialogue pour aider SQL Server à comprendre les possibilités du fournisseur. SQL Server installe des fournisseurs de données communes, toutefois lorsque le produit qui fournit les données est modifié, le fournisseur installé par SQL Server peut ne pas prendre en charge toutes les nouvelles fonctionnalités. La meilleure source d'informations à propos des fonctions du produit qui fournit les données est la documentation pour ce produit.
Pour ouvrir la page Options des fournisseurs de serveurs liés dans SSMS :

  1. Ouvrez l' Explorateur d'objets.
  2. Développez Objets serveur.
  3. Développez Serveurs liés.
  4. Développez Fournisseurs.
  5. Cliquez avec le bouton droit sur un fournisseur et sélectionnez Propriétés.

Définissez le fournisseur avec les options suivantes :

  • Paramètre dynamique
    Indique que le fournisseur autorise la syntaxe de marqueur de paramètre '?' pour les requêtes paramétrables. Définissez cette option uniquement si le fournisseur prend en charge l'interface ICommandWithParameters et accepte le point d'interrogation en tant que marqueur de paramètre. La configuration de cette option permet à SQL Server d'exécuter des requêtes paramétrables sur le fournisseur. Cette possibilité peut améliorer les performances de certaines requêtes.

  • Requêtes imbriquées
    Indique que le fournisseur autorise les instructions SELECT imbriquées dans la clause FROM. La configuration de cette option permet à SQL Server de déléguer au fournisseur certaines requêtes nécessitant l'imbrication des instructions SELECT dans la clause FROM.

  • Uniquement niveau zéro
    Seules les interfaces OLE DB de niveau 0 sont invoquées pour le fournisseur.

  • Autoriser inprocess

    SQL Server autorise l’instanciation du fournisseur en tant que serveur in-process. Lorsque cette option n'est pas définie, le comportement par défaut consiste à instancier le fournisseur en dehors du processus SQL Server. L'instanciation du fournisseur en dehors du processus SQL Server protège le processus SQL Server des erreurs contenues dans le fournisseur. Lorsque le fournisseur est instancié en dehors du processus SQL Server, les mises à jour ou insertions faisant référence à des colonnes longues (text, ntext ou image) ne sont pas autorisées.

  • Mises à jour non transactionnelles
    SQL Server autorise les mises à jour, même si ITransactionLocal n’est pas disponible. Si cette option est activée, les mises à jour du fournisseur ne sont pas récupérables puisque celui-ci ne prend pas en charge les transactions.

  • Indexer en tant que chemin d'accès
    SQL Server tente d’utiliser les index du fournisseur pour extraire des données. Par défaut, les index sont uniquement utilisés pour les métadonnées et ne sont jamais ouverts.

  • Interdire l'accès ad hoc
    SQL Server n’autorise pas l’accès ad hoc par le biais des fonctions OPENROWSET et OPENDATASOURCE au fournisseur OLE DB. Lorsque cette option n'est pas définie, SQL Server ne permet pas non plus l'accès d'égal à égal.

  • Prend en charge l'opérateur 'Like'
    Indique que le fournisseur prend en charge les requêtes qui utilisent le mot clé LIKE.

Créer un serveur lié avec Transact-SQL

Pour créer un serveur lié à l’aide de Transact-SQL, utilisez les instructions sp_addlinkedserver (Transact-SQL), CREATE LOGIN (Transact-SQL) et sp_addlinkedsrvlogin (Transact-SQL).

Cet exemple crée un serveur lié à une autre instance de SQL Server avec Transact-SQL :

  1. Dans l’éditeur de requête, entrez la commande Transact-SQL suivante pour créer une liaison avec une instance de SQL Server nommée SRVR002\ACCTG :

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

  2. Exécutez le code suivant pour configurer le serveur lié de manière à utiliser les informations d'identification de domaine de la connexion qui utilise le serveur lié.

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

Suivi : mesures à prendre après avoir créé un serveur lié

Les étapes suivantes vous aident à valider un serveur lié.

Tester le serveur lié

Envisagez l’une des deux approches suivantes pour tester l’authentification d’un serveur lié dans votre contexte de sécurité actuel.

  • Pour tester la capacité de se connecter à un serveur lié dans SSMS, accédez au serveur lié dans l’Explorateur d’objets, cliquez avec le bouton droit sur le serveur lié, puis sélectionnez Tester la connexion.

  • Pour tester la capacité de se connecter à un serveur lié dans T-SQL, exécutez une simple instruction SELECT, par exemple pour récupérer les informations de base du catalogue de la base de données. Cet exemple retourne les noms des bases de données sur le serveur lié.

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

Joindre des tables à partir d’un serveur lié

Utilisez des noms en quatre parties pour faire référence à un objet sur un serveur lié. Exécutez le code suivant pour retourner une liste de toutes les connexions sur le serveur local avec leurs connexions correspondantes sur le serveur lié.

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

Quand NULL est retourné pour la connexion au serveur lié, cela indique que la connexion n’existe pas sur le serveur lié. Ces connexions ne seront pas en mesure d'utiliser le serveur lié, à moins que le serveur lié ne soit configuré pour passer un contexte de sécurité différent ou que le serveur lié accepte des connexions anonymes.

Serveurs liés avec Azure SQL Managed Instance

Si vous utilisez Azure SQL Managed Instance, consultez les exemples suivants dans sp_addlinkedserver (Transact-SQL) :

Étapes suivantes

Pour en savoir plus sur la gestion des serveurs liés, consultez ces articles :