Triangle of Happiness – Rate your job satisfaction

Something that’s interested me for a long time is what makes people tick, as far as motivational factors in the workplace. We all spend a long time in our respective workplaces, and how you measure your relative happiness is an interesting, and individual thing.

The triangle of happiness

I’ve grossly over-simplified the factors down to three measures that I’ve been banging on about for the last 10 years, after a conversation with an old friend and colleague. I’ve always known it as the ‘triangle of happiness’, and it looks a bit like the image to the left…

If you’re comfortable with 2 of the 3 factors, then you’re doing OK.  If you’re not happy with 2 or more factors, then it’s potentially tipping the balance on your overall job satisfaction, and your engagement/willingness to stay in your current position or company.

You may find that you’re more than 3-dimensional, so I used the ideas mentioned above, along with some inspiration I got from Scott Hanselman and his KeysLeft 1 page app, and I came up with a 1 page web app for you good people to model your job satisfaction.

It was also a good opportunity for me to have a play with KnockoutJS, the Foundation framework, and a few other bits and pieces.  It’s still rough around the edges, but I’d appreciate any feedback, so I can improve it in the future.

Posted in IT Musings | Comments closed

Integrating ASP.NET MVC into an existing ASP.NET Webforms application

If, like me you’re not always blessed with the opportunity to build every application from scratch, you may find yourself wanting to introduce the wholesome goodness of ASP.NET MVC into an existing ‘classic’ ASP.NET Webforms application. Most tutorials out there concentrate on nice green field development.

What follows is largely a reference for me to remember how to do this.  It’s basically a matter of manually injecting what the project templates do for a new application.  I’m also not professing to have come up with all of these steps – I’m just bringing them together.

I’ll assume you’ve got all the necessary prerequisites (MVC 4.0) installed already, and if you have a web ‘site’ project, then I’d suggest you update it to a web application before doing all of this.

Getting the structure and configuration to look like MVC

There’s a number of standard folders, and bits of code you’ll find dotted around MVC applications – Models/Views/Controller for instance :)

The following article goes through the first steps of getting those folders into your project (assuming you don’t have a naming conflict).

Mixing ASP.NET WebForms and ASP.NET MVC

Updating to MVC4

This is all good, but that article’s a bit old, and you’ll find the next one brings you (mostly) up to date with MVC 4.0

Adding MVC 4.0 to WebForms Project

If you want to use any of the newer features such as bundling, or if you’ve copied some views from an MVC4 project into your new MVC project, you’ll need the ASP.NET Web Optimization Framework (get it from NuGet).

You may also want to take an MVC4 project and convert the global.asax code to call off to the classes in the App_Start folder…

    public class MvcApplication : System.Web.HttpApplication
        protected void Application_Start()


Getting the Visual Studio MVC Template Goodness

This is great, but the icing on the cake is to make Visual Studio think this is an MVC project, so you get the nice right-click options, like add–>Controller if you’re in the controllers folder.  It turns out you just need to fool Visual Studio by adding a project type guid in your web projects csproj file…

With Visual Studio I just did a quick diff between a new MVC 4 project’s project file, and my ‘hybrid’ project’s file.

The following is what you’re looking for…


If you just add the first ‘ProjectTypeGuids’ guid to the corresponding place in your project file, and reload, the magic happens, and Visual Studio thinks it’s now an MVC project.  You’ll probably find you already had the other 2 guids.

Posted in ASP.NET, MVC | Tagged , , , | Comments closed

Book Review : Trojan Horse by Mark Russinovich

It’s been a while since I read Mark’s last book – Zero Day, and I finally got a copy of the sequel; Trojan Horse.

Like any followup action story, the main challenges for Russinovich here were to develop the main characters, introduce new characters, angles and plotlines, and also develop/increase the action.  I’m glad to report all of these were successfully undertaken, and Trojan Horse kept me as entertained as Zero Day.

In this story we see Jeff Aitken and his partner (in more ways than one), Darryl Haugen get involved in another global cyber-crisis, with even more tangled international players than before, and even more audacious technical threats employed.  Again, they find themselves getting rather closer to the action than they planned, and their simple ‘computer consultant’ existence is transformed into cyber-agent once again.

The action centres around the Iranian nuclear program, and all the interested players in its outcome.  International governments and secret agents galore, keep things moving pretty quickly, and the book is again organised in a way to make you piece together disparate events yourself to reveal the main plot.

