Posts Tagged ‘Scripts’

Basic Column Encryption of data with SQL Server August 6th, 2014

Vinod Kumar

The more I talk with customers on basic architecture, more are the questions about how to implement. These scenarios when talked look great on whiteboard and when they implement and come back (say after 6 months) is completely different. These are some challenges when working with customer developer teams who has just started their career in writing DB level code.

One such scenario I talk to customers who have requirements of security is around column level encryption. It is one of the most simplest implementation and yet difficult to visualize. The scenario is simple where-in most of the HRMS (Hospital Management Systems) come with a simple requirement that data of one person must be masked to other.

So in this post, I thought it will be worth take a step-by-step tutorials to what I am talking. These capabilities are with SQL Server since the 2005 version and can be used by anyone. I am not talking about the infra best practices or the deployment strategy yet, that will be for a future post.

Creating Users for Demo

— Creating the Logins for demo
CREATE LOGIN doctor1 WITH password = ‘MyComplexPass@w0rd’
go
CREATE LOGIN doctor2 WITH password = ‘MyComplexPass@w0rd’
go
CREATE DATABASE hospitaldb
go
USE hospitaldb
go
CREATE USER doctor1
go
CREATE USER doctor2
go

For our example we have two doctors, we want to build a mechanism where Doctor1 patients details must not be visible to Doctor2. Let us create our simple table to keep values.

— Create tables
CREATE TABLE patientdata
(
id         INT,
name       NVARCHAR(30),
doctorname VARCHAR(25),
uid        VARBINARY(1000),
symptom    VARBINARY(4000)
)
go
— Grant access to the table to both doctors
GRANT SELECT, INSERT ON patientdata TO doctor1;
GRANT SELECT, INSERT ON patientdata TO doctor2;

Basic Encryption steps

Next step is to create our keys. To start with, we need to create our Master Key first. Then we will create the Certificates we will use. In this example, I am using a Symmetric key which will be encrypted by the certificates as part of logic.

— Create the Master Key
CREATE master KEY encryption BY password = ‘HospitalDBpwd@123’

CREATE CERTIFICATE doctor1cert AUTHORIZATION doctor1 WITH subject =
‘Doctor1cert’, start_date = ’07/07/2014′ 
GO
CREATE CERTIFICATE doctor2cert AUTHORIZATION doctor2 WITH subject =
‘Doctor2cert’, start_date = ’07/07/2014′ 
GO

CREATE symmetric KEY doctor1key AUTHORIZATION doctor1 WITH algorithm =
triple_des encryption BY certificate doctor1cert 
GO
CREATE symmetric KEY doctor2key AUTHORIZATION doctor2 WITH algorithm =
triple_des encryption BY certificate doctor2cert 
GO 

Let us next look at the Keys we just created using the DMVs.

SELECT *
FROM   sys.symmetric_keys 

A typical output looks like:

Adding Data into table

Next is to simulate as two different users and enter some data into our tables. Let us first impersonate as Doctor1 and enter values, next we will do it for Doctor2.

EXECUTE AS LOGIN = ‘Doctor1’

OPEN SYMMETRIC KEY doctor1key DESCRIPTION BY CERTIFICATE doctor1cert 

— View the list of open keys in the session
SELECT *
FROM   sys.openkeys 

Insert into our table.

INSERT INTO patientdata
VALUES      (1,
‘Jack’,
‘Doctor1’,
Encryptbykey(Key_guid(‘Doctor1Key’), ‘1111111111’),
Encryptbykey(Key_guid(‘Doctor1Key’), ‘Cut’))

INSERT INTO patientdata
VALUES      (2,
‘Jill’,
‘Doctor1’,
Encryptbykey(Key_guid(‘Doctor1Key’), ‘2222222222’),
Encryptbykey(Key_guid(‘Doctor1Key’), ‘Bruise’))

INSERT INTO patientdata
VALUES      (3,
‘Jim’,
‘Doctor1’,
Encryptbykey(Key_guid(‘Doctor1Key’), ‘3333333333’),
Encryptbykey(Key_guid(‘Doctor1Key’), ‘Head ache’))

In this example the Doc1 has 3 records for him. Next is to revert back to Doctor2 and do the same set of operations.

— Close all opened keys
CLOSE ALL symmetric keys

REVERT 

Impersonate as Doctor2 and do the same steps.

EXECUTE AS login = ‘Doctor2’

OPEN symmetric KEY doctor2key decryption BY certificate doctor2cert

–view the list of open keys in the session
SELECT *
FROM   sys.openkeys

