sp_addlinkedserver (Transact-SQL)

S’applique à : SQL Server Azure SQL Managed Instance

Crée un serveur lié. 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éé à l’aide sp_addlinkedserverd’un serveur lié, les requêtes distribuées peuvent être exécutées sur ce serveur. Si le serveur lié est défini comme une instance de SQL Server, les procédures stockées distantes peuvent être exécutées.

Conventions de la syntaxe Transact-SQL

Syntaxe

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

Arguments

[ @server = ] N’server'

Nom du serveur lié à créer. @server est sysname, sans valeur par défaut.

[ @srvproduct = ] N’srvproduct'

Nom du produit de la source de données OLE DB à ajouter en tant que serveur lié. @srvproduct est nvarchar(128), avec la valeur par défaut NULL. Si la valeur est SQL Server, @provider, @datasrc, @location, @provstr et @catalog ne doivent pas être spécifiées.

[ @provider = ] N’provider'

Identificateur programmatique unique (PROGID) du fournisseur OLE DB qui correspond à cette source de données. Le @provider doit être unique pour le fournisseur OLE DB spécifié installé sur l’ordinateur actuel. @provider est nvarchar(128), avec la valeur par défaut NULL.

• Dans SQL Server 2019 (15.x) et les versions antérieures, si @provider est omis, SQLNCLI est utilisé. L’utilisation SQLNCLI redirige SQL Server vers la dernière version du fournisseur OLE DB SQL Server Native Client. Le fournisseur OLE DB doit être inscrit dans le Registre avec le PROGID spécifié. Au lieu de SQLNCLI, MSOLEDBSQL est recommandé.

• À compter de SQL Server 2022 (16.x), vous devez spécifier un nom de fournisseur. MSOLEDBSQL est recommandé. Si vous omettez @provider, vous pouvez rencontrer un comportement inattendu.

[ @datasrc = ] N’datasrc'

Nom de la source de données tel qu’interprété par le fournisseur OLE DB. @datasrc est nvarchar(4000), avec la valeur par défaut NULL. @datasrc est passé en tant que DBPROP_INIT_DATASOURCE propriété pour initialiser le fournisseur OLE DB.

[ @location = ] N’location'

Emplacement de la base de données tel qu’interprété par le fournisseur OLE DB. @location est nvarchar(4000), avec la valeur par défaut NULL. @location est passé en tant que DBPROP_INIT_LOCATION propriété pour initialiser le fournisseur OLE DB.

[ @provstr = ] N’provstr'

Le chaîne de connexion spécifique au fournisseur OLE DB qui identifie une source de données unique. @provstr est nvarchar(4000), avec la valeur par défaut NULL. Le provstr d’argument est passé à IDataInitialize ou défini comme DBPROP_INIT_PROVIDERSTRING propriété pour initialiser le fournisseur OLE DB.

Lorsque le serveur lié est créé sur le fournisseur OLE DB SQL Server Native Client, l’instance peut être spécifiée à l’aide du SERVER mot clé pour SERVER=servername\instancename spécifier une instance spécifique de SQL Server. Le nom du serveur est le nom de l’ordinateur sur lequel SQL Server est en cours d’exécution, et le nom d’instance est le nom de l’instance spécifique de SQL Server à laquelle l’utilisateur sera connecté.

• Pour accéder à une base de données miroir, une chaîne de connexion doit contenir le nom de la base de données. Ce nom est nécessaire pour permettre au fournisseur d'accès aux données d'effectuer des tentatives de basculement. La base de données peut être spécifiée dans le paramètre @provstr ou @catalog . Le cas échéant, la chaîne de connexion peut également fournir un nom de partenaire de basculement.

• Si vous exécutez sp_addlinkedserver à partir d’une connexion locale ou d’une connexion qui ne fait pas partie du rôle sysadmin , vous pouvez recevoir l’erreur suivante :

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

  Pour résoudre ce problème, ajoutez le User ID paramètre à votre chaîne de connexion. Dans l’exemple suivant, myUser l’ID d’utilisateur est passé à l’chaîne de connexion :

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

  Pour plus d’informations, consultez Accès au serveur distant refusé, car aucun mappage de connexion n’existe.

[ @catalog = ] N’catalog'

Catalogue à utiliser lorsqu’une connexion est établie au fournisseur OLE DB. @catalog est sysname, avec la valeur par défaut NULL. @catalog est passé en tant que DBPROP_INIT_CATALOG propriété pour initialiser le fournisseur OLE DB. Lorsque le serveur lié est défini sur une instance de SQL Server, le catalogue fait référence à la base de données par défaut à laquelle le serveur lié est mappé.

[ @linkedstyle = ] linkedstyle

Identifié à titre d'information uniquement. Non pris en charge. La compatibilité future n'est pas garantie.

Valeurs des codes de retour

0 (réussite) or 1 (échec).

Jeu de résultats

Aucune.

Notes

