Come archiviare in modo sicuro i dettagli della stringa di connessione in VBA

Dispongo di un modello di Excel con percorso MDB con accesso diretto a Ms nel codice VBA utilizzato per connettersi alle tabelle di Access e salvare, recuperare i dati.

Ho migrato il database MS Access su SQL Server con l’autenticazione integrata per gli utenti del modello Excel.

La mia domanda è: Qual è il modo consigliato / Best Practice per l’archiviazione della stringa di connessione del DB di SQL Server e il recupero in Excel VBA di Excel 2007 per salvare e recuperare i dati?

In passato, ho fatto quanto segue.

  1. Utilizzare un’impostazione della chiave del Registro di sistema con la stringa di connessione. Quindi, in VBA, scrivere una funzione che legge la chiave di registro e restituisce la stringa di connessione.

  2. Avere un foglio nascosto “Impostazioni” all’interno del modello di Excel, con cella denominata per la stringa di connessione. Leggi la stringa di connessione in VBA accedendo all’intervallo indicato.

  3. Utilizzare un file txt .INI che accompagna il modello di Excel. (Questo non è l’ideale e voglio evitarlo poiché costruisce una dipendenza da quel file esterno)

Non mi piace # 1 perché voglio evitare di scrivere / leggere dal registro se ansible. # 2 sembra ok, pensavo non sono sicuro se c’è un modo migliore “più pulito” per fare questo.

qualche idea?

Questo è quello che farei per memorizzare in modo sicuro le credenziali della stringa di connessione

Scarica e installa Visual Studio Express 2012 per Windows ( GRATUITAMENTE )

Aprilo come amministratore e crea un nuovo progetto. Selezionare Visual C# quindi Class Library e rinominarlo in HiddenConnectionString

inserisci la descrizione dell'immagine qui

In Esplora soluzioni , rinominare Class1.cs in MyServer.cs

inserisci la descrizione dell'immagine qui

Fare clic con il tasto destro del mouse sul progetto MyConnection in Solution Explorer e selezionare Add Reference

Digitare activeX nella casella di ricerca e spuntare la Microsoft ActiveX Data Objects 6.1 Library

inserisci la descrizione dell'immagine qui

Copia e incolla il codice seguente in MyServer.cs sostituendo completamente ciò che si trova nel file.

 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(); } } } 

Individua la variabile cnStr nel codice e aggiorna i dettagli della stringa di connessione.

Fare clic con il pulsante destro del mouse sulla soluzione *HiddenConnectionString * in Esplora soluzioni e selezionare Proprietà.

Fare clic sulla scheda Application sul lato sinistro, quindi su Assembly Info e selezionare Make Assembly COM-Visible

inserisci la descrizione dell'immagine qui

Fare clic su *Build* dal menu a sinistra e selezionare Register For COM Interop

inserisci la descrizione dell'immagine qui

Nota: se stai sviluppando per Office a 64 bit, assicurati di cambiare Platform Target della Platform Target nel menu Genera su x64 ! Questo è obbligatorio per le librerie COM Office a 64 bit per evitare eventuali errori relativi ad ActiveX.


Fare clic con il tasto destro su HiddenConnectionString in Solution Explorer e selezionare Build dal menu.

Se tutto è andato a posto, allora HiddenConnectionString.dll e HiddenConnectionString.tlb dovrebbero essere generati correttamente. Vai a questo percorso ora

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

e dovresti vedere i tuoi file.

inserisci la descrizione dell'immagine qui


Ora apri Excel e vai su VBE. Fai clic su Tools e seleziona References .

Fare clic sul pulsante Sfoglia e selezionare HiddenConnectionString.tlb .

Inoltre, aggiungi i riferimenti alla Microsoft ActiveX Object 6.1 Library : è così ansible utilizzare la libreria ADODB.

inserisci la descrizione dell'immagine qui

Ora fai clic con il tasto destro in qualsiasi punto della finestra Esplora progetti e inserisci un nuovo Module

copia e incolla il codice qui sotto

 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 

Sostituisci [TABLE_NAME] con un nome di tabella reale nel tuo database.

Premi F5 o premi il pulsante di riproduzione verde sulla barra multifunzione.

inserisci la descrizione dell'immagine qui

Se tutto è andato a posto, ora dovresti vedere la tabella restituita sul tuo foglio di lavoro.

il mio esempio:

inserisci la descrizione dell'immagine qui


Come potete vedere. L’aggiunta di riferimenti alla propria libreria COM e l’archiviazione delle credenziali di accesso e di altri dati sensibili all’interno del .dll compilato proteggono i dati (stringa di connessione). È molto difficile decompilare il file *.dll per ottenere informazioni sensibili da esso. Ci sono varie tecniche di codifica per proteggere il tuo *.dll ancora di più, ma non ho intenzione di entrare nei dettagli ora. Questo stesso realizza ciò che hai chiesto.

myCn.GetConnection restituisce l’object ADODB.Connection inizializzato nella libreria COM di riferimento. Nessun utente di Excel verrà presentato con la stringa di connessione o dati sensibili (in realtà nessun altro).

È ansible modificare il codice C # per accettare parametri da VBA come login, password, catalogo iniziale, query da eseguire ecc … se si hanno utenti con privilegi diversi sull’istanza di SQL Server non sarebbe una ctriggers idea consentire persone per accedere.


Nota: non è stata aggiunta alcuna gestione degli errori nel codice C # e VBA. Consiglio vivamente di lavorarci su se hai intenzione di utilizzare la tecnica che ho descritto sopra.


Che ne dici di conservarlo in CustomDocumentProperties ?

Nota: non sono sicuro, se la cartella di lavoro (basata su un determinato modello) erediterà la proprietà definita utilizzando CustomDocumentProperties nel modello.