Cómo crear y configurar un servidor enlazado en SQL Server Management Studio

 Thank to: https://www.sqlshack.com/

Los servidores enlazados permiten enviar una sentencia T-SQL a una instancia de SQL Server, que retorna datos desde otras instancias SQL Server. Un servidor enlazado permite combinar datos desde muchas instancias SQL Server usando una sola sentencia T-SQL cuando los datos existen en múltiples bases de datos en diferentes instancias SQL. Usando un servidor enlazado para recabar los datos desde muchas instancias SQL, la única cosa que debería hacerse es conectarse a una instancia SQL.

Hay dos maneras de configurar un servidor enlazado en SSMS. Una manera es usando el procedimiento almacenado sp_addlinkedserver y otra es usando la interfaz de usuario de SQL Server Management Studio (SSMS).

En este artículo, explicaremos cómo configurar un servidor enlazado usando una fuente de datos SQL Server. Más información acerca de otras fuentes de datos puede ser encontrada en este enlace.

Para ver todos los servidores enlazados creados en SSMS, debajo de Object Explorer elija la carpeta Server Objects y expanda la carpeta Linked Server:

Para crear un servidor enlazado en SSMS, haga clic derecho en la carpeta Linked Servers y desde el menú contextual, seleccione la opción New Linked Server:

El diálogo New Linked Server aparece:

En este diálogo, el nombre de un servidor enlazado y el de un tipo de servidor tienen que ser identificados. Los servidores enlazados pueden ser definidos para diferentes tipos de fuentes de datos si el botón radial Other data source está seleccionado. Para propósitos de este artículo, el botón radial de SQL Server debajo de la sección Server type será elegido y, en la caja de texto Linked server, un nombre para el servidor será ingresado:

Si el tipo de SQL Server es elegido para configurar un servidor enlazado de SQL Server, el nombre especificado en la caja de texto Linked server debe ser el nombre del SQL Server remoto.

Si una instancia de SQL Server es la instancia por defecto, escriba el nombre de la computadora que hospeda la instancia de SQL Server (por ejemplo, WSERVER2012). Si es una instancia de SQL Server con nombre, escriba el nombre de la computadora y el nombre de la instancia, separados por una barra oblicua (por ejemplo, WSERVER2012\SQLEXPRESS).

De otra manera, puede ocurrir el siguiente error cuando el botón OK es presionado:

Para establecer cómo un usuario se autenticaría a la instancia WSERVER2012\SQLEXPRESS, debajo de la sección Select a page o en la parte superior izquierda del diálogo New Linked Server, seleccione el elemento Security:

Aquí, diferentes maneras de autenticarse al servidor enlazado pueden ser configuradas.

Debajo de Local server login to remote server login mappings, dos maneras de ingreso local a un recurso remoto pueden ser establecidas. Una manera es asociar un inicio de sesión local con un inicio de sesión remoto, y otra manera es personificar.

Inicio de sesión local

En el campo Local Login, todos los inicios de sesión locales serán listados. El inicio de sesión local puede ser de SQL Server Authentication:

O de Windows Authentication:

Ahora, cuando se hace clic en el botón OK en el diálogo New Linked Server, el siguiente mensaje aparecerá:

The login mappings should either be impersonate or have a remote login name (Los mapeos de los inicios de sesión deberían ser personificados o tener un nombre de inicio de sesión remoto).

Vea la imagen a continuación:

Esto pasa porque la casilla Impersonate no está seleccionada.

Personificar

Cuando la casilla Impersonate está seleccionada, pasa las credenciales al inicio de sesión al servidor enlazado. Para la Autenticación de SQL Server, el mismo inicio de sesión con las credenciales exactas debe existir en el servidor enlazado, de otra manera, cuando se conecte al servidor con la Autenticación de SQL Server, la lista de bases de datos bajo la carpeta Catalogs puede verse como esto:

Para inicios de sesión de Windows, debe haber un inicio de sesión válido en el servidor enlazado. Para usar la personificación, la delegación entre el servidor local y el servidor enlazado debe ser establecida.

Creemos un servidor enlazado usando el inicio de sesión de Windows. Desde la caja Local Login, seleccione el inicio de sesión de Windows y seleccione la casilla Impersonate y presione el botón OK:

Debajo de la carpeta Catalogs, todas las bases de datos que están localizadas en el servidor enlazado serán listadas:

Usuario Remoto

La opción de usuario remoto permite a los usuarios desde el SQL Server local conectarse al servidor enlazado SQL Server incluso aunque sus credenciales no están presentes en el servidor remoto usando las credenciales desde el usuario que existe en el servidor remoto.

Contraseña Remota

Especifique la contraseña del usuario remoto.

Desde el menú desplegable Local Login, elija un inicio de sesión que debería mapear al inicio de sesión remoto. En el campo Remote User, ingrese el nombre del usuario remoto que existe en el servidor remoto y, en el campo Remote Password, ingrese la contraseña de ese usuario remoto. Luego, presione el botón OK:

