Monday, January 19, 2009

Web Camera, Rovio, Remote Surveillance - in 20 minutes

Have you ever wondered how to grab pictures from a web cam into your .NET application. What if that web cam is remote and password protected. What if it is mobile web cam such as Rovio. What if you like to detect motion events and fire some logic to notify you of such motion.

Well, today I would like to walk you through on how to create such application.

Before we begin you need to download AForge if you like to have motion sensing capabilities - http://code.google.com/p/aforge/ . "AForge.NET is a C# framework designed for developers and researchers in the fields of Computer Vision and Artificial Intelligence - image processing, neural networks, genetic algorithms, machine learning, etc."

You would not need the whole package just several classes. I had to modify some of the original AForge classes to suit my needs, but you are welcome to use original provided you comply with licensing requirements.

Part 1 - Figuring out where web cams store their pictures.

In my case I had two types of web cams. TRENDnet Wireless Internet Camera Server TV-IP201W and Rovio which I mentioned earlier. By logging in to my web cam server I could see the following page. This web page is generated by a camera embedded web server. My guess that it is running some sort of windows embedded OS since it is rendering aspx pages, but leave this to the reader to figure out since it is irrelevant for this post.

WebServer

This front page image is updated every time you request a page. So my guess was that camera stores temporary images internally and provides a link to those images. The next step is to figure out the exact uri of the image. This was easily done by opening page source and examining html code. Please see the screen shot below.

Image_link

Yes, that weird number is a relative reference to server's folder. Convert it to full uri and you would get something like http://MyCameraServer.com/goform/capture?12378465237865336 .
This is our first type of cameras.

Now lets explore Rovio, which has a very simple to use set of APIs. There I found that I can just go to something like http://MyCameraServer:MyPort/Jpeg/CamImg0000.jpg , where MyCameraServer:Myport is an address where you set up your Rovio. That's it. Easy.

Part 2 - Connecting to remote server.

To connect to remote web server we would utilize classes WebRequest and WebResponce from System.Net namespace. It is very simple. First we need to create web request and provide URI, then we need to add credentials, then execute request, which returns response as instance of WebResponce class. Later we can access stream reader of web response and read our data.
Below is an example.

Dim request As WebRequest
Dim response As WebResponse
Dim reader As Stream
Dim data(8191) As Byte
Dim count As Integer
Dim total As Integer

request = WebRequest.Create(url)
request.Credentials = New NetworkCredential("login name", "password")
response = request.GetResponse()
reader = response.GetResponseStream()
total = 0

Dim myBitmap As Bitmap = New Bitmap(reader, True)

While True
count = reader.Read(data, 0, 8192)

If count <= 0 Then
Exit While
End If

total += 8192
End While

reader.Close()



Part 3 - Automatic refresh

You probably would not want to refresh this picture every time. So here is what I did, there are many ways, I prefer the quickest one :). Set the timer and refresh the picture by timer. It works great, except now if you set it to refresh too often your UI will freeze. So the idea is to load pictures on a separate thread. And for that I have created this simple class ThreadingHelper.


Imports System.Threading

Public Class ThreadingHelper
Private Shared _currentForm As Form

''----------------------------------------------------------------------------
'' methods related to threading 
''----------------------------------------------------------------------------
Public Delegate Function DelegateBegin(ByVal endCode As DelegateEnd) As DelegateEnd
Public Delegate Sub DelegateEnd()

Public Sub BeginCode(ByVal currentForm As Form, _
ByVal _beginCode As DelegateBegin, _
ByVal _endCode As DelegateEnd)

_currentForm = currentForm

Dim callbackTransfer As AsyncCallback = New AsyncCallback(AddressOf TransferContext)

_beginCode.BeginInvoke(_endCode, callbackTransfer, Nothing)

End Sub


Public Sub TransferContext(ByVal asyncResult As System.IAsyncResult)

Dim asyncResultBegin As Runtime.Remoting.Messaging.AsyncResult
Dim delegateBegin As DelegateBegin
Dim endCode As DelegateEnd