Probably the most significant character development in the book was Darryl as she gains new strength through some traumatic events.  Jeff continues to excel from the technical side, whilst showing more reality, being rather more bumbling in other areas, like driving a manual car.

Talking of reality, it could be said that a few areas are somewhat Bond’esque in the licence they take from coincidence, good luck, and timing.  I’m a big Bond fan, so I actually don’t mind if he escapes several times in a completely implausible fashion.  I guess it all adds to the action – and again, would look great on screen.

I actually found it hard to put down as early as around half-way through, as it seemed things were slowly but significantly building to the climax from there.

The action is solid and the technical detail as always is second-to-none.  Jeff dabbles in other areas, such as Android in this story, so I do wonder quite how many languages he knows and areas of specialism he has!

As an IT guy too, I still find it hard to believe that Jeff can maintain his physique with the hours he works.  Darryl is obviously good for him :)

Looking forward to the next one.

Posted in Book Review | Tagged , | Comments closed

Book Review : Zero Day by Mark Russinovich

Zero DayI’ve long followed the output and talent of Mark Russinovich, as any self-respecting Windows tech should know and use his Winternals and Sysinternals utilities. I don’t know what i would have done in some situations in the past without Process Explorer, Process Monitor, and all the other PsTools. Needless to say, Mark knows what he’s talking about in the world of computers.

I was surprised and intrigued recently when catching up on a Scott Hanselman podcast, he was the interviewee, on topics including his new novel.  Scott seemed genuinely surprised too that Mark had moved into this area, and also impressed with the way he’s managed to bridge the often ‘huge’ gulf between normal life and techiedom.

Zero Day is Mark’s first novel. An action thriller, centered around high technology crime (published in 2011), and he’s followed that up with Trojan Horse; further developing the main characters in the first book.

Zero Day features Jeff Aitkin as the lead character. A technical wizard, specialising in computer security, but seemingly normal in many ways that non-technical people think strange for a geek. He’s fit and good looking.  In the interview, Mark admits Jeff is based on him. :)

What’s interesting first of all, is that the novel was even published, as the subject matter simply wouldn’t have had mass appeal even just a few short years ago. The exploding popularity of the internet, and its crossover into normal people’s daily lives has obviously made the story more accessible than ever before. Also, the subject matter of coordinated cyber attacks was something even Mark himself thought may have happened for real before he finished the book; making the story less relevant. I guess he’s thankful in more ways than one we’ve survived up to now.

The story starts with the seemingly isolated virus infection of a company’s network, in the midst of a rather racy opening scene. Mark doesn’t hold back on language and imagery to paint the picture. I don’t know why I was surprised by this, but I guess that helps the book to sell.  Maybe it was also an attempt to quickly set the tone that this is not just a book for techheads.

We quickly then learn of many other, and varied incidents, involving computers in all sorts of places, and applications. Some with fatal consequences. What follows is a well constructed plot that explores not only the technical detail of viruses, exploits, triggers and rootkits, but the human factors that motivate individuals away from doing ‘good’, and the ways they justify their actions to themselves.

Clearly the author has done his research on many topics. The technical stuff is a given, but the characters involved are from many different backgrounds, explored well, and their back story plausible in most cases. Jeff’s own previous connection with the government, and the events leading up to 9/11 is interesting, albeit quite convenient for the story line.

My only real character comment is that the story seems to centre around a small group of very attractive computer people.  Jeff finds himself surprised when his new client is attractive, as his previous experience was that of the few women in IT, very few of them were attractive. His emerging sidekick and love interest also just happened to be a whizzkid, and complete knockout. I guess they’d better get moving on the movie!

Things develop, and it seems that this thing is bigger than individual viruses and hackers. The characters start to connect in unexpected ways, often unknown to them, and it’s a good rollercoaster ride across the world, as the characters chase the ‘cure’.  But – would it make any difference anyway on Zero Day?

Whether enough loose ends were resolved at the end of the book I’m still deciding, but that may be another reason to read Trojan Horse.

Being someone who doesn’t read many novels, I enjoyed this a lot as the story and technical detail held my interest throughout. Also, like the information age itself, the book is split into bitesize chunks that move quickly between the various storylines. I found myself wondering which thread I was going to join() again next (pardon the developer pun).

Trojan Horse - the bookI’m looking forward to getting into the next Jeff Aitkin adventure, Trojan Horse, which is also now available.

Check out the rest of Mark’s work on his site

