由于加密消息，无法将存储过程添加到数据库 [英] Cannot add a stored procedure to database due to encryption message
问题描述
我设置了一个本地数据库(SQL Server 2017 Express).一切正常，但是在 SSMS 中创建一个简单的存储过程时我也会出错.
I set up a local database (SQL Server 2017 Express). Everything works fine, but I'm getting errors when creating even a simple stored procedure in SSMS.
比如这个
CREATE PROCEDURE [dbo].[EMS_Operations_SyncAssetTableByID2]
@Table_Name VARCHAR(255),
@Ids_For_Update VARCHAR(255),
@Is_Test BIT = 0
AS
BEGIN
DECLARE @DB_String varchar(55) ='Redesign'
END
不会运行，我收到错误消息:
Will not run, and I get the error message:
消息 156，级别 15，状态 1，第 1 行
关键字PROCEDURE"附近的语法不正确.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'PROCEDURE'.
消息 156，级别 15，状态 1，第 1 行
关键字PROCEDURE"附近的语法不正确.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'PROCEDURE'.
消息 8180，级别 16，状态 1，过程 sp_describe_parameter_encryption，第 1 行 [批处理起始行 0]
无法准备报表.
Msg 8180, Level 16, State 1, Procedure sp_describe_parameter_encryption, Line 1 [Batch Start Line 0]
Statement(s) could not be prepared.
消息 8180，级别 16，状态 1，过程 sp_describe_parameter_encryption，第 1 行 [批处理起始行 0]
无法准备报表.
Msg 8180, Level 16, State 1, Procedure sp_describe_parameter_encryption, Line 1 [Batch Start Line 0]
Statement(s) could not be prepared.
执行批处理时出错.错误信息是:内部错误.sp_describe_parameter_encryption 返回的结果集格式无效.结果集之一丢失.
An error occurred while executing batch. Error message is: Internal error. The format of the resultset returned by sp_describe_parameter_encryption is invalid. One of the resultsets is missing.
我不确定是什么导致了这种情况，或者数据库中是否存在损坏，因为数据库中有加密，而且我不确定它在调用什么.
I'm not sure what is causing this, or if there is corruption in the database, as there is encryption in the database, and I'm not sure what it's calling.
我还注意到批处理中的第一个 SQL 语句下面有一条蓝线表示
I also notice that there is a blue line underneath the first SQL Statement in the batch that says
@DB_String 将被转换为具有以下属性的 System.Data.SqlClient.SqlParameter:SqlDbType = varchar,Size 55, Precision=0
@DB_String will be converted into a System.Data.SqlClient.SqlParameter with the following properties: SqlDbType = varchar,Size 55, Precision=0
当我在远程服务器上运行相同的代码时，它可以毫无问题地完成.知道是什么原因造成的吗?
When I run the same code on our remote server, it completes without issue. Any idea what could be causing this?
推荐答案
这可能是由 Always Encrypted 功能引起的.
It might be caused by Always Encrypted feature.
如果您不打算使用参数化.您可以在声明变量后手动设置所有变量.
例如:
DECLARE @DB_String varchar(55)
SET @DB_String ='Redesign'
If you are not intend to use parameterization. You can either set all variables manually after you declare them.
For example:
DECLARE @DB_String varchar(55)
SET @DB_String ='Redesign'
或者，您可以右键单击查询->连接->更改连接->选项>>定位到[附加连接参数]页面->在该框区域中，输入:
列加密设置=已禁用
这也将禁用参数化.
Or, you can right click query->Connection->Change Connection->Options>>locate to [Additional Connection Parameters] page->in that box area, put:
Column Encryption Setting = Disabled
That will also disable the parameterization.
如果您确实需要参数化来加密这些变量，那么目前看来您只能在查询级别执行此操作.存储过程不适用于 Always Encrypted，因为它可以被其他设置不同列加密设置的用户调用.
And if you do need the parameterization to encrypted those variables, it seems you can only do it in query level currently. The Stored Procedure does not work well with Always Encrypted, since it can be called by other user which has Column Encryption Setting been set differently.
这篇关于由于加密消息，无法将存储过程添加到数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
