如何共享需密码连接MS SQL Server的Excel Power Query？

问题描述 #

我用Excel的Power Query从Microsoft SQL Server数据库的多张表中获取数据，输入账号密码后可正常取数。现在希望他人能运行报表查看数据，但不想提供密码，也不允许他们修改数据。

他人运行查询时会被要求输入密码，我尝试将密码随查询保存：右键查询进入属性的定义选项卡，发现保存密码的复选框呈灰色不可选。

我通过资料了解到可通过VBA设置该属性，编写并运行了如下代码：

Sub Set_Password()
Dim ws As Worksheet
Dim RHO As Integer
    For RHO = 1 To ActiveWorkbook.Connections.Count
        With ActiveWorkbook.Connections(RHO).OLEDBConnection
            .SavePassword = True
        End With
    Next RHO
    MsgBox "Done!"
End Sub

运行后用另一代码验证SavePassword已设为True：

Sub test_password_flag()
Dim RHO As Integer
    For RHO = 1 To ActiveWorkbook.Connections.Count
        Debug.Print RHO, ActiveWorkbook.Connections(RHO).Name, ActiveWorkbook.Connections(RHO).OLEDBConnection.SavePassword
        Debug.Print ActiveWorkbook.Connections(RHO).OLEDBConnection.CommandText
    Next RHO
End Sub

验证结果显示所有查询的SavePassword均为True，且CommandText匹配对应查询，但属性中的复选框仍未勾选。请问我的处理方向是否错误？

解答 #

你的处理方向没错，问题出在Power Query连接的界面显示逻辑和实际属性的差异上，以下是具体说明和补充方案：

• 界面灰色不代表属性未生效
  Power Query创建的连接，其属性窗口的部分选项会被Power Query引擎接管，所以即使通过VBA成功设置SavePassword = True，界面上的复选框依然会呈灰色。这只是显示问题，你可以直接让用户打开文件测试，大概率不会再弹出密码输入框。

• 验证密码是否真的保存
  修改你的验证代码，添加连接字符串的输出，确认密码是否已写入：

  Sub test_password_flag()
  Dim RHO As Integer
      For RHO = 1 To ActiveWorkbook.Connections.Count
          With ActiveWorkbook.Connections(RHO).OLEDBConnection
              Debug.Print RHO, .Name, .SavePassword
              Debug.Print .CommandText
              Debug.Print .Connection ' 新增这行查看连接字符串
          End With
      Next RHO
  End Sub
  

  如果输出的连接字符串中包含Password=xxx的字段，说明密码已经成功保存。

• 补充数据保护措施
  要实现不让他人修改数据的需求，还可以做这些操作：

  • 将加载数据的工作表设置为保护状态，禁止编辑单元格；
  • 在Power Query编辑器中，设置查询的加载选项为「仅创建连接」，或加载后隐藏查询编辑器入口；
  • 将文件保存为.xlsm格式后，保护VBA项目，防止他人篡改代码。

• 安全风险提示
  注意：密码是以明文形式保存在Excel文件中的，有技术能力的用户可以通过读取连接字符串获取密码。如果对安全性要求较高，建议改用Windows域身份验证，或者给SQL Server创建只读权限的专用账号，降低风险。

内容的提问来源于stack exchange，提问作者CLH