Cannot add a stored procedure to database due to encryption message

Question

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.

For example, this

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:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'PROCEDURE'.

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'PROCEDURE'.

Msg 8180, Level 16, State 1, Procedure sp_describe_parameter_encryption, Line 1 [Batch Start Line 0]
Statement(s) could not be prepared.

Msg 8180, Level 16, State 1, Procedure sp_describe_parameter_encryption, Line 1 [Batch Start Line 0]
Statement(s) could not be prepared.

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.

I also notice that there is a blue line underneath the first SQL Statement in the batch that says

@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?

@GuidoG No, that is not true and, even if it were, it would cause a different error message. — Aaron Bertrand
Tools > Options > Query Execution > SQL Server > Advanced > Enable Parameterization for Always Encrypted. Is this checked? Try unchecking it (at least temporarily). — Aaron Bertrand

Joe Joe · Accepted Answer · 2018-08-27T19:36:27

It might be caused by Always Encrypted feature.

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.

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.

Cannot add a stored procedure to database due to encryption message

2 Answers