Accediendo a bases de datos externas utilizando CLR stored procedures

Ciertas aplicaciones necesitan acceder a información de múltiples orígenes de datos, es decir, más de una base de datos. Más aún, los datos pueden venir de servidores diferentes e incluso de motores de base de datos diferentes. Estos son escenarios comunes en ambientes que almacenan grandes cantidades de datos heterógeneos, en donde no solo intervienen bases de datos Oracle o SQL Server, sino también otros tipos de motores como DB2 y similares.

En los casos en los que SQL Server interviene como uno de los motores que proveerán de información a la aplicación, puede aprovecharse la capacidad de escribir código CLR en SQL Server para lograr que sea SQL Server quien consulte a los otros motores de bases de datos que intervienen en la aplicación. ¿Con qué objetivo? Pues una razón válida, aunque no necesariamente ideal, puede ser para abstraer por completo a nuestra aplicación .NET de la necesidad de interactuar con múltiples bases de datos simultáneamente.

Típicamente, para acceder a orígenes de datos diferentes a SQL Server y Oracle, utilizamos el proveedor de datos ODBC que incluye el .NET Framework. Siendo así, la estrategia sería utilizar aquel proveedor desde nuestro stored procedure escrito, por ejemplo, en C#.

Sin embargo, el acceder a una base de datos externa mediante ODBC desde un stored procedure hecho en CLR presenta desafíos con respecto a las seguridades predeterminadas que maneja SQL Server 2005. Si queremos instruir a SQL Server para que "confíe" en nuestro código que accederá a bases de datos externas, debemos hacer lo siguiente:

1. En el proyecto que contiene al stored procedure hecho en CLR:
   1. Abrir las propiedades del proyecto.
   2. Seleccionar el Tab "DataBase"
   3. En Permission Level seleccionar "Unsafe"
2. En SQL Management Studio de SQL Server 2005, en la base de datos que contendrá el stored procedure, ejecutar el siguiente query:

ALTER DATABASE <NombreBaseDatos> SET TRUSTWORTHY ON

Una vez hecho esto, la interacción con la base de datos externa no debe presentar ningún inconveniente. Aquí un código ejemplo que consulta la base de datos NorthWind en un servidor externo por medio de un DSN accedido por ODBC:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Security.Permissions;
using System.Data.Odbc;
 

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void EmpleadosTraerLista()
    {
        using (OdbcConnection cn = new OdbcConnection("DSN=NorthWind;UID=elUserId;PWD=elPassword"))
        {
            OdbcCommand cmd = new OdbcCommand("SELECT CustomerID, CompanyName FROM Customers", cn);
            OdbcDataReader rdr = null;
 
            try
            {
                cn.Open();
                rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                while (rdr.Read())
                {
                    SqlContext.Pipe.Send(rdr.GetString(0) + " " + rdr.GetString(1));
                }
            }
            finally
            {
                if (rdr != null)
                {
                    rdr.Close();
                }
            }
        }
    }
};

De esta forma, nuestra aplicación nunca tendría que conocer cómo interactuar con cada base de datos, pues únicamente invocaría el stored procedure hecho en CLR.