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

Cette rubrique montre comment créer un serveur lié et accéder à des données à partir d’un autre serveur SQL Server à l’aide de SQL Server Management Studio ou de Transact-SQL. En créant un serveur lié, vous pouvez utiliser des données provenant de plusieurs sources. Le serveur lié n’a pas besoin d’être une autre instance de SQL Server, mais il s’agit d’un scénario courant.

Contexte

Un serveur lié permet d’accéder à des requêtes distribuées et hétérogènes sur des sources de données OLE DB. Une fois qu’un serveur lié est créé, les requêtes distribuées peuvent être exécutées sur ce serveur, et les requêtes peuvent joindre des tables à partir de plusieurs sources de données. Si le serveur lié est défini comme une instance de SQL Server, les procédures stockées distantes peuvent être exécutées.

Les fonctionnalités et les arguments requis du serveur lié peuvent varier considérablement. Les exemples de cette rubrique fournissent un exemple classique, mais toutes les options ne sont pas décrites. Pour plus d’informations, consultez sp_addlinkedserver (Transact-SQL).

Sécurité

Autorisations

Lorsque vous utilisez des instructions Transact-SQL, il est nécessaire de disposer d'une autorisation sur le serveur ou d'appartenir au rôle serveur fixe setupadmin. Quand vous utilisez Management Studio, vous devez CONTROL SERVER disposer d’autorisations ou d’appartenance au rôle serveur fixe sysadmin .

Création d’un serveur lié

Vous pouvez utiliser l’une des options suivantes :

SQL Server Management Studio
Transact-SQL

Utilisation de SQL Server Management Studio

Pour créer un serveur lié à une autre instance de SQL Server à l’aide de SQL Server Management Studio

Dans SQL Server Management Studio, ouvrez l’Explorateur d’objets, développez Objets serveur, cliquez avec le bouton droit sur Serveurs liés, puis cliquez sur Nouveau serveur lié.
Dans la page Général , dans la zone Serveur lié , tapez le nom de l’instance de SQL Server à laquelle vous établissez une liaison.

SQL Server
Identifiez le serveur lié en tant qu’instance de MicrosoftSQL Server. Si vous utilisez cette méthode de définition d’un serveur lié SQL Server, le nom spécifié dans le serveur lié doit être le nom réseau du serveur. En outre, toutes les tables récupérées à partir du serveur proviennent de la base de données par défaut définie pour la connexion sur le serveur lié.

Autre source de données
Spécifiez un type de serveur OLE DB autre que SQL Server. Cliquez sur cette option pour activer les options ci-dessous.

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

Nom du produit
Tapez le nom du 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’interprété par le fournisseur OLE DB. Si vous vous connectez à une instance de SQL Server, indiquez le nom de l’instance.

Chaîne de fournisseur
Tapez l’identificateur programmatique 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’interprété par le fournisseur OLE DB.

Catalogue
Tapez le nom du catalogue à utiliser lors de l’établissement d’une connexion au fournisseur OLE DB.

Pour tester la possibilité de se connecter à un serveur lié, dans l’Explorateur d’objets, cliquez avec le bouton droit sur le serveur lié, puis cliquez sur Tester la connexion.

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 SQL Server est une instance nommée, entrez le nom de l’ordinateur et le nom de l’instance, tel que Accounting\SQLExpress.
Dans la zone type de serveur, sélectionnez SQL Server pour indiquer que le serveur lié est une autre instance de SQL Server.
Dans la page Sécurité , spécifiez le contexte de sécurité qui sera utilisé lorsque sql Server d’origine se connecte au serveur lié. Dans un environnement de domaine où les utilisateurs se connectent à l'aide de leurs identifiants de domaine, sélectionner Être effectué en utilisant le contexte de sécurité actuel de la connexion est souvent le meilleur choix. Lorsque les utilisateurs se connectent à SQL Server d’origine à l’aide d’une connexion SQL Server , le meilleur choix est souvent de sélectionner à l’aide de ce contexte de sécurité, puis de fournir les informations d’identification nécessaires pour s’authentifier sur le serveur lié.

