Un Servidor Vinculado es una definición de una Cadena de Conexión OLEDB y un Proveedor OLEDB que asociamos a un nombre lógico, es decir, nos permite definir un nombre para el servidor vinculado, y a dicho nombre especificarle qué Proveedor OLEDB y qué Cadena de Conexión se debe utilizar para acceder al correspondiente Origen de Datos OLEDB cuando se solicite acceso a dicho Servidor Vinculado. Como hablamos, un Servidor Vinculado sirve para acceder a Orígenes de Datos OLEDB desde SQL Server (es decir, para ejecutar Consultas Distribuidas, ejecutar Procedimientos Almacenados en servidores remotos, etc.).

Como consecuencia de esto, se puede apreciar como ventaja de utilizar un Servidor Vinculado frente a especificar los datos de conexión al vuelo (ej: OPENROWSET u OPENDATASOURCE), el hecho de que el Servidor Vinculado actúa como una capa de abstracción de los datos de conexión y del Proveedor OLEDB utilizado. Es decir, una vez que hemos creado un Servidor Vinculado podemos acceder repetidas veces a dicho Origen de Datos sin tener que volver a especificar los datos de conexión (sólo el nombre del Servidor Vinculado). Es más, si accedemos a dicho Origen de Datos en múltiples consultas en múltiples sitios, si en un futuro deseamos que la conexión se realice a otro Origen de Datos (ej: por motivo de una migración del mismo), es posible actualizar la definición del Servidor Vinculado con nuevos datos de conexión sin necesidad de modificar cada consulta, y todo funcionará correctamente (elegante a la par que sencillo ;-).

Sobre un Servidor Vinculado (Linked Server) es posible configurar con qué credenciales se conectarán los usuarios al Origen de Datos externo, lo cual, resulta de gran utilidad si el Origen de Datos externos lo permite (es decir, si es susceptible de que le indiquemos usuario y password). Para conseguirlo, podemos:

• Configurar un mapeo de Inicios de Sesión locales a usuarios remotos, de tal modo, que para cada Inicio de Sesión podemos configurar un Usuario y Contraseña específico, o bien, podemos configurar Impersonate (Representar). La opción de Impersonar (Impersonate o Representar), permite que se pasen las credenciales (nombre de usuario y contraseña) del Inicio de Sesión local al Servidor Vinculado. En caso de Inicios de Sesión de Windows, para el correspondiente usuario de Directorio Activo, debe estar desactivada la propiedad La cuenta es importante y no se puede delegar. Además, los servidores local y remoto, deben tener un SPN (Service Principal Name) válido registrado en Directorio Activo utilizando la utilidad setspn.exe (si utilizamos los nombres NetBIOS de los servidores, no debemos tener problemas, pero si accedemos a los servidores a través de nombres DNS, si deberemos tener en cuenta el detalle del SPN y setspn.exe).
• Especificar la acción a realizar cuando un Inicio de Sesión que no esté en el mapeo anterior, solicite acceso al Servidor Vinculado. Se puede configurar una de las siguientes opciones:
  • Not be made (No se establecerán). Esto implica que los Inicios de Sesión que no estén mapeados, no podrán conectarse al Servidor Vinculado.
  • Be made without using a security context (Se establecerán sin usar un contexto de seguridad). Esta opción puede resultar de utilidad para acceder a Orígenes de Datos que no requieran Usuario y Password, por ejemplo, porque esta información esté embebida dentro de la Cadena de Conexión (y por ello, no sea necesario especificarla) o porque sencillamente no es necesario (ej: ficheros de texto).
  • Be made using the login’s current security context (Se establecerán usando el contexto de seguridad actual del inicio de sesión). Utilizar las credenciales de actual Inicio de Sesión, para acceder al Servidor Vinculado.
  • Be made using this security context (Se establecerán usando el contexto de seguridad de un Usuario y Contraseña remota). Especificar un Usuario y Contraseña específico para acceder al Servidor Vinculado.

Mi recomendación (en la medida que se pueda, y en los casos que sea útil) es utilizar siempre los mapeos de Inicios de Sesión, y para aquellos Inicios de Sesión que no estén mapeados, asignarles la acción de Not be made (No establecer la conexión), para así poder controlar quién puede acceder al Servidor Vinculado y quién no (por motivos evidentes de Seguridad).

Además, sobre un Servidor Vinculado (Linked Server) es posible definir varias opciones, que pueden resultar de gran utilidad. En particular, las opciones de un Servidor Vinculado que más se suelen utilizar (ojo, que no son las únicas) son:

