Как безопасно хранить информацию о строках соединения в VBA

У меня есть шаблон Excel, который имеет жестко заданный путь MDB MDB в коде VBA, который используется для подключения к таблицам Access и сохранения, получения данных.

Я перенесла базу данных MS Access на SQL Server со встроенной аутентификацией для пользователей шаблонов Excel.

Мой вопрос: что такое рекомендуемый способ / наилучшая практика для хранения строки подключения к базе данных SQL Server и ее возврата в Excel 2007 VBA для сохранения и извлечения данных?

Раньше я делал следующее.

  1. Используйте параметр реестра, в котором есть строка подключения. Затем в VBA напишите функцию, которая считывает ключ реестра и возвращает строку подключения.

  2. У вас есть скрытый лист «Настройки» в шаблоне Excel с именованной ячейкой для строки подключения. Прочитайте строку соединения в VBA, обратившись к указанному диапазону.

  3. Используйте файл .INI txt, который идет с шаблоном Excel. (Это не идеально, и я хочу избежать этого, поскольку он создает зависимость от этого внешнего файла)

Мне не нравится # 1, потому что я хочу, чтобы избежать записи в / чтение из реестра, если это возможно. # 2 чувствует себя хорошо, подумал, что я не уверен, есть ли лучший «более чистый» способ для этого.

Есть предположения?

    Это то, что я сделал бы безопасно хранить учетные данные строки подключения

    Загрузите и установите Visual Studio Express 2012 для Windows ( БЕСПЛАТНО )

    Откройте его как администратор и создайте новый проект. Выберите Visual C# затем Class Library и переименуйте его в HiddenConnectionString

    введите описание изображения здесь

    В обозревателе решений переименуйте Class1.cs в MyServer.cs

    введите описание изображения здесь

    Щелкните правой кнопкой MyConnection проект MyConnection в обозревателе решений и выберите Add Reference MyConnection

    Введите activeX в поле поиска и отметьте Microsoft ActiveX Data Objects 6.1 Library

    введите описание изображения здесь

    Скопируйте и вставьте приведенный ниже код в MyServer.cs полностью заменив все, что находится в файле.

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

    Найдите переменную cnStr в коде и обновите данные строки подключения.

    Щелкните правой кнопкой мыши по *HiddenConnectionString * в обозревателе решений и выберите «Свойства».

    Перейдите на вкладку « Application » на левой стороне, затем « Assembly Info и отметьте « Make Assembly COM-Visible

    введите описание изображения здесь

    Нажмите « *Build* в меню слева и отметьте « Register For COM Interop

    введите описание изображения здесь

    Примечание. Если вы разрабатываете 64-разрядный Office, убедитесь, что вы изменили Platform Target в меню « Построение» на x64 ! Это необходимо для 64-битных библиотек Office COM, чтобы избежать ошибок, связанных с ActiveX.


    Щелкните правой кнопкой мыши HiddenConnectionString в обозревателе решений и выберите « Build в меню.

    Если все прошло нормально, ваши HiddenConnectionString.dll и HiddenConnectionString.tlb должны быть успешно сгенерированы. Перейти к этому пути сейчас

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

    и вы должны увидеть свои файлы.

    введите описание изображения здесь


    Теперь откройте Excel и перейдите в VBE. Нажмите « Tools и выберите « References .

    Нажмите кнопку « Обзор» и перейдите к HiddenConnectionString.tlb .

    Кроме того, добавьте ссылки на Microsoft ActiveX Object 6.1 Library – это значит, что вы можете использовать библиотеку ADODB.

    введите описание изображения здесь

    Теперь щелкните правой кнопкой мыши в любом месте windows Project Explorer и вставьте новый Module

    скопируйте и вставьте ниже код к нему

     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 

    Замените [TABLE_NAME] фактическим именем таблицы в вашей базе данных.

    Нажмите F5 или нажмите зеленую кнопку воспроизведения на ленте.

    введите описание изображения здесь

    Если все пойдет по порядку, вы должны увидеть возвращенную таблицу в своей таблице.

    мой образец:

    введите описание изображения здесь


    Как вы видете. Добавление ссылок на вашу собственную COM-библиотеку и сохранение учетных данных входа и других конфиденциальных данных внутри скомпилированной .dll защищает ваши данные (строку подключения). Очень сложно декомпилировать файл *.dll чтобы получить от него разумную информацию. Существуют различные методы кодирования , чтобы еще больше защитить вашу *.dll но теперь я не буду вдаваться в подробности. Это само по себе достигает того, о чем вы просили.

    myCn.GetConnection возвращает объект ADODB.Connection который был инициализирован внутри указанной библиотеки COM. Никакой пользователь Excel не будет представлен строкой подключения или конфиденциальными данными (на самом деле никого нет).

    Вы можете изменить код C #, чтобы принимать параметры из VBA, т. Е. Логин, пароль, начальный каталог, запрос для выполнения и т. Д. … если у вас есть пользователи с разными привилегиями на экземпляре вашего SQL Server, было бы неплохо позволить чтобы войти в систему.


    Примечание: в коде C # и VBA отсутствует обработка ошибок. Я настоятельно рекомендовал бы работать над этим, если вы планируете использовать технику, описанную выше.


    Как сохранить его в CustomDocumentProperties ?

    Примечание. Я не уверен, что если книга (на основе данного шаблона) наследует свойство, определенное с помощью CustomDocumentProperties в шаблоне.

    Давайте будем гением компьютера.