asyncResultBegin = CType(asyncResult, Runtime.Remoting.Messaging.AsyncResult)

delegateBegin = asyncResultBegin.AsyncDelegate

endCode = delegateBegin.EndInvoke(asyncResult)
Try
If _currentForm.InvokeRequired Then
_currentForm.Invoke(endCode)
Else
endCode()
End If
Catch ex As NullReferenceException
''if error occured on a different thread for the purpose of this streaming application we are just going to swallow it.
'' write youe handler if you like 
Catch ex As ObjectDisposedException

Catch ex As Exception


End Try

End Sub
End Class


Here is how to use it.


myThreading = New ThreadingHelper()

myThreading.BeginCode(Me, AddressOf GetPicture, AddressOf UpdatePicture)



Part 4 - Rest of the code.

Imports System.Net
Imports System.IO
Imports MotionDetection

Public Class Form1
Private Const url1 As String = "http://myserver/goform/capture?1078432126434196"
Private Const url2 As String = "http://myserver/goform/capture?1073114869312048"
Private Const url3 As String = "http://myserver/goform/capture?1225387068534147"
Private Const url4 As String = "http://myserver/goform/capture?1225387347966623"
Private Const url5 As String = "http://myserver/goform/capture?1082672364580373"
Private Const url6 As String = "http://myserver/Jpeg/CamImg0000.jpg"

Private cameras(,) As String = { _
{"camera 1", url1}, _
{"camera 2", url2}, _
{"camera 3", url3}, _
{"camera 4", url4}, _
{"camera 5", url5}, _
{"rovio 1", url6} _
}

Private Shared _cameraIndex As Integer = 0
Private Shared _motionLevel As Double = 10.0
Private Shared _detectMotion As Boolean = False
Private Shared globalBitmap As Bitmap
Private Shared _motionDetector As MotionDetector3
Private myThreading As ThreadingHelper

Private Shared urlCurr As String = url1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

For i As Integer = 0 To (cameras.Length / 2) - 1
ComboBoxCameras.Items.Add(cameras(i, 0))
Next

ComboBoxCameras.SelectedIndex = 0
urlCurr = cameras(ComboBoxCameras.SelectedIndex, 1)

myThreading = New ThreadingHelper()

_motionDetector = New MotionDetector3()
_motionDetector.MotionLevelCalculation = True

Timer1.Start()
End Sub

Private Sub LoadFile(ByVal url As String)
Try
Dim request As WebRequest
Dim response As WebResponse
Dim reader As Stream
Dim data(8191) As Byte
Dim count As Integer
Dim total As Integer

request = WebRequest.Create(url)
request.Credentials = New NetworkCredential("login name", "password")
response = request.GetResponse()
reader = response.GetResponseStream()
total = 0

Dim myBitmap As Bitmap = New Bitmap(reader, True)

While True
count = reader.Read(data, 0, 8192)

If count <= 0 Then
Exit While
End If

total += 8192
End While

reader.Close()

If Not globalBitmap Is Nothing Then
globalBitmap.Dispose()
End If

''to prevent cross threading access to a shared memeber you would need to clone image since it would be stored on the local stack and you would need to update it
globalBitmap = myBitmap.Clone(New Rectangle(0, 0, myBitmap.Width, myBitmap.Height), Imaging.PixelFormat.Format32bppRgb)

If Not myBitmap Is Nothing Then
myBitmap.Dispose()
End If
response.Close()
Catch ex As Exception
'MessageBox.Show(ex.Message)
End Try
End Sub

Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
Try
Timer1.Stop()
myThreading.BeginCode(Me, AddressOf GetPicture, AddressOf UpdatePicture)
Catch ex As Exception
MessageBox.Show(ex.Message)
Timer1.Start()
End Try

End Sub

Private Function GetPicture(ByVal endCode As ThreadingHelper.DelegateEnd) As ThreadingHelper.DelegateEnd
Try
LoadFile(urlCurr)

Catch ex As Exception

End Try

Return endCode
End Function

