Symmetric Key (Encryption – Decryption in SQL Server 2008 Part 6)

Well this is sixth article in the series of Encryption and Decryption. It was really good to have good response from all of you about this series. I would like to give links about past 5 article in these series for those who directly landed to this page.

Ok, so above links are well enough, if you want to get the concept of encryption – decryption in Microsoft SQL Server 2008 from scratch, do read all articles given above too from part 1.
Anyway, let us move ahead with Symmetric key. This is at the very bottom level in hierarchy of Encryption-Decryption. Further you can refer hierarchy chart given in my first article.  Symmetric key can either use same key for encryption and decryption or use different key for encryption and decryption but do keep in mind that both the keys are mathematically related via simple transformation. Symmetric key could be encrypted by Certificates or via Asymmetric key as per your need.
Use AdventureWorks
GO
Create Table emps
(
      name varchar(20),
      dept varchar(20),
      EncryptedName Varbinary(256),
      DecryptedName Varchar(20)
)
go
Insert into emps (Name,Dept)
Select ‘Ritesh’,‘MIS’ union all
Select ‘Rajan’,‘Acct’
GO
Select * from emps
go
–create Database Master Key (DMK)
Create master key
Encryption by Password =‘$qlhub’
–create certificate which will be used to
–encrypt symmetric key
Create Certificate AdvCert
With Subject= ‘Certificate to encrypt emps table’,
Start_date = ‘2009-09-29’,
Expiry_date =‘2012-02-07’
GO
–creating symmetric key
Create Symmetric Key AdvSym
With Algorithm =AES_256
Encryption by Certificate AdvCert
GO
–once you create symmetric key
–you need to open it before use.
Open Symmetric key AdvSym
Decryption by certificate AdvCert
GO
Select Name,Dept,EncryptByKey(Key_Guid(N‘AdvSym’),Name) as EncryptedName from emps
go
–now this is time to update table
update emps set EncryptedName=EncryptByKey(Key_Guid(N‘AdvSym’),Name)
–let us check data
select * from emps
Select Name,Dept,EncryptByKey(Key_Guid(N‘AdvSym’),Name) as EncryptedName,
Convert(Varchar(20), DecryptByKey(EncryptedName)) as DecryptedName from emps
go
–now this is time to decrypt data and update table.
Update emps set DecryptedName=Convert(Varchar(20), DecryptByKey(EncryptedName))
–check your data again
select * from emps
go
So this is how Symmetric key works!!!
I want to add one more article in this series which will be complete practice script from top to bottom.
Hope to have it for you very soon.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Linked Server User in SQL Server 2008/2005 – MUST Read this

Microsoft has recently found memory leak issues in Linked Server query in SQL Server 2008 and SQL Server 2005. If you are using Linked server frequently than please do read this article to know what kind of problem could happens.
SQL Server 2008
  • ANY remote stored procedure execution will leak memory for each execution on the local server (the server where you initiated the remote procedure execution). The leak is not large for each execution (around 40 bytes) but over time this can add up. This specific problem involves the RETURN status of a stored procedure so there is no way to avoid it (even if you don’t use RETURN in your proc a return status is sent back to the client) if you execute remote stored procedures.
  • If you use sql_variant SQL Server data types you could also face a different memory leak (again on the local server only). These leaks can occur under the following conditions: 
    • If you use a sql_variant type for an OUTPUT parameter of a remote stored procedure you will leak around 40 bytes just for using a sql_variant OUTPUT parameter (the leak is per parameter). If the value of the parameter is of type char, varchar, nchar, nvarchar,binary, or varbinary, you will also leak the size of the value itself for each parameter. So a large character string over time could result in a considerable memory leak.
    • if you run a remote query that returns a result with a sql_variant column AND the value of the column is a char, varchar, nchar, nvarchar, binary, or varbinary value, you will leak the value of that variant for each row returned to the local server. This one has a potential to cause a fairly significant leak depending on how big the values are and how many rows are returned.
SQL Server 2005
  • You are only affected by the sql_variant problems listed above.
Please click here to read complete article on official Microsoft website.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder ofhttp://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Search latest modified SP and View with its text in SQL Server 2008/2005

Today I have seen interesting question in one forum that how can I find modified Stored Procedure and Views after certain date with its text. Question is really interesting but the answer is really very small T-SQL.
If you use Sys.Objects, you can get list of all available objects in your database. Sys.Object has much important information about that object along with “Modify_Date” but this system view doesn’t contain text of those objects.
Well, in this scenario Object_Defination method comes to rescue us. If you pass Object’s ID in Object_Defination method, it will return Text for that object. So now let me show you short T-SQL for this task.
select name,OBJECT_DEFINITION(object_id),modify_date from sys.objects
where [type] in (‘V’,‘p’)  and convert(varchar,modify_date,112)>‘20090601’



Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Find size of each table in database of SQL Server 2005/2008

