Friday, September 25, 2009

Problem rendering AJAX PopupExtender over ActiveX in IE 7, IE 8 and FireFox.

If you happen to have some sort of ActiveX control on your page in an IFrame, such as PDF viewer, then some of the AJAX controls which use PopupExtender will be obscured by the ActiveX or any other browser plug-in which rendering happens out of the main page context.

For example menus, and CalendarExtender suffer this problem, since they inherit PopupBehavior.

What happens is Ajax PopupExtender behavior explicitly checks for browser version, in particular IE 6. In IE 6 there was a known problem with z-index so you had to use IFrames to render some popup controls, so PopupBehavior checks whether browser is IE 6 and creates an IFrame around control, otherwise no IFrame created, hence the problem in newer IEs and FireFox with browser plug-ins.

To fix this problem, first download source code for AJAX Control Toolkit from CodePlex. And find PopupExtender folder in VS project. Then find this function:

addBackgroundIFrame : function()

Remember to duplicate your effort for debug and release versions of code, since there are two separate files.

Now lets take a look at the original code:

addBackgroundIFrame : function() {
/// <summary>
/// Add an empty IFRAME behind the popup (for IE6 only) so that SELECT, etc., won't
/// show through the popup.
/// </summary>

// Get the child frame
var element = this.get_element();
if ((Sys.Browser.agent === Sys.Browser.InternetExplorer) && (Sys.Browser.version < 7)) {
var childFrame = element._hideWindowedElementsIFrame;

// Create the child frame if it wasn't found
if (!childFrame) {
childFrame = document.createElement("iframe");
childFrame.src = "javascript:'<html></html>';";
childFrame.style.position = "absolute";
childFrame.style.display = "none";
childFrame.scrolling = "no";
childFrame.frameBorder = "0";
childFrame.tabIndex = "-1";
childFrame.style.filter = "progid:DXImageTransform.Microsoft.Alpha(style=0,opacity=0)";
element.parentNode.insertBefore(childFrame, element);
element._hideWindowedElementsIFrame = childFrame;
this._moveHandler = Function.createDelegate(this, this._onMove);
Sys.UI.DomEvent.addHandler(element, "move", this._moveHandler);
}

// Position the frame exactly behind the element
$common.setBounds(childFrame, $common.getBounds(element));

childFrame.style.left = element.style.left;
childFrame.style.top = element.style.top;

childFrame.style.display = element.style.display;

if (element.currentStyle && element.currentStyle.zIndex) {
childFrame.style.zIndex = element.currentStyle.zIndex;
} else if (element.style.zIndex) {
childFrame.style.zIndex = element.style.zIndex;
}
}
},

Notice the section above, which has IF statement.

Remove that IF statement completely. Recompile your AjaxControlToolKit project and reference this new dll in your ASP.NET project. Drum roll… it works! ActiveX does not render over your popup control anymore.

Keep in mind, if you have many IFrames on your page it would slower rendering, so try to keep number of IFrames to a minimum.

Another ISolvable problem :).

Wednesday, September 16, 2009

Restoring WPF window of another process

Recently I was working on a WPF single instance application, which hides it’s main window and shows system tray icon. My task was to restore main window whenever user tries to open another instance of the application. The good thing I have control over source code, which means I can do anything to the target app. However there are 3 bad things related to WPF

WPF shortfalls
  • no out of the box support for single instance applications (in WinForms it is just a flag in project properties tab)
  • it is hard to get a handle of main window of another process, if that window is hidden. Process.MainWindowHandle would return 0.
  • when restoring window of a different process WPF does not listen for window events and thus WPF thread does not start rendering, as a result you get black window with XP blue frame around it.
1st problem

First problem is easily ISolvable either using Process.GetProcessByName(yourAppName) when returns true and process id is different from current process, then most likely there is another instance of the app is running, unless name of your app for some reason, is the same as some other app running on the box. In that case you can use mutex to solve the problem. In fact using Mutex is a more robust approach. You can find implementations in here.

2nd Problem

Second problem turned out to be lengthy and requires use of SharedMemoryFile and a bunch of other APIs. You can see how it is solved in the same article. This article however does not address WPF issue, since it was written prior WPF release.

3d Problem

While easily solvable, it took me some time to figure it out.
When you have a code like this:

'' if mutex was not created that means other instance is running, 
'' so we need to restore window of other application.
If Not IsMutexCreated Then
Try
Dim mainWindowHanle As IntPtr = System.IntPtr.Zero

SyncLock GetType(FilesView)
mainWindowHanle = MemoryMappedFile.ReadHandle("Local\sharedMemoryFilesView")
End SyncLock

If mainWindowHanle <> IntPtr.Zero Then
Dim result As Boolean
result = ShowWindowAsync(mainWindowHanle, SW_SHOWDEFAULT)
result = SetForgroundWindow(mainWindowHanle)
result = UpdateWindow(mainWindowHanle)
SetFocus(mainWindowHanle)
End If
Catch ex As Exception

End Try
Application.Current.Shutdown()

Try
_mutex.ReleaseMutex()
Catch ex As Exception

End Try
End If

the ShowWindow or ShowWindowAsync function will indeed restore the window, only with a little problem. It is going to be black. As WPF rendering runs on a separate thread and apparently not listening for main window events :(. Notice that I am not using GC.KeepAlive and GC.Collect like in the original article, but I declared mutex as a class member of Application class, which is a main class in WPF applications. In my case reference to mutex object is kept until Application class is disposed, which is when application shuts down. So GC (Garbage Collector) will not reclaim memory occupied by object mutex because it would have active reference.

blackWindow

So WPF thread is silent when some other process calls window restore or window show. Well, here is a good thing I mentioned in the beginning. I have complete control of the source code. And it means I can include event listener in the application main message loop and from there restore WPF window.

Since I know I am calling ShowWindow and SetFocus I can concentrate on the events which are fired in those two cases. It is most likely would be GotFocus, Activated or IsVisibleChanged events for WPF window.
In there I call Show method, and … it works!



Private Sub FilesView_Activated(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Activated
Application.Current.MainWindow.Show()
End Sub

Private Sub FileView_GotFocus(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs) Handles MyBase.GotFocus
Application.Current.MainWindow.Show()
' or simply
' Me.Show() 'if you are already inside main window class
End Sub

' this should also work
Private Sub FilesView_IsVisibleChanged(ByVal sender As System.Object, ByVal e As System.Windows.DependencyPropertyChangedEventArgs) Handles MyBase.IsVisibleChanged
Me.Show()
End Sub

That’s it. A lot of sweat for a simple problem, but hey it is ISolvable :).

Monday, September 14, 2009

VB.NET short circuit IF statement and Nullable(of T)

Now this was an interesting bug to find. May be it is not a bug, but I can’t explain this behavior otherwise.

When working with databases and trying to cover impedance mismatch cases it is common to use the following logic:

...
Dim primaryUserId As Integer?
...
Using reader As IDataReader = dataAccess.ExecuteReader("usp_CostCenter_Get", params)
With reader
While .Read()
returnResult = True
costCenter = If(.IsDBNull(0), "", .GetString(0))
description = If(.IsDBNull(1), "", .GetString(1))
primaryUserId = If(.IsDBNull(2), Nothing, .GetInt32(2)) '' <<<<-- incorrect behavior,
'' if condition is true primaryUserId will get 0 instead of Nothing.
'' 0 - is default value for type Integer, but not for type Integer? or Nullable(of Integer)
'' Nothing - should be the correct value in this case.
End While
End With
End Using
...


In case when condition is true you would expect true part of IF statement to execute, while something else happens and primaryUserId receives default value for type Integer not for type Nullable(of Integer) or Integer? .

This is how to correct such behavior:

...
Using reader As IDataReader = dataAccess.ExecuteReader("usp_CostCenter_Get", params)
With reader
While .Read()
returnResult = True
costCenter = If(.IsDBNull(0), "", .GetString(0))
description = If(.IsDBNull(1), "", .GetString(1))

'' expanding IF statement
If .IsDBNull(2) Then
primaryUserId = Nothing
Else
primaryUserId = .GetInt32(2)
End If

End While
End With
End Using
...


Please let me know if you had experienced this before and agree or disagree with me.

Thank you!

Visual Studio 2008 XAML designer crashes

There are several reasons why Visual Studio can crash.
- http://code.msdn.microsoft.com/KB963035
- http://code.msdn.microsoft.com/KB963676

and some other which I don’t remember now. But I am not going to talk about the above issues, rather about some other case when Visual Studio could crash.

Here is what happens.
Visual Studio Designer could create instances of some of the controls if for example you placed a child control on a form the base class for that child would be instantiated, similar if you have referenced external assemblies which have controls that are on a form, those controls could be instantiated, it depends on a control logic.

If you are creating a control you have to check for Design time compilation versus run-time. See my previous blog on how to handle it in Win Forms and there is plenty of topics on the web how to handle a similar issue for WPF.

Now if control resides in an assembly which is located on a shared or networking folder, then logic of that control would be executed in a different security context, and AccessDenied exception could be thrown. In my case Visual Studio was not handling this exception properly and was crashing. DWatson was executing and collecting crash data but that did help.

What should happen is VS IDE should handle such exception and cancel rendering with a proper message and type of the exception.

I solved it by copying all referenced assemblies into local project folder, then recompiling project and restarting Visual Studio IDE.


I usually copy assemblies for real projects. I ran into this problem by trying to create real quick prototype and was lazy to transfer dlls locally.

Try and let me know.
This was another ISolvable problem. I am sorry for being out of touch, but I will try to get back and post since I have a lot of things to share.

Monday, February 16, 2009

Prevent Visual Studio 2005 Form’s Designer from executing base form behavior.

Have you ever tried to inherit a form and then in Visual Studio Designer you got an error message? Something like this:

InheritedFormError_Example2

or like this:

InheritedFormError_Example1

That is because Visual Studio Designer in order to render base form controls would create an instance of the base form and apparently would try to execute handlers for base form events. If events contain some logic where you call middle tier and connect to a database or make calls that could fail at design time, such as making a request for Principal Permission, this could generate error messages like the ones above, and in turn Designer would not be able to properly render inherited form. 
There are several principles and ways to avoid such behavior.  Don’t forget to recompile your project every time you make changes to base forms. It also helps to close designer window of the inherited form and re open it, that is when new instance of the base form is created and reloaded in the designer.

Basic Principles

Since now we are aware that code on the base form could be executed in the designer we could design our parent class in a way which would avoid such execution.
- In the parameterless constructor for the base (parent) form do not add any logic besides InitializeComponent()
- Or create an overloaded constructor with Protected access modifier, which would be executed only by a child form. 
- Avoid adding logic into Load event of the base form.
- And for that matter avoid adding logic into any event handler for the base form… Don’t you think this is too much? I do.

Ways to prevent base form to execute logic at design time.

There is however a useful property that Form has. It is called DesignMode. You may evaluate this property after instance is created and handle is passed to the Designer. Which means do not check this property in constructor, since it would return false.
If this property evaluates to True than you could stop your code from executing.

Now what if you have multiple base form event handlers and you don’t want to spend time modifying these event handlers by adding this condition. For this purpose there is another neat property on the Form, which is called Events. You could dispose all the events on the form if your base form is in the Designer. So you could add the following code in your base form Load event handler.

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

    If Me.DesignMode Then
        Me.Events.Dispose()
        Exit Sub
    End If
    '' your logic       

End Sub

So this was another ISolvable problem. :)

 

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 ;)