Cómo almacenar de forma segura los detalles de la Cadena de conexión en VBA

Tengo una plantilla de Excel que ha codificado la ruta de MS Access MDB en el código de VBA utilizado para conectarse a las tablas de acceso y guardar y recuperar datos.

Migré la base de datos MS Access a SQL Server con autenticación integrada para los usuarios de plantilla de Excel.

Mi pregunta es, ¿cuál es la forma recomendada / mejor práctica para almacenar la cadena de conexión DB de SQL Server y recuperarla en Excel 2007 VBA para guardar y recuperar datos?

En el pasado, he hecho lo siguiente.

  1. Use una configuración de clave de registro que tenga la cadena de conexión. Luego, en el VBA, escriba una función que lea la clave de registro y devuelva la cadena de conexión.

  2. Tenga una hoja oculta “Configuraciones” dentro de la Plantilla de Excel, con una celda con nombre para la cadena de conexión. Lea la cadena de conexión en VBA accediendo a ese rango con nombre.

  3. Use un archivo .txt txt que vaya con la plantilla de Excel. (Esto no es ideal y quiero evitar esto ya que crea una dependencia en ese archivo externo)

No me gusta el número 1 porque quiero evitar escribir / leer en el registro si es posible. # 2 se siente bien, pensó que no estoy seguro si hay una mejor manera “limpia” para hacer esto.

¿Alguna idea?

Esto es lo que haría con seguridad almacenar las credenciales de la cadena de conexión

Descargue e instale Visual Studio Express 2012 para Windows ( GRATIS )

Ábrelo como administrador y crea un nuevo proyecto. Seleccione Visual C# luego Class Library y HiddenConnectionString nombre a HiddenConnectionString

enter image description here

En el Explorador de soluciones , cambie el nombre de Class1.cs a MyServer.cs

enter image description here

Haga clic con el botón derecho en su proyecto MyConnection en el Explorador de soluciones y seleccione Add Reference

Escriba activeX en el cuadro de búsqueda y marque la Microsoft ActiveX Data Objects 6.1 Library

enter image description here

Copie y pegue el siguiente código en MyServer.cs reemplazando por completo lo que esté en el archivo.

 using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Runtime.InteropServices; using System.IO; using ADODB; namespace HiddenConnectionString { [InterfaceType(ComInterfaceType.InterfaceIsDual), Guid("2FCEF713-CD2E-4ACB-A9CE-E57E7F51E72E")] public interface IMyServer { Connection GetConnection(); void Shutdown(); } [ClassInterface(ClassInterfaceType.None)] [Guid("57BBEC44-C6E6-4E14-989A-B6DB7CF6FBEB")] public class MyServer : IMyServer { private Connection cn; private string cnStr = "Provider=SQLOLEDB; Data Source=SERVER\\INSTANCE; Initial Catalog=default_catalog; User ID=your_username; Password=your_password"; public MyServer() { } public Connection GetConnection() { cn = new Connection(); cn.ConnectionString = cnStr; cn.Open(); return cn; } public void Shutdown() { cn.Close(); } } } 

Ubique la variable cnStr en el código y actualice los detalles de su cadena de conexión.

Haga clic con el botón derecho en la solución *HiddenConnectionString * en el Explorador de soluciones y seleccione Propiedades.

Haga clic en la pestaña Application en el lado izquierdo, luego Assembly Info y marque Make Assembly COM-Visible

enter image description here

Haga clic en *Build* en el menú de la izquierda y marque Register For COM Interop

enter image description here

Nota: Si está desarrollando para Office de 64 bits, asegúrese de cambiar el Platform Target la Platform Target en el menú Generar a x64 . Esto es obligatorio para las bibliotecas COM de 64 bits para evitar cualquier error relacionado con ActiveX.


Haga clic con el botón derecho en HiddenConnectionString en Solution Explorer y seleccione Build en el menú.

Si todo fue bien, tu HiddenConnectionString.dll y HiddenConnectionString.tlb deberían generarse correctamente. Ve a este camino ahora

 C:\Users\administrator\Documents\Visual Studio 2012\Projects\HiddenConnectionString\HiddenConnectionString\bin\Debug 

y deberías ver tus archivos.

enter image description here


Ahora abre Excel y ve a VBE. Haga clic en Tools y seleccione References .

Haga clic en el botón Examinar y navegue hasta HiddenConnectionString.tlb .

Además, agregue referencias a la Microsoft ActiveX Object 6.1 Library ; esto es para que pueda usar la biblioteca ADODB.

enter image description here

Ahora haga clic derecho en cualquier lugar en la ventana del Explorador de proyectos e inserte un nuevo Module

Copia y pega el código a continuación

 Option Explicit Sub Main() Dim myCn As MyServer Set myCn = New MyServer Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset rs.Open "Select * from [TABLE_NAME]", myCn.GetConnection Range("A1").CopyFromRecordset rs rs.Close myCn.Shutdown Set rs = Nothing Set myCn = Nothing Columns.AutoFit End Sub 

Reemplace el [TABLE_NAME] con un nombre de tabla real en su base de datos.

Presiona F5 o presiona el botón de reproducción verde en la cinta.

enter image description here

Si todo salió bien, ahora debería ver la Tabla devuelta en su hoja de cálculo.

mi muestra:

enter image description here


Como puedes ver. Agregar referencias a su propia biblioteca COM y almacenar las credenciales de inicio de sesión y otros datos confidenciales dentro del .dll comstackdo protege sus datos (cadena de conexión). Es muy difícil descomstackr el archivo *.dll para obtener información sensible de él. Hay varias técnicas de encoding para proteger su *.dll aún más, pero no voy a entrar en detalles ahora. Esto mismo logra lo que pediste.

myCn.GetConnection devuelve el objeto ADODB.Connection que se inicializó dentro de la biblioteca COM referenciada. A ningún usuario de Excel se le presentará la cadena de conexión o los datos confidenciales (en realidad nadie más).

Puede modificar el código C # para aceptar parámetros de VBA, es decir, inicio de sesión, contraseña, catálogo inicial, consulta para ejecutar etc. … si tiene usuarios con diferentes privilegios en la instancia de su SQL Server, no sería una mala idea permitir personas para iniciar sesión


Nota: no se ha agregado ningún error en el código C # y VBA. Recomiendo encarecidamente trabajar en ello si planea usar la técnica que he descrito anteriormente.


¿Qué tal si lo almacena en CustomDocumentProperties ?

Nota: No estoy seguro, si el libro de trabajo (basado en una plantilla dada) heredará la propiedad definida usando CustomDocumentProperties en la plantilla.