Connexion locale
Spécifiez la connexion locale qui peut se connecter au serveur lié. La connexion locale peut être une connexion à l’aide de l’authentification SQL Server ou d’une connexion d’authentification Windows. Utilisez cette liste pour restreindre la connexion à des connexions spécifiques ou autoriser certaines connexions à se connecter en tant que connexion différente.

Se faire passer pour
Transmettez le nom d’utilisateur et le mot de passe de la connexion locale au serveur lié. Pour l’authentification SQL Server, une connexion portant exactement le même nom et le même mot de passe doivent 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 répondre à l’exigence de délégation.

Utilisateur distant
Utilisez l’utilisateur distant pour mapper les utilisateurs non définis dans la connexion locale. L'utilisateur distant doit être un login d'authentification SQL Server sur le serveur distant.

Mot de passe distant
Spécifiez le mot de passe de l’utilisateur distant.

Ajouter
Ajoutez une nouvelle connexion locale.

Supprimer
Supprimez une connexion locale existante.

Ne pas faire
Spécifiez qu’une connexion ne sera pas établie pour les connexions non définies dans la liste.

Être fait sans recours à un contexte de sécurité
Spécifiez qu’une connexion sera établie sans utiliser de contexte de sécurité pour les connexions non définies dans la liste.

Effectué avec le contexte de sécurité actuel de la connexion
Spécifiez qu’une connexion sera établie à l’aide du contexte de sécurité actuel de la connexion pour les connexions non définies dans la liste. Si vous êtes connecté au serveur local à l’aide de l’authentification Windows, vos informations d’identification Windows sont utilisées pour se connecter au serveur distant. Si vous êtes connecté au serveur local à l’aide de l’authentification SQL Server, le nom de connexion et le mot de passe sont utilisés pour se connecter au serveur distant. Dans ce cas, une connexion portant exactement le même nom et le même mot de passe doivent exister sur le serveur distant.

Être effectuée à l’aide de ce contexte de sécurité
Spécifiez qu’une connexion sera établie à l’aide de la connexion et du mot de passe spécifiés dans les zones de connexion à distance et avec mot de passe pour les connexions non définies dans la liste. La connexion à distance doit être une connexion d’authentification SQL Server sur le serveur distant.
Si vous le souhaitez, pour afficher ou spécifier des options de serveur, cliquez sur la page Options du serveur .

Compatibilité des classements
Affecte l’exécution des requêtes distribuées sur les serveurs liés. Si cette option a la valeur true, SQL Server suppose que tous les caractères du serveur lié sont compatibles avec le serveur local, en ce qui concerne le jeu de caractères et la séquence de classement (ou l’ordre de tri). Cela permet à SQL Server d’envoyer des comparaisons sur des colonnes de caractères au fournisseur. Si cette option n’est pas définie, SQL Server évalue toujours les comparaisons sur les colonnes de caractères localement.

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

Accès aux données
Active et désactive un serveur lié pour l’accès aux requêtes distribuées.

RPC
Active RPC à partir du serveur spécifié.

RPC Out
Active RPC sur le serveur spécifié.

Utiliser le classement distant
Détermine si la collation d'une colonne distante ou celle d'un serveur local sera utilisée.

Si la valeur est true, le classement des colonnes distantes est utilisé pour les sources de données SQL Server, et le classement spécifié dans le nom de classement est utilisé pour les sources de données non-SQL Server.

Si la valeur est false, les requêtes distribuées utilisent toujours le classement par défaut du serveur local, tandis que le nom du classement 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 l’utilisation du classement distant est true et que la source de données n’est pas une source de données SQL Server. Le nom doit être l’une des collations prises en charge par SQL Server.

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

Le serveur lié doit prendre en charge un classement unique à utiliser pour toutes les colonnes de ce serveur. Ne définissez pas cette option si le serveur lié prend en charge plusieurs classements au sein d’une seule source de données, ou si le classement du serveur lié ne peut pas être déterminé pour correspondre à l’un des classements SQL Server.

Délai de connexion
Valeur de délai d’attente en secondes pour la connexion à un serveur lié.

Si la valeur est 0, utilisez la valeur par défaut de l’option sp_configuredélai d’expiration de connexion à distance.