Private Sub UpdatePicture()
Try
If Not PictureBox1.Image Is Nothing Then
PictureBox1.Image.Dispose()
End If

Dim myBitmap As Bitmap

myBitmap = globalBitmap.Clone(New Rectangle(0, 0, globalBitmap.Width, globalBitmap.Height), Imaging.PixelFormat.Format32bppRgb)

If _detectMotion Then
_motionDetector.ProcessFrame(myBitmap)
If (_motionDetector.MotionLevel * 100 > _motionLevel) Then
_motionLevel += 10
TextBoxMotionLevel.Text = _motionLevel.ToString()
MessageBox.Show("Motion detected, here we can automatically send e-mail and deliver files over network somehow.")
_motionDetector.Reset()
End If
End If

PictureBox1.Image = myBitmap


Timer1.Start()
Catch ex As Exception
MessageBox.Show(ex.Message)
Timer1.Start()
End Try

End Sub

Private Sub Form1_FormClosing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles MyBase.FormClosing
Timer1.Stop()
If Not PictureBox1.Image Is Nothing Then
PictureBox1.Image.Dispose()
End If
End Sub

Private Sub ComboBoxCameras_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBoxCameras.SelectedIndexChanged
urlCurr = cameras(ComboBoxCameras.SelectedIndex, 1)
_cameraIndex = ComboBoxCameras.SelectedIndex
End Sub


Private Sub CheckBoxDetectMotion_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CheckBoxDetectMotion.CheckedChanged
_detectMotion = CheckBoxDetectMotion.Checked
End Sub

Private Sub ButtonSetMotionLevel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonSetMotionLevel.Click
Dim newLevel As Double
If (Double.TryParse(TextBoxMotionLevel.Text, newLevel)) Then
_motionLevel = newLevel
End If
End Sub

Private Sub ButtonResetDetector_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonResetDetector.Click
_motionDetector.Reset()
End Sub
End Class


And here is the result:
We could have almost streaming video :). The picture appears dark, because I have no control over the light switch in a remote facility. but hopefully you get the idea.


WebCam

And finally with motion detector.

MotionDetector

You could set level of the motion in % and then write your custom code and what to do about detected motion. For example you could send an e-mail with picture attached or using Rovio you could send a specific sound command and alert an "intruder" :).

In my next post I will provide a little more explanation on what modifications I made to AForge library.

This was a very nice ISolvable problem :)

Friday, January 16, 2009

SQL Server 2005 Symmetric Encryption

This is a brief summary of SQL Server 2005 symmetric encryption, encryption keys, their hierarchy and usage. At the end of this blog you may find an example script on how to use symmetric key encryption.

Symmetric encryption

  • Maximum number of characters which can be encrypted in one function is 7943.
  • Symmetric encryption decrypts 1000 times faster than asymmetric encryption.
  • Symmetric key is stored in the database and could be encrypted by database master key, by certificate and by password.
  • If symmetric key was created using the same KEY_SOURCE, ALGORITHM and IDENTITY_VALUE it would be the same, even if created in different databases.
  • When database backed up or detached symmetric key is kept in the database.
  • When symmetric key is encrypted by password, Triple DES algorithm is used to encrypt symmetric key. Triple DES is weaker than AES. So a key for a stronger encryption is encrypted with a weaker encryption algorithm. It is advised to use certificate to encrypt symmetric key and to use AES 256 algorithm for symmetric encryption.
  • AES encryption works only on Windows Server 2003 and later. (I have not tried it on Win XP SP 3, which came out in May 2008, may it has this encryption already.)
  • Certificate can be encrypted/decrypted by database master key. If database master key is OPEN, then certificate decryption/encryption is applied automatically when you OPEN symmetric key.
  • To OPEN database master key a user must have CONTROL permissions in the database.

Database Master Keys.

(This is where different articles say different things, so I was trying to give priority to the article with the later date and my experience.)

Here is an MSDN article about SQL Server 2005 encryption hierarchy.

And below is an overview of the encryption hierarchy.

SQL2005_encryption_hierarchy

