Create SQL Login with Powershell and T-SQL

If you ever have the need to create a number of logins and users for a specific database(s) programmatically, then the following scripts may be of use to you.

The first script leverages the strength of Powershell and can be extended for example to pull a number of usernames and passwords from a file and then use these to create the necessary logins. For our purposes today, I’ve decided to use a hardcoded username and password but it is nice to know that Powershell can do much more. The script is attached below:

The second script is written in T-SQL and performs the same exact operation as the previous PS script. The script is attached below:

Happy coding…

.NET, Blog, Software Development

Setting up a new Windows 8.1 machine the easy way – using Chocolatey Nuget

I admit it! I like new, shiny things. Besides, I wanted to sync all my devices (Surface and laptop) to the same version of the OS. The Surface Pro upgrade was a breeze. Since I don’t keep any data on my Surface, it was a case of just running the upgrade. However, the laptop upgrade wasn’t as straightforward, since I’m running Windows 8 on a Mac Book Pro Retina using Bootcamp. Yes, I know, it’s wrong, but I like it so deal with it.

Since the inline upgrade didn’t work and rendered my machine useless (track pad didn’t work, mouse didn’t respond, I couldn’t click or select anything) I decided to wipe out the whole Windows partition and re-install the new Windows 8.1 from scratch with a clean install. My data is securely stored on my NAS and SkyDrive so at least I didn’t have to worry about this side of things.

However, as many Windows users know, after some time you accumulate a significant amount of applications and programs, some of them useful, some not; and the hardest thing is to remember which applications you really want and how to get them back. In the good old days, to install an application on your machine, you would have to open the browser, search for the application, find the right/legit one, download the installer and run it. Then repeat the same process for every application!!

And this is where Chocolatey comes in. Chocolatey is a command line (Powershell), machine package manager built with Windows in mind and it allows you to quickly search and install packages from the command line. No downloads, no 64/32-bit installers. You tell it which application you want and it works out which package to get and install on your machine!

To use Chocolatey, you first need to install it. Open the command prompt and copy-paste the following:

The command above will install and initialize Chocolatey and that’s all you have to do. Now you can start installing things as you please.

To install an application/package you have to issue a short command, e.g if you want to install Git you need to run the following from the command line:

For a full list of all the available packages, you can visit the Chocolatey website.

Happy coding…

Blog, Web Development

Working with Radio buttons and jQuery

Working with Radio buttons and jQuery Radio buttons are one of the essential HTML elements that you can find in almost any website that contains a form for users to enter data. In this blog today we will do the following:

  • Add radio buttons dynamically using jQuery
  • And, get the value and text of the selected radio button using jQuery

First you need a standard boiler plate html page that has a reference to the jQuery library. For this example we will use the Google jQuery CDN to pull the library on our page. At this stage, you page should look like this:

Now we can add our basic html elements. The page will consist of 2 buttons and 1 div where the radio buttons will eventually be displayed. Within the body add the following html:

Finally, between the closing </body> and closing </html> tags add the following javascript code:

The first method uses a string array in order to populate the radio buttons with values and text. We also use the .append method to add the radio buttons to the DOM and make them visible. The first time that we iterate through the array, we also set the radio button to ‘checked’ so that the user can see the first option pre-selected when presented with the radio buttons. This is optional and you can of course opt out from having any radio buttons implemented.

The second method uses jQuery in order to retrieve the value and the test of the selected radio button and displays an alert (message box) with the selected values. For me, the trickiest part was getting the selected text, especially if it is different from the radio button value itself.

You can see a working version of this code on jsFiddle

Happy coding…

.NET, Blog, Web Development

Resolving “HttpContextWrapper is not serializable” errors after upgrading Glimpse.

If you are doing any ASP.NET development and not using Glimpse you are seriously missing out. It helps is so many ways to troubleshoot and improve performance that I cannot believe how I’ve done any web development without in the past. I’m really grateful for the work that it has been done by the developers.

However, today I decided to update all the NuGet packages I thought they were safe and as you would have guessed it, the update broke my MVC4 website. A simple MVC website will probably not come across this issue, but I have added some code to intercept all application errors and exceptions and redirect to an error page where a meaningful message can be displayed. The code that does the interception within the Application_Error method in Global.asax is listed below:

The line that started failing is highlighted. If you try to run the code you will be confronted with the following exception:

An unhandled System.Runtime.Serialization.SerializationException exception occurred while processing the request.——————————————————————————-
Type ‘System.Web.HttpContextWrapper’ in assembly ‘System.Web.Abstractions, Version=, Culture=neutral, PublicKeyToken=31bf3856ad364e35′ is not marked as serializable.
   at Microsoft.VisualStudio.WebHost.Host.ProcessRequest(Connection conn)
   at Microsoft.VisualStudio.WebHost.Server.OnSocketAccept(Object acceptedSocket)
