您的位置: 首页  >  技术问答  >  将Always Encrypted Certificate放在IIS 7.5 Web服务器上的何处？

将Always Encrypted Certificate放在IIS 7.5 Web服务器上的何处？

分类: 技术问答 • 2022-04-26 10:34:06
问题描述：

我们有一个采用Always Encrypted的SQL Server 2016数据库。我们最近发布的ASP.net网站试图从这个数据库中提取数据，当它发生时我们得到这个错误：

We have a SQL Server 2016 database that employs Always Encrypted. Our recently published ASP.net web site attempts to pull data from this database, and when it does we get this error:

错误：失败解密列'EnSSd'。无法使用密钥库提供程序解密列加密密钥：'MSSQL_CERTIFICATE_STORE'。加密列加密密钥的最后10个字节是：'B8-48-B3-62-90-0B-1D-A6-7D-80'。证书位置'CurrentUser'中的证书存储'My'中找不到指纹'97B0D3A64CADBE86FE23559AEE2783317655FD0F'的证书。验证数据库中列主密钥定义中的证书路径是否正确，并且证书已正确导入证书位置/存储中。参数名称：masterKeyPath

现在我们知道这意味着证书尚未放置在服务器上的正确位置。在开发过程中，我们只是将证书放在个人证书存储区下的证书管理单元中，这样就可以了，但是现在网站已经发布了，我们尝试在网络服务器上做同样的事情，但它不起作用（我们认为它不会）。

Now we know that this means that the certificate has not been placed in the proper location on the server. During development we simply placed the certificate in the Certificates snap-in under the Personal Certificate Store, and that worked, however now that the site has been published we tried doing the same on the web server but it's not working (we kind of figured it wouldn't).

网站上启用了匿名身份验证，匿名用户身份是IUSR。 ASP.NET模拟被禁用。

Anonymous Authentication is enabled on the site and the anonymous user identity is IUSR. ASP.NET impersonation is disabled.

放置证书的适当位置在哪里？

Where is the proper place to put the certificate?

更新 - 我们通过将应用程序池标识帐户更改为创建证书的帐户来使其工作。它也是将证书添加到Web服务器上的当前用户个人列表时使用的帐户。我们宁愿不使用这个帐户，所以再次，在哪里放置证书的适当位置？

UPDATE - we got it to work by changing the Application Pool Identity account to the one that created the Certificate. It was also the account used when adding the certificate to the Current User-Personal list on the web server. We would rather not use this account, so again, where is the proper place to put the certificate?

答

IIS不能识别来自本地用户的证书，在SQL服务器中创建证书时，默认情况下它会放入本地用户存储，执行以下操作并确保在本地计算机下生成证书 - >当前用户证书存储

IIS can't recognize the certificate from local user, while creating the certificate in SQL server, by default it's putting in to the local user store, do the following things and make sure the certificate generated under local machine -> current user certificate store


  1. 使用默认证书生成加密列

  2. 将所有加密列撤消到纯文本

  3. 从表安全性您的数据库 - >表 - >安全性 - >始终加密密钥转到证书和密钥，然后右键单击CEK_Auto 1 - >脚本列加密密钥作为 - >创建新窗口，保留此生成的脚本

  4. 删除CEK_Auto 1

  5. 执行CMK_Auto 1证书的步骤3并将其删除
    6. CMK_Auto 1脚本中的
  6. 更改证书路径CurrentUser进入LocalMachine

  7. 你的例子路径将是这样的N'LocalMachine / my / G4452V8ERH035D2557N235B29MWR0SV834263G26

  8. 执行CMK_Auto 1和CEK_Auto 1脚本

  9. 确保证书生成本地计算机个人目录

  10. 它可以工作，如果没有用IIS Express测试那意味着你的本地用户个人目录中保存的证书

  11. 其余全部相同，请确保在连接字符串中添加Column Encryption Setting = Enabled。

  1. Generate the encrypted columns with default certificates
  2. Undo all encrypted columns in to plain text
  3. Go to the certificate and key from the table security "Your DB - > Tables - > Security -> Always Encrypted Keys" and right click the "CEK_Auto 1" -> Script Column Encryption Key as -> Create to new window, keep this generated script
  4. Delete the CEK_Auto 1
  5. Do the step 3 for "CMK_Auto 1" certificate and delete this as well
  6. in the "CMK_Auto 1" script change the certificate path "CurrentUser" in to "LocalMachine"
  7. you example path will be like this "N'LocalMachine/my/G4452V8ERH035D2557N235B29MWR0SV834263G26'"
  8. execute the CMK_Auto 1 and CEK_Auto 1 script
  9. make sure the certificate generated local machine personal directory
  10. it will work, if not test with IIS express that means still your certificate held in local user personal directory
  11. rest all same make sure the "Column Encryption Setting = Enabled" added in the connection string.

谢谢

John Rajesh J

John Rajesh J

相关推荐