Here is a similar MSDN article only for SQL Server 2008.

And an accompanying MSDN diagram for permissions hierarchy.

SQL2005_permissions_hierarchy

Here is original MSDN article.

When I was writing encryption script for our database I put these two diagrams on the wall in front of me, it helps :).

  • Database Master Key (DMK) can be created with encryption by PASSWORD only.
  • DMK is a symmetric key (according to may 2008 article and public/private key according to 2007 article, so I would go with 2008 J).
  • DMK can be backed up.
  • Other encryption can be added to DMK like so

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;

  • it is recommended to drop encryption by password and back up the key immediately before using it. Like so :

ALTER MASTER KEY DROP ENCRYPTION BY PASSWORD = 'whatever the original password is';

  • the only problem with that is, IT DOES NOT WORK. MSDN says that a key can not be without any encryption, that is why the error message is thrown, but even when I added encryption by Service Master Key it did not help. Because SMK encryption is in master database only and thus when dropping password encryption it tries to drop it from the original database leaving key unencrypted, which is not allowed. So I have to add some other encryption to DMK before dropping password encryption.
  • What it means is that the key can be accessed in the following 3 ways (assuming the database is restored from FULL database back up or original MDF file is attached.)
    • When database is restored on the computer and SQL instance where the original key was created.
    • When database is restored under the same SQL Server service account which was used when creating original key.
    • When database is restored on a completely different computer and under very different service account, but a correct password for DMK is provided.
  • When you ADD ENCRYPTION BY SERVICE MASTER KEY to DMK it creates a copy of DMK in master database and SQL Server Service account and SQL Server computer instance identity is used to encrypt the key. Thus there are 2 copies of DMK, one in the database itself and encrypted by password and another in master database and encrypted by SMK. When SMK encryption is added for DMK, there is no need to open DMK by password, in fact the whole command can be omitted, DMK gets unencrypted automatically. Which is what we want, because we don’t want to provide DMK password in our scripts and if it changes we would not want to update password in all T-SQL code (in a wrapper function in our case, but still we don’t want to make it visible even there).
  • No matter how easy it is to decrypt the data I would still create a back up for database master key and put in a safe somewhere. Or Print and store securely all the parameters required to recreate symmetric key.
  • The is_master_key_encrypted_by_server column of the sys.databases catalog view in master database indicates whether the database master key is encrypted by the service master key.
  • Information about the database master key is visible in the sys.symmetric_keys catalog view.
  • Please check that before dropping any other encryptions for DMK. If DMK is not encrypted by server (SMK) then it is better to know how to open DMK manually or add SMK encryption to it.

I looked up the syntax for ALTER MASTER KEY and the only encryptions which can be added are SMK and password:

ALTER MASTER KEY <alter_option>

<alter_option> ::=
<regenerate_option> <encryption_option>

<regenerate_option> ::=
[ FORCE ] REGENERATE WITH ENCRYPTION BY PASSWORD = 'password'

<encryption_option> ::=
ADD ENCRYPTION BY [ SERVICE MASTER KEY PASSWORD = 'password' ]

DROP ENCRYPTION BY [ SERVICE MASTER KEY PASSWORD = 'password' ]



But I still don’t get why they have DROP password if it does not work? Some articles say that SMK always has password encryption in case database gets detached and needs to be restored. May be this syntax is for the future release. Who knows, it was in SQL Server 2005 books online. The REGENERATE option re-creates the database master key and all the keys it protects. The keys are first decrypted with the old master key, and then encrypted with the new master key. This resource-intensive operation should be scheduled during a period of low demand, unless the master key has been compromised. So for now we are going to have a password for DMK, unless something new comes up.

Below are the encryption script and examples on how to use it.

Encryption script.

Please test variables' sizes before going into production to make sure that data is not truncated.


-- =============================================
-- Author: Ivan A
-- Copyright 2009 by ISolvable - http://isolved.spaces.live.com/