Version Information: ASP.NET Development Server

It seems that this is bug that has slipped to the latest update as per the original thread on Github here. Depending on how you are using HttpContextWrapper in your implementation, there may be a few ways to bypass the current bug, but for me the simplest solution was to switch from using the VS2012 Development Server for debugging to IIS Express.

I hope this helps anyone else coming across this issue.

Happy coding…


Fixing TextChanged event firing twice in Windows Phone 7 (WP7)

I’m currently playing around with WP7 and WP8 development trying to get to grips with the framework. I don’t have a specific application in mind but I’m trying to create a basic application that allows me take notes on the fly. Nothing fancy there.

One of the “requirements” is to be able to search across all my notes for a specific word and get all the results back in one continuous list that will contain the name/title of the note that matches the search criteria. In my effort to be fancy, I decided against using a separate button. Instead, I wanted to be able to initiate a search once the textbox had 2 or more characters, sort of an autocomplete textbox.

So my page looks like this:
WP7_SearchPage Image

The xaml code for this page is listed below:

The important part is highlighted. A normal TextBox with no special properties other than alignment and a method to handle the TextChanged event. To quickly test the method for the TextChange event I added a MessageBox.Show(“hello”); line.

I compiled and run the code, navigated to the page and started typing. This immediately brought to the surface an existing WP7 bug where the TextChanged event fires off twice. The reason behind it is fairly simple – a bug – and StefanWick  was kind enough to give a great explanation on StackOverflow here on why this is happening. In summary, the TextBox contains two (yes, that’s 2) text controls, the second being there to handle the disabled/read-only state. Hence, the event first twice, once for each control within the template.

The solution is simple, as long as your textbox doesn’t need a disabled/read-only state. First you need to create a new template by copying the existing one and then you need to remove all the xaml that relates to the second disabled/read-only textbox. Finally, you will need to add this new resource to the appropriate textbox(es). If you don’t feel confident messing with xaml templates (neither do I), just copy/paste the code below to the top of your page:

Ensure you edit your TextBox to use the new template resource similarly to this:

This workaround will stop the TextChanged event firing twice.

Happy coding…

.NET, Blog, Software Development

Dealing with “Cannot drop database because it is currently in use” error in ADO.NET

I’m a big fun of Entity Framework but every once a while I come across a case where EF is not adequate or the right tool for the job. In one such case, I had to implement some code to perform bulk inserts in order to improve performance. Shortly after I implemented the helper methods my unit tests started failing when attempting inside the tear down method which was responsible for clearing out the test data and deleting the test databases. The error?

“Cannot drop database “7139e838-0de2-41b7-8b99-c0eb6a5b02c8″ because it is currently in use.”

Below is a method extract that bulk inserts data from an ADO.NET DataTable straight into a SQL Database table using bulk insert:

The code is pretty straight forward:

  1. Create a connection
  2. Create a transaction
  3. Open the connection
  4. Perform the bulk insert
  5. Commit the transaction
  6. If an error occurred then rollback and
  7. Finally, close the connection

That should do it right? So why did I suddenly started getting errors about open connections then? As it turns out, the bulk insert uses a connection from the Connection Pool. In this case, the connection doesn’t close even if you call Connection.Close(). Instead, the connection, which is managed by the Connection Pool, get closed only after a period of inactivity. Consequently, if you attempt to delete a database shortly after performing a bulk insert or make any other raw ADO.NET calls, you may get this error.

The solution is fairly simple: just make sure you call SqlConnection.ClearPool() or SqlConnection.ClearAllPools() at the end of the process. This will ensure that all connections are closed and the database is free.

Note: Be careful when calling these two methods as they may have impact on performance. Ensure that you have completed all your database operations and you wont need another connection soon.

Using the code from the previous example, we can fix the problem by amending the code inside the “finally” block:

Happy coding….


Fixing error: “Cannot generate SSPI context” after changing SQL service account

Everyone knows that it is good practice to use a domain or service account to run the SQL service. I’m sure you do too! However, once you do the right thing and change the SQL Service account, you may start getting the following error message when attempting to connect to the sql server:

“The target principal name is incorrect.  Cannot generate SSPI context.”

The explanation, as given by Microsoft in this KB article is:

Simplified explanation:

If you run the SQL Server service under the LocalSystem account, the SPN is automatically registered and Kerberos authentication interacts successfully with the computer that is running SQL Server. However, if you run the SQL Server service under a domain account or under a local account, the attempt to create the SPN will fail in most cases because the domain account and the local account do not have the right to set their own SPNs. When the SPN creation is not successful, this means that no SPN is set up for the computer that is running SQL Server. If you test by using a domain administrator account as the SQL Server service account, the SPN is successfully created because the domain administrator-level credentials that you must have to create an SPN are present.