INSERT INTO patientdata
VALUES      (4,
‘Rick’,
‘Doctor2’,
Encryptbykey(Key_guid(‘Doctor2Key’), ‘4444444444’),
Encryptbykey(Key_guid(‘Doctor2Key’), ‘Cough’))

INSERT INTO patientdata
VALUES      (5,
‘Joe’,
‘Doctor2’,
Encryptbykey(Key_guid(‘Doctor2Key’), ‘5555555555’),
Encryptbykey(Key_guid(‘Doctor2Key’), ‘Asthma’))

INSERT INTO patientdata
VALUES      (6,
‘Pro’,
‘Doctor2’,
Encryptbykey(Key_guid(‘Doctor2Key’), ‘6666666666’),
Encryptbykey(Key_guid(‘Doctor2Key’), ‘Cold’))

CLOSE ALL symmetric keys

— View the list of open keys in the session
SELECT *
FROM   sys.openkeys

REVERT

Check on the data

let us do a simple select on the table to check how the values are stored.

— Select data and see values encrypted
SELECT *
FROM   patientdata 

As you can see the values are not visible as-is but has some garbage.

Impersonate as Doctor and show Values

The next step is to show that Doctor1 can see his data and Doctor2 can see his data alone. The steps are simple:

EXECUTE AS LOGIN = ‘Doctor1’

OPEN SYMMETRIC KEY doctor1key decryption DESCRIPTION BY CERTIFICATE doctor1cert

SELECT id,
name,
doctorname,
CONVERT(VARCHAR, Decryptbykey(uid))      AS UID,
CONVERT (VARCHAR, Decryptbykey(symptom)) AS Symptom
FROM   patientdata

CLOSE ALL SYMMETRIC keys

REVERT 

The output would be like:

Now let us impersonate as Doctor2 and check for values.

EXECUTE AS LOGIN = ‘Doctor2’

OPEN SYMMETRIC KEY doctor2key decryption DESCRIPTION BY CERTIFICATE doctor2cert

SELECT id,
name,
doctorname,
CONVERT(VARCHAR, Decryptbykey(uid))      AS UID,
CONVERT (VARCHAR, Decryptbykey(symptom)) AS Symptom
FROM   patientdata

CLOSE ALL SYMMETRIC keys

REVERT 

The output for this stage would be:

Conclusion

As you can see this is a very simple implementation of column level encryption inside SQL Server and can be quite effective to mask data from each others in a multi-tenant environment. There are a number of reasons one can go for this solution. I thought this will be worth a shout even though the implementation has been in industry for close to a decade now.

Continue reading...


 

Excel Tip: Month Name Sorting in Excel July 14th, 2014

Vinod Kumar

In my previous article Excel Tip: Month Name Sorting with PowerPivot / PowerView, I wrote about sorting of month name inside PowerPivot. I got a number of ping about the same functionality inside standard Excel sheets. I thought this would be the easiest and known to many – but to my surprise, not all know about the capability of Excel. So in this post, let me take you through the same process for Excel tables.

So let me prep you with the data first. I have two columns Month Name and Sales. When I try to sort by the Month Name, you can see how the sorting happens for A-Z and for Z-A. It is nowhere near to what one would expect to sort as month.

That bring us to an interesting option. It has been there all along, select the “Sort by Color” –> “Custom Sort…”.

This comes up with a small dialog which I am sure most of you would have used. There right at the order dropdown, is a hidden gem called as “Custom List…”. Select this to see the magic.

This bring a standard set of list or feel free to define your own New List for sorting and build the table.

Click on “OK” and see. This is awesome because now the list is sorted automatically back in your table. Now if we sort A-Z and Z-A to see the difference. So how easy and cool is it? Have you ever used this option before, let me know.

Continue reading...


 

Excel Tip: Month Name Sorting with PowerPivot / PowerView June 25th, 2014

Vinod Kumar

I do a lot of presentation on Excel related topics to customers as part of my BI conversations at MTC, Bangalore. Recently one of the developer when looking at the PowerView demo said, he had an unique problem. He said that the PowerView was not ordering the month names in the correct order of Jan, Feb, Mar and so on but was sorting based on Alphabets. I repeatedly asked him if they had enabled the proper sort order. He was little clueless and wanted to see it in action. Here is the 30 seconds demo that I showed how this can be achieved.

In our PowerPivot dataset we have the following. It is a list of MonthNames and sales marked against them.

When I use this dataset and create a PowerView report, I select MonthName and Sales. This comes up with an report like this irrespective of the sort I do. This is the problem that the customer was mentioning.