• Data Access (Acceso a datos). Por defecto es True. Esta opción tiene que estar activada para poder acceder a los datos del Servidor Vinculado, como por ejemplo, para ejecutar una simple SELECT sobre el Servidor Vinculado. Puede resultar de utilidad, como medio para deshabilitar un Servidor Vinculado, ya que sin necesidad de eliminarlo, conseguimos que se deje de acceder a dicho Origen de Datos externo (es decir, al Servidor Vinculado).
• RPC Out (Salida RPC). Por defecto es False. Es necesario activar esta opción (RPC Out True) para poder ejecutar Procedimientos Almacenados remotos (es decir, ejecutar Procedimientos Almacenados en el Servidor Vinculado). Si no activamos RPC Out e intentamos ejecutar un Procedimiento Almacenado remoto, obtendremos el siguiente error (lo pongo en español y en inglés):
  
  Mens. 7411, Nivel 16, Estado 1, Línea 1
  El servidor 'VSQL01' no está configurado para RPC.
  
  Msg 7411, Level 16, State 1, Line 1
  Server 'VSQL01' is not configured for RPC.
  
  La opción de RPC Out es bastante importante. Por ejemplo, para ejecutar SQL Dinámico en un Servidor Vinculado (es decir, ejecutar SQL Dinámico en un servidor remoto), es necesario activar RPC Out (Salida RPC) en las opciones del Servidor Vinculado, indiferentemente de que utilicemos sp_executesql N’Codigo Dinámico’ o EXEC(’Codigo Dinámico’) AT. Quizás pueda parecer incorrecto que RPC Out esté deshabilitado por defecto. Sin embargo, esta es una muy buena práctica desde el punto de vista de la seguridad, de tal modo, que si no es necesario ejecutar Procedimientos Almacenados remotos (sobre Servidores Vinculados), no activamos RPC Out y evitamos que algún usuario pueda ser tentado por la curiosidad ;-)

En la ayuda de SQL Server (los Libros en Pantalla – Book On Line), puede encontrarse un mayor detalle de todas las opciones de configuración de los Servidores Vinculados.

Las principales diferencias entre diferencia entre utilizar Servidores Vinculados (con OPENQUERY o con Notación de 4 partes) o especificar los datos de conexión al vuelo (con OPENROWSET o con OPENDATASOURCE y Notación de 4 partes), son precisamente que con los Servidores Vinculados podemos realizar los Mapeos de Inicios de Sesión y personalizar las opciones del Servidor Vinculado (como Data Access y RPC Out). Si especificamos los datos de conexión al vuelo, será necesario hard-codear (es decir, escribir en el código de forma fija) los datos de conexión (por lo tanto, serán los mismos para todos los usuarios excepto que nos montemos algún invento) y no podemos personalizar opciones como Data Access o RPC Out, es decir, no podríamos limitar que los usuario ejecuten un Procedimiento Almacenado remoto o deshabilitar el acceso a datos para realizar una operación de mantenimiento.

Además, el hecho de hard-codear implica que en el caso de que cambien los datos de conexión será necesario alterar el código fuente que utilice datos de conexión al vuelo (OPENROWSET u OPENDATASOURCE) en Procedimientos Almacenados, ficheros SQL que puedan tener los usuarios, código T-SQL que pueda estar embebido en aplicaciones, etc., lo cual complica el mantenimiento frente a la utilización de Servidores Vinculados.

También es importante tener en cuenta, que en el caso de SQL Server 2005 es necesario activar la opción de configuración Ad Hoc Distributed Queries a través del procedimiento almacenado sp_configure para poder utilizar OPENROWSET u OPENDATASOURCE, ya que en caso contrario, ni los administradores (es decir, ni los miembros de sysadmin) podrán acceder al Origen de Datos externo a través consultas o Procedimientos Almacenados Transact-SQL con OPENROWSET u OPENDATASOURCE. Si no está activo Ad Hoc Distributed Queries e intentamos utilizar OPENROWSET u OPENDATASOURCE, obtendremos el siguiente error (lo muestro en español y en inglés):

Mens. 15281, Nivel 16, Estado 1, Línea 1
SQL Server bloqueó el acceso a STATEMENT 'OpenRowset/OpenDatasource' del componente 'Ad Hoc Distributed Queries' porque este componente está desactivado como parte de la configuración de seguridad de este servidor. Un administrador del sistema puede habilitar el uso de 'Ad Hoc Distributed Queries' mediante sp_configure. Para obtener más información sobre cómo habilitar 'Ad Hoc Distributed Queries', vea el tema sobre la configuración de superficie en los Libros en pantalla de SQL Server.

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

Para configurar o activar la opción Ad Hoc Distributed Queries con sp_configure, se debe ejecutar algo similar a:

Sin embargo, esta no es la única barrera para poder utilizar OPENROWSET u OPENDATASOURCE. Para que un usuario no miembro de sysadmin (es decir, un usuario que no es administrador) pueda ejecutar consultas AdHoc o código Transact-SQL con OPENROWSET u OPENDATASOURCE, debe estar deshabilitada la opción DisallowAdhocAccess del Proveedor OLEDB utilizado (ej: para acceder a SQL Server es el Proveedor OLEDB SQLNCLI, para acceder a Analysis Services es el Proveedor OLEDB MSOLAP, para acceder a ODBC es el Proveedor OLEDB MSDASQL, etc.). De no ser así, sólo podremos utilizar OPENROWSET u OPENDATASOURCE con Inicios de Sesión miembros de sysadmin (como es el caso del inicio de sesión sa), sin embargo, para los Inicios de Sesión no miembros de sysadmin se mostrará el siguiente error (lo muestro en español y en inglés):

Mens. 7415, Nivel 16, Estado 1, Línea 1
Denegado el acceso ad hoc al proveedor OLE DB 'SQLNCLI'. El acceso debe realizarse mediante un servidor vinculado.

Msg 7415, Level 16, State 1, Line 1
Ad hoc access to OLE DB provider 'SQLNCLI' has been denied. You must access this provider through a linked server.

Las opciones de los Proveedores OLEDB, se puede ver y modificar accediendo a sus propiedades desde la carpeta Proveedor de SQL Server Management Studio (SSMS).

Existen múltiples propiedades, pero la propiedad que nos interesa es No permitir accessos “ad hoc”. Sin embargo, existe un problema con el diálogo de Propiedades del Proveedor OLEDB para el establecimiento de ésta propiedad, debido a que al activar la opción desde SSMS se crea correctamente la clave de registro DisallowAdhocAccess a 1, y en consecuencia el cambio tiene éxito. Sin embargo, al desactivar la opción, en vez de poner DisallowAdhocAccess a 0, elimina la clave de registro, y en consecuencia el cambio no tiene efecto, por lo cual seguirá produciéndose el mensaje de error 7415.

Mens. 7415, Nivel 16, Estado 1, Línea 1
Denegado el acceso ad hoc al proveedor OLE DB 'SQLNCLI'. El acceso debe realizarse mediante un servidor vinculado.

Msg 7415, Level 16, State 1, Line 1
Ad hoc access to OLE DB provider 'SQLNCLI' has been denied. You must access this provider through a linked server.

En consecuencia, es necesario modificar la opción DisallowAdhocAccess del Proveedor OLEDB directamente modificando la correspondiente clave a través del editor de registro de Windows (regedit.exe), y teniendo en cuenta no confundirnos de Proveedor OLEDB ni de Instancia de SQL Server al modificar el registro de Windows.

Debe tenerse en cuenta, que en ocasiones, es necesario crear la rama en el registro para el Proveedor OLEDB que deseeamos configurar. Por ejemplo, en la anterior pantalla capturada, no existe la rama del Proveedor OLEDB MSOLAP, por lo tanto, si lo deseamos configurar utilizando el Editor de Registro de Windows, primero será necesario crear la rama del registro, y luego crear la clave del registro.

Para configurar la opción DisallowAdhocAccess en SQL Server 2005, deberemos identificar cuál es nuestra instancia, y en qué directorio está instalada (ej: MSSQL.1, MSSQL.2, etc.), de tal modo, que los Proveedores OLEDB de dicha Instancia de SQL Server estarán definidos bajo la siguiente rama del Registro de Windows (tomando como ejemplo la Instancia MSSQL.1):

HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers

De este modo, si deseamos modificar el Proveedor OLEDB de SQL Server 2005 (que es SQLNCLI – SQL Server Native Client), nos interesará en particular la siguiente rama del Registro de Windows:

HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\SQLNCLI

En esta rama del Registro de Windows, será necesario crear (o modificar, si existe) una clave con nombre DisallowAdhocAccess de tipo REG_DWORD y con valor 0.

Para configurar la opción DisallowAdhocAccess en SQL Server 7 y en SQL Server 2000, puede consultarse el siguiente Artículo de Soporte de Microsoft:

HOW TO: Use the DisallowAdHocAccess Setting to Control Access to Linked Servers (SQL Server 7 y SQL Server 2000)

