Are you using “Always Encrypted? If your answer is “Yes” then you should read this

I have been fortunate enough to be able to work with latest technology but I can say it has not been an easy path.
As an example, If you have been using “Always Encrypted” on SQL Server, you may already know that it’s required to have SQL Server Management Studio (SSMS) 17.X to be able to insert into, update and filter data by values stored in encrypted columns from a Query Editor window, instead of having to create a custom application for that.
Once I installed this version and started using it, I was happy it worked well but I also noticed some strange behavior when dealing with stored procedures. One day, I’m working on a stored procedure that was created years ago by another developer which was not source controlled but by adding comments to the code( I know, this process sucks). I only needed to remove a filter from a select statement in the stored procedure. The change was made, code executed and stored procedure modified.
Results were as expected(Great!!) but I remembered I needed to add more comments to the code explaining the reason and why those changes happened on that day.
When I script the stored procedure…..
“Ahhhhhhh, What happened here? This does not look like my code.”
After extensive investigation and testing, I could reproduce the issue, as follow:

1) Let’s consider the following stored procedure already exists in the database:
USE [AdventureWorks2014]
GO
/****** Object:  StoredProcedure [dbo].[Proc01]   ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author:        Dan de Sousa
— Create date: 01/02/2017
— Description:    This is just a simple stored procedure
— =============================================
ALTER PROCEDURE [dbo].[Proc01]
— Add the parameters for the stored procedure here
@Valor01 varchar(50),
@BusinessEntityID INT OUTPUT
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;
    — Insert statements for procedure here
SELECT
TOP 1 [BusinessEntityID] as BusinessEntityID
FROM
[AdventureWorks2014].[Person].[Person]
END

2) Using the Always Encrypted parameterization feature, which is enabled by:

3) With the feature enabled, the stored procedure is then modified to:
USE [AdventureWorks2014]
GO
/****** Object:  StoredProcedure [dbo].[Proc01]   ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author:        Dan de Sousa
— Create date: 01/02/2017
— Description:    This is just a simple stored procedure
— Revision
— 001    DS        This is just a normal comment for testing
— =============================================
ALTER PROCEDURE [dbo].[Proc01]
— Add the parameters for the stored procedure here
@Valor01 varchar(50),
@BusinessEntityID INT OUTPUT
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;
–This is my first comment before the SELECT
— Insert statements for procedure here
SELECT
TOP 1 [BusinessEntityID] as BusinessEntityID
FROM
[AdventureWorks2014].[Person].[Person]
–This is my last comment after the SELECT
END

4) If I right click the stored procedure and then select “Modify”, the code becomes:
USE [AdventureWorks2014]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Proc01]
@Valor01 varchar(50),
@BusinessEntityID INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
    SELECT TOP 1 [BusinessEntityID] as BusinessEntityID
FROM [AdventureWorks2014].[Person].[Person]
END
Note that all comments were removed as well as any formatting including indentation.
My recommendation is that if you are using Always Encrypted, you make sure that the parameterization feature is only enabled when you are using the feature. Whenever you do anything else, I would highly recommend to deactivate the feature to avoid any unexpected behaviour/issues.
I hope this was useful.

 


This bug has been logged with Microsoft and if you are facing the same issue, you can help prioritize it here:
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s