26018

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:

<blockquote>

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

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

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

Msg 8180, Level 16, State 1, Procedure sp_describe_parameter_encryption, Line 1 [Batch Start Line 0]<br /> 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.

</blockquote>

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

<blockquote>

@DB_String will be converted into a System.Data.SqlClient.SqlParameter with the following properties: SqlDbType = varchar,Size 55, Precision=0

</blockquote>

When I run the same code on our remote server, it completes without issue. Any idea what could be causing this?

Answer1:

The comment that Aaron Betrand posted above

'Tools > Options > Query Execution > SQL Server > Advanced > Enable Parameterization for Always Encrypted. Is this checked.'

solved the issue. I had been experimenting with Always Encrypted previously, and had removed it, but this setting was still on, and parameterizing the procedure, which caused this.

Answer2:

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.<br />For example:<br />DECLARE @DB_String varchar(55)<br />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:<br />Column Encryption Setting = Disabled<br />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.

Recommend

  • react-native componentWillUnmount not working while navigating
  • Can't connect to Mongo DB via Spark
  • Swift: How to programatically advance a UIPageViewController that is inside a Container View?
  • How to avoid creating multiple string objects in java?
  • Setting the AWS region programmatically
  • R ggmap Error: object 'f' not found
  • How to test sub-domains on my localhost on a mac?
  • Can I tell NDepend to ignore a single result?
  • check phone settings for haptic feedback
  • Updating DIV with XML content on hover
  • Exception thrown: read access violation. **dynamicArray** was 0x1118235. occurred
  • how to solve access denied when using UWP GetFolderFromPathAsync
  • Why unsigned int stills signed? [duplicate]
  • How to add multiple events for “input” in post messages
  • HTML Gui designer + code generator
  • spring-data-redis Jackson serialization
  • Python Django ImportError: No module named website
  • summarize data from csv using R
  • Bootstrap multi select grouping dynamicaly using array
  • ggplot2 - facet labels with special characters/symbols and actual values?
  • Missing App Store icon codename one build
  • JavaFX resize children relative to parent
  • iOS Today Extension created as .app rather than .appex
  • WSO2 Identity Server + Rest STS Client (without ESB)
  • using vtkTimerCallback with QVTKRenderWindowInteractor not working
  • Reader monad - how does it conform to Monad interface?
  • Keep rows with certain values always at the bottom while sorting in jquery tablesorter plugin
  • Wireshark Display Filter for Unique Source/Destination IP and Protocol
  • C++ STL stack pop operation giving segmentation fault
  • Drag and drop unicode TText in DelphiXe4
  • Comma decimal separator is ignored by ASP.NET MVC model binder
  • ARKit code issue {unknown error -1=ffffffffffffffff error: Task failed with exit 1}