-- Create date: 07/21/2008
-- Description: PLEASE DO NOT STORE THIS SCRIPT IN PRODUCTION DATABASE.
-- This is a script which creates database keys.
-- Passwords need to be changed in production.
-- =============================================
ALTER PROCEDURE [dbo].[CREATE_KEYS]
-- Add the parameters for the stored procedure here

AS
BEGIN

-- this to prevent accidental executions of this stored procedure and to inform a user.
RAISERROR (N'This procedure cannot be executed. Please examine the procedure code first!',16,1)

/*<DO_NOT_EXECUTE,,*/--THIS STORPROC>

-- BACKUP KEYS.
-- DO NOT STORE THIS CODE IN PRODUCTION.
-- CODE WITH PASSWORDS TO BE STORED IN A SAFE(TBD).
-- BAKUP KEYS TO BE STORED IN A SAFE(TBD).
-- THIS CODE HAS ONLY MOCK UP PASSWORD.
-- CHANGE PASSWORDS BEFORE USE IN PRODUCTION.
-- DO NOT RECREATE KEYS IF SOME DATA IS ALREADY ENCRYPTED.
-- DECRYPT DATA FIRST. RECREATE KEYS. THEN ENCRYPT DATA WITH NEW KEYS.
-- BACKUP NEW KEYS.
-- THE CODE FOR THIS PROCEDURE WOULD BE COMMENTED OUT, TO PREVENT ACCIDENTAL EXECUTION.


-- PRESS CTRL-SHIFT-M and fill in the appropriate values.
-- THEN EXECUTE CODE SEPARATELY FROM STORED PROCEDURE STATEMENTS.

-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- =============================================

/*
----------------------------------------------------------------------------------------------
-- this creates a Database Master Key (DMK) in the original database
-- and encrypts MASTER KEY by password.
-- MUST MEET WINDOWS PASSWORD POLICY.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<master_key_password,,password>'

-- this copies DMK into master database
-- and encrypts DMK using SQL Server Service Master Key.
-- After this statement is executed DMK would be opened/decrypted automatically.
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

-- this exports DMK into a file (ON THE SERVER NOT ON LOCAL COMPUTER)
-- and encrypts it using a password.
-- store this file in a safe and secure location.
BACKUP MASTER KEY TO FILE = '<Back_up_file_path_on_server,,c:\temp>'
ENCRYPTION BY PASSWORD = '<master_key_password,,password>'

-- this creates certificates which protects database encryption keys (symmetric or asymmetric).
CREATE CERTIFICATE [<Cert_Name,,CTrack_Cert>] WITH SUBJECT = '<Certificate_Subject,,Key Protection>'

-- this creates symmetric key and ecnrypts it with a certificate.
CREATE SYMMETRIC KEY [<Symmetric_Key_Name,,CTrack_Sym_Key>] WITH
ALGORITHM = <Symmetric_Algorithm,,AES_256>
ENCRYPTION BY CERTIFICATE [<Cert_Name,,CTrack_Cert>];

----------------------------------------------------------------------------------------------
*/
/*
----------------------------------------------------------------------------------------------
-- Stored Procedures to be created.
----------------------------------------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: This procedure opens current symmetric key.
-- =============================================
CREATE PROCEDURE [dbo].[usp_Sys_Open_Key]
-- Add the parameters for the stored procedure here

AS
BEGIN
-- This is to be called subsequently with other stored procedures
-- within the same connection session.
-- As soon as connection session is closed the key is also closed.
-- This opens a key defined in usp_CREATE_KEYS script (not in production)
OPEN SYMMETRIC KEY [<Symmetric_Key_Name,,CTrack_Sym_Key>]
DECRYPTION BY CERTIFICATE [<Cert_Name,,CTrack_Cert>]
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: This procedure closes current symmetric key.
-- =============================================
CREATE PROCEDURE [dbo].[usp_Sys_Close_Key]
-- Add the parameters for the stored procedure here

AS
BEGIN
-- This is to be called subsequently with other stored procedures
-- within the same connection session.
-- As soon as connection session is closed the key is also closed.
-- This opens a key defined in usp_CREATE_KEYS script (not in production)
CLOSE SYMMETRIC KEY [<Symmetric_Key_Name,,CTrack_Sym_Key>]
END
GO
----------------------------------------------------------------------------------------------
*/
/*
----------------------------------------------------------------------------------------------
-- Functions to be created.
----------------------------------------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: This function encrypts a string using database symmetric key
-- =============================================
CREATE FUNCTION [dbo].[udf_Encrypt]
(
-- Add the parameters for the function here
@text varchar(7944)
)
RETURNS varchar(7988)
AS
BEGIN

-- if provided text length is greater than what can be encrypted than
-- an error message is thrown to prevent truncation.
if len(@text) > 7943
begin
DECLARE @temp as int
RETURN (1 + 'Argument text should be less than or equal to 7943')
end

-- Declare the return variable here
DECLARE @Result varchar(7988)

-- opening and closing key is resource intensive operation
-- if encryption needs to be applied multiple times please
-- use usp_Sys_Open_Key and usp_Sys_Close_Key stored procedures
-- and call encryptbykey function directly from your code.
-- usp_Sys_Open_Key procedure does the following.
--OPEN SYMMETRIC KEY [<Symmetric_Key_Name,,CTrack_Sym_Key>]
--DECRYPTION BY CERTIFICATE [<Cert_Name,,CTrack_Cert>];

-- use encrypt function
SELECT @Result = encryptbykey(key_guid('<Symmetric_Key_Name,,CTrack_Sym_Key>'),@text)

-- close symmetric key using usp_Sys_Open_Key procedure,
-- which does the following.
-- CLOSE SYMMETRIC KEY [<Symmetric_Key_Name,,CTrack_Sym_Key>]

-- Return the result of the function
RETURN @Result

END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: This function decrypts a string using database symmetric key
-- =============================================
CREATE FUNCTION udf_Decrypt
(
-- Add the parameters for the function here
@text varchar(7988)
)
RETURNS varchar(7943)
AS
BEGIN

-- Declare the return variable here
DECLARE @Result varchar(7943)

-- opening and closing key is resource intensive operation
-- if encryption needs to be applied multiple times please
-- use usp_Sys_Open_Key and usp_Sys_Close_Key stored procedures
-- and call encryptbykey function directly from your code.
-- usp_Sys_Open_Key procedure does the following.
--OPEN SYMMETRIC KEY [<Symmetric_Key_Name,,CTrack_Sym_Key>]
--DECRYPTION BY CERTIFICATE [<Cert_Name,,CTrack_Cert>];

-- use encrypt function
SELECT @Result = decryptbykey(@text)

-- close symmetric key using usp_Sys_Open_Key procedure,
-- which does the following.
-- CLOSE SYMMETRIC KEY [<Symmetric_Key_Name,,CTrack_Sym_Key>]

-- Return the result of the function
RETURN @Result

END
GO

----------------------------------------------------------------------------------------------
*/
/*
----------------------------------------------------------------------------------------------
-- Other ways to use
----------------------------------------------------------------------------------------------
-- you may use the following command to open the key,
-- however the wrapper procedure would be provided, to avoid
-- changing key names in multiple code files.
OPEN SYMMETRIC KEY [<Symmetric_Key_Name,,CTrack_Sym_Key>]
DECRYPTION BY CERTIFICATE [<Cert_Name,,CTrack_Cert>];

-- you may use the following command to close the key.
CLOSE SYMMETRIC KEY [<Symmetric_Key_Name,,CTrack_Sym_Key>]

----------------------------------------------------------------------------------------------
*/
/*
----------------------------------------------------------------------------------------------
-- Procedures to be called from .NET
----------------------------------------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: This procedure is for use from .NET,
-- it encrypts plain text using database symmetric key.
-- =============================================
CREATE PROCEDURE [dbo].[usp_Encrypt_BySymKey]
-- Add the parameters for the stored procedure here
@plaintext as varchar(1000)
AS
BEGIN

-- the size of the encrypted string
-- should be at least 60 bytes larger than the original plain text.
DECLARE @encrypted as varchar(1060)

-- Open symmetric key
Exec dbo.usp_Sys_Open_Key

-- encrypt plaint text
SET @encrypted = dbo.udf_Encrypt(@plaintext)

-- close symmetric key
Exec dbo.usp_Sys_Close_Key

select @encrypted
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: This procedure is for use from .NET,
-- it decrypts into plain text using database symmetric key.
-- =============================================
CREATE PROCEDURE [dbo].[usp_Decrypt_BySymKey]
-- Add the parameters for the stored procedure here
@encrypted as varchar(1060)
AS
BEGIN

-- the size of the encrypted string
-- should be at least 60 bytes larger than the original plain text.
DECLARE @decrypted as varchar(1060)

-- Open symmetric key
Exec dbo.usp_Sys_Open_Key

-- encrypt plaint text
SET @decrypted = dbo.udf_Decrypt(@encrypted)

-- close symmetric key
Exec dbo.usp_Sys_Close_Key

select @decrypted
END
GO

----------------------------------------------------------------------------------------------
*/