Well, recently I have observed that data file of my database started growing like anything and I wonder which table(s) is causing this. Generally by looking at datafile you can’t assume that which table(s) is growing high so you need to check size of table and index. I decided to first look at the size of table, how can do that? Well there is one stored procedure in SQL Server which is really very handy in this scenario.
use adventureworks
go

–look at overall scenario about total size of table
–and index and database size etc.
EXEC sp_spaceused

–now let us look at the size of perticular table
EXEC sp_spaceused ‘Production.ProductProductPhoto’
Last T-SQL statement would give you total number of rows in table, reserved size, data size, index size and unused space. WOW, really quick and handy SP, isn’t it????
But this SP will give you information about just one table and what, if you want to look at the details about all tables in your database.
There are two ways to go for in this scenario.
1.)    Use following T-SQL which uses SP_MSForEachTable (undocumented SP), If you want to know more about this SP, click here to look at my past article.
exec sp_MSforeachtable @command1=‘print ”?” exec sp_spaceused ”?”’

2.)    Another way is really easy to go for, it’s a readymade graphical report provided by SQL Server itself. Just open up your SSMS, right click on database, Select Reports, click on Standard Reports and click on “Disk Usage by Top Tables”. There are so many other useful report too, which you can study and can use whenever you need it.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder ofhttp://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Difference between LEN and DataLength in SQL Server 2008

Recently I have seen that people face confusion between two very useful string functions of Microsoft SQL Server LEN and DATALENGTH. There is great and technical difference between these two functions. Basically LEN returns integer number by counting each character in given string after removing right side blanks whereas DATALENGTH will return number of byte required to store that expression.
Let me make your concept more clearly by giving you short example.
DECLARE @STR varchar(10)
SET @STR=‘RITESH ‘
SELECT @STR AS ‘original value’,LEN(@str) AS ‘Length’, DATALENGTH(@STR) AS ‘DataLength’

Original value will be my name with one blank space after name (right end), LEN will give you 6, it will just count character of my name and will remove right side blank whereas DATALENGTH  will give you 7 as it will return bytes which will be needed to store string variable @STR.
Hope it helps.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Array or Generic list pass to SQL Server stored procedure to get result of SELECT query

I many time see questions in few different forums that how can we pass Array or Generic list to SQL Server to get result of SELECT statement? Well this obvious question comes to C# or any front-end developer but unfortunately there is no concept of array or list in SQL Server. SQL Server works on set based but we can have work around of this situation. You can pass value separated by comma or any other separator and use it in your WHERE condition. I have written one user define function to SPLIT value which are separated by provided separator. You can use that function here. Click here to look at my user define function to separate value.
We can use that function in our stored procedure. Let us have a look at small code snippet about how to use that?
–create table
USE [adventureworks]
GO
CREATE TABLE [dbo].[emps](
[Name] [varchar](50),
[Dept] [varchar](10),
[Company] [varchar](15)
) ON [PRIMARY]
GO


–insert records
INSERT INTO emps
SELECT ‘RITESH’,‘MIS’,‘ECHEM’ UNION ALL
SELECT ‘Rajan’,‘acct’,‘mar’ UNION ALL
SELECT ‘Bihag’,‘MIS’,‘ct’
GO


–use split function in SP on comma separated value
CREATE PROC SearchName
@Value varchar(max),
@separator varchar(1)
as
select * from emps where name in (select data from dbo.SplitData(@Value,@separator))
GO


–let us get comma separated value and call our SP “SearchName”
EXEC SearchName ‘Ritesh,Rajan,ravi,ram’,‘,’
–you will get only two records from emps table,
–third record will be eliminated as name ‘Bihag’ was not
–included in above comma separated value

Happy SQL
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder ofhttp://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Asymmetric Key (Encryption – Decryption in SQL Server 2008 Part 5)

Asymmetric Key is a small brother of Certificate which I have explained in my previous article. Asymmetric Key is a combination of public key and private key which uses some different algorithms to encrypt and decrypt key. Algorithms are RSA_512, RSA_1024, RSA_2048. Details of these algorithms are beyond the scope of this article, I will write detailed article on these kind of algorithm very soon.
Now let us look small T-SQL practice for this.
–create database master key before creating Asymmetric key
–if it is not already exist
CREATE MASTER KEY ENCRYPTION BY PASSWORD=‘$qlhub’


–creaet asymmetric key with
–RSA_512 algorithm
CREATE ASYMMETRIC KEY AsymADV
WITH ALGORITHM = RSA_512
go