Posted in Book Review | Tagged , | Comments closed

Returning flattened data from a SQL table containing pairs of rows

I like little challenges. The ones that don’t take all day to figure out, but are enough to capture your interest for a little while. Yesterday I had such a problem to solve.

I had some data in a table that was basically in ‘pairs’ of rows. It was actually different to the example below, but the example we’ll use is a ‘Message’ table, that contains requests and replies, that are linked through a particular identifier.

Our simple example looks like this (my actual table had more fields).

    MessageType CHAR(1) NOT NULL,
    TransactionID INT NOT NULL,
    MessageBody VARCHAR(30),
    CreatedDate DATETIME DEFAULT GetDate()

We’ll add a bit of sample data (script generated from my insert generator stored proc)


INSERT Message(MessageID,MessageType,TransactionID,MessageBody,CreatedDate) VALUES('1','Q','1','Request Message 1',convert(datetime,'2012-08-30 13:55:07.213',121))
INSERT Message(MessageID,MessageType,TransactionID,MessageBody,CreatedDate) VALUES('2','R','1','Reply Message 1',convert(datetime,'2012-08-30 13:55:37.680',121))
INSERT Message(MessageID,MessageType,TransactionID,MessageBody,CreatedDate) VALUES('3','Q','2','Request Message 2',convert(datetime,'2012-08-30 13:55:51.183',121))
INSERT Message(MessageID,MessageType,TransactionID,MessageBody,CreatedDate) VALUES('4','R','2','Reply Message 2',convert(datetime,'2012-08-30 13:56:04.020',121))



MessageID   MessageType TransactionID MessageBody                    CreatedDate
----------- ----------- ------------- ------------------------------ -----------------------
1           Q           1             Request Message 1              2012-08-30 13:55:07.213
2           R           1             Reply Message 1                2012-08-30 13:55:37.680
3           Q           2             Request Message 2              2012-08-30 13:55:51.183
4           R           2             Reply Message 2                2012-08-30 13:56:04.020

We can see that some of the fields are consistent from row to row (in pairs), and some of the fields are unique to each row. My challenge was to represent a pair of messages in one row.

On the face of it, this seems like it would be simple – just grouping by the TransactionID (the field that links the two rows). The problem is that you won’t be able to get the unique information from both rows without some assumptions (that may not be solid).

For example, this will happily give you the MessageID’s of both sides of the transaction (given the assumption that the request comes before the reply, and that there are two messages in a transaction) …

SELECT TransactionID, MIN(MessageID) AS RequestID, MAX(MessageID) AS ReplyID
FROM [Message]
GROUP BY TransactionID HAVING COUNT(*) = 2

TransactionID RequestID   ReplyID
------------- ----------- -----------
1             1           2
2             3           4

But – it’s doesn’t give you the unique data related to each ID, as you’d need to correlate the MessageBody to the right MessageID – MIN(MessageBody) won’t necessarily relate to the ‘Request’.

So… We can think about how to correlate the data to get the result we want. There’s a few options…

1. Use temporary tables, and get the result in two steps (reusing the query above)..


–1 – Two Step Process

SELECT TransactionID, MIN(MessageID) AS RequestID, MAX(MessageID) AS ReplyID
INTO #MessagePair
FROM [Message]
GROUP BY TransactionID HAVING COUNT(*) = 2

SELECT  REQ.MessageID AS RequestMessageID,
        REQ.MessageBody AS RequestBody,
        REQ.CreatedDate AS RequestDate,
        RPY.MessageID AS ReplyMessageID,
        RPY.MessageBody AS ReplyBody,
        RPY.CreatedDate AS ReplyDate
FROM #MessagePair MP
   ON REQ.MessageID = MP.RequestID
   ON RPY.MessageID = MP.ReplyID

RequestMessageID TransactionId RequestBody                    RequestDate             ReplyMessageID ReplyBody                      ReplyDate
---------------- ------------- ------------------------------ ----------------------- -------------- ------------------------------ -----------------------
1                1             Request Message 1              2012-08-30 13:55:07.213 2              Reply Message 1                2012-08-30 13:55:37.680
3                2             Request Message 2              2012-08-30 13:55:51.183 4              Reply Message 2                2012-08-30 13:56:04.020

2. Nasty correlated subquery and joins (not even going there)

3. Single query that makes use of the assmption that a request happens before a reply (meaning the messageId will be a lower value)