Le tableau suivant présente les façons dont un serveur lié peut être configuré pour des sources de données accessibles via OLE DB. Un serveur lié peut être configuré au moyen de plusieurs méthodes pour une même source de données ; il peut y avoir plusieurs lignes pour un type de source de données. Ce tableau montre également les valeurs de sp_addlinkedserver paramètre à utiliser pour configurer le serveur lié.

¹ Cette façon de configurer un serveur lié force le nom du serveur lié à être identique au nom réseau de l’instance distante de SQL Server. Utilisez @datasrc pour spécifier le serveur.

² « Any » indique que le nom du produit peut être tout.

Le fournisseur OLE DB SQL Server Native Client est le fournisseur utilisé avec SQL Server si aucun nom de fournisseur n’est spécifié ou si SQL Server est spécifié comme nom de produit. Même si vous spécifiez l’ancien nom du fournisseur, SQLOLEDB, il passe à SQLNCLI lorsqu’il est conservé dans le catalogue.

Les paramètres @datasrc, @location, @provstr et @catalog identifient la base de données ou les bases de données vers qui le serveur lié pointe. Si l’un de ces paramètres est NULL, la propriété d’initialisation OLE DB correspondante n’est pas définie.

Dans un environnement ordonné en clusters, lorsque vous spécifiez des noms de fichiers qui pointent vers des sources de données OLE DB, utilisez le nom UNC (Universal Naming Convention) ou un lecteur partagé pour spécifier l'emplacement.

La procédure sp_addlinkedserver stockée ne peut pas être exécutée dans une transaction définie par l’utilisateur.

autorisations

L’instruction sp_addlinkedserver requiert l’autorisation ALTER ANY LINKED SERVER . (SQL Server Management Studio La boîte de dialogue Nouveau serveur lié est implémentée de manière à exiger l’appartenance au rôle serveur fixe sysadmin .)

Exemples

R. Utiliser le fournisseur OLE DB Microsoft SQL Server

L'exemple suivant crée un serveur lié nommé SEATTLESales. Le nom du produit est SQL Server ; aucun nom de fournisseur n'est utilisé.

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

L’exemple suivant crée un serveur S1_instance1 lié sur une instance de SQL Server à l’aide du pilote OLE DB SQL Server.

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

L’exemple suivant crée un serveur S1_instance1 lié sur une instance de SQL Server à l’aide du fournisseur OLE DB SQL Server Native Client.

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

B. Utiliser le fournisseur Microsoft OLE DB pour Microsoft Access

Le fournisseur Microsoft.Jet.OLEDB.4.0 se connecte aux bases de données Microsoft Access qui utilisent le format 2002-2003. L'exemple suivant crée un serveur lié nommé SEATTLE Mktg.

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. Utiliser le fournisseur Microsoft OLE DB pour ODBC avec le datasrc paramètre

L’exemple suivant crée un serveur lié nommé SEATTLE Payroll qui utilise le fournisseur Microsoft OLE DB pour ODBC (MSDASQL) et le paramètre @datasrc .

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

D. Utiliser le fournisseur Microsoft OLE DB pour la feuille de calcul Excel

Pour créer une définition de serveur lié à l’aide du fournisseur Microsoft OLE DB pour Jet pour accéder à une feuille de calcul Excel au format 1997 - 2003, créez d’abord une plage nommée dans Excel en spécifiant les colonnes et les lignes de la feuille de calcul Excel à sélectionner. Le nom de la plage peut correspondre à un nom de table dans une requête distribuée.

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

Pour accéder aux données d'une feuille de calcul Excel, associez une plage de cellules à un nom. La requête suivante peut s'utiliser pour accéder à la plage nommée SalesData en tant que table en utilisant le serveur lié défini précédemment.

SELECT *
   FROM ExcelSource...SalesData;
GO

Si SQL Server s’exécute sous un compte de domaine qui accède à un partage distant, un chemin UNC peut être utilisé au lieu d’un lecteur mappé.

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

E. Utiliser le fournisseur Microsoft OLE DB pour Jet pour accéder à un fichier texte

Le code exemple suivant crée un serveur lié pour accéder directement aux fichiers texte, sans lier les fichiers en tant que tables dans un fichier .mdb de Microsoft Access. Le fournisseur est Microsoft.Jet.OLEDB.4.0 ; la chaîne de caractères du fournisseur est Text.

La source de données est le chemin d'accès complet au répertoire qui contient les fichiers texte. Un fichier schema.ini, qui décrit la structure des fichiers texte, doit se trouver dans le même répertoire que les fichiers texte. Pour plus d’informations sur la création d’un fichier schema.ini, consultez la documentation jet Moteur de base de données.

Tout d’abord, créez un serveur lié.

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

Configurez les mappages de connexion.

EXEC sp_addlinkedsrvlogin txtsrv, FALSE, Admin, NULL;

Répertoriez les tables dans le serveur lié.

EXEC sp_tables_ex txtsrv;

Interrogez l’une des tables, dans ce cas file1#txt, à l’aide d’un nom en quatre parties.

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

F. Utiliser le Fournisseur OLE DB Microsoft pour DB2

L’exemple suivant crée un serveur lié nommé DB2 qui utilise le Fournisseur OLE DB Microsoft pour 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. Ajouter une base de données Azure SQL en tant que serveur lié pour une utilisation avec des requêtes distribuées sur des bases de données cloud et locales

Vous pouvez ajouter une base de données Azure SQL en tant que serveur lié, puis l’utiliser avec des requêtes distribuées qui s’étendent sur les bases de données locales et cloud. Il s’agit d’un composant pour les solutions hybrides de base de données couvrant les réseaux d’entreprise locaux et le cloud Azure.

Le produit de zone SQL Server contient la fonctionnalité de requête distribuée, qui vous permet d’écrire des requêtes pour combiner des données à partir de sources de données locales et de données provenant de sources distantes (y compris les données provenant de sources de données non-SQL Server) définies en tant que serveurs liés. Chaque base de données Azure SQL (à l’exception de la base de données du master serveur logique) peut être ajoutée en tant que serveur lié individuel, puis utilisée directement dans vos applications de base de données comme n’importe quelle autre base de données.

Les avantages de l’utilisation d’Azure SQL Database incluent la facilité de gestion, la haute disponibilité, la scalabilité, l’utilisation d’un modèle de développement familier et un modèle de données relationnelles. Les exigences de votre application de base de données déterminent comment elle utiliserait Azure SQL Database dans le cloud. Vous pouvez déplacer toutes vos données à la fois vers Azure SQL Database ou déplacer progressivement certaines de vos données tout en conservant les données restantes locales. Pour une telle application de base de données hybride, Azure SQL Database peut désormais être ajouté en tant que serveurs liés et l’application de base de données peut émettre des requêtes distribuées pour combiner des données à partir d’Azure SQL Database et de sources de données locales.

Voici un exemple expliquant comment se connecter à une base de données Azure SQL à l’aide de requêtes distribuées.

Tout d’abord, ajoutez une base de données Azure SQL en tant que serveur lié à l’aide de SQL Server Native Client.

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

Ajoutez des informations d’identification et des options à ce serveur lié.

EXEC sp_addlinkedsrvlogin
  @rmtsrvname = 'LinkedServerName',
  @useself = 'false',
  @rmtuser = 'LoginName',
  @rmtpassword = 'myPassword';

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

À présent, utilisez le serveur lié pour exécuter des requêtes à l’aide de noms en quatre parties, même pour créer une table et insérer des données.

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;

Interrogez les données à l’aide de noms en quatre parties :

SELECT * FROM LinkedServerName.DatabaseName.SchemaName.TableName;

H. Créer un serveur lié Azure SQL Managed Instance avec l’authentification d’identité managée

Pour créer un serveur lié avec l’authentification d’identité managée, exécutez la commande T-SQL suivante, en <managed_instance> remplaçant par votre propre instance managée SQL. La méthode d’authentification utilise ActiveDirectoryMSI dans le paramètre @provstr . Envisagez d’utiliser @locallogin = NULL éventuellement pour autoriser toutes les connexions locales.

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

Pour activer l’authentification avec des identités managées, une identité managée affectée à Azure SQL Managed Instance doit être ajoutée en tant que connexion à l’instance managée distante. Les identités managées affectées par le système et affectées par l’utilisateur sont prises en charge.

Si une identité principale est définie, elle est utilisée, sinon l’identité managée affectée par le système est utilisée. Si l’identité managée est recréée avec le même nom, la connexion sur l’instance distante doit également être recréée, car le nouvel ID d’application d’identité managée et le SID du principal du service SQL Managed Instance ne correspondent plus. Pour vérifier que ces deux valeurs correspondent, convertissez le SID en ID d’application avec la requête suivante.

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

I. Créer un serveur lié SQL Managed Instance en utilisant l'authentification Microsoft Entra pass-through

Pour créer un serveur lié avec l’authentification directe, exécutez la commande T-SQL suivante, en remplaçant <managed_instance> par votre propre serveur d’instance managée SQL :

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

Avec l’authentification directe, le contexte de sécurité de la connexion locale est transféré à l’instance distante. L’authentification directe nécessite que le principal Microsoft Entra soit ajouté en tant que connexion sur l’instance gérée Azure SQL Managed Instance locale et distante. Les deux instances managées doivent se trouver dans un groupe d’approbations de serveur. Lorsque les exigences sont remplies, l’utilisateur peut se connecter à une instance locale et interroger l’instance distante via l’objet serveur lié.

Contenu connexe

• Procédures stockées de requêtes distribuées (Transact-SQL)
• sp_addlinkedsrvlogin (Transact-SQL)
• sp_addserver (Transact-SQL)
• sp_dropserver (Transact-SQL)
• sp_serveroption (Transact-SQL)
• sp_setnetname (Transact-SQL)
• Procédures stockées système (Transact-SQL)
• Tables système (Transact-SQL)