Délai d’expiration de la requête
Valeur de délai d’attente en secondes pour les requêtes sur un serveur lié.

Si 0, utilisez la valeur par défaut de l’option remote query timeout dans sp_configure.

Activer la promotion des transactions distribuées
Utilisez cette option pour protéger les actions d’une procédure serveur à serveur via une transaction Microsoft Distributed Transaction Coordinator (MS DTC). 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).
Cliquez sur OK.

Pour afficher les options du fournisseur

Pour afficher les options que le fournisseur met à disposition, cliquez sur la page Options des fournisseurs .

Tous les fournisseurs n’ont pas les mêmes options disponibles. Par exemple, certains types de données ont des index disponibles et certains peuvent ne pas le faire. Utilisez cette boîte de dialogue pour aider SQL Server à comprendre les fonctionnalités du fournisseur. SQL Server installe certains fournisseurs de données courants, toutefois lorsque le produit fournit les modifications de données, le fournisseur installé par SQL Server peut ne pas prendre en charge toutes les fonctionnalités les plus récentes. La meilleure source d’informations sur les fonctionnalités du produit fournissant les données est la documentation de ce produit.

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 prend en charge un « ? » comme marqueur de paramètre. La définition de cette option permet à SQL Server d’exécuter des requêtes paramétrables sur le fournisseur. La possibilité d’exécuter des requêtes paramétrables sur le fournisseur peut entraîner de meilleures performances pour certaines requêtes.

Requêtes imbriquées
Indique que le fournisseur autorise les instructions imbriquées SELECT dans la clause FROM. La définition de cette option permet à SQL Server de déléguer certaines requêtes au fournisseur qui nécessitent l’imbrication d’instructions SELECT dans la clause FROM.

Niveau zéro uniquement
Seules les interfaces OLE DB de niveau 0 sont appelées sur le fournisseur.

Autoriser le traitement
SQL Server permet au fournisseur d’être instancié 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 contre les erreurs dans le fournisseur. Lorsque le fournisseur est instancié en dehors du processus SQL Server, les mises à jour et insertions concernant des colonnes longues (text, ntext, ou image) ne sont pas autorisées.

Mises à jour non traitées
SQL Server autorise les mises à jour, même si ITransactionLocal n’est pas disponible. Si cette option est activée, les mises à jour par rapport au fournisseur ne sont pas récupérables, car le fournisseur ne prend pas en charge les transactions.

Index en tant que chemin d’accès
SQL Server tente d’utiliser des index du fournisseur pour extraire des données. Par défaut, les index sont utilisés uniquement 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 via les fonctions OPENROWSET et OPENDATASOURCE sur le fournisseur OLE DB. Lorsque cette option n’est pas définie, SQL Server n’autorise pas non plus l’accès ad hoc.

Prend en charge l’opérateur 'Like'
Indique que le fournisseur prend en charge les requêtes à l’aide du mot clé LIKE.

Utilisation de 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).

Pour créer un serveur lié à une autre instance de SQL Server à l’aide de Transact-SQL

Dans l’Éditeur de requête, entrez la commande Transact-SQL suivante pour établir un lien vers 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  
```
Exécutez le code suivant pour configurer le serveur lié pour 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 : étapes à suivre après la création d’un serveur lié

Pour tester le serveur lié

Exécutez le code suivant pour tester la connexion au serveur lié. Cet exemple montre comment renvoyer les noms des bases de données sur le serveur lié.
```
SELECT name FROM [SRVR002\ACCTG].master.sys.databases ;  
GO  
```

Écriture d’une requête qui joint 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 renvoyer une liste de toutes les connexions sur le serveur local et 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  
```
Lorsque la valeur NULL est retournée pour la connexion du serveur lié, elle indique que la connexion n’existe pas sur le serveur lié. Ces connexions ne pourront pas utiliser le serveur lié, sauf si le serveur lié est configuré pour passer un contexte de sécurité différent ou si le serveur lié accepte les connexions anonymes.

Voir aussi

Serveurs liés (Moteur de base de données)
sp_addlinkedserver (Transact-SQL)
sp_serveroption (Transact-SQL)

Last updated on 2015-11-20

Partager via