Featured

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

T-SQL Tuesday: People who have made a difference

T-SQL-Tuesday-Logo

First of all, I’d like to thank Ewald Cress (blog | twitter) for hosting this month’s T-SQL Tuesday and for selecting the topic “Folks Who Have Made a Difference”, great choice.

Where do I start? Hmmm, let’s do it from the beginning.

Firstly, I would like to thank Mr Evandro who was my first database teacher back in 1994. I remember we, students, did not want to think about databases at the time because VB and C programming was like a “fever” in Brazil, so everybody wanted to do it. But he, on his own way and time, selected a few of us to coach as he could see a good future on us as we were able to learn and pick up things very quickly. He taught us how learning both programming and databases very well would have a huge impact in our future. And he was right, I learnt dBase III Plus, foxPro and also SQL Server 6.5 with him, never stopped and currently use SQL 2016. So Thank you Evandro.

Another person to mention is Diego Nogare, who I met a few years ago at PASS Summit. We exchanged a lot of ideas during the event and he instigated me to start blogging and speaking about data. I did start speaking(virtually) by doing an online presentation of a topic that I was working on at the time and knew about, with his guidance in developing the topic and brainstorming ideas. Nowadays we are good friends. So Thank you Nogare.

One other person to remember is the guy who “made” me start speaking in public(not virtually anymore) who is Martin Cairney, who organises SQL Server User Group in Melbourne(SQL Social) as well as SQL Saturday Melbourne. I firstly spoke about Service Broker at the user group and boy I was scared, being the first time. But then based on the feedback received from that session, I could work on some points and felt more confident when preparing my talk for SQL Saturday Melbourne. I ended up speaking on the same year at SQL Saturday Sydney, SQL Saturday Brisbane and SQL Saturday Oregon. At the same time, he inspired me to start volunteering, on top of the speaking, on those events as well. So Thank you Martin.

Last, but not least, I would like to thank Rob Farley, David Lean, Hamish WatsonMartin Catherall, Warwick Rudd, Steve Jones and Randolph West who in the last couple years have been helping me a lot, being by developing ideas for career improvements, bouncing ideas about sessions to present on SQL Saturdays and virtual groups or just to have a talk (it’s good to have someone to talk to). What’s more, they have been helping me develop myself as a person as well as a professional with their advice. So Thank You guys.

I much appreciate everything that you all have done and I know that I would have not reached this far in my life and career without your help and guidance.

SO THANK YOU ALL FROM THE BOTTOM OF MY HEART.

Blogging: My first post

This is my first blog post.

I have been trying to start this blog for the last couple years but because of other priorities and time constraints, I have not been able to do so.

From this week, after returning from PASS Summit 2017, I am committing some time every week and will try to write at least one post per week.

The posts will cover multiple subjects related to SQL Server including SQL Server, Reporting Services, Integration Services, Analysis Services, Containers, Linux and some other…

I hope you enjoy and any comment is much appreciated.