如何在 VBA 中安全地存储连接字符串详细信息
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3217014/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to securely store Connection String details in VBA
提问by Shiva
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.
我有一个 Excel 模板，它在用于连接到 Access 表并保存、检索数据的 VBA 代码中硬编码 Ms Access MDB 路径。
I migrated the MS Access Database over to SQL Server with Integrated Authentication for the Excel Template Users.
我将 MS Access 数据库迁移到带有 Excel 模板用户集成身份验证的 SQL Server。
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?
我的问题是，存储 SQL Server 数据库连接字符串并在 Excel 2007 VBA 中检索它以保存和检索数据的推荐方式/最佳实践是什么？
In the past, I have done the following.
过去，我做了以下工作。
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.
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.
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)
使用具有连接字符串的注册表项设置。然后在 VBA 中，编写一个函数来读取注册表项并返回连接字符串。
在 Excel 模板中有一个“设置”隐藏表，其中包含连接字符串的命名单元格。通过访问该命名范围来读取 VBA 中的连接字符串。
使用与 Excel 模板配套的 .INI txt 文件。（这并不理想，我想避免这种情况，因为它依赖于该外部文件）
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.
我不喜欢#1，因为我想尽可能避免写入/读取注册表。# 2 感觉还可以，我想我不确定是否有更好的“更干净”的方法来做到这一点。
Any thoughts?
有什么想法吗？
回答by
This is what I would do safely store connection string credentials
这就是我会安全地存储连接字符串凭据的操作
Download and install Visual Studio Express 2012 for Windows (FREE)
下载并安装Visual Studio Express 2012 for Windows（免费）
Open it as Administratorand create a New Project. Select Visual C#then Class Libraryand rename it to HiddenConnectionString
以管理员身份打开它并创建一个新项目。选择Visual C#然后Class Library将其重命名为HiddenConnectionString
In the Solution Explorer, rename Class1.csto MyServer.cs
在解决方案资源管理器中，重命名Class1.cs为MyServer.cs
Right click your MyConnectionproject in the Solution Explorerand select Add Reference
MyConnection在解决方案资源管理器中右键单击您的项目并选择Add Reference
Type activeXin the search box and tick the Microsoft ActiveX Data Objects 6.1 Library
键入activeX在搜索框中，并勾选Microsoft ActiveX Data Objects 6.1 Library
Copy and paste the below code into the MyServer.cscompletely replacing whatever is in the file.
将以下代码复制并粘贴到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();
}
}
}
Locate the cnStrvariable in the code and updateyour connection string details.
cnStr在代码中找到该变量并更新您的连接字符串详细信息。
Right click the *HiddenConnectionString* solution in the Solution Explorerand select Properties.
右键单击*HiddenConnectionString解决方案资源管理器中的* 解决方案并选择属性。
Click the Applicationtab on the left side, then Assembly Infoand tick Make Assembly COM-Visible
单击Application左侧的选项卡，然后Assembly Info勾选Make Assembly COM-Visible
Click the *Build*from the menu on the left and tick Register For COM Interop
单击*Build*左侧菜单中的 并勾选Register For COM Interop
Note: If you are developing for 64-bit Office then make sure you change the Platform Targeton the Buildmenu to x64! This is mandatory for 64-bit Office COM libraries to avoid any ActiveX related errors.
注意：如果您正在为 64 位 Office 进行开发，请确保Platform Target将Build菜单上的更改为x64! 这对于 64 位 Office COM 库是必需的，以避免任何与 ActiveX 相关的错误。
Right click the HiddenConnectionStringin the Solution Explorerand select Buildfrom the menu.
右键单击HiddenConnectionString在解决方案资源管理器，然后选择Build从菜单。
If everything went OK then your HiddenConnectionString.dlland HiddenConnectionString.tlbshould be successfully generated. Go to this path now
如果一切顺利，那么您的HiddenConnectionString.dll和HiddenConnectionString.tlb应该会成功生成。现在走这条路
C:\Users\administrator\Documents\Visual Studio 2012\Projects\HiddenConnectionString\HiddenConnectionString\bin\Debug
and you should see your files.
你应该看到你的文件。
Now open Excel and go to VBE. Click Toolsand select References.
现在打开 Excel 并转到 VBE。单击Tools并选择References。
Click the Browsebutton and navigate to the HiddenConnectionString.tlb.
单击Browse按钮并导航到HiddenConnectionString.tlb.
Also, add references to Microsoft ActiveX Object 6.1 Library- this is so you can use ADODB library.
此外，添加对Microsoft ActiveX Object 6.1 Library- 这样您就可以使用 ADODB 库的引用。
Now right click anywhere in the Project Explorerwindow and Inserta new Module
现在右键单击Project Explorer窗口中的任意位置并插入一个新的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
Replace the [TABLE_NAME]with an actual table name in your database.
将 替换为[TABLE_NAME]数据库中的实际表名。
Hit F5or hit the green playbutton on the ribbon.
点击F5或点击功能区上的绿色播放按钮。
If everything went OK, you should now see the returned Table on your spreadsheet.
如果一切顺利，您现在应该会在电子表格中看到返回的表格。
my sample:
我的样本：
As you can see. Adding references to your own COM-library and storing the login credentials and other sensitive data inside the compiled .dllprotects your data(connection string). It's very difficult to decompile the *.dllfile to get any sensible information from it. There are various coding techniquesto protect your *.dlleven more but I am not going to go into details now. This itself achieves what you asked for.
如你看到的。添加对您自己的 COM 库的引用并将登录凭据和其他敏感数据存储在编译的内部.dll可以保护您的数据（连接字符串）。反编译*.dll文件以从中获取任何合理的信息是非常困难的。有多种编码技术可以为您提供*.dll更多保护，但我现在不打算详细介绍。这本身就实现了您的要求。
myCn.GetConnectionreturns the ADODB.Connectionobject 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).
myCn.GetConnection返回在ADODB.Connection引用的 COM 库中初始化的对象。不会向 Excel 用户显示连接字符串或敏感数据（实际上其他人也不会）。
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 的参数，即登录名、密码、初始目录、要执行的查询等……如果您的用户在 SQL Server 实例上具有不同的权限，那么允许人登录。
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.
注意：C# 代码和 VBA 中没有添加错误处理。如果您打算使用我上面描述的技术，我强烈建议您进行研究。
回答by shahkalpesh
How about storing it under CustomDocumentProperties?
把它放在下面CustomDocumentProperties怎么样？
Note: I am not sure, if the workbook (based on a given template) will inherit the property defined using CustomDocumentPropertiesin the template.
注意：我不确定工作簿（基于给定模板）是否会继承模板中定义的属性CustomDocumentProperties。