There are 3 ways to fix the problem:

  1. Revert to using the Network Service or Local System account (NOT RECOMMENDED)
  2. Assign the domain account to the Domain Admins group (NOT IDEAL – due to the elevated permissions)
  3. Fix the problem by giving the domain account just the appropriate permissions in Active Directory. Permissions required are

- ServicePrincipalName: Read
- ServicePrincipalName: Write

We will use the 3rd option to fix the error. First, it is good practice to verify that the problem is actually due to permission issues. Log in to the server where you SQL Instance is running. Go to the error logs and look for the last time that the SQL service was restarted. You should find an error message similar to this:

Date                    10/17/2013 9:29:50 AM
Log                       SQL Server (Archive #1 – 10/17/2013 10:53:00 AM)
Source                Server
The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/ ] for the SQL Server service. Windows return code: 0×2098, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

This is great. At least now we have verified that the problem is related to the SPN and we are ready to apply the fix.

Log in to the server running your Active Directory service and execute the following steps:

  1. Run Adsiedit.msc
  2. In the ADSI Edit snap-in, expand Domain [YourDomainName], expand DC= RootDomainName, expand CN=Users, right-click CN= [YourAccountName, and then click Properties.
  3. In the CN= AccountName Properties dialog box, click the Security tab.
  4. On the Security tab, click Advanced.
  5. In the Advanced Security Settings dialog box, select one (any) of "SELF"'s row
  6. Click Edit, Open Permission Entry dialog box.
  7. Make sure Pricipal is "SELF", Type is "Allow" and "Applied to" is "This Object Only", in Properties section, select the properties below:
    - Read servicePrincipalName
    - Write servicePrincipalName

Click OK to apply all changes and exit the ADSI Edit snap-in

Finally, you need to restart the SQL Service(s) that use the account in question.

You can verify that the SPN has been registered successfully upon the restart by going to the SQL Server logs. You should now see an entry similar to this:

Date                    10/17/2013 10:53:58 AM
Log                       SQL Server (Current - 10/17/2013 10:54:00 AM)
Source                Server
The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/ ] for the SQL Server service.

Connections to SQL Server should now succeed!

Happy coding…

Azure, Blog

Azure – Error deleting VHD: There is currently a lease on the blob and no lease ID was specified in the request.

Working with Azure the last couple of days, I came across an issue where I deleted a VM but I was unable to delete the associated vhd blob. The error received was similar to this:

There is currently a lease on the blob and no lease ID was specified in the request

While this error is expected if a VHD is still registered as a disk or image in the portal, the problem arises where a lease remains even if the blob is not registered as a disk or image in the azure portal.

If you receive one of these errors, first make sure the VHD is not in use (i.e deleted):

  1. In the Windows Azure management portal, if the disk shows up under Virtual Machines, Disks, and the Attached To column is not blank, you should first remove that VM in the Attached To column by going to VM Instances, selecting the VM, then clicking Delete.
  2. If Attached To is blank or the VM in the Attached To column has already been removed, try removing the disk by highlighting it under Disks and clicking Delete Disk (this will not physically delete the VHD from the blob storage, it only removes the disk object in the portal). Example image below

Azure VM Disks


Useful Tip: You can use the magnifying glass to filter to the required disk if you have multiple pages of disks.

Happy Coding…

.NET, Blog, Software Development, Testing

Using NCrunch to enhance your tests in Visual Studio

I have recently come across nCrunch during a code demo. nCrunch is an automated concurrent testing tool for Visual Studio and I have to say that I am pretty impressed with this ‘little’ piece of software.

nCrunch is an 3rd party tool that allows you to continuously run tests as you write them. Actually, the tests are run every time you change any part of your code that is being monitored by nCrunch and may have an impact on your tests. The tool is responsible for building and running tests as it sees fit.

Installation was easy and painless. As soon as you install nCrunch you are presented with a wizard that takes you through the process of setting up nCrunch to run your tests. It is at this stage that you decide how to run your tests. Some of the settings include:

  • nCrunch Enabled/Disabled
  • Engine mode (concurrent, sequencial etc)
  • Which tests to run
  • Number of Threads to use

Once you complete all the wizard tests and as long as you enabled nCrunch to run your tests, nCrunch kicks off and runs in the background. The image below shows how to quickly check the test progress:

Running Test Icon


For example, the image above informs me that 2 of my tests are failing. To see the failing tests, you can either click on the little icon on the task bar or you can use the nCrunch Tests Window (Ctrl + Shift + M). The window should look similar to the one below:

nCrunch Window

