如何在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数据库转移到具有针对Excel模板用户的集成身份验证的SQL Server。
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.
-
使用具有连接字符串的注册表项设置。然后在VBA中，编写一个读取注册表项并返回连接字符串的函数。
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
下载并安装<一个href = http://www.microsoft.com/visualstudio/chi/downloads#d-express-windows-desktop rel = noreferrer>适用于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();
}
}
}
找到<$ c $代码中的c> cnStr 变量，并更新您的连接字符串详细信息。
Locate the cnStr variable in the code and update your connection string details.
右键单击<$ c $在 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.
右键单击 <解决方案资源管理器中的code> 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.
点击< kbd>浏览按钮并导航到 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.
这篇关于如何在VBA中安全存储连接字符串详细信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
