Compartir vía

Creación de servidores vinculados (Motor de base de datos de SQL Server)

  • Artículo

Se aplica a: SQL Server Azure SQL Managed Instance

En este artículo se muestra cómo crear un servidor vinculado y acceder a datos desde otro SQL Server, Azure SQL Managed Instance u otro origen de datos mediante SQL Server Management Studio (SSMS) o Transact-SQL. Los servidores vinculados permiten que el motor de base de datos de SQL Server y Azure SQL Managed Instance lean datos de los orígenes de datos remotos y ejecuten comandos en los servidores de bases de datos remotos (por ejemplo, orígenes de datos OLE DB) fuera de la instancia de SQL Server.

Fondo

Los servidores vinculados normalmente se configuran para habilitar el motor de base de datos a fin de ejecutar una instrucción Transact-SQL que incluye las tablas de otra instancia de SQL Server u otro producto de base de datos como Oracle. Muchos tipos de orígenes de datos pueden configurarse como servidores vinculados, como proveedores de bases de datos externos y Azure Cosmos DB.

Después de crear un servidor vinculado, las consultas distribuidas se pueden ejecutar en este servidor. Las consultas pueden unir tablas de varios orígenes de datos. Si el servidor vinculado se define como una instancia de SQL Server o Azure SQL Managed Instance, se pueden ejecutar procedimientos almacenados remotos.

Las capacidades y los argumentos requeridos de los servidores vinculados pueden variar significativamente. En los ejemplos de este artículo se proporcionan un ejemplo típico, pero no se describen todas las opciones. Para obtener más información, vea sp_addlinkedserver (Transact-SQL).

Permisos

Cuando se usan instrucciones de Transact-SQL, se requiere el permiso ALTER ANY LINKED SERVER en el servidor o la pertenencia al rol fijo de servidor setupadmin. Cuando se usa Management Studio, se requiere el permiso CONTROL SERVER o la pertenencia al rol fijo de servidor sysadmin.

Creación de servidor vinculado con SSMS

Cree un servidor vinculado con SSMS mediante el procedimiento siguiente:

Apertura del cuadro de diálogo Nuevo servidor vinculado

En SQL Server Management Studio (SSMS):

  1. Abra el Explorador de objetos.
  2. Expanda Objetos de servidor.
  3. Haga clic con el botón derecho en Servidores vinculados.
  4. Seleccione Nuevo servidor vinculado.

Edición de la página General de las propiedades del servidor vinculado

En el cuadro Servidor vinculado de la página General , escriba el nombre de la instancia de SQL Server al que esté vinculando.

Nota:

Si la instancia de SQL Server es la instancia predeterminada, escriba el nombre del equipo que hospede la instancia de SQL Server. Si SQL Server es una instancia con nombre, escriba el nombre del equipo y el de la instancia, por ejemplo, Accounting\SQLExpress.

Especifique el tipo de servidor y la información relacionada si es necesario:

  • SQL Server
    Identifique el servidor vinculado como una instancia de Microsoft SQL Server o Azure SQL Managed Instance. Si usa este método para definir un servidor vinculado, el nombre especificado en Servidor vinculado tiene que ser el nombre de red del servidor. Además, cualquier tabla obtenida del servidor pertenecerá a la base de datos predeterminada definida para el inicio de sesión del servidor vinculado.

  • Otro origen de datos
    Especifique un tipo de servidor OLE DB distinto de SQL Server. Al hacer clic en esta opción, se activan las opciones que aparecen debajo.

    • Proveedor
      Seleccione un origen de datos OLE DB del cuadro de lista. El proveedor OLE DB se ha registrado con el PROGID especificado en el registro.

    • Nombre del producto
      Escriba el nombre del producto del origen de datos OLE DB para agregarlo como servidor vinculado.

    • Origen de datos
      Escriba el nombre del origen de datos como lo interpreta el proveedor OLE DB. Si se está conectando a una instancia de SQL Server, proporcione el nombre de instancia.

    • Cadena de proveedor
      Escriba el identificador de programación (PROGID) único del proveedor OLE DB que corresponde al origen de datos. Para ver ejemplos de cadenas de proveedores válidas, vea sp_addlinkedserver (Transact-SQL).

    • Ubicación
      Escriba la ubicación de la base de datos según la interpretación del proveedor OLE DB.

    • Catálogo
      Escriba el nombre del catálogo que se va a usar cuando se establezca la conexión al proveedor OLE DB.

Edición de la página Seguridad de las propiedades del servidor vinculado