You can click on each of the test to see the failure as nCrunch handily displays the exception below. It will also display reasons for failure if your test fails on a specific assertion. Double-click on any of the tests and can instantly go to the failing code, like in the picture below:

Failing Test Image

The beauty of nCrunch is that it provides immediate and continuous feedback. Here we can see the line where the code within the is failing – marked with a red x. Obviously I will need to do a bit more digging to fix the problem, but I know straight away where to look. Also, another thing worth noting in the example above is the line marked with the amber/orange circle. This is nCrunch telling me that the code executed in this line is taking longer and I may want to look at it! Isn’t this great?

But this is not the only feature that nCrunch has to offer! In addition to continuous and seamless testing we also get:

  •           Automatic Concurrent Testing
  •           Code Coverage
  •           Performance Metrics
  •           Inline Exception Details
  •           Intelligent Test Execution
  •           Small Memory Footprint
  •           Parallel Execution
  •           Optimised Build Process
  •           Smart Multiprocessor Support
  •           Easy Debugging
  •           Part Continuous/Manual Mode

I like the fact that straight out of the box you get all this. It is true that the memory footprint is small but at times I’ve found my machine working a bit harder when nCrunch goes through a full test cycle when the code changes impact a large number of tests. The tool is intelligent enough to work out which tests to target depending on your changes.

The parallel execution is a great feature as well, but there are a few caveats that you need to be aware of. If your tests rely on database operations and/or file access, then you need to work around the concurrency issues. There are two ways to approach this problem:

  1. You disable parallel execution by assigning only one thread to nCrunch. This can be done easily through the nCrunch Menu and will result in tests being run sequentially.
  2. You set up your tests to use unique databases and files (using Guids instead of hard-coded file names or database names). For example, my integration tests test my repositories to ensure that all CRUD operations work and that Entity Framework performs as expected. My tests have a setup and tear-down method that creates a database and then deletes it for each test fixture (I’m using nUnit as my testing framework). My original code wasn’t optimized for nCrunch and as such most of the integration tests were failing as they were unable to obtain exclusive access to the database. So instead of relying on a connection string being passed from the app.config, I configured the tests to create a connection string on the fly using a Guid for the database name. Something like this:

Note that my dbContext is designed to take either a connection string or a connection name and work out how to establish a connection to the database.

There are similar workarounds for file access and you will find that nCrunch has a nice website and great documentation and support. Since I installed nCrunch, I have barely run any unit test manually and it has significantly sped up my development. Instead of waiting around to run the tests, I can now dive into the code and focus on the things that matter. As soon as nCrunch complains about a failure, I make sure that the offending code is fixed and carry on. Finally, I have started focusing a lot more on Code Coverage without waiting on my CI or other tools to indicate that some piece of code in my class is not reachable and not covered by tests.

I would recommend nCrunch to anyone who’s serious about unit testing and wants to improve his/her development speed. I hope that you will find this tool as useful as I did.

Happy coding…

Xamarin Logo
.NET, Blog, Mobile, Software Development

Configuring Visual Studio 2012 to work with the Xamarin framework and PCLs

If you want to do cross development using Xamarin and Visual studio, you need to install the Xamarin tools for Visual Studio. This will allow you to create solutions that target one or multiple platforms using the magic of MVVM (Model – View – ViewModel) and PCLs (Portable Class Libraries).

The Visual Studio installer can be acquired here.

The download is over 1.2Gb so it may take some time depending on your connection. The installation is fairly quick and painless with only a few steps to follow on the wizard.

Once the installation is complete, you can start working with the new Android and iOS frameworks. One common task is to create a core project which can be shared across multiple platforms. So going through the usual steps you would create a new project:

Portable Class Library Project


Select the name and location and click OK. Then you would be presented with the following screen:Portable class library framework selection

At this stage, you would realize that there is no option to add either the Android or the iOS frameworks. To resolve this, you have to carry out a few manual steps but nothing too hacky and nowhere near the registry!

Close down Visual Studio and navigate to the following location:

C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETPortable\v4.0\Profile\Profile104\SupportedFrameworks

In this location, you need to create two new files in order to allow Visual Studio to reference the Android and iOS frameworks when creating of PCLs.

First create a new MonoAndroid.xml file. Open and add the following xml content:

Save and close. Then create a new MonoTouch.xml file. Open and add the following xml content:

Save and close. Fire up Visual Studio again and try to create a new PCL project. This time you will be presented with all the applicable frameworks as per below:


Now, don’t freak out if you see this error above. If you are like me, you may think that something went wrong. However, all is good and the way to resolve this is to change the drop down value from Windows Phone 7 and Higher to Windows Phone 7.5 and Higher like in the screenshot below:



If you follow all these steps, you will be able to get your project “off the ground” straight away and without any exceptions.

Happy Coding…