如何在 VBA 中安全地存储连接字符串详细信息 [英] How to securely store Connection String details in VBA

问题描述

我有一个 Excel 模板，它在 VBA 代码中硬编码了 Ms Access MDB 路径，用于连接到 Access 表并保存、检索数据.

I have an Excel Template that has hardcoded Ms Access MDB path in the VBA code used to connect to the Access tables and save, retrieve data.

我将 MS Access 数据库迁移到 SQL Server，并为 Excel 模板用户提供集成身份验证.

I migrated the MS Access Database over to SQL Server with Integrated Authentication for the Excel Template Users.

我的问题是，存储 SQL Server DB 连接字符串并在 Excel 2007 VBA 中检索它以保存和检索数据的推荐方式/最佳实践是什么?

My question is, What is the Recommend Way / Best Practice for storing the SQL Server DB connection string and retreiving it in Excel 2007 VBA to save and retrieve data?

过去，我做了以下事情.

In the past, I have done the following.

1. 使用具有连接字符串的注册表项设置.然后在 VBA 中编写一个函数来读取注册表项并返回连接字符串.

1. Use a Registry Key setting that has the Connection String. Then in the VBA, write a function that reads the registry key and returns the connection string.

在 Excel 模板中有一个设置"隐藏表，其中包含用于连接字符串的命名单元格.通过访问该命名范围读取 VBA 中的连接字符串.

Have a "Settings" hidden sheet within the Excel Template, with named cell for the connection string. Read the connection string in VBA by accessing that named range.

使用与 Excel 模板配套的 .INI txt 文件.(这并不理想，我想避免这种情况，因为它依赖于该外部文件)

Use a .INI txt file that goes with the Excel template. (This is not ideal and I want to avoid this as it builds a dependency on that external file)

我不喜欢#1，因为我想尽可能避免写入/读取注册表.# 2 感觉还可以，我不确定是否有更好的更干净"的方法来做到这一点.

I don't like # 1 because I want to avoid writing to/reading from Registry if possible. # 2 feels ok, thought I am not sure if there is a better "cleaner" way for doing this.

有什么想法吗?

推荐答案

这就是我会安全地存储连接字符串凭据的做法

This is what I would do safely store connection string credentials

下载并安装 Visual Studio Express 2012 for Windows(免费)

Download and install Visual Studio Express 2012 for Windows (FREE)

以管理员身份打开并创建一个新项目.选择 Visual C#，然后选择 Class Library 并将其重命名为 HiddenConnectionString

Open it as Administrator and create a New Project. Select Visual C# then Class Library and rename it to HiddenConnectionString

在解决方案资源管理器中，将Class1.cs重命名为MyServer.cs

在 Solution Explorer 中右键单击您的 MyConnection 项目，然后选择 Add Reference

Right click your MyConnection project in the Solution Explorer and select Add Reference

在搜索框中输入 activeX 并勾选 Microsoft ActiveX Data Objects 6.1 Library

Type activeX in the search box and tick the Microsoft ActiveX Data Objects 6.1 Library

将以下代码复制并粘贴到 MyServer.cs 中，完全替换文件中的任何内容.

Copy and paste the below code into the MyServer.cs completely replacing whatever is in the 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();
        }
    }
}

在代码中找到 cnStr 变量并更新您的连接字符串详细信息.

Locate the cnStr variable in the code and update your connection string details.

在解决方案资源管理器中右键单击*HiddenConnectionString*解决方案并选择属性.

Right click the *HiddenConnectionString* solution in the Solution Explorer and select Properties.

点击左侧的Application标签，然后Assembly Info并勾选Make AssemblyCOM-可见

点击左侧菜单中的*Build*并勾选Register For COM Interop

Click the *Build* from the menu on the left and tick Register For COM Interop

注意:如果您正在为 64 位 Office 进行开发，那么请确保将 Build 菜单上的 Platform Target 更改为 x64！这对于 64 位 Office COM 库是强制性的，以避免任何与 ActiveX 相关的错误.

Note: If you are developing for 64-bit Office then make sure you change the Platform Target on the Build menu to x64! This is mandatory for 64-bit Office COM libraries to avoid any ActiveX related errors.

右键单击解决方案资源管理器中的HiddenConnectionString，然后选择Build菜单.

Right click the HiddenConnectionString in the Solution Explorer and select Build from the menu.

如果一切顺利，那么您的 HiddenConnectionString.dll 和 HiddenConnectionString.tlb 应该会成功生成.现在去这条路

If everything went OK then your HiddenConnectionString.dll and HiddenConnectionString.tlb should be successfully generated. Go to this path now

C:UsersadministratorDocumentsVisual Studio 2012ProjectsHiddenConnectionStringHiddenConnectionStringinDebug

你应该会看到你的文件.

and you should see your files.

现在打开 Excel 并转到 VBE.单击 Tools 并选择 References.

Now open Excel and go to VBE. Click Tools and select References.

单击浏览按钮并导航到HiddenConnectionString.tlb.

此外，添加对 Microsoft ActiveX Object 6.1 库 的引用 - 这样您就可以使用 ADODB 库.

Also, add references to Microsoft ActiveX Object 6.1 Library - this is so you can use ADODB library.

现在右键单击项目资源管理器窗口中的任意位置，然后插入一个新的模块

Now right click anywhere in the Project Explorer window and Insert a new Module

复制以下代码并粘贴到其中

copy and paste the below code to it

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].

Replace the [TABLE_NAME] with an actual table name in your database.

点击 F5 或点击功能区上的绿色播放按钮.

Hit F5 or hit the green play button on the ribbon.

如果一切顺利，您现在应该会在电子表格中看到返回的表格.

If everything went OK, you should now see the returned Table on your spreadsheet.

我的样本:

如你所见.添加对您自己的 COM 库的引用并将登录凭据和其他敏感数据存储在编译的 .dll 中可以保护您的数据(连接字符串).反编译 *.dll 文件以从中获取任何合理的信息是非常困难的.有多种编码技术可以更好地保护您的*.dll，但我现在不打算详细介绍.这本身就实现了您的要求.

As you can see. Adding references to your own COM-library and storing the login credentials and other sensitive data inside the compiled .dll protects your data(connection string). It's very difficult to decompile the *.dll file to get any sensible information from it. There are various coding techniques to protect your *.dll even more but I am not going to go into details now. This itself achieves what you asked for.

myCn.GetConnection 返回在引用的 COM 库中初始化的 ADODB.Connection 对象.不会向 Excel 用户显示连接字符串或敏感数据(实际上其他人也不会).

myCn.GetConnection returns the ADODB.Connection object that was initialized inside the referenced COM library. No Excel user will be presented with the connection string or sensitive data (actually nobody else neither).

您可以修改 C# 代码以接受来自 VBA 的参数，即登录名、密码、初始目录、要执行的查询等...如果您的用户在 SQL Server 实例上具有不同的权限，那就不错了允许人们登录的想法.

You can modify the C# code to accept parameters from VBA i.e. login, password, initial catalog, query to execute etc... if you have users with different privileges on the instance of your SQL Server it wouldn't be a bad idea to allow people to log in.

注意:C# 代码和 VBA 中没有添加错误处理.如果您打算使用我上面描述的技术，我强烈建议您进行研究.

Note: there is no error handling added in the C# code and VBA. I would strongly recommending working on it if you're planning to use the technique I have described above.

这篇关于如何在 VBA 中安全地存储连接字符串详细信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！