Ahora, cuando esté conectado al servidor local usando la Autenticación de SQL Server, con las credenciales de Miki o Zivko, debajo de la carpeta Catalogs, todas las bases de datos que están disponibles en el servidor remoto para el inicio de sesión remoto de Nenad serán listadas primero:

Adicionalmente, en el diálogo Linked Server, puede ser identificado cómo los inicios de sesión no están establecidos en la lista Local server login to remote server login mappings se conectarán al servidor enlazado, ya que hay cuatro opciones que pueden usadas y están localizadas debajo de la sección For a login not defined in the list above, connections will:

Not be made (a no ser hecho)

Si este botón radial está seleccionado, cualquier usuario que no esté identificado en la lista Local server login to remote server login mappings no puede establecer la conexión al servidor enlazado.

Por ejemplo, si ingresa con una cuenta diferente (por ejemplo, Ben) que no está establecido en la lista de mapeo de inicios de sesión de la lista de bases de datos bajo la carpeta Catalogs, se verá así:

Si intenta ejecutar una consulta de servidor enlazado:

1	SELECT * FROM [WSERVER2012\SQLEXPRESS].AdventureWorks2014.HumanResources.Employee e

El siguiente resultado aparecerá:

Msg 7416, Level 16, State 1, Line 1
Access to the remote server is denied because no login-mapping exists.

Be made without using a security context (a ser hecho sin usar un context de seguridad)

La opción Be made without using a security context es usada para conectarse a las Fuentes de datos que no requieren ninguna autenticación, por ejemplo, como un archivo de texto. Cuando esta opción es seleccionada para conectarse a un servidor enlazado, tendrá el mismo efecto que seleccionar la opción “Not to be made”.

Si usted intenta ejecutar una consulta de servidor enlazado:

1	SELECT * FROM [WSERVER2012\SQLEXPRESS].AdventureWorks2014.HumanResources.Employee e

El siguiente mensaje aparecerá:

OLE DB provider “SQLNCLI11” for linked server “WSERVER2012\SQLEXPRESS” returned message “Invalid authorization specification”.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider “SQLNCLI11” for linked server “WSERVER2012\SQLEXPRESS” reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider “SQLNCLI11” for linked server “WSERVER2012\SQLEXPRESS”.

Be made using the login’s current security context (a ser hecho usando el context actual de seguridad del inicio de sesión)

Si esta opción es seleccionada, pasará el contexto de seguridad del inicio de sesión local al inicio de sesión remoto. Si la Autenticación de Windows es usada, las credenciales de Windows serán usadas para conectarse al servidor remoto SQL Server. Si se usa la Autenticación de SQL Server, entonces las credenciales del inicio de sesión local serán pasadas al SQL Server remoto. Note que, para establecer la conexión al servidor remoto exitosamente, el usuario con las exactas mismas credenciales debe existir en el servidor remoto. De otra manera, cuando se ejecute un consulta de servidor enlazado:

1	SELECT * FROM [WSERVER2012\SQLEXPRESS].AdventureWorks2014.HumanResources.Employee e

El siguiente mensaje aparecerá:

Msg 18456, Level 14, State 1, Line 1
Login failed for user ‘Ben’.

Be made using this security context (a ser hecho usando este context de seguridad)

La cuarta opción debajo de la sección For a login not defined in the list above, connections will es Be made using this security context. En los campos Remote login y With password, ingrese las credenciales de la Autenticación de SQL Server que existen en el servidor remoto, de otra manera, el siguiente error podría ocurrir:

El último elemento debajo del menú Select a page es el elemento Server Options. Cuando se selecciona esta opción, la siguiente ventana será mostrada:

Aquí, opciones adicionales para servidores enlazados pueden ser vistas o establecidas.

Collation Compatible (colación compatible)

La primera opción es Collation Compatible. Esta opción es usada para identificar si el servidor enlazado tiene la misma colación que el servidor loca. Esta opción debería estar en True (verdadero) sólo si es conocido que el servidor enlazado tiene la misma colación que el local, de otra manera debería estar establecido en False (falso, opción por defecto).

Data Access (acceso de datos)

Esta opción es usada para permitir/denegar el acceso a los datos del servidor enlazado. Si esta opción está establecida a False, el acceso remoto estará denegado. Esta opción es útil para deshabilitar el acceso al servidor remoto temporalmente. El siguiente mensaje aparecerá cuando se ejecute una consulta al servidor enlazado y esta opción esté en Falso:

Msg 7411, Level 16, State 1, Line 1
Server ‘WSERVER2012\SQLEXPRESS’ is not configured for DATA ACCESS.

Por defecto, la opción está establecida en True.

RPC y RCP Out

RCP (Llamada de Procedimiento Remoto, Remote Procedure Call en inglés) es usado para habilitar el acceso a procedimiento remotos para ser llamados desde el servidor enlazado o para ser llamados al servidor enlazado.

Si estas opciones están en False, el siguiente error aparecerá cuando algunos procedimiento desde el servidor enlazado sean llamados:

Msg 7411, Level 16, State 1, Line 4
Server ‘WSERVER2012\SQLEXPRESS’ is not configured for RPC.

Por defecto, el valor Falso está establecido para las opciones RPC y RCP Out.

Use Remote Collation (usar colación remota)

Cuando esta opción está establecida en True, la colección de columnas remotas será usada y la colección especificada en el campo Collation Name será usada para fuentes de datos que no están en las fuentes de datos de SQL Server, pero si esta opción está en False, entonces la colación para el servidor local será usada. Por defecto, está establecida a Falso.

Collation Name (nombre de colación)

Si el campo Use Remote Collection está en Verdadero, esta opción es usada para especificar el nombre de la colación del servidor enlazado para las fuentes de datos que no están en las fuentes de datos de SQL Server. Cuando se elije un nombre de colación debe ser una colación que SQL Server soporta.

Connection Timeout (tiempo de expiración de conexión)

Esta opción es usada para establecer el máximo tiempo que el servidor local debería esperar para obtener una conexión a la instancia SQL Server del servidor enlazado. Si 0 (cero) está establecido, entonces la opción del servidor remote login timeout es usada. Por defecto, 10 segundos está establecido para esta opción. Note que el valor por defecto para SQL Server 2008 es 20 segundos.

Query Timeout (tiempo de expiración de consulta)

Esta opción es usada para establecer cuánto, en segundos, un proceso remoto puede tomar antes de expirar. El valor por defecto es 600 segundos (10 minutos). Para deshabilitar el tiempo de expiración de consultas ponga 0 (cero) en el campo Query Timeout y la consulta esperará hasta que esté completada.

Distributor (distribuidor)

En esta opción, puede ser especificado si el servidor enlazado está participando en la replicación como un Publicador de distribución.

El Distribuidor es una instancia de la base de datos, que actúa como un almacén para datos específicos de replicación asociados con uno o más Publicadores.

Publisher (publicador)

En esta opción, puede establecerse si el servidor enlazado será un publicador de replicación o no. Si está en True, el servidor enlazado es un publicador. De otra manera, no.

El Publicador es una instancia de la base de datos que hace a los datos disponibles a otras localizaciones a través de la replicación.

Subscriber (suscriptor)

En esta opción, puede especificarse si el servidor enlazado es un subscriptor de replicación o no.

Un Subscriptor es una instancia de la base de datos que recibe datos replicados.

Más información acerca de las opciones Distributor, Publisher, Subscriber puede ser encontrada en la página Vista General del Modelo de Publicación de Replicación.

Lazy schema validation (validación diferida de esquema)

Esta opción revisa cambios de esquema que han ocurrido desde la compilación en las tablas remotas. Si esta opción está en False (estado por defecto), SQL Server revisa cambios antes de la ejecución de una consulta y si hay algunos cambios, recompila la consulta. Si Lazy schema validation está en True, SQL server revisa las demoras de esquema en las tablas remotas hasta la ejecución de la consulta.

Enable Promotion of Distributed Transactions (habilitar promoción de transacciones distribuídas)

Esta opción es usada para proteger las acciones de un procedimiento servidor a servidor a través de la transacción Microsoft Distributed Transaction Coordinator (MS DTC). Si esta opción está en True, llamar a un procedimiento almacenado inicia una transacción distribuida y enlista la transacción con MS DTC.

Ahora, cuando todo esté configurado, haga clic en el botón OK en el diálogo New Linked Server. Un nuevo servidor enlazado aparecerá debajo de la carpeta Linked Server.

Para probar que el servidor enlazado trabaja apropiadamente, haga clic en ese servidor enlazado y elija Test Connection:

Si la conexión con el servidor enlazado es establecida exitosamente, el siguiente cuadro de información aparecerá:

De otra manera, un mensaje de error será mostrado, indicando un problema que evita que una conexión exitosa sea establecida:

Consultando datos usando un servidor enlazado

Consultar datos usando el servidor enlazado es un poco diferente que consultar datos desde el SQl Server local. En las consultas normales, una notación de dos partes es usada: [Esquema].[NombreDeObjeto], por ejemplo, HumanResources.Employee:

1	SELECT * FROM HumanResources.Employee e

Cuando se consulta a una tabla desde un servidor enlazado, la notación de cuatro partes es usada: ServidorEnlazado.BaseDeDatos.Esquema.NombreDeObjeto. Para obtener datos desde la tabla Employee, la cual está localizada en una base de datos en el servidor enlazado, el código de consulta ser verá así:

1	SELECT * FROM [ZIVKO\SQLEXPRESS2016].[AdventureWorks2014].[HumanResources].[ Employee]

Eliminando un servidor enlazado

Para eliminar un servidor enlazado, debajo de la carpeta Linked Servers, haga clic derecho en el servidor enlazado y desde el menú contextual elija el comando Delete:

Esto abrirá el diálogo Delete Object:

Haga clic en el botón OK y desde la caja de mensajes, elija el botón Yes:

Si todo va bien, el servidor enlazado será removido de la carpeta Linked Servers.