En la página Seguridad, especifique el contexto de seguridad que se usa cuando la instancia original se conecte con el servidor vinculado. Existen dos estrategias de configuración que se pueden usar solas o combinadas. La primera consiste en asignar inicios de sesión del servidor local al servidor remoto y la segunda, en definir el modo en el que el servidor vinculado tiene que tratar los inicios de sesión que no están asignados.

Adición de asignaciones de inicio de sesión

Opcionalmente, puede especificar cómo se autentican los inicios de sesión de servidor local específicos mediante el servidor vinculado.

En Local server login to remote server login mappings (Asignaciones de inicio de sesión de servidor local a servidor remoto), repita el proceso siguiente para cada inicio de sesión que quiera asignar:

  1. Seleccione Agregar.

  2. Especifique un inicio de sesión local.

    Permite especificar el inicio de sesión local que se puede conectar al servidor vinculado. El inicio de sesión local puede ser un inicio de sesión que utilice la autenticación de SQL Server o un inicio de sesión de autenticación de Windows. No se admite el uso de un grupo de Windows o un usuario de base de datos independiente. Utilice esta lista para restringir la conexión a inicios de sesión específicos o para permitir que algunos inicios de sesión se conecten como un inicio de sesión diferente.

    Nota:

    Los problemas comunes con los servidores vinculados que usan la autenticación de Windows en una instancia de SQL Server remota surgen de problemas con los nombres de entidad de seguridad de servicio (SPN). Para obtener más información, consulte Compatibilidad con Nombre de la entidad de seguridad del servicio (SPN) en conexiones cliente. Microsoft Administrador de configuración de Kerberos para SQL Server es una herramienta de diagnóstico que sirve para solucionar problemas de conectividad de Kerberos relacionados con SQL Server. Para obtener más información, vea Administrador de configuración de Microsoft Kerberos para SQL Server.

  3. Seleccione Suplantar (opcional).

    Pasa el nombre de usuario y la contraseña del inicio de sesión local al servidor vinculado. En la autenticación de SQL Server, debe existir un inicio de sesión con el mismo nombre y contraseña en el servidor remoto. En los inicios de sesión de Windows, el inicio de sesión debe ser un inicio de sesión válido en el servidor vinculado.

    Para utilizar la suplantación, la configuración debe cumplir los requisitos de la delegación.

  4. Especifique un usuario remoto si no usa la suplantación.

    Use el usuario remoto para asignar usuarios definidos en el Inicio de sesión local. El Usuario remoto debe ser un inicio de sesión de autenticación de SQL Server en el servidor remoto.

  5. Especifique una contraseña remota si no usa la suplantación.

    • Permite especificar la contraseña del usuario remoto.

  6. Seleccione Quitar para quitar un inicio de sesión local existente, si lo desea.

Especificación del contexto de seguridad predeterminado para los inicios de sesión que no estén presentes en la lista de asignaciones

En un entorno de dominio donde los usuarios se conectan mediante sus inicios de sesión, la selección de Se establecerán usando el contexto de seguridad actual del inicio de sesión suele ser la mejor opción. Cuando los usuarios se conecten a la versión original de SQL Server usando un inicio de sesión de SQL Server , la mejor opción suele ser seleccionar Se establecerán usando este contexto de seguridady, a continuación, proporcionar las credenciales necesarias para la autenticación en el servidor vinculado.

Seleccione una de las siguientes opciones:

  • No se establecerán
    No se establecerán conexiones para los inicios de sesión que no estén definidos en la lista.

  • Se establecerán sin usar un contexto de seguridad
    No se establecerán conexiones sin utilizar un contexto de seguridad para los inicios de sesión no definidos en la lista.

  • Se establecerán usando el contexto de seguridad actual del inicio de sesión
    Se establecerá una conexión con el contexto de seguridad actual del inicio de sesión para los inicios de sesión no definidos en la lista. Si está conectado al servidor local mediante la autenticación de Windows, las credenciales de Windows se utilizarán para conectarse al servidor remoto. Si está conectado al servidor local mediante la autenticación de SQL Server, la contraseña y el nombre de usuario de inicio de sesión se utilizarán para conectar al servidor remoto. En este caso, debe existir un inicio de sesión con el mismo nombre y contraseña en el servidor remoto.

  • Se establecerán usando este contexto de seguridad
    Una conexión con el inicio de sesión y la contraseña especificados en los cuadros Inicio de sesión remoto y Con contraseña para los inicios de sesión que no estén definidos en la lista. El usuario remoto debe ser un inicio de sesión de autenticación de SQL Server en el servidor remoto.

    Precaución

    Si se configura un servidor vinculado con la opción Se establecerán usando este contexto de seguridad, cualquier usuario de la instancia puede acceder al servidor vinculado remoto mediante este contexto. Esta opción puede tener potencialmente el resultado no deseado de facilitar el abuso o el acceso interno malintencionado. El inicio de sesión remoto autenticado de SQL proporcionado al servidor vinculado debe contar con los permisos mínimos necesarios en el servidor remoto para garantizar un principio de privilegios mínimos y reducir la superficie expuesta a ataques.

