sp_addlinkedserver (Transact-SQL)

Se aplica a: SQL Server Azure SQL Managed Instance

Crea un servidor vinculado. Un servidor vinculado permite obtener acceso a consultas heterogéneas distribuidas en orígenes de datos OLE DB. Una vez creado un servidor vinculado mediante sp_addlinkedserver, las consultas distribuidas se pueden ejecutar en este servidor. Si el servidor vinculado se define como una instancia de SQL Server, se pueden ejecutar procedimientos almacenados remotos.

Convenciones de sintaxis de Transact-SQL

Sintaxis

sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ]   
     [ , [ @provider= ] 'provider_name' ]  
     [ , [ @datasrc= ] 'data_source' ]   
     [ , [ @location= ] 'location' ]   
     [ , [ @provstr= ] 'provider_string' ]   
     [ , [ @catalog= ] 'catalog' ]   

Argumentos

[ @server = ] 'server'

Es el nombre del servidor vinculado que se va a crear. El servidor de argumentos es sysname, sin ningún valor predeterminado.

[ @srvproduct = ] 'product_name'

Es el nombre del producto del origen de datos OLE DB para agregarlo como servidor vinculado. El valor product_name es nvarchar(128), con un valor predeterminado de NULL. Si el valor es SQL Server, no es necesario especificar provider_name, data_source, ubicación, provider_string y catálogo.

[ @provider = ] 'provider_name'

Es el identificador de programación (PROGID) único del proveedor OLE DB que corresponde a este origen de datos. El provider_name debe ser único para el proveedor OLE DB especificado instalado en el equipo actual. El valor provider_name es nvarchar(128) .

  • Antes de SQL Server 2022 (16.x), si @provider se omite, se usa SQLNCLI. El uso de SQLNCLI redirigirá SQL Server a la última versión del proveedor OLE DB de SQL Server Native Client. Se espera que el proveedor OLE DB se registre con el PROGID especificado en el registro. En lugar de SQLNCLI, se recomienda MSOLEDBSQL.
  • A partir de SQL Server 2022 (16.x), debe especificar un nombre de proveedor. Se recomienda MSOLEDBSQL.

Importante

SQL Server Native Client (a menudo abreviado SNAC) se ha quitado de SQL Server 2022 (16.x) y SQL Server Management Studio 19 (SSMS). Para nuevos trabajos de desarrollo, no se recomiendan ni el proveedor OLE DB de SQL Server Native Client (SQLNCLI o SQLNCLI11) ni el proveedor OLE DB de Microsoft para SQL Server (SQLOLEDB) heredado. Utilice el nuevo Controlador Microsoft OLE DB (MSOLEDBSQL) para SQL Server de ahora en adelante.

[ @datasrc = ] 'data_source'

Es el nombre del origen de datos como lo interpreta el proveedor OLE DB. El valor data_source es nvarchar(4000). data_source se pasa como la propiedad DBPROP_INIT_DATASOURCE para inicializar el proveedor OLE DB.

[ @location = ] 'location'

Es la ubicación de la base de datos según la interpretación del proveedor OLE DB. La ubicación del valor es nvarchar(4000), con un valor predeterminado de NULL. La ubicación del argumento se pasa como la propiedad DBPROP_INIT_LOCATION para inicializar el proveedor OLE DB.

[ @provstr = ] 'provider_string'

Es la cadena de conexión específica del proveedor OLE DB que identifica un origen de datos único. El valor provider_string es nvarchar(4000) con un valor predeterminado de NULL. El argumento provstr se pasa a IDataInitialize o se establece como la propiedad DBPROP_INIT_PROVIDERSTRING para inicializar el proveedor OLE DB.

Cuando se crea el servidor vinculado con el proveedor OLE DB de SQL Server Native Client, la instancia se puede especificar mediante la palabra clave SERVER como SERVER=servername\\instancename para especificar una instancia específica de SQL Server. Servername es el nombre del equipo en el que se ejecuta SQL Server y instancename es el nombre de la instancia específica de SQL Server a la que se conectará el usuario.

Nota

Para tener acceso a una base de datos reflejada, una cadena de conexión debe contener el nombre de la base de datos. Este nombre es necesario para que el proveedor de acceso a datos pueda intentar la conmutación por error. La base de datos se puede especificar en el parámetro @provstr o @catalog . Opcionalmente, la cadena de conexión también puede proporcionar un nombre de asociado de conmutación por error.

[ @catalog = ] 'catalog'

