sp_addlinkedserver (Transact-SQL)

Se aplica a: SQL Server Azure SQL Managed Instance

Crea un servidor vinculado. Un servidor vinculado proporciona acceso a consultas distribuidas y heterogéneas en orígenes de datos OLE DB. Después de crear 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 = ] N'server'
    [ , [ @srvproduct = ] N'srvproduct' ]
    [ , [ @provider = ] N'provider' ]
    [ , [ @datasrc = ] N'datasrc' ]
    [ , [ @location = ] N'location' ]
    [ , [ @provstr = ] N'provstr' ]
    [ , [ @catalog = ] N'catalog' ]
    [ , [ @linkedstyle = ] linkedstyle ]
[ ; ]

Argumentos

[ @server = ] N'server'

Nombre del servidor vinculado que se va a crear. @server es sysname, sin ningún valor predeterminado.

[ @srvproduct = ] N'srvproduct'

Nombre del producto del origen de datos OLE DB que se va a agregar como servidor vinculado. @srvproduct es nvarchar(128), con un valor predeterminado de NULL. Si el valor es SQL Server, @provider, @datasrc, @location, @provstr y @catalog no es necesario especificar.

[ @provider = ] N'provider'

Identificador de programación único (PROGID) del proveedor OLE DB que corresponde a este origen de datos. El @provider debe ser único para el proveedor OLE DB especificado instalado en el equipo actual. @provider es nvarchar(128), con un valor predeterminado de NULL.

• En SQL Server 2019 (15.x) y versiones anteriores, si se omite @provider , SQLNCLI se usa . El uso SQLNCLI redirigirá SQL Server a la versión más reciente 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, MSOLEDBSQL se recomienda.

• A partir de SQL Server 2022 (16.x), debe especificar un nombre de proveedor. Se recomiendaMSOLEDBSQL . Si omite @provider, puede experimentar un comportamiento inesperado.

[ @datasrc = ] N'datasrc'

Nombre del origen de datos como lo interpreta el proveedor OLE DB. @datasrc es nvarchar(4000), con un valor predeterminado de NULL. @datasrc se pasa como propiedad DBPROP_INIT_DATASOURCE para inicializar el proveedor OLE DB.

[ @location = ] N'location'

Ubicación de la base de datos como interpreta el proveedor OLE DB. @location es nvarchar(4000), con un valor predeterminado de NULL. @location se pasa como propiedad DBPROP_INIT_LOCATION para inicializar el proveedor OLE DB.

[ @provstr = ] N'provstr'

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

Cuando se crea el servidor vinculado en el proveedor OLE DB de SQL Server Native Client, la instancia se puede especificar mediante la SERVER palabra clave 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.

• 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.

• Si se ejecuta sp_addlinkedserver desde un inicio de sesión local o un inicio de sesión que no forma parte del rol sysadmin , es posible que reciba el siguiente error:

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

  Para resolver este problema, agregue el parámetro a la User ID cadena de conexión. En el ejemplo siguiente, myUser es el identificador de usuario que se pasa al cadena de conexión:

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

  Para obtener más información, consulte Acceso al servidor remoto denegado porque no existe ninguna asignación de inicio de sesión.

[ @catalog = ] N'catalog'

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

[ @linkedstyle = ] linkedstyle

Solamente se identifica con fines informativos. No compatible. La compatibilidad con versiones posteriores no está garantizada.

Valores de código de retorno

0 (correcto) o 1 (erróneo).

Conjunto 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.

¹ 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 @datasrc para especificar el servidor.

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

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

Los parámetros @datasrc, @location, @provstr y @catalog 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 establece la propiedad de inicialización 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.

Permisos

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

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.

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.

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

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 @datasrc .

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

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

Para crear una definición de servidor vinculado mediante microsoft OLE DB Provider for Jet para tener acceso a una hoja de cálculo de Excel en el formato 1997 - 2003, cree primero un rango 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 de schema.ini, consulte la documentación de Jet Motor de base de datos.

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 ejemplo siguiente se crea un servidor vinculado denominado DB2 que usa el Proveedor OLE DB de Microsoft para 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. Adición de 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. Este es un componente para las soluciones híbridas de base de datos que abarcan redes corporativas locales y la nube de Azure.

El producto de cuadro SQL Server 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 que no son de SQL Server) definidos como servidores vinculados. Todas las bases de datos de Azure SQL (excepto la base de datos del master servidor lógico) se pueden agregar como un servidor vinculado individual y, a continuación, se usan directamente en las aplicaciones de base de datos como cualquier otra base de datos.

Entre las ventajas de usar Azure SQL Database se incluyen la capacidad de administración, la alta disponibilidad, la escalabilidad, el trabajo con un modelo de desarrollo conocido y un modelo de datos relacional. 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, Azure SQL Database ahora se puede agregar 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 que explica cómo conectarse a una base de datos de Azure SQL mediante consultas distribuidas.

En primer lugar, agregue una base de datos de Azure SQL como servidor vinculado mediante 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 de Azure SQL Instancia administrada con autenticación de identidad administrada

Para crear un servidor vinculado con autenticación de identidad administrada, ejecute el siguiente T-SQL, reemplazando por <managed_instance> su propia instancia administrada de SQL. El método de autenticación usa ActiveDirectoryMSI en el parámetro @provstr . 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_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';

Para habilitar la autenticación con identidades administradas, se debe agregar una identidad administrada asignada a azure SQL Instancia administrada como inicio de sesión en la instancia administrada remota. Tanto las identidades administradas asignadas por el usuario y por el sistema están admitidas.

Si se establece una identidad principal, se usa; de lo contrario, se usa 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 el SID de la entidad de servicio de SQL Instancia administrada ya no coinciden. Para comprobar que estos dos valores coinciden, convierta el SID al identificador de aplicación con la siguiente consulta.

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

I. Creación de un servidor vinculado de SQL Managed Instance con autenticación de paso a través para Microsoft Entra

Para crear un servidor vinculado con autenticación de paso a través, ejecute el siguiente T-SQL y reemplace por <managed_instance> su propio servidor de instancia administrada de SQL:

EXEC master.dbo.sp_addlinkedserver
    @server = N'MyLinkedServer',
    @srvproduct = N'',
    @provider = N'MSOLEDBSQL',
    @datasrc = N'<mi_name>.<dns_zone>.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 la instancia remota. La autenticación de paso a través requiere que la entidad de seguridad de Microsoft Entra se agregue como inicio de sesión en el Instancia administrada de Azure SQL local y remoto. Ambas instancias administradas deben estar en un grupo de confianza de 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.

Contenido relacionado

• Procedimientos almacenados de consultas distribuidas (Transact-SQL)
• sp_addlinkedsrvlogin (Transact-SQL)
• sp_addserver (Transact-SQL)
• sp_dropserver (Transact-SQL)
• sp_serveroption (Transact-SQL)
• sp_setnetname (Transact-SQL)
• Procedimientos almacenados del sistema (Transact-SQL)
• Tablas del sistema (Transact-SQL)