Edición de la página Opciones del servidor en las propiedades del servidor vinculado (opcional)

Para ver o especificar las opciones del servidor, seleccione la página Opciones del servidor. Puede editar cualquiera de las opciones siguientes:

  • Compatible con la intercalación
    Afecta a la ejecución de consultas distribuidas en los servidores vinculados. Si esta opción se establece en true, SQL Server asume que todos los caracteres del servidor vinculado son compatibles con el servidor local en lo que respecta a juego de caracteres y secuencia de intercalación (o criterio de ordenación). Esta opción habilita a SQL Server para enviar comparaciones en columnas de caracteres al proveedor. Si no se establece esta opción, SQL Server siempre evalúa localmente las comparaciones en las columnas de caracteres.

    Esta opción solo se debe establecer si se tiene la certeza de que el origen de datos correspondiente al servidor vinculado tiene el mismo juego de caracteres y criterio de ordenación que el servidor local.

  • Acceso a datos
    Habilita y deshabilita un servidor vinculado para el acceso a consultas distribuidas.

  • RPC
    Habilita las llamadas a procedimiento remoto (RPC) desde el servidor especificado.

  • RPC fuera
    Habilita RPC en el servidor especificado.

  • Usar intercalación remota
    Determina si se utilizará la intercalación de una columna remota o de un servidor local.

    Si es true, para los orígenes de datos de SQL Server se utilizará la intercalación de columnas remotas, y la intercalación especificada en el nombre de la intercalación se utilizará para los orígenes de datos que no sean de SQL Server.

    Si es false, las consultas distribuidas siempre utilizarán la intercalación predeterminada del servidor local, mientras que el nombre de intercalación y la intercalación de columnas remotas se pasarán por alto. El valor predeterminado es false.

  • Nombre de intercalación
    Especifica el nombre de la intercalación que ha utilizado el origen de datos remoto si Usar intercalación remota es true y el origen de datos no es de SQL Server. El nombre debe pertenecer a una de las intercalaciones que admite SQL Server.

    Utilice esta opción cuando se obtenga acceso a un origen de datos OLE DB que no sea SQL Server, pero que tenga una intercalación que coincida con una de las intercalaciones de SQL Server.

    El servidor vinculado debe permitir el uso de una única intercalación para todas las columnas de ese servidor. No establezca esta opción si el servidor vinculado admite varias intercalaciones dentro de un único origen de datos o si no se puede determinar si la intercalación del servidor vinculado coincide con alguna de las intercalaciones de SQL Server.

  • Tiempo de espera de la conexión
    Valor del tiempo de espera en segundos para conectarse a un servidor vinculado.

    Si es 0, use el valor de la opción Tiempo de espera de inicio de sesión remoto predeterminado sp_configure.

  • Tiempo de espera de la consulta
    Valor del tiempo de espera en segundos para las consultas que se realizan en un servidor vinculado.

    Si es 0, use el valor de la opción Tiempo de espera de consulta remota predeterminado sp_configure.

  • Habilitar promoción de transacciones distribuidas
    Use esta opción para proteger las acciones de un procedimiento entre servidores a través de una transacción del Coordinador de transacciones distribuidas de Microsoft (MS DTC). Cuando esta opción es TRUE, al llamar a un procedimiento remoto almacenado se inicia una transacción distribuida y se da de alta en MS DTC. Para obtener más información, vea sp_serveroption (Transact-SQL).

Guardado del servidor vinculado

Seleccione Aceptar.

Visualización o edición de las opciones del proveedor de servidores vinculados en SSMS

Todos los proveedores no tienen las mismas opciones disponibles. Por ejemplo, algunos tipos de datos tienen índices disponibles y otros pueden no tenerlos. Utilice este cuadro de diálogo para que SQL Server pueda conocer mejor las capacidades del proveedor. SQL Server instala algunos proveedores de datos comunes; con todo, cuando cambia el producto que proporciona los datos, el proveedor instalado por SQL Server podría no admitir todas las características más recientes. La mejor fuente de información sobre las capacidades del producto que proporciona los datos es la documentación del producto.
Para abrir la página Opciones de proveedor del servidor vinculado en SSMS:

  1. Abra el Explorador de objetos.
  2. Expanda Objetos de servidor.
  3. Expanda Servidores vinculados.
  4. Expanda Proveedores.
  5. Haga clic con el botón derecho en un proveedor y seleccione Propiedades.