Es el catálogo que se va a usar cuando se realiza una conexión al proveedor OLE DB. El catálogo de valores es sysname, con un valor predeterminado de NULL. El catálogo de argumentos se pasa como la propiedad DBPROP_INIT_CATALOG para inicializar el proveedor OLE DB. Cuando el servidor vinculado se define en una instancia de SQL Server, catalog hace referencia a la base de datos predeterminada a la que se asigna el servidor vinculado.

Valores de código de retorno

0 (correcto) o 1 (error)

Conjuntos de resultados

Ninguno.

Comentarios

En la tabla siguiente se muestran las distintas maneras de configurar un servidor vinculado para los orígenes de datos a los que se puede tener acceso a través de OLE DB. Existen varias formas de configurar un servidor vinculado para un origen de datos determinado; puede haber más de una fila por tipo de origen de datos. En esta tabla también se muestran los sp_addlinkedserver valores de parámetro que se usarán para configurar el servidor vinculado.

Origen de datos remotos de OLE DB Proveedor OLE DB Product_name provider_name data_source ubicación provider_string catalog
SQL Server Proveedor OLE DB de Microsoft SQL Server Native Client SQL Server 1 (valor predeterminado)
SQL Server Proveedor OLE DB de Microsoft SQL Server Native Client SQLNCLI Nombre de red de SQL Server (para la instancia predeterminada) Nombre de la base de datos (opcional)
SQL Server Proveedor OLE DB de Microsoft SQL Server Native Client SQLNCLI Nombredeservidor\instancename (para una instancia específica) Nombre de la base de datos (opcional)
Oracle, versión 8 y posterior Proveedor Oracle para OLE DB Any OraOLEDB.Oracle Alias para base de datos Oracle
Access/Jet Proveedor Microsoft OLE DB para Jet Any Microsoft.Jet.OLEDB.4.0 Ruta de acceso completa del archivo de base de datos Jet
Origen de datos ODBC Proveedor Microsoft OLE DB para ODBC Any MSDASQL DSN del sistema del origen de datos ODBC
Origen de datos ODBC Proveedor Microsoft OLE DB para ODBC Any MSDASQL Cadena de conexión ODBC
Sistema de archivos Proveedor OLE DB de Microsoft para el servicio de indexación Any MSIDXS Nombre de catálogo de Servicios de Index Server
Hoja de cálculo de Microsoft Excel Proveedor Microsoft OLE DB para Jet Any Microsoft.Jet.OLEDB.4.0 Ruta de acceso completa del archivo Excel Excel 5.0
Base de datos IBM DB2 Proveedor Microsoft OLE DB para DB2 Any DB2OLEDB Consulte Proveedor OLE DB de Microsoft para DB2 documentación. Nombre del catálogo de la base de datos DB2

1 Esta forma de configurar un servidor vinculado obliga a que el nombre del servidor vinculado sea el mismo que el nombre de red de la instancia remota de SQL Server. Use data_source para especificar el servidor.

2 "Any" indica que el nombre del producto puede ser cualquier cosa.

El proveedor OLE DB de Microsoft SQL Server Native Client es el proveedor que se usa con SQL Server si no se especifica ningún nombre de proveedor o si se especifica SQL Server como nombre del producto. Incluso si especifica el nombre de proveedor antiguo, SQLOLEDB, éste se cambiará a SQLNCLI cuando se mantenga en el catálogo.

Los parámetros data_source, ubicación, provider_string y catálogo identifican la base de datos o las bases de datos a las que apunta el servidor vinculado. Si alguno de estos parámetros es NULL, no se establecerá la propiedad de inicialización de OLE DB correspondiente.

En un entorno en clúster, al especificar nombres de archivo para apuntar a orígenes de datos OLE DB, utilice el nombre UNC (convención de nomenclatura universal) o una unidad compartida para especificar la ubicación.

El procedimiento sp_addlinkedserver almacenado no se puede ejecutar dentro de una transacción definida por el usuario.

Importante

Azure SQL Managed Instance actualmente solo admite SQL Server, SQL Database y otros SQL Managed Instance como orígenes de datos remotos.

Importante

Cuando se crea un servidor vinculado mediante sp_addlinkedserver, se agrega una asignación automática predeterminada para todos los inicios de sesión locales. En el caso de los proveedores que no son de SQL Server, es posible que SQL Server inicios de sesión autenticados puedan obtener acceso al proveedor en la cuenta de servicio de SQL Server. Los administradores deben tener en cuenta el uso de sp_droplinkedsrvlogin <linkedserver_name>, NULL para quitar la asignación global.

Permisos