END



Summary of instructions on how to use procedures and functions created by the above script. Detailed instructions are in the script comments above.

The script creates 2 stored procedures and 2 functions.

  • usp_Sys_Open_Key
  • usp_Sys_Close_Key
  • udf_Encrypt
  • udf_Decrypt

When setting up a database, you would need:
  • to open this script.
  • Press CTRL-SHIFT-M
  • Fill in the required parameters.
  • Make sure data is NOT encrypted before recreating the keys.
  • Run code to create stored procedures and functions.

Here is an example on how to use these things:



DECLARE       @original as varchar(100)
-- make sure variable for encrypted text is at least 64 bytes larger, or better double it.
, @encrypted as varchar(200)
, @decrypted as varchar(100)

-- creating original text
SET @original = 'some text to encrypt'

SELECT @original, len(@original)


-- open symmetric key
Exec usp_Sys_Open_Key

-- Encrypt text, make sure you do not trim.
-- Do not cast without specifying a size.
SET @encrypted = dbo.usp_Encrypt(@original)

SELECT len(cast(@encrypted as varbinary(68))), len(@encrypted)


-- Do casting like so. This would be correctly decrypted.
SET @decrypted = dbo.usp_Decrypt(cast(cast(@encrypted as varbinary(68)) as varchar(68)))

SELECT @decrypted

-- always close key or close connection session as soon as key is not needed.
Exec usp_Sys_Close_Key


-- the return value of the decrypt function is 7943, you may do casting, make sure to specify the size
-- like in the following example.

drop table temp2
Exec usp_Sys_Open_Key

select *
, cast(dbo.usp_Decrypt(accnum_enc) as varchar(20)) as accnum_dec
into temp2
from temp1

Exec usp_Sys_Close_Key



Again this problem turned out to be compatible with ISolvable interface.
Ok, I think this is enough for today. It is 11:00 PM on January 15, 2009, and tomorrow I need to wake up at 4:40 am to go to work at 6:am. Need to get some sleep so I can actually work tomorrow :).
P.S. The post could have some errors, since I wrote most of it in July of 2008, please write me if you find some problems or have answers to my questions.

First Post - 109 years to copy...

Well today is my first post in here. I am just trying blog's features.

 109YearsToCopy

Take Care,

Ivan

Disk Management hangs while "Connecting to Virtual Disk Service..."

Today I got an external 1TB  drive (made by Western Digital). I needed it to back up my system periodically. When I tried to change partition and file system to NTFS from FAT 32 I ran into a problem. In Administrative Tools -> Computer Management -> Disk Management snap-in panel hanged on "Connecting to Virtual Disk Service..."

After wasting some time searching for answers on the Internet, I decided to run Computer Management as Administrator... Duh... I should have done it right away. So Disk Management snap-in panel opened up fine and I was able to configure partitions on my external hard drive.

In order to run as Administrator right click on the program icon and select "Run as administrator".

Run_as_administrator

Have a good new year.

This problem was conformant to ISolvable interface ;)