Ahora que hemos comentado la importancia de las propiedades de los Proveedores OLEDB para acceder a Orígenes de Datos externos desde SQL Server, y que sabemos que dichas propiedades son por cada Proveedor OLEDB e Instancia de SQL Server, resulta apropiado comentar otra Propiedad de los Proveedores OLEDB que nos puede resultar de ayuda, la propiedad Allow InProcess (Permitir InProcess), que podremos encontrar en el Registro de Windows como AllowInProcess. La propiedad Allow InProcess de un Proveedor OLEDB le especifica a SQL Server si cuando tiene que utilizar dicho Proveedor OLEDB, debe instanciarlo dentro del propio proceso de la Instancia de SQL Server, o en un proceso separado (es decir, fuera de proceso). El método más seguro es fuera de proceso (Out of Process, es decir, deshabilitar Allow InProcess), de tal modo que en caso de que se produjesen errores en el Proveedor OLEDB, dichos errores no afecten a la estabilidad del proceso de SQL Server. Otro aspecto práctico de la propiedad Allow InProcess (Permitir InProcess) de un Proveedor OLEDB en SQL Server, es debido a los errores 7399 y 7303 que en alguna ocasión nos podríamos encontrar. Por ejemplo, me he encontrado los errores 7399 y 7303 al utilizar el Proveedor MSOLAP (Analysis Server 2005) desde un Servidor Vinculado de SQL Server 2005. Adjunto el error (lo muestro en español y en inglés):

Mens. 7399, Nivel 16, Estado 1, Línea 1
El proveedor OLE DB "MSOLAP" para el servidor vinculado "AnalysisServices2005" informó de un error. El proveedor no proporcionó información acerca del error.

Mens. 7303, Nivel 16, Estado 1, Línea 1
No se puede inicializar el objeto de origen de datos del proveedor OLE DB "MSOLAP" para el servidor vinculado "AnalysisServices2005".

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSOLAP" for linked server "AnalysisServices2005" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSOLAP" for linked server "AnalysisServices2005".

La solución que encontré fué activar la propiedad Allow InProcess (Permitir InProcess) para el Proveedor OLEDB MSOLAP (el de Analysis Services 2005, que es el que estaba utilizando en el Servidor Vinculado). Una vez activada la propiedad Allow InProcess del Proveedor OLEDB MSOLAP, el acceso a Analysis Services 2005 desde el Servidor Vinculado de SQL Server 2005 funcionó correctamente.

Por todo esto, la recomendación general que se debe seguir es utilizar SIEMPRE Servidores Vinculados (con OPENQUERY o con Notación de 4 partes) para el acceso a Orígenes de Datos externos, y sólo especificar los datos de conexión al vuelo (OPENROWSET u OPENDATASOURCE) de forma excepcional para usos esporádicos o pruebas aisladas.

Para crear o eliminar un Servidor Vinculado (Linked Server) se debe utilizar los Procedimientos Almacenados de Sistema sp_addlinkedserver y sp_dropserver, para configurar las opciones de los Servidores Vinculados se puede utilizar el Procedimiento Almacenado de Sistema sp_serveroption, y para consultar los Servidores Vinculados existentes, puede consultarse la vista del catálogo sys.servers. Para configurar con qué usuarios conectarse al Origen de Datos externo (los Mapeos de Inicios de Sesión comentados anteriormente en este mismo Artículo) se puede utilizar el Procedimiento Almacenado del Sistema sp_addlinkedsrvlogin. No es posible modificar un Servidor Vinculado, por lo tanto, será necesario eliminarlo y volverlo a crear, aunque si es posible cambiar sus opciones. También es posible crear, eliminar y consultar los Servidores Vinculados existentes a través de SQL Server Management Studio (SSMS), desde el Object Explorer, en Objetos del Servidor -> Servidores Vinculados (Server Objects -> Linked Servers). A continuación se muestra un ejemplo de creación de un Servidor Vinculado a una Instancia remota de SQL Server, desde Transact-SQL, utilizando los Procedimientos Almacenados del Sistema comentados:

Ahora que ya sabemos qué son los Servidores Vinculados, conocemos algunos de los errores típicos que podemos tener al trabajar con ellos, sabemos crear Servidores Vinculados y configurarlos, ha llegado el momento de saber cómo podemos utilizarlos. Principalmente disponemos de dos métodos para especificar la consulta (o Procedimiento Almacenado, etc.) que deseamos ejecutar a través de un Servidor Vinculado:

• Consultas Passthrough: Especificar la consulta en una cadena, la cual, será ejecutada tal cual por el Origen de Datos externo (OPENQUERY). Esta es quizás la opción más versátil y óptima, ya que permite utilizar la sintaxis del Origen de Datos externo, podemos especificar una cláusula WHERE para que viajen por la red el menor número de datos posible, y además, el procesamiento de la consulta sea realizado por el Origen de Datos externo (liberando de dicha carga a SQL Server). Por ejemplo, si deseamos consultar Analysis Services de SQL Server, podremos especificar la consulta MDX a través de OPENQUERY. En el caso de otros motores de base de datos relacionales, podemos utilizar la sintaxis y funciones específicas del Origen de Datos (ej: funciones de texto, de fechas, etc. propias del Origen de Datos). Así, para obtener las primeras 10 filas de una tabla, en SQL Server ejecutaríamos una consulta tipo SELECT TOP 10, mientras que en Informix ejecutaríamos una consulta tipo SELECT FIRST 10. En el caso de utilizar OPENQUERY para consultar Informix, podríamos especificar la sintaxis SELEC FIRST así como cualquiera de las funciones propias de Informix. Otra ventaja de utilizar OPENQUERY es que funciona con todos los Orígenes de Datos OLEDB, al margen de que puedan producirse problemas particulares por la naturaleza de los distintos tipos de datos de SQL Server y el Origen de Datos OLEDB externo (es decir, por la diferencia de precisión entre los tipos de datos), o alguna otra peculiaridad. También, en el caso de tener problemas con los tipos de datos, podemos realizar un Casting en la consulta remota, para que nos lleguen los datos a SQL Server de una forma correcta para su procesamiento (en el peor de los casos, siempre casting a texto, y en SQL Server otro casting al tipo deseado ;-).
• Utilizar la Notación de 4 partes. Permite acceder a un Origen de Datos externo como si fuese parte de nuestra instancia de SQL Server. Este comportamiento se basa en la sintaxis utilizada por SQL Server para identificar un objeto cualquier:
  Nombre_Servidor.Nombre_BaseDatos.Nombre_Esquema.Nombre_Objeto
  Aunque habitualmente cuando ejecutamos una consulta, sólo indicamos el nombre de los objetos implicados, como mucho prefijados por el Esquema (Nombre_Esquema.Nombre_Objeto), realmente el nombre completo de un objeto está formado por 4 parte: Nombre del Servidor, Nombre de la Base de Datos, Nombre del Esquema, y Nombre del Objeto. De hecho, el Nombre del Servidor, puede tratarse del servidor local (en el que se ejecuta la consulta) o de un Servidor Vinculado.
  Tiene la ventaja, de ser una sintaxis muy cómoda y fácil de interpretar (desde el punto de vista del mantenimiento de software), tanto para acceder a tablas en SELECT, INSERT, UPDATE, etc. como para ejecutar Procedimientos Almacenados remotos.
  Tiene como inconveniente, que SQL Server se traerá la tabla completa, es decir, viajarán todos los datos de la tabla remota por la red, y en caso de existir una cláusula WHERE, la misma será procesada por SQL Server, por lo tanto el procesamiento de la consulta será realizada por SQL Server.
  Además, no todos los Orígenes de Datos soportan la Notación de 4 partes, por lo que habitualmente suele utilizarse sólo con SQL Server (ojo, que también se puede utilizar con otros Orígenes de Datos, pero claro, con SQL Server se lleva especialmente bien ;-)

Con esto, hemos aprendido algo más: La recomendación general (al margen de casos particulares) es utilizar Servidores Vinculados y OPENQUERY, aunque existen excepciones, ya que por ejemplo, al trabajar con servidores remotos de tipo SQL Server, es muy cómodo utilizar la Notación de 4 partes, tanto para ejecutar Procedimientos Almacenados remotos, como para acceder a tablas que necesitemos leer de forma completa (si sólo necesitamos unas filas, mejor OPENQUERY).

Supongamos que tenemos un Servidor Vinculado denominado SQLProduccion. Podríamos acceder al mismo ejecutando consultas como las siguientes:

Un detalle muy importante al trabajar con OPENQUERY: no es posible especificar una variable ni concatenar al especificar la consulta SQL a ejecutar con OPENQUERY. Si deseamos ejecutar una consulta SQL dinámica (es decir, que pueda variar sus parámetros, etc.) a través de un Servidor Vinculado con OPENQUERY, o bien, especificar de forma selectiva el Servidor Vinculado sobre el cual ejecutar la consulta, tendremos que utilizar SQL Dinámico, como se muestra en el siguiente ejemplo:

Puede encontrarse un ejemplo más detallado en el artículo ¿Cómo ejecutar consultas dinámicas sobre OPENROWSET o sobre Servidores Vinculados (OPENQUERY)?

Y de momento poco más... se podrían contar muchas más cosas sobre los Servidores Vinculados, pero para empezar, esta sesión introductoria no está mal ;-)