The solution is simple. Get back to PowerPivot Tab, under Home –> Sort by Column –> Select “Sort by Column”.

We will be presented by the following dialog. Here I am mentioning, whenever the “Month Name” column is selected, use the “Month” integer column to sort behind the scenes. That is it !!

One the above step is complete, our PowerView will show a dialog of change in Data Model, select OK.

And the change can be seen now. The months seems to be in correct order.

Have you encountered this requirement in your report generation anytime? Do let me know.

Continue reading...


 

Identify SQL Server 2014 Cardinality Estimator June 4th, 2014

Vinod Kumar

SQL Server 2014 released a new cardinality estimator (CE) and more can be learnt from the MSDN documentation on the specifics. In this post I just wanted to call out how you can quickly identify if we are using the old estimator or the new CE. This information stays inside the execution plans or the properties pane.

Recently one of the customers did see me talking about this and complained that his queries run on Adventureworks were never using the new CE and he had to always use a trace flag. This got me interested and I will tell you what I found as part of this learning.

The steps:

  1. Create some random query in samples DB or a DB of your choice. I used the AdventureworksDB for my experiment.
  2. Enable Actual Execution Plan.
  3. Execute the query, and wait for the Execution Plan Tab to appear.
  4. On the Execution Plan tab, right click select “Show Execution Plan XML …”

This now opens up the XML Plan for the query under question. And right at the top we will find the element which says “CardinalityEstimatorModelVersion”, if this is 70 then we are using the old CE and if this reads 120 then we are using the new CE.

We can also find this information in the Properties Pane (F4 shortcut). This is shown in the diagram below.

It was baffling to me why this was always showing as 70 for my environment. So the first place for me to check was the compatibility of the Database. It was at 110 (SQL Server 2012), I changed the same to 120 (SQL Server 2014) and I reran the same query.

As you can see, SQL Server has picked up the new CE and has evaluated the same. There is so much to learn about the new CE that I will reserve it for future posts.

Continue reading...


 

Checksum on Backup and Restores May 28th, 2014

Vinod Kumar

I am a firm believer of using new features that enhance productivity and life of a DBA. In that category is the feature of CHECKSUM. I have written a number of article on them in the past and are worth a mention:

  1. Torn Page Vs CHECKSUM
  2. CHECKSUM and SQL Server
  3. CHECKSUM and TempDB
  4. CHECKSUM Vs FileStream
  5. SQL Server 2014: Checksum with backups

SQL Server BACKUP and RESTORE statements provide the CHECKSUM option to include checksum protection on the backup stream and trigger the matching validation operations during restore. To achieve a checksum-enabled backup, the BACKUP command must include the CHECKSUM option. Read more syntax of CHECKSUMS from MSDN.

Backup and restore operations that use checksum capabilities increase data integrity protection and also increase CPU usage requirements at the point of backup. A backup or restore with the checksum option requires that each byte be checked as it is streamed, thereby increasing CPU usage. The checksum that is used for backup and restore uses the same algorithm to calculate the checksum value for the backup media as is used for data pages and log blocks.

The following rules apply to the BACKUP and RESTORE command CHECKSUM operations:

  1. By default, SQL Server BACKUP and RESTORE operations maintain backward compatibility (NO_CHECKSUM is the default). This needs to be specified explicitly.
  2. The database’s PAGE_VERIFY setting has no effect on backup and restore operations; only the CHECKSUM setting on the backup or restore command is relevant.
  3. The backup and restore checksum is a single value representing the checksum of the complete stream; it does not represent individual pages or log blocks located in the backup stream. The value is calculated during the backup and stored with the backup. The value is recalculated during the restore and checked against the stored value.
  4. Backup with the CHECKSUM option will not change the pages as it saves them to the backup media; a page’s protection state (NONE, CHECKSUM, or TORN) is maintained as read from the database file. If a checksum was already stored on the data page, it is verified before the page is written to the backup stream.
  5. Restore and Verify commands can be used to validate the CHECKSUM if the backup was created by using the CHECKSUM option. Trying to restore with the CHECKSUM option on a backup without a checksum returns an error as shown below.

Msg 3187, Level 16, State 1, Line 4
RESTORE WITH CHECKSUM cannot be specified because the backup set does not contain checksum information.

From an third-party utilities perspective and CHECKSUM, please read the KB-2656988.

Now that you got some of the fineprints with CHECKSUM and backups, I hope you will use the same in your environments in future atleast if you are not using.

Continue reading...