SELECT  REQ.MessageID AS RequestMessageID,
        REQ.MessageBody AS RequestBody,
        REQ.CreatedDate AS RequestDate,
        RPY.MessageID AS ReplyMessageID,
        RPY.MessageBody AS ReplyBody,
        RPY.CreatedDate AS ReplyDate
FROM [Message] REQ
    ON REQ.TransactionID = RPY.TransactionID
AND REQ.MessageID < RPY.MessageID

This produces the same result as above, and is what I ended up going with. I reckon there’s probably a few more potential viable solutions, so I’d be interested to see anyone’s alternative solutions.

Posted in Database, SQL Server | Tagged , | Comments closed

NDepend – Visual Studio Addin: takes you as far as you want to go

First of all I’d like to point out that I was kindly given a license by the folks at NDepend (not very often that sort of thing happens I can assure you!) and I’m under absolutely no obligation to write anything about it.

In the beginning…

The funny thing is that it was probably over a year ago when I first installed the product without any specific requirement or
expectation. I had a little play with it (on Visual Studio 2008 as I recall), then the work I ‘had’ to do overtook my will to learn this new product and it lay gathering dust on my hard drive.  This probably explains why I haven’t posted in all that time!

But then…

Recently, I picked up an existing project (on visual Studio 2010), and wanted to have a good look inside to see what I was getting myself into. I dusted off NDepend and told myself I’d give it a good go this time…

First Impressions

The first thing I learned is that this is one significant addin, and you realistically need to ‘know you need it’ before you get it (see ‘laying
dormant comment above’). This also means you need to know what it can do for you – which is plenty!

If you’re reading this and thinking of trialling NDepend, then you either have problems to solve or you’re wanting to invest in ongoing improvement to your code. Both are very good reasons as it happens.

NDepend has few limitations in what it can do, as it has your entire codebase, Visual Studio extensibility and its own powerful rules engine at its disposal. It also employs its own CQL (code query language), to allow you to find all sorts of patterns and complexity problems with your code.

The biggest problem is knowing where to start, or discovering that first task you want to achieve with it. It’s easy to get overwhelmed by the
information it bombards you with when you spin it up).

To be fair, there’s plenty of links trying to lead you to ‘what you’re looking at is…’

Reasons to try/buy

If you’re interested in the quality of your code I believe there really is no equal.  This is the tool you need. You may already be using FX Cop in your build process to check for certain snytactical rules, and ReSharper for sorting out your code as you go, but NDepend can do all sorts of ‘different’ funky stuff (through CQL) that goes in depth to your code to enforce things that would be otherwise difficult to do It can obviously do all the simple stuff like show you where your dependencies are between methods, classes and projects, and redundant code etc.

Some highlights I quite like – made possible through CQL:

  • Enforcing of layering constraints – i.e. ‘this UI project cannot directly reference ‘that’ ‘DAL’ project
  • Simple spot check stuff like queries on a ‘lines of code’ threshold – indicating complexity
  • Code not meeting an acceptable test coverage
  • For all the possibilities you’ll need to look here.

Things to be aware of

  • It’s a technical tool, and it’s easy to get a little overwhelmed with what it can do and where to start.
  • Time is needed to understand some of the concepts and power of the product.
  • You’ll need a beefy machine to avoid things slowing down with the addin loaded (I had to disable it for a while when I was using a solution with 60 projects as I was starting to experience memory issues).  If you don’t want to run it in Visual Studio, you can run it in the standalone ‘Visual NDepend’ application.
  • I’ll admit I haven’t spent a lot of time with the interactive reports, and I don’t find some of the graphical representations of the metrics that easy to use.
  • I think like most products, you get comfortable with what you see as valuable, and tend to only try other things when you have time.


Clearly NDepend’s a very impressive tool for any serious development team to be using. It will help you to learn about reducing complexity, dependencies and generally designing your code in an efficient way. It’s basically all about improving quality.

It’s also a big product that’s not for the faint hearted. You basically get out what you put in as far as effort in understanding what it’s trying to achieve for you.

I think the key is finding the right balance between all the technical information it presents, the time you have available, and the business benefit you’ll get from code improvements.

As I said at the start. It can basically take you as far as ‘you’ want to go.

Worth taking a look at:

Posted in Refactoring, Tools, Visual Studio | Tagged , , | Comments closed

LINQ Group by MAX Date Query

I’ve found some weird, wonderful and ridiculously complicated LINQ queries for getting the row with MAX(DATE) based on a key.  Most unnecessarily use lambda expressions, and some just had several interim steps.  I knew there had to be a better way, and found an unassuming post at the bottom of a StackOverflow page.

Here’s my non-lambda’d, contrived example…  assuming you’ve got an EntityFramework model (i.e. context)

            //Get Client Order with (max) order date 
            var maxclientOrder = (from clientOrder in context.ClientOrders
                               where clientOrder.OrderDate ==
                               (from clientOrder2 in context.ClientOrders
                                where clientOrder2.ClientID == clientOrder.ClientID
                                    select clientOrder2.OrderDate).Max()
                                select clientOrder).ToList();

Posted in C#, LINQ | Tagged , | Comments closed

Simple WPF Page Navigation From an MVVM ViewModel

Navigation in WPF is easy – unless of course you’re trying to apply an MVVM pattern.  Most examples tell you about all the great things you can do with MVVM/WPF whilst brushing such things as navigation under the carpet.  I’ve not found one person who’s adequately explained an MVVM example with ‘all’ of the facets you’ll need when writing a real application.

I’m not going to tell you how to implement full-scale configurable multi-context navigation using MVVM, but I’ll briefly discuss one approach to a nagging issue – that of triggering and controlling navigation from the ViewModel.  I’m also talking specifically about ‘pages’ here too, as I’m targeting a browser with this application.

WPF ‘Page’ objects expose a NavigationService property, which hooks into the WPF navigation framework.  This is very convenient and powerful.  MVVM effectively steers you away from doing anything behind your ‘views’, and tries to substitute the traditional coupling between view/controller/viewmodel (depending on the flavour) with reliance on data binding to give the viewmodel everything it needs to perform all the UI logic.

Your ViewModel isn’t supposed to have any reference to (or knowledge of) your view.  This means you won’t have a reference to the Page to be able to access its NavigationService.  There’s other ways to navigate, like using more of a frame appropach (most of the examples so this), but if you want to navigate ‘web-style’ from Page1 to Page2 to Page3 etc – controlling this from your ViewModel, what do you do?

After messing around with quite a number of approaches I’ve currently settled for a very simple technique that doesn’t feel ‘too’ dirty.  It became clear (in my case) that loading the ViewModel from the View is actually more appropriate and practical than loading the view from the ViewModel (through a DataTemplate mapping as others would suggest).  I found starting everything from the ViewModel paints you into something of a technical corner, as some core WPF functionality only exists at the view level.  You can of course write your own implementations, but I’ve always thought patterns are meant to ‘help’, and when they cease to help, you stop.

In our example, the Application object effectively sets things up by being the all-seeing eye on the navigation framework.

The code below simply sets the startup uri (from another library), and subscribes to the ‘navigated’ event, which will fire after every page movement.

    public partial class App : Application

        private static NavigationService navigator;

        protected override void OnStartup(StartupEventArgs e)
            this.StartupUri =              new Uri("pack://application:,,,/MyPageLibrary;component/MyStartupPage.xaml");

        void App_Navigated(object sender, NavigationEventArgs e)
            Page page = e.Content as Page;
            if (page != null)
                ApplicationHelper.NavigationService = page.NavigationService;


The ApplicationHelper class is a simple static implementation to provide the whole application with what is in effect a ‘bus’ service – the means to navigate, using the NavigationService injected from the Application.  I said this was simple.

    public static class ApplicationHelper
        private static NavigationService navigator;

        public static NavigationService NavigationService
                navigator = value;
                return navigator;



The ViewModel is then free to navigate whereever it likes (I’m constructing the pages as objects here with parameters to use in constructing the viewmodel, rather than using a uri).

    //Now navigate to the detail view    //Datacontext used to construct the ViewModel    MyNextPage nextPage = new MyNextPage(SomeDataContext);     ApplicationHelper.NavigationService.Navigate(nextPage);

I’m sure this will evolve again (like everything I’m finding with WPF), but for now this seems to perform my basic requirements

Posted in C#, WPF | Tagged , | Comments closed

Generate SQL Server Inserts from existing data – with Identity Inserts

A good while ago I posted a stored proc that would generate insert statements for table data, along with simple filtering capability.

I broke this out again today, as I needed to recreate part of a database on a local machine.  I didn’t have knowledge of the schema so I just went about fixing each constraint error and adding the reference tables as required to my script.  After manually adding ‘SET IDENTITY_INSERT xx’ about a dozen times I added the functionality to the Stored Proc – so if your table has an identity column it will now wrap the results in IDENTITY_INSERT statements, saving you a bit more time, and headaches.

An interesing side effect if you’re explicitly inserting identity values is that the order of your inserts may become pertinent – especially if you’ve got foreign keys referencing the same table.  I then just added the capability to ‘order by’.
There’s no automatic way to switch off column headers, so you’ll need to configure that in the Resutls Output options in Query Analyzer (or management studio).

If you run the proc into ‘Northwind’ and run:

usp_insertgenerator Employees, @order=’EmployeeID’

You’ll get…


INSERT Employees(EmployeeID,LastName,FirstName,Title,TitleOfCourtesy,BirthDate,HireDate,
  VALUES(’1′,’Davolio’,'Nancy’,'Sales Representative’,'Ms.’,convert(dateti
INSERT Employees(EmployeeID,LastName,FirstName,Title,TitleOfCourtesy,BirthDate,HireDate,
  VALUES(’2′,’Fuller’,'Andrew’,'Vice President, Sales’,'Dr.’,convert(datet
INSERT Employees(EmployeeID,LastName,FirstName,Title,TitleOfCourtesy,BirthDate,HireDate,
  VALUES(’3′,’Leverling’,'Janet’,'Sales Representative’,'Ms.’,convert(date
INSERT Employees(EmployeeID,LastName,FirstName,Title,TitleOfCourtesy,BirthDate,HireDate,
  VALUES(’4′,’Peacock’,'Margaret’,'Sales Representative’,'Mrs.’,convert(da
INSERT Employees(EmployeeID,LastName,FirstName,Title,TitleOfCourtesy,BirthDate,HireDate,
  VALUES(’5′,’Buchanan’,'Steven’,'Sales Manager’,'Mr.’,convert(datetime,’1
INSERT Employees(EmployeeID,LastName,FirstName,Title,TitleOfCourtesy,BirthDate,HireDate,
  VALUES(’6′,’Suyama’,'Michael’,'Sales Representative’,'Mr.’,convert(datet
INSERT Employees(EmployeeID,LastName,FirstName,Title,TitleOfCourtesy,BirthDate,HireDate,
  VALUES(’7′,’King’,'Robert’,'Sales Representative’,'Mr.’,convert(datetime
INSERT Employees(EmployeeID,LastName,FirstName,Title,TitleOfCourtesy,BirthDate,HireDate,
  VALUES(’8′,’Callahan’,'Laura’,'Inside Sales Coordinator’,'Ms.’,convert(d
INSERT Employees(EmployeeID,LastName,FirstName,Title,TitleOfCourtesy,BirthDate,HireDate,
  VALUES(’9′,’Dodsworth’,'Anne’,'Sales Representative’,'Ms.’,convert(datet


Here’s the script.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_InsertGenerator]') and
OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_InsertGenerator]

CREATE PROC dbo.usp_InsertGenerator
(@tableName varchar(100), @where varchar(1000) = NULL, @order varchar(1000) = NULL) as


--Check whether the table has an identity column
DECLARE @TableHasIdentityColumn BIT
SELECT @TableHasIdentityColumn = 1
where TABLE_SCHEMA = 'dbo'
and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
AND TABLE_NAME = @tableName

IF ISNULL(@TableHasIdentityColumn, 0) = 1
    PRINT 'SET IDENTITY_INSERT ' + @tableName + ' ON'

--Declare a cursor to retrieve column specific information for the specified table
SELECT column_name,data_type FROM information_schema.columns WHERE table_name = @tableName
OPEN cursCol
DECLARE @string nvarchar(3000) --for storing the first half of INSERT statement
DECLARE @stringData nvarchar(3000) --for storing the data (VALUES) related statement
DECLARE @dataType nvarchar(1000) --data types returned for respective columns
SET @string='INSERT '+@tableName+'('
SET @stringData=''

DECLARE @colName nvarchar(50)

FETCH NEXT FROM cursCol INTO @colName,@dataType

IF @@fetch_status<>0
 print 'Table '+@tableName+' not found, processing skipped.'
 close curscol
 deallocate curscol

IF @dataType in ('varchar','char','nchar','nvarchar')
 SET @stringData=@stringData+''''+'''+isnull('''''+'''''+'+ @colName+'+'''''+''''',''NULL'')+'',''+'
if @dataType in ('text','ntext') --if the datatype is text or something else 
 SET @stringData=@stringData+'''''''''+isnull(cast('+ @colName+' as varchar(2000)),'''')+'''''',''+'
IF @dataType = 'money' --because money doesn't get converted from varchar implicitly
 SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+ @colName+' as varchar(200)),''0.0000'')+''''''),''+'
IF @dataType='datetime'
 SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+ @colName+',121)+'''''+''''',''NULL'')+'',121),''+'
IF @dataType='image'
 SET @stringData=@stringData+'''''''''+isnull(cast(convert(varbinary,'+ @colName+') as varchar(6)),''0'')+'''''',''+'
ELSE --presuming the data type is int,bit,numeric,decimal 
 SET @stringData=@stringData+''''+'''+isnull('''''+'''''+convert(varchar(200),'+ @colName+')+'''''+''''',''NULL'')+'',''+'

SET @string=@string+@colName+','

FETCH NEXT FROM cursCol INTO @colName,@dataType
DECLARE @Query nvarchar(4000)

SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM '+@tableName
 SET @query = @query + ' where ' + @where
 SET @query = @query + ' order by ' + @order

exec sp_executesql @query

CLOSE cursCol

IF ISNULL(@TableHasIdentityColumn, 0) = 1
    PRINT 'SET IDENTITY_INSERT ' + @tableName + ' OFF'

Posted in Setup and Deployment, SQL Server, Tools | Tagged , | Comments closed

Use SQL Server Trusted Connections with ASP.NET on Windows 2003 without impersonation

Access control and troubleshooting 401 errors must be one of the most annoying and recurring issues with configuring IIS.  One of the problems is that it’s often quite a long time between issues, and you simply forget how you solved something last time.  This time I decided to write it all down as I set things up.

Target Scenario
My target scenario is a local intranet, where you want to use a ‘service account’ to access SQL Server, directly from your ‘trusted’ web application, removing the need for impersonation. 

The benefits of this are of course that you can take advantage of connection pooling, but also removing the need to configure passwords in web.config for SQL users (or specific, impersonated domain users).  This also removed the overhead of configuring specific domain users and their SQL Server permissions.  It may also be that you just want to simplify your security model to work only on Windows authentication across the stack.  

SQL Server

  1. Create a new role in the database you’re accessing, for the purposes of your application
  2. Add your service domain user account to the role in SQL Server
  3. Assign permissions to objects, stored procedures etc to the role (not directly to the user)

IIS/Web Site

  1. Set up your web site/application as you would normally – one way to do things….
    1. Create your web application root folder on the web server
    2. Copy your files (or use your deployment tools/scripts to do this)
    3. Create a new application pool to house your new web application (probably model this from the default web site).  This is important as this is where the credentials will be set
    4. Create the new IIS web application against the root folder (if not already done as part of step 2)
    5. Associate the new IIS application with the new application pool
    6. Set the ASP.NET version of your IIS application appropriate (may need to restart IIS here)
  2. Ensure ‘Integrated Security’ is set ON in the Directory Security tab, and ‘Anonymous’ access is switched OFF
  3. Set the application pool’s ‘identity’ to the domain user you want to run the application (and connect to SQL Server) as
  4. Open a command window and Go to the windows\Microsoft.NET\Framework\vXXXXX folder
    1. run aspnet_regiis -ga <domain>\<user> to grant necessary access to the metabase etc for the service account (as per )
    2. In the command window go to the inetpub\adminscripts folder, and set
      NTAuthenticationHeaders metabase property as per instructions at  you can also use MetaEdit from
      the IIS Resource Kit to change this.  If you’re fully configured to use Kerberos then you can potentially skip this step, as it’s all about making IIS use NTLM authentication.
  5. Navigate to ‘Web Service Extensions’ in IIS Manager, and ensure that the ASP.NET version you’re targeting is ‘allowed’.  e.g. ASP.NET 4.0 is ‘prohibited’ by default.

So here we’ve circumvented the need to use impersonation by running the ASP.NET application as a specific domain user that is configured as a SQL Server Login, and granted the right access by means of a SQL Server role.  The main work is the plumbing to get IIS to work happily with that user in standard NTLM authentication (you may be able to use Kerberos depending on your network configuration).

Other background on creating service accounts can be found at

Posted in ASP.NET, Setup and Deployment, SQL Server, Web | Tagged , | Comments closed