La sp_addlinkedserver instrucción requiere el permiso ALTER ANY LINKED SERVER . (El cuadro de diálogo Nuevo servidor vinculado de SQL Server Management Studio se implementa de forma que requiera la pertenencia al sysadmin rol fijo de servidor).

Ejemplos

A. Uso del proveedor OLE DB de Microsoft SQL Server

En el siguiente ejemplo se crea un servidor vinculado denominado SEATTLESales. El nombre del producto es SQL Server y no se utiliza ningún nombre de proveedor.

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

En el ejemplo siguiente se crea un servidor S1_instance1 vinculado en una instancia de SQL Server mediante el controlador OLE DB de SQL Server.

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

En el ejemplo siguiente se crea un servidor S1_instance1 vinculado en una instancia de SQL Server mediante el proveedor OLE DB de SQL Server Native Client.

Importante

El proveedor OLE DB de SQL Server Native Client (SQLNCLI) permanece en desuso y no se recomienda utilizarlo para nuevos trabajos de desarrollo. En su lugar, use el nuevo Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL), que se actualizará con las características de servidor más recientes.

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

B. Uso del proveedor OLE DB de Microsoft para Microsoft Access

El proveedor de Microsoft.Jet.OLEDB.4.0 conecta a las bases de datos de Microsoft Access que usan el formato 2002-2003. En el siguiente ejemplo se crea un servidor vinculado denominado SEATTLE Mktg.

Nota

En este ejemplo se supone que Microsoft Access y la Northwind base de datos de ejemplo están instaladas y que la Northwind base de datos reside en C:\Msoffice\Access\Samples en el mismo servidor que la instancia de SQL Server.

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. Usar el proveedor OLE DB de Microsoft para ODBC con el parámetro data_source

En el ejemplo siguiente se crea un servidor vinculado denominado SEATTLE Payroll que usa el proveedor OLE DB de Microsoft para ODBC (MSDASQL) y el parámetro data_source .

Nota

El nombre del origen de datos ODBC especificado debe definirse como DSN del sistema en el servidor antes de utilizar el servidor vinculado.

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

D. Usar la hoja de cálculo del proveedor OLE DB de Microsoft para Excel

Para crear una definición de servidor vinculado mediante el proveedor OLE DB de Microsoft para Jet para tener acceso a una hoja de cálculo de Excel en el formato 1997 - 2003, primero cree un intervalo con nombre en Excel especificando las columnas y filas de la hoja de cálculo de Excel que se va a seleccionar. Entonces, podrá hacerse referencia al nombre del intervalo como un nombre de tabla en una consulta distribuida.

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

Para obtener acceso a los datos de una hoja de cálculo Excel, asocie un nombre a un intervalo de celdas. La consulta siguiente se puede utilizar para obtener acceso al intervalo denominado SalesData especificado como una tabla utilizando el servidor vinculado que se creó anteriormente.

SELECT *  
   FROM ExcelSource...SalesData;  
GO  

Si SQL Server se ejecuta en una cuenta de dominio que tiene acceso a un recurso compartido remoto, se puede usar una ruta de acceso UNC en lugar de una unidad asignada.

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

E. Usar el proveedor OLE DB de Microsoft para Jet para acceder a un archivo de texto

En este ejemplo se crea un servidor vinculado para obtener acceso directamente a archivos de texto, sin necesidad de vincular los archivos como tablas en un archivo .mdb de Access. El proveedor es Microsoft.Jet.OLEDB.4.0 y la cadena de proveedor es Text.

El origen de datos es la ruta de acceso completa del directorio que contiene los archivos de texto. En el mismo directorio que los archivos de texto, debe existir un archivo schema.ini, que describe la estructura de dichos archivos. Para obtener más información sobre cómo crear un archivo schema.ini, consulte la documentación del motor de base de datos Jet.

En primer lugar, cree un servidor vinculado.

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

Configurar asignaciones de inicio de sesión.

EXEC sp_addlinkedsrvlogin txtsrv, FALSE, Admin, NULL;  

Enumere las tablas del servidor vinculado.

EXEC sp_tables_ex txtsrv;  

Consulte una de las tablas, en este caso file1#txt, con un nombre de cuatro partes.

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

F. Usar el Proveedor OLE DB de Microsoft para DB2

En el siguiente ejemplo se crea un servidor vinculado llamado DB2 que utiliza el Microsoft OLE DB Provider for 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. Agregar una base de datos de Azure SQL como servidor vinculado para su uso con consultas distribuidas en bases de datos locales y en la nube

Puede agregar una base de datos de Azure SQL como servidor vinculado y, a continuación, usarla con consultas distribuidas que abarcan las bases de datos locales y en la nube. Se trata de un componente para soluciones híbridas de base de datos que abarcan redes corporativas locales y la nube de Azure.

El producto SQL Server box contiene la característica de consulta distribuida, que permite escribir consultas para combinar datos de orígenes de datos locales y datos de orígenes remotos (incluidos los datos de orígenes de datos no SQL Server) definidos como servidores vinculados. Cada base de datos de Azure SQL (excepto la base de datos del master servidor lógico) se puede agregar como un servidor vinculado individual y, a continuación, se usa directamente en las aplicaciones de base de datos como cualquier otra base de datos.

Las ventajas de usar Azure SQL Database incluyen la capacidad de administración, la alta disponibilidad, la escalabilidad, el trabajo con un modelo de desarrollo conocido y un modelo de datos relacionales. Los requisitos de la aplicación de base de datos determinan cómo usaría Azure SQL Database en la nube. Puede mover todos los datos a la vez a Azure SQL Database o mover progresivamente algunos de los datos mientras mantiene los datos restantes en el entorno local. Para esta aplicación de base de datos híbrida, ahora se puede agregar Azure SQL Database como servidores vinculados y la aplicación de base de datos puede emitir consultas distribuidas para combinar datos de Azure SQL Database y orígenes de datos locales.

Este es un ejemplo sencillo que explica cómo conectarse a una base de datos de Azure SQL mediante consultas distribuidas.

En primer lugar, agregue una base de datos Azure SQL como servidor vinculado, mediante el uso de SQL Server Native Client.

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

Agregue credenciales y opciones a este servidor vinculado.

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

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

Ahora, use el servidor vinculado para ejecutar consultas con nombres de cuatro partes, incluso para crear una nueva tabla e insertar datos.

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; 

Consulte los datos con nombres de cuatro partes:

SELECT * FROM LinkedServerName.DatabaseName.SchemaName.TableName; 

H. Creación de un servidor vinculado con autenticación de identidad administrada para Azure AD

Para crear un servidor vinculado con autenticación de identidad administrada, ejecute el siguiente T-SQL. El método de autenticación usa ActiveDirectoryMSI en el @provstr parámetro . Considere la posibilidad de usar @locallogin = NULL opcionalmente para permitir todos los inicios de sesión locales.

EXEC master.dbo.sp_addlinkedserver
@server     = N'MyLinkedServer',
@srvproduct = N'',
@provider   = N'MSOLEDBSQL',
@provstr    = N'Server=mi.35e5bd1a0e9b.database.windows.net,1433;Authentication=ActiveDirectoryMSI;';

EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'MyLinkedServer',
@useself    = N'False',
@locallogin = N'user1@domain1.com';  

Si Azure SQL Managed Instance identidad administrada (anteriormente denominada identidad de servicio administrada) se agrega como inicio de sesión a una instancia administrada remota, la autenticación de identidad administrada es posible con el servidor vinculado creado como en el ejemplo anterior. Se admiten las identidades administradas asignadas por el sistema y las asignadas por el usuario.

Si se establece la identidad principal, se usará; de lo contrario, se usará la identidad administrada asignada por el sistema. Si la identidad administrada se vuelve a crear con el mismo nombre, también es necesario volver a crear el inicio de sesión en la instancia remota, ya que el nuevo identificador de aplicación de identidad administrada y Instancia administrada SID de la entidad de servicio ya no coinciden. Para comprobar que estos dos valores coinciden, convierta el SID al identificador de aplicación con la consulta siguiente.

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

I. Creación de un servidor vinculado con autenticación de paso a través para Azure AD

Para crear un servidor vinculado con autenticación de paso a través, ejecute después de T-SQL.

EXEC master.dbo.sp_addlinkedserver
@server     = N'MyLinkedServer',
@srvproduct = N'',
@provider   = N'MSOLEDBSQL',
@datasrc    = N'mi.35e5bd1a0e9b.database.windows.net,1433';

Con la autenticación de paso a través, el contexto de seguridad del inicio de sesión local se transfiere a una instancia remota. La autenticación de paso a través requiere que la entidad de seguridad de AAD se agregue como inicio de sesión en la Azure SQL Managed Instance local y remota. Ambas instancias administradas deben estar en un grupo de confianza del servidor. Cuando se cumplen los requisitos, el usuario puede iniciar sesión en una instancia local y consultar la instancia remota a través del objeto de servidor vinculado.

Consulte también