Posts Tagged ‘InterOp’

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...


 

Creating Excel Interactive View July 24th, 2014

Vinod Kumar

I have been wanting to write on this topic for ages but seem to have missed out for one reason or the other. How many times in your life seen a web page with a bunch of tables and it is so boring to read them? The numbers or tables sometimes might have sorting capability but lacks a striking visualization to say the least. So I am going to borrow a table from a Wikipedia page about Indian Population. There are a number of tables and the table of interest to be in Literacy rate. So the rough table looks like:

State/UT Code India/State/UT Literate Persons (%) Males (%) Females (%)
01 Jammu and Kashmir 86.61 87.26 85.23+-
02 Himachal Pradesh 83.78 90.83 76.60
03 Punjab 76.6 81.48 71.34
04 Chandigarh 86.43 90.54 81.38

Well, this is as boring as it can ever get even when pasted as-is on this blog. Now here is the trick we are going to do called as Excel Interactive View. As the name suggests, we are going to use the power of Excel to make this mundane table into some fancy charts for analysis. This includes a couple of scripts that needs to be added as part of the HTML Table and we are done. It is really as simple as that. So let me add the complete table with the script added. Just click on the button provided above to see the magic:

[excel-interactive-view title=”Literacy Rate of India” style=”Standard” filename=”Literacy” attribution=”Wikipedia Data”]

State/UT Code India/State/UT Literate Persons (%) Males (%) Females (%)
01 Jammu and Kashmir 86.61 87.26 85.23+-
02 Himachal Pradesh 83.78 90.83 76.60
03 Punjab 76.6 81.48 71.34
04 Chandigarh 86.43 90.54 81.38
05 Uttarakhand 79.63 88.33 70.70
06 Haryana 76.64 85.38 66.77
07 Delhi 86.34 91.03 80.93
08 Rajasthan 67.06 80.51 52.66
09 Uttar Pradesh 69.72 79.24 59.26
10 Bihar 63.82 73.39 53.33
11 Sikkim 82.20 87.29 76.43
12 Arunachal Pradesh 66.95 73.69 59.57
13 Nagaland 80.11 83.29 76.69
14 Manipur 79.85 86.49 73.17
15 Mizoram 91.58 93.72 89.40
16 Tripura 87.75 92.18 83.15
17 Meghalaya 75.48 77.17 73.78
18 Assam 73.18 78.81 67.27
19 West Bengal 77.08 82.67 71.16
20 Jharkhand 67.63 78.45 56.21
21 Odisha 72.9 82.40 64.36
22 Chhattisgarh 71.04 81.45 60.59
23 Madhya Pradesh 70.63 80.53 60.02
24 Gujarat 79.31 87.23 70.73
25 Daman and Diu 87.07 91.48 79.59
26 Dadra and Nagar Haveli 77.65 86.46 65.93
27 Maharashtra 83.2 89.82 75.48
28 Andhra Pradesh 67.66 75.56 59.74
29 Karnataka 75.60 82.85 68.13
30 Goa 87.40 92.81 81.84
31 Lakshadweep 92.28 96.11 88.25
32 Kerala 93.91 96.02 91.98
33 Tamil Nadu 80.33 86.81 73.86
34 Puducherry 86.55 92.12 81.22
35 Andaman and Nicobar Islands 86.27 90.11 81.84

So how cool is this Excel visualisation? I am sure you will want to build or use this capability in your webpages or internal sites in your organizations too. I hope you learnt something really interesting.

If you want to learn more about using this feature in your dataset and web pages, well read the documentation from Excel Interactive View.

PS: the data comes from Wikipedia and I have just used a snapshot to show the same. So please dont read too much into the data etc, look at the Excel view capabilities.

Continue reading...


 

SQL Server: Columnstore Index on Temp Tables June 20th, 2014

Vinod Kumar

Recently I was talking to a customer during performance testing and they had an interesting twist to using Columnstore Index. Since they were on SQL Server 2012, I had suggested them to use Non-Clustered Columnstore index for their workload. He started to quiz me with a number of questions and I thought it would be appropriate to point them to some resources I had already written. So I took the liberty to send them the two articles:

  1. SQL Server 2012: ColumnStore Characteristics
  2. SQL Server 2012: ColumnStore Part II

The fundamentals and concepts have not changed but where worth a mention and read. Having said that, the counter question after about an hour of their research and reading the contents. “Will it work with Temp Tables? We are doing a reporting workload and are using Temp tables extensively.”

I thought, wow, interestingly I have not written on it till date and is worth a mention though. The easy answer is “YES”, it is quite possible. Why didnt you try was my answer :)

Columnstore Index on Temp Tables

To drive home the concept, in the below example I have created a simple temp table called as #Temp_Account. When I query the table, the execution plan shows as “RowStore” under storage as shown below.

The next logical step is to create our Non-Clustered Columnstore Index. I am using the standard syntax as shown below.

If we try to insert into this temp table, it is still readonly because non-clustered columnstore index has been defined in the table.

Msg 35330, Level 15, State 1, Line 19
INSERT statement failed because data cannot be updated in a table that has a nonclustered columnstore index. Consider disabling the columnstore index before issuing the INSERT statement, and then rebuilding the columnstore index after INSERT has completed.

After index creation, if we make a simple query we can see the change in Execution Plan. The storage is now showing as “ColumnStore” as shown in the execution plan below.

The above query is simple in nature and hence the execution mode is “Row”. In future posts I will show how this can be changed to Batch mode.

Continue reading...


 

Installing SQL Server 2012 Analysis Services–Tabular mode June 6th, 2013

Vinod Kumar