–let us encrypt and decrypt data with Asymmetric key
DECLARE @Text nvarchar(max)
DECLARE @TextEnrypt varbinary(128)
DECLARE @TextDecrypt nvarchar(max)
SET @Text=N’hi’–, this is first Assymetric test, created by Ritesh Shah’
SET @TextEnrypt=ENCRYPTBYASYMKEY(AsymKey_ID(N’AsymADV’),@Text)
SET @TextDecrypt=DECRYPTBYASYMKEY (AsymKey_ID(N’AsymADV’),@TextEnrypt)
SELECT @Text AS ‘ORIGINAL TEXT’,@TextEnrypt AS ‘Encrypted Text’,@TextDecrypt as ‘Decrypted Text’
GO


–drop Asymmetric key
DROP ASYMMETRIC KEY AsymADV
GO


–drop master key
drop master key



Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder ofhttp://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Certificate (Encryption – Decryption in SQL Server 2008 Part 4)

Well, we are moving one step forward for data encryption and decryption, I have already described some of the essential topics in this area which are mandatory before implementing this step. If you want to go backward, have a look at series of article on this topic at below given link.
Once you have SMK and DMK, you will have choice to go for Certificates, Asymmetric key, Symmetric key. Scope of this article is use of certificate. Let us start our journey.
Certificate is a kind of Asymmetric encryption with some additional metadata. In Asymmetric encryption data got encryption by two different methods but mathematically both are same. You can use “CREATE CERTIFICATE” T-SQL for creating new or use existing certificate.
–create new cerfiticate
CREATE CERTIFICATE ADV
ENCRYPTION BY PASSWORD =‘$qlhub’
WITH SUBJECT =‘ADVENTUREWORKS CERTIFICATE’,
START_DATE=’09/14/2009′, –IF NOT PROVIDED, CURRENT DATE IS START DATE
EXPIRY_DATE=’09/13/2015′— IF NOT PROVIDED, ONE YEAR AFTER START_DATE IS EXPIRY_DATE
go


I have observed that people are really very cautious about the data and used to take regular backup of data but not that much cautious for certificates. I HIGHLY recommend taking backup of your certificate as soon as you create it, so let us seeing the script of that.
BACKUP CERTIFICATE ADV
TO FILE = ‘d:\ADV.CER’
WITH PRIVATE KEY
(
      FILE=‘d:\ADV.PVK’,
      ENCRYPTION BY PASSWORD=‘$qlhub’,
      DECRYPTION BY PASSWORD=‘$qlhub’
)
go


Generally you think of restore command as soon as you finish backup. Let me tell you that there is no restore certificate command as you can restore your existing certificate from your backup file itself from CREATE CERTIFICATE command only. Have a look.
–LET US drop just created certificate and restore it from
–.cer file so that we are sure that certificate is created
–perfectly
DROP CERTIFICATE ADV
–restore from file
CREATE CERTIFICATE ADV
FROM FILE=‘D:\ADV.CER’


Once you create certificate and take a backup of it, now, you are ready to encrypt your data with certificate and decrypt it whenever it is needed.


DECLARE @Text nvarchar(max)
DECLARE @TextEnrypt varbinary(128)
DECLARE @TextDecrypt nvarchar(max)
SET @Text=N’hi, this is first certificate test, created by Ritesh Shah’
SET @TextEnrypt=ENCRYPTBYCERT(CERT_ID(‘ADV’),@Text)
SET @TextDecrypt=DECRYPTBYCERT(CERT_ID(‘ADV’),@TextEnrypt,N’$qlhub’)
SELECT @Text AS ‘ORIGINAL TEXT’,@TextEnrypt AS ‘Encrypted Text’,@TextDecrypt as ‘Decrypted Text’
GO

Hope you have enjoyed this small tour.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder ofhttp://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Find last Friday in SQL Server 2008

Microsoft SQL Server has very rich tools for date and time but just due to unawareness people find it difficult to make operation on datetime in SQL Server and used to go to front-end languages like .NET. I used to prefer SQL Server for performance issue. So far I have written quite a few articles on date time operation in SQL Server which is as follow.
If you will go through above given articles, you will come to know that datetime functions are really very handy and useful. Anyway, the main purposes to write this article to show you one another very small T-SQL for find out last Friday of the day.
SELECT DATEADD(day, (DATEDIFF (day, ‘19800104’, CURRENT_TIMESTAMP) / 7) * 7, ‘19800104’) as ‘Last Friday’



It was really very easy. Right?
Actually the logic behind this small query is, there was a Friday on 4th Jan 1980 so I am getting difference between 4th Jan 1980 to current date, dividing it with 7 than multiplying with 7 and whatever number comes, I am adding it to the same date and I will get last Friday.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder ofhttp://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Bug Fix – Msg 15118 Password validation failed

When you are dealing with Database Master Key  and provide encryption password, you have to give strong password there. It uses Windows Complexity Password policy if exist any. In you won’t provide password according to your Windows Complexity Password policy, it won’t accept it and you will greeted with following error.

Msg 15118, Level 16, State 1, Line 1
Password validation failed. The password does not meet Windows policy requirements because it is not complex enough.



Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah