加密excel-vba ADODB连接 [英] Encrypt excel-vba ADODB connection

问题描述

我正在创建一些Excel报表，并且正在尝试加密我的SQL连接.在服务器端，我已正确设置了所有程序，并且通过Microsoft Management Studio，我可以建立安全连接.但是我找不到Excel的任何解决方案.我想我需要类似 Encrypt = yes 的东西，或者这是ADODB.Connection不支持SSL吗?

I am in the process of creating some Excel reports and i am trying to encrypt my SQL connections. On server side i have everything setup properly and with Microsoft management studio i am able to establish a secure connection. However i cant find any solution for excel. I think i need something like Encrypt = yes or is this ADODB.Connection not supporting SSL ?

Private Function GetDataFromADO(ByVal Sel As String) As ADODB.Recordset

'Declare variables'
    Dim objMyConn As ADODB.Connection
    Dim objMyCmd As ADODB.Command
    Dim objMyRecordset As ADODB.Recordset

    Set objMyConn = New ADODB.Connection
    Set objMyCmd = New ADODB.Command
    Set objMyRecordset = New ADODB.Recordset

'Open Connection'
    objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=" & sqlServer & ";Database=" & _
        sqlDB & ";User ID=" & sqlUsername & ";Password=" & sqlPassword & ";"
    Dim s As String
    s = objMyConn.ConnectionString
    objMyConn.Open


'Set and Excecute SQL Command'
    Set objMyCmd.ActiveConnection = objMyConn
    objMyCmd.CommandText = Sel
    objMyCmd.CommandType = adCmdText

'Open Recordset'
    Set objMyRecordset.Source = objMyCmd
    objMyRecordset.Open


Set GetDataFromADO = objMyRecordset

预先感谢

推荐答案

如果要使用SSL或TLS保护与SQL Server的每个连接的安全，则应将安全加密"选项设置为是".服务器并安装由域CA或公共CA生成的证书.将强制加密"设置为是"会使用从SQL Server生成的自签名证书对所有连接进行加密.这就使得在中间攻击中一个人的可能性敞开了.CA或证书颁发机构是用于证明用户，计算机和组织的身份以及生成，管理和更新证书的服务或应用程序.

If you want to secure every connection to your SQL Server using SSL or TLS, then you should set the Force Encryption option to yes for the set of protocols on the server and also install a certificate generated by the Domain CA or a public CA. Setting Force Encryption to yes encrypts all connections with a self-signed certificate generated from the SQL Server. This leaves the possibility of a man in the middle attack open. The CA, or Certificate Authority, is the service or application used to attest to the identity of users, computers, and organizations as well as generate, manage and renew certificates.

让我们假设您正在使用域CA生成的证书.只要您的SQL Server，域CA系统和客户端系统可以相互通信，应该发生的情况是客户端启动与SQL Server的会话，然后该SQL Server响应对安全会话的请求并出示从生成的证书将域CA服务器发送给客户端以进行验证.然后，客户端应通过两种方式来验证证书，即与域CA系统一起验证该证书是否有效，以及使用数字签名来验证它是否已被修改.

Lets assume you are using a certificate generated by the domain CA. As long as your SQL Server, domain CA system and client system can communicate with each other, what should happen is that the client initiates a session with the SQL Server, which then responds with the request for a secure session and presents a certificate generated from the domain CA server to the client for validation. Then the client should validate the certificate in two ways, by verifying with the domain CA system that it is a valid certificate and by using the digital signature to verify that it has not been modified.

在SQL Server上启用SSL的基础是将证书和私钥导入到运行数据库引擎的服务帐户的证书存储中，然后在使用SQL的服务器协议的属性中将强制加密"属性设置为是"服务器配置管理器，然后在证书"选项卡上选择从域CA或公共CA生成的证书.

The basics of enabling SSL on your SQL Server are to import the certificate and private key into the certificate store of the service account running the database engine, then set the Force Encryption property to yes in the properties of the server protocols using SQL Server Configuration Manager and on the Certificate tab select the certificate generated from the domain CA or a public CA.

这篇关于加密excel-vba ADODB连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！