I wrote last week about DAX (Introduction to DAX in Excel) and we did mention there are many more blog posts on its way on that topic in future. I had a few requests from people on how to install SQL Server 2012 Analysis Services Tabular Mode. This is a picture blog post in a way showing the Step-By-Step process.

Q: The reason I wrote this is because a customer asked me with SQL Server 2012 Analysis Services already installed in their environment, how can they convert it into a tabular mode.

A: The matter of fact remains, we cannot convert the already installed multi-dimensional SSAS instance into tabular mode. We need to install a separate instance for this.

Installation Steps – Tabular mode

So if you have the SQL Server 2012 installation binaries ready, start the “setup.exe”. And go through the wizard as follows:

Select “New SQL Server stand-alone installation or add features to an existing installation” option.

Let the Setup Support Rules run and Click “OK”.

Ignore the Warning for “Windows Firewall” for now and press “Next”.

Select “Perform a new installation of SQL Server 2012”. Here is assumption is we already have an SSAS multi-dimension installation. If this is a fresh installation of SSAS, you can choose the second option.

If you have a valid Product Key, enter the same here or feel free to use an Evaluation version. Also go through the License Term and move next to “Setup Role”.

Under Setup Role, select “SQL Server Feature Installation”.

Select “Analysis Services” under the Feature Selection.

Press “Next” after checking the status in “Installation Rules”.

Under Instance Configuration, Give a “Named instance” – here we are calling it as POWERPIVOT. Our assumption is our default instance is the multi-dimensional SSAS instance. Press “Next”.

Check the Disk Space requirements and Server Configuration details and Click “Next”.

Under “Analysis Services Configuration” screen, select the “Tabular Mode”. This is the most important step. Also make sure to add the current user from the below button. Press “Next” to move ahead in installation.

Check the “Installation Configuration Rules” pass and you will be in “Ready to Install” screen as above. Please take a note of the “Configuration file path” and feel free to check the ConfigurationFile.ini if you want to use it in future for unattended installation. At this step, we are all set and ready to install. Click the “Install” button.

You will be presented with the final screen as above post install. This also has the Summary log file for installation. If any of your installation errors out, the details are present in this txt file mentioned.

This completes our installation of SQL Server 2012 Analysis Services Tabular Mode. In future blog posts we will write queries to this instance and show the functionality possible with this mode of installation.

Continue reading...


 

SQL Server 2012: ColumnStore Part II June 5th, 2013

Vinod Kumar

I wrote about ColumnStore Indexes before and those were around the basic restrictions and things to consider while working with ColumnStore Index with SQL Server 2012. In this blog post let me take a moment to look at the boundaries, use with SSMS and other diagnostics information. Obviously some of the restrictions have been relaxed for next version of SQL Server 2014. That will be for a different blog post.

Datatype restrictions

In SQL Server 2012, columnstore index creation is supported only for the following data types:

  1. char, varchar, nchar and nvarchar (except varchar(max) and nvarchar(max))
  2. decimal (and numeric)
  3. int, bigint, smallint, and tinyint
  4. float, real
  5. bit
  6. money and smallmoney

New DDL

There is now a new keyword COLUMNSTORE in the CREATE INDEX DDL which is required to create a columnstore index. More about it can be read from MSDN.

CREATE COLUMNSTORE INDEX <Name> ON <tableName>(<Cols>)

Index hint

We can force an index hint to use the columnstore index in a query if required:

… FROM mytable WITH (INDEX (mycsindex)) …

Query hint

We can either use a table hint to force the use of a different index or we can use a new query hint: IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX (MSDN). This new hint will prevent the use of any nonclustered columnstore indexes in the query. Below is an example of using the hint to prevent use of any nonclustered columnstore index in a query:

SELECT DISTINCT (SalesTerritoryKey)
FROM dbo.FactResellerSalesCopy
OPTION (IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX);

CATALOG VIEWS and DMVs 

There are a number catalog views which will show different values for ColumnStore. I am listing a few here:

sys.indexes:

type : 6

type_desc: NONCLUSTERED COLUMNSTORE

sys.partitions:

data_compression: 3

data_compression_desc: COLUMNSTORE

sys.index_columns:

key_ordinal: 0 (not a key column; columnstore index does not have a search key like a row-based index)
is_descending_key: 0 (default value; not a key column and index is not "sorted" like a row-based index)
is_included_column: 1 for every column mentioned in the create columnstore index ddl.

sys.dm_db_index_operational_stats:

Columns
nonleaf_insert_count,
nonleaf_delete_count,
nonleaf_update_count,
nonleaf_allocation_count,
nonleaf_page_merge_count,
tree_page_latch_wait_count,
tree_page_latch_wait_in_ms,
tree_page_io_latch_wait_count,
tree_page_io_latch_wait_in_ms,
Leaf_page_merge_count,
page_compression_attempt_count,
page_compression_success_count – Has a value of 0 for columnstore index.

Showplan Enhancement

Three additional properties have been added to the showplan data for columnstore indexes:

  1. Storage – row or columnstore
  2. EstimatedExecutionMode : row or batch
  3. ActualExecutionMode : row or batch

SSMS Enhancements for Columnstore

I am outlining some of the visible enhancements for columnstore with SQL Server 2012 Management Studio. Firstly, Columnstore indexes are represented by a different icon than traditional row based indexes.

image

The next UI changes is for creating a New Columnstore Index.

image

The third part is around adding columnstore columns as we create the index.

image

Final Words

These are some of the basic enhancements with SQL Server 2012 I thought was worth sharing. Do let me know if you have used columnstore index in your production environments and what are the scenarios you found using this type of index useful.

Continue reading...