如何从经典ASP中读取SQL Always Encrypted列 [英] How to read SQL Always-encrypted column from classic ASP

查看：84
本文介绍了如何从经典ASP中读取SQL Always Encrypted列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我维护着一个经典的ASP应用程序(是的，我知道，我们正在开发中)，并且需要访问SQL 2017中的Always Encrypted列.

我已经导入了证书并在SSMS和PowerShell中进行了测试，并且可以正常工作.我在ASP中能做的最好的事情就是将加密后的值作为字节数组获取.我尝试的连接字符串组合超出了我的记忆.我的ASP开发箱是Windows 10；数据服务器是SQL 2017.

  cnn ="Provider = MSOLEDBSQL; DataTypeCompatibility = 80;" _&"DRIVER = {用于SQL Server的ODBC驱动程序17};" _&服务器= xxxx; UID = xxxxx; PWD = xxxxx;数据库= xxxx;" _&"ColumnEncryption =启用；列加密设置=启用;"设置oDB = CreateObject("ADODB.Connection")oDB.Open CNN设置oCmd = Server.CreateObject("ADODB.Command")oCmd.ActiveConnection = cnnoCmd.CommandText =选择前10个ID，Enc FROM tbl1"设置rst = oCmd.Execute()

代码可以正常工作，但是加密列(Enc，varchar(50))作为字节数组返回.当我应该获取纯文本值时，似乎正在获取加密值.我也尝试过调用具有相同结果的存储过程.查询中没有过滤器，因此无需进行参数设置.有什么想法下一步可以尝试吗?

答案:
1)以与该Web应用程序的AppPool身份相同的用户身份导入证书.
2)将Web应用程序的Anon授权设置为应用程序池标识".
3)使用以下连接字符串:

  cnn ="Provider = MSDASQL;"_&"扩展属性=" Driver = {用于SQL Server的ODBC驱动程序17};"_&服务器= ***; UID = ***; PWD = ***;"_&"DATABASE = ***; ColumnEncryption = Enabled;""

解决方案

SQL Server的新Microsoft OleDb提供程序(MSOLEDBSQL)目前不支持AlwaysEncrypted.您必须使用ODBC，这意味着OleDb提供程序应该是Microsoft ODBC的Microsoft OleDb提供程序(MSDASQL).因此，您可以使用Microsoft®ODBC Driver 17 for SQL Server为系统DSN配置连接字符串，例如:

  cnn ="Provider = MSDASQL; DSN = testdb;"

或将所有ODBC驱动程序参数嵌入MSDASQL连接字符串的扩展属性"中.像

  cnn ="Provider = MSDASQL;扩展属性=""Driver = {用于SQL Server的ODBC驱动程序17}; Server = localhost; Database = testdb; Trusted_Connection = yes; ColumnEncryption = Enabled;"

这里是演练，在使用ASP进行测试之前，先使用第一个VBScript进行测试.

开始于:

 创建数据库testdb去使用testdb创建表tbl1(id int，Enc varchar(200))插入tbl1(id，enc)值(1，'Hello')

然后通过运行SSMS的计算机上的SSMS中的列加密向导运行，该向导为当前用户存储证书:

然后列出query.vbs:

  cnn ="Provider = MSDASQL;扩展属性=""Driver = {用于SQL Server的ODBC驱动程序17}; Server = localhost; Database = testdb; Trusted_Connection = yes; ColumnEncryption = Enabled;"设置oDB = CreateObject("ADODB.Connection")oDB.Open CNN设置oCmd = CreateObject("ADODB.Command")oCmd.ActiveConnection = cnnoCmd.CommandText =选择前10个ID，Enc FROM tbl1"设置rst = oCmd.Execute()rst.MoveFirst()msgbox(cstr(rst("Enc")))

可以通过以下命令在命令行中运行

:

  cscript.\ query.vbs

要从ASP中执行此操作，您还必须根据文档

这是一个ASP页面，可用于测试:

 <！DOCTYPE html>< html><身体>< p>输出:</p><％设置objNetwork = CreateObject("Wscript.Network")Response.write(当前用户为"& objNetwork.UserName)cnn ="Provider = MSDASQL;扩展属性=""Driver = {用于SQL Server的ODBC驱动程序17}; Server = localhost;数据库= testdb; Trusted_Connection = yes; ColumnEncryption = Enabled;""设置oDB = CreateObject("ADODB.Connection")oDB.Open CNN设置oCmd = CreateObject("ADODB.Command")oCmd.ActiveConnection = cnnoCmd.CommandText =选择前10个ID，Enc FROM tbl1"设置rst = oCmd.Execute()rst.MoveFirst()Response.write(cstr(rst("Enc")))％></body></html>

I maintain a classic ASP app (yeah, I know, we're working on it) and need to access Always Encrypted columns in SQL 2017.

I've imported the cert and tested in SSMS and PowerShell and that much works. The best I've been able to do in ASP is to get the encrypted value as a byte array. I've tried more connection string combinations than I can remember. My ASP dev box is Windows 10; the data server is SQL 2017.

cnn = "Provider=MSOLEDBSQL; DataTypeCompatibility=80; " _
    & "DRIVER={ODBC Driver 17 for SQL Server}; " _
    & "SERVER=xxxx; UID=xxxxx; PWD=xxxxx; DATABASE=xxxx; " _
    & "ColumnEncryption=Enabled; Column Encryption Setting=Enabled;"
Set oDB = CreateObject( "ADODB.Connection" )
oDB.Open cnn
set oCmd = Server.CreateObject("ADODB.Command") 
oCmd.ActiveConnection = cnn
oCmd.CommandText = "SELECT top 10 ID, Enc FROM tbl1"
set rst = oCmd.Execute()

The code works without error but the encrypted column (Enc, varchar(50)) is returned as a byte array. I seem to be getting the encrypted value when I should get the plain text value. I've also tried calling a Stored Procedure with the same results. No filter in the query, so nothing to parameterize. Any ideas what to try next?

Answer:
1) Import the cert as the same user as the AppPool Identity for that web app.
2) Set Anon authorization for the web app to Application Pool Identity.
3) Use this connection string:

  cnn = "Provider=MSDASQL;" _
      & "Extended Properties=""Driver={ODBC Driver 17 for SQL Server};" _
      & "SERVER=***; UID=***; PWD=***;" _
      & "DATABASE=***;ColumnEncryption=Enabled;"" "

解决方案

The new Microsoft OleDb Provider for SQL Server (MSOLEDBSQL) doesn't support AlwaysEncrypted (currently). You'll have to use ODBC, which means the OleDb provider should be the Microsoft OleDb Provider for ODBC (MSDASQL). So you can either configure a system DSN using Microsoft® ODBC Driver 17 for SQL Server with a connection string like: 

cnn = "Provider=MSDASQL;DSN=testdb;"

or embed all the ODBC driver parameters in the "Extended Properties" of the MSDASQL connection string. Like 

cnn = "Provider=MSDASQL;Extended Properties=""Driver={ODBC Driver 17 for SQL Server};Server=localhost;Database=testdb;Trusted_Connection=yes;ColumnEncryption=Enabled;"" "

Here's walkthrough, using first VBScript for testing before testing with ASP.

Starting with:

create database testdb
go
use testdb

create table tbl1(id int, Enc varchar(200))

insert into tbl1(id,enc) values (1,'Hello')

Then running through the column encryption wizard in SSMS, which stores the certificate on for the current user, on the machine running SSMS:

Then the listing of query.vbs:

cnn = "Provider=MSDASQL;Extended Properties=""Driver={ODBC Driver 17 for SQL Server};Server=localhost;Database=testdb;Trusted_Connection=yes;ColumnEncryption=Enabled;"" "
Set oDB = CreateObject( "ADODB.Connection" )
oDB.Open cnn
set oCmd = CreateObject("ADODB.Command") 
oCmd.ActiveConnection = cnn
oCmd.CommandText = "SELECT top 10 ID, Enc FROM tbl1"
set rst = oCmd.Execute()
rst.MoveFirst()
msgbox( cstr(rst("Enc")) )

Which can be run from the commandline with:

cscript  .\query.vbs

To do this from ASP you'll additionally have to place the certificate in the user certificate store of the IIS App Pool account, per the docs here. Note that the relative path to the certificate has to be the same for all users. You can't store it in the machine store on the IIS box if you initially configured it to be stored in the user's certificate store. SQL Server stores the key_path of the key and instructs the clients where to find the certificate, eg CurrentUser/my/388FF64065A96DCF0858D84A88E1ADB5A927DECE.

So discover the key path of the Column Master Key

select name, key_path from sys.column_master_keys

Then export the certificate from the machine that has it:

 PS C:\Windows> $path = "cert:\CurrentUser\My\388FF64065A96DCF0858D84A88E1ADB5A927DECE"
 PS C:\Windows> $mypwd = ConvertTo-SecureString -String "xxxxxxx" -Force -AsPlainText
 PS C:\Windows> Export-PfxCertificate -Cert $path -FilePath c:\temp\myexport.pfx -ChainOption EndEntityCertOnly  -Password $mypwd

Running as the app pool identity user on the IIS server, import it

PS C:\WINDOWS> $mypwd = ConvertTo-SecureString -String "xxxxxxx" -Force -AsPlainText
PS C:\WINDOWS> Import-PfxCertificate -FilePath C:\temp\myexport.pfx -CertStoreLocation Cert:\LocalMachine\My -Password $mypwd

And if you're using Anonymous/Forms auth sure you've configured IIS Anonymous Auth to run under the App Pool identity, not the default IUSR.

Here's an ASP page to test with:

<!DOCTYPE html>
<html>
<body>

<p>Output :</p>

<%

Set objNetwork = CreateObject("Wscript.Network")
Response.write("The current user is " & objNetwork.UserName)

cnn = "Provider=MSDASQL;Extended Properties=""Driver={ODBC Driver 17 for SQL Server};Server=localhost;Database=testdb;Trusted_Connection=yes;ColumnEncryption=Enabled;"" "
Set oDB = CreateObject( "ADODB.Connection" )
oDB.Open cnn
set oCmd = CreateObject("ADODB.Command") 
oCmd.ActiveConnection = cnn
oCmd.CommandText = "SELECT top 10 ID, Enc FROM tbl1"
set rst = oCmd.Execute()
rst.MoveFirst()
Response.write(cstr(rst("Enc")) )

%>

</body>
</html>

这篇关于如何从经典ASP中读取SQL Always Encrypted列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