如何在 VBA 中安全地存储连接字符串详细信息

答

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

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.