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

答

这是我将安全存储连接字符串凭据的方式

This is what I would do safely store connection string credentials

下载并安装适用于Windows的Visual Studio Express 2012（免费）。

以管理员身份打开并创建一个新项目。选择 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

In the Solution Explorer, rename Class1.cs to MyServer.cs

在 Solution Explorer 中右键单击您的 MyConnection 项目，然后选择 添加参考

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

在搜索框中输入 activeX 并在 Microsoft ActiveX数据对象6.1库

将以下代码复制并粘贴到 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.

右键单击 Solution Explorer 中找到c> * HiddenConnectionString *解决方案，然后选择属性。

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

单击 应用程序 标签在左侧，然后 组装信息 并勾选使程序集COM可见

从左侧菜单中单击 * Build * 勾号注册COM互操作

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

注意：如果您正在开发64位Office，请确保更改 Build 菜单上的c $ c>平台目标 更改为 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:\Users\administrator\Documents\Visual Studio 2012\Projects\HiddenConnectionString\HiddenConnectionString\bin\Debug

，您应该会看到文件。

现在打开Excel并转到VBE。点击工具并选择参考。

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.

现在右键单击 Project Explorer中的任何地方窗口并插入一个新的 Module

Now right click anywhere in the Project Explorer window and Insert a new 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] 替换为数据库中的实际表名。

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

按 F5 或按功能区上的绿色 play 按钮。

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 返回 ADODB.Connection 对象在引用的COM库中初始化的代码。不会向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.