Las opciones de proveedores se definen de la forma siguiente:

  • Parámetro dinámico
    Indica que el proveedor permite la sintaxis de marcador de parámetro '?' para consultas con parámetros. Establezca esta opción solo si el proveedor admite la interfaz ICommandWithParameters y '?’ como marcador de parámetro. Si establece esta opción, permitirá a SQL Server ejecutar consultas con parámetros en el proveedor. La capacidad de ejecutar consultas con parámetros en el proveedor puede mejorar el rendimiento de determinadas consultas.

  • Consultas anidadas
    Indica que el proveedor permite instrucciones SELECT anidadas en la cláusula FROM. Si establece esta opción, permitirá a SQL Server delegar en el proveedor determinadas consultas que precisan anidar instrucciones SELECT en la cláusula FROM.

  • Solo nivel cero
    Solo se invocan interfaces OLE DB de nivel 0 en el proveedor.

  • Permitir en proceso

    SQL Server permite crear una instancia del proveedor como un servidor en proceso. Si no se establece esta opción, el comportamiento predeterminado consiste en crear una instancia del proveedor fuera del proceso de SQL Server. Crear una instancia del proveedor fuera del proceso de SQL Server permite proteger este proceso de errores en el proveedor. Si se crea una instancia del proveedor fuera del proceso de SQL Server, no se permitirán actualizaciones ni inserciones que hagan referencia a columnas long (text, ntext, o image).

  • Actualizaciones no realizadas
    SQL Server permite realizar actualizaciones, aunque ITransactionLocal no esté disponible. Si esta opción está habilitada, no podrá recuperar las actualizaciones en el proveedor, ya que éste no admite transacciones.

  • Índice como ruta de acceso
    SQL Server tratará de usar los índices del proveedor para capturar los datos. De forma predeterminada, los índices solo se utilizan para metadatos y nunca se abren

  • Denegar el acceso ad hoc
    SQL Server no permite el acceso ad hoc mediante las funciones OPENROWSET y OPENDATASOURCE en el proveedor OLE DB. Si esta opción no esta establecida, SQL Server no permite el acceso ad hoc.

  • Admite el operador LIKE
    Indica que el proveedor admite consultas mediante la palabra clave LIKE.

Creación de un servidor vinculado con Transact-SQL

Para crear un servidor vinculado mediante Transact-SQL, siga las instrucciones sp_addlinkedserver (Transact-SQL), CREATE LOGIN (Transact-SQL), y sp_addlinkedsrvlogin (Transact-SQL).

En este ejemplo se crea un servidor vinculado a otra instancia de SQL Server con Transact-SQL:

  1. En el Editor de consultas, escriba el siguiente comando de Transact-SQL para vincular a una instancia de SQL Server denominada SRVR002\ACCTG:

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

  2. Ejecute el siguiente código para configurar el servidor vinculado con el fin de que use las credenciales de dominio del inicio de sesión que usa el servidor vinculado.

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

Seguimiento: pasos que se deben realizar después de crear un servidor vinculado

Los pasos a continuación le ayudarán a validar un servidor vinculado.

Pruebe el servidor vinculado.

Considere cualquiera de los dos enfoques a continuación para probar la autenticación de un servidor vinculado en el contexto de seguridad actual.

  • Para comprobar la capacidad de conexión a un servidor vinculado en SSMS, vaya al servidor vinculado en el Explorador de objetos, haga clic con el botón derecho en él y, luego, seleccione Probar conexión.

  • Para probar la capacidad de conexión a un servidor vinculado en T-SQL, ejecute una instrucción SELECT simple, por ejemplo, para recuperar información básica del catálogo de bases de datos. En este ejemplo se devuelven los nombres de las bases de datos del servidor vinculado.

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

Unión de tablas desde un servidor vinculado

Use nombres de cuatro partes para hacer referencia a un objeto de un servidor vinculado. Ejecute el código siguiente para que se devuelva una lista de todos los inicios de sesión del servidor local y sus inicios de sesión coincidentes en el servidor vinculado.

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

Cuando se devuelve NULL para el inicio de sesión del servidor vinculado, se indica que el inicio de sesión no existe en el servidor vinculado. Estos inicios de sesión no podrán usar el servidor vinculado a menos que este se configure para pasar un contexto de seguridad distinto o el servidor vinculado acepte conexiones anónimas.

Servidores vinculados con Azure SQL Managed Instance

Si usa Azure SQL Managed Instance, consulte los ejemplos siguientes de sp_addlinkedserver (Transact-SQL):

Pasos siguientes

Obtenga más información sobre la administración de servidores vinculados en estos artículos: