SQL Server-将存储过程与列加密一起使用 [英] SQL Server - Using Stored Procedures with Column Encryption
问题描述
大家好，
希望有一个简单的问题.
我有一个SQL Server 2010(标准)数据库，并且正在尝试使用列加密.
一切顺利，我似乎无法使用ASP.Net网站上的存储过程从表中检索出数据.
调用该存储过程，并发出SELECT语句，并返回一个DataTable.尝试从列中检索数据时，ASP.Net代码认为数据为NULL.
我可以在SQL Server Studio中使INSERTS和SELECTS正常工作，以向我显示数据.
语句的SELECT部分(节选)如下所示
...
CAST(DecryptByKey(FirstName)AS varchar(50))作为FirstName
...
返回代码后，我可以看到(调试时)该列存在于DataTable中，但其中没有数据.
任何人都可以帮忙，我的想法已经用光了，和以往一样，时间表很短.... :)
非常感谢
朱利安
Hi all,
Hopefully a quick question.
I have a SQL Server 2010 (standard) database and am experimenting with column encryption.
All going well, excpet that I don''t appear to be able to retrieve the data back out of the table using a stored procedure from an ASP.Net web site.
The stored procedure is called and the SELECT statement is issued and a DataTable is returned. On attempting to retrieve the data from the column, the ASP.Net code thinks that the data is NULL.
I can get the INSERTS and SELECTS working fine in SQL Server studio to show me the data.
The SELECT part (excerpt) of the statement looks like this
...
CAST(DecryptByKey(FirstName) AS varchar(50)) As FirstName
...
and on returning to the code I can see (whilst debugging) that the column exists in the DataTable but with no data in it.
Can anyone help, I''m running out of ideas and, as ever, timescales are short.... :)
Many thanks
Julian
推荐答案
别担心，对其进行了排序.
我的存储过程中的OPEN SYMMTERIC KEY语句放置在错误的位置.
看起来像这样:
OPEN SYMMETRIC KEY SymKey1_TOOS
通过证书CERT1_TOOS解密；
更改过程[dbo].[Test004]
(
@CustomerID int
)
AS
选择
客户ID，
等...
代替:
更改过程[dbo].[Test004]
(
@CustomerID int
)
AS
开始
OPEN SYMMETRIC KEY SymKey1_TOOS
通过证书CERT1_TOOS解密；
选择
客户ID，
等...
万岁...
J
Don''t worry, sorted it.
I had my OPEN SYMMTERIC KEY statement in the Stored Procedure in the wrong place.
It looked like this:
OPEN SYMMETRIC KEY SymKey1_TOOS
DECRYPTION BY CERTIFICATE CERT1_TOOS;
ALTER PROCEDURE [dbo].[Test004]
(
@CustomerID int
)
AS
SELECT
CustomerID,
etc...
instead of:
ALTER PROCEDURE [dbo].[Test004]
(
@CustomerID int
)
AS
BEGIN
OPEN SYMMETRIC KEY SymKey1_TOOS
DECRYPTION BY CERTIFICATE CERT1_TOOS;
SELECT
CustomerID,
etc...
Hooray...
J
朱利安，
请点击以下链接，这可能会帮助您找到解决方案.
http://www.kodyaz.com/articles/sql-server-2005-database-encryption-step-by-step.aspx
Hi julian,
Plese follow the following link which may help you to find out the solution.
http://www.kodyaz.com/articles/sql-server-2005-database-encryption-step-by-step.aspx
这篇关于SQL Server-将存储过程与列加密一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
