Setup and Deployment

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

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 ScenarioMy 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

Create a new role in the database you’re accessing, for the purposes of your application

Add your service domain user account to the role in SQL Server

Assign permissions to objects, stored procedures etc to the role (not directly to the user)

IIS/Web Site

Set up your web site/application as you would normally – one way to do things….

Create your web application root folder on the web server

Copy your files (or use your deployment tools/scripts to do this)

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

Create the new IIS web application against the root folder (if not already done as part of step 2)

Associate the new IIS application with the new application pool

Set the ASP.NET version of your IIS application appropriate (may need to restart IIS here)

Ensure ‘Integrated Security’ is set ON in the Directory Security tab, and ‘Anonymous’ access is switched OFF

Set the application pool’s ‘identity’ to the domain user you want to run the application (and connect to SQL Server) as

Open a command window and Go to the windows\Microsoft.NET\Framework\vXXXXX folder

In the command window go to the inetpub\adminscripts folder, and set
NTAuthenticationHeaders metabase property as per instructions athttp://support.microsoft.com/kb/326985. 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.

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.

SummarySo 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).

I’d added some projects to SubVersion today and later on added a CruiseControl.NET build. I then started getting build failures due to the following:

ASPNETCOMPILER The Target Directory could not be deleted. Please delete it manually, or choose a different target

After a bit of looking around with the SysInternals Process Monitor I couldn’t find anything weird (e.g access denied due to someone locking the folder) and then saw that my output folder from the project (ASP.NET Web Deployment Project) was under SubVersion control (below)

Whoops! – after a swift delete of the folder (also in SubVersion) normality was resumed. That’s another good reason not to put binaries into source control!

We start with Windows XP SP2 (doesn’t really need to be a ‘server’). My requirement is to rely on the .NET framework and open source tools – i.e. not requiring a Visual Studio Licence. We’re using .NET 2.0 so everything will be in reference to that.

I assume here that you’ll be familiar with the actual software below and just want to get a Build Server up and running without having to install Visual Studio. If you’re not familiar with Continuous Integration then start by looking at Martin Fowler’s Continuous Integration article and then the info on CruiseControl.NET, as that’s the tool that pulls everything together.

There’s help on each of the sites below for installing and using each of the tools, so I won’t go into detail about each one. The order of the list isn’t critical, but you’ll probably have some issues unless all are done.

Install IIS from Add/Remove Windows Components (used for CruiseControl.NET) if not already present

Open up a command prompt and run C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis.exe -i (to install ASP.NET in IIS)

Install CruiseControl.NET (1.4) from http://confluence.public.thoughtworks.org/display/CCNET/Download. NOTE: If you’re using a remote SubVersion repository (as in my case) then you’ll need to run the CruiseControl.NET Service as a domain user that has access to the network location of the repository rather than LocalSystem. This is because CruiseControl will be using the SVN client to poll for changes.

To save getting errors when building Web Projects (and other types) the easiest thing is to copy c:\program files\msbuild from your dev machine to the build server (otherwise you’ll have to alter the path of where the targets are in each project).

Once you’ve got all of these set up then you’ll be able to add some builds to your ccnet.config file ( most likely c:\program files\CruiseControl.NET\server\ccnet.config)

Test CruiseControl’s happy by going to http://localhost/ccnet It should come up OK but basically show ‘no projects’.

I’m not going to go into detail about setting up the builds here but may cover that in a follow up article, as it requires some more setup with SVN and a ‘standard’ project structure (to get benefit from reuse of build scripts). If you’re still with me then I’ll share one last thing which might help…

Some people choose to have NAnt in a standard place and just reference it from the PATH, but I now use the power of svn:externals to drag in NAnt, NUnit and other common external dependencies like MS Enterprise Library from a shared SVN location to each project. This means you just get latest of a project and it has ‘everything’ needed to build – no installations or assumptions about locations of tools.

Just spent a little while looking into this. I thought I’d be clever and add my build script to the solution it builds so I can get some intellisense from Resharper (although it’s somewhat limited). This added the file into ‘Solution Items’ which is a virtual folder.

This is one from the archives that obviously got away. I had need for this today so thought I’d post so I don’t have to look so hard next time!

It’s pretty much a case of create the SP in your chosen database and run with a tablename as the parameter. I originally found this on one of the SQL sites (probably SQLServerCentral), and amended it slightly to add some basic filtering in the @where parameter.

There’s a few limitations from memory – with blob, image and text fields, but for your average tables this will probably do the trick just fine.

I’ve also used a different method that basically builds up a single INSERT with SELECT ‘literal values’ UNION SELECT ‘literal values’. That works fine and is potentially easier to control transaction-wise as everthing’s in a single statement. Anyway here’s the code….

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

SET @string=@string+@colName+‘,’

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

In my ongoing love (but mostly) hate relationship with ASP.NET Web ‘Site’s’ I’ve been using Web Deployment projects to make things more bearable.

I currently swap in connection strings from 3 files – one for each build configuration (debug, test, release – connectionstrings.debug.config etc ). This works fine as per the doco on WDP. I use CruiseControl.NET and NAnt to automate builds, and a few nagging ‘automated’ pieces were missing from the puzzle.

Encryption of connectionStrings (or other web.config sections that you want to protect) – without affecting the ‘source’ file. I’ve assumed here that ‘internal’ people are trusted.

Changing of other config stuff (like debug=false) in the test and release builds. (My attempts to get this to work had previously failed as you don’t seem to be able to specify system.web as a replaceable section.

Encrypting Forms authentication passwords, using MD5 hash. This isn’t difficult, I just didn’t have a tool to generate the hash value.

If I put this anywhere other than ‘AfterBuild’ it didn’t seem to do anything. I certainly learnt a bit more about aspnet_regiis, as I’d only used it previously to ‘install’ ASP.NET. I also had to specify the path to aspnet_regiis, but you could obviously use a property for this (I’m new to MSBuild – only dipping in when I have to, so the framework path may already be a standard property?).

Replacement of system.web sections

The key thing here (which I don’t believe was documented very well anywhere) is how to replace system.web elements. Other typical replacements – e.g. appSettings or connectionStrings are children of the root config element. You’d therefore assume that you need to replace the whole of system.web (which is a little inconvenient – but still worth it). This doesn’t work and you’ll get a ‘nice’ WDP00002 error saying it can’t find the system.web element (a bit like saying ‘can’t find printer’ when it’s right next to the computer!).

You just have to go one level down as follows (in the Deployment –> Web.config file section replacements property page):

system.web/compilation=compilation.release.config

compilation.release.config may be as simple as…

<compilation debug=”false”></compilation>

You might have a warning saying ‘compilation’ isn’t a valid element, but this is just the intellisense barking as it validates against the config schema.

Encrypting Forms Authentication Passwords

This is pretty simple and there’s lots of docs to support this, but I wanted a simple tool to generate the hash for a given string, and a quick google yielded a nice little command-line tool…

This way you can plug it into your build if you need to, but also replace for different environments using the techniques above.

I seem to be getting back to basics at the moment with some of the things I’m doing – and didn’t imagine myself having to get an automated FTP update working this week…

My objective was to ‘get’ files from a remote machine using a folder pattern consisting of the date in an ‘yymmdd’ format. I’d forgotten that script files used with ftp (ftp -s:myscript.txt) are dumb text files, and if you want to ‘get’ from a dynamic location – or dynamic filenames you need to magic something up.

My interest was sparked here, and although I found a C# FTP library on CodeProject that would clearly do the job, and would be cool to use in itself, I was more interested in using ‘old skool’ lo-tech methods to solve the problem.

I read one article that suggested using batch files to spit out the ftp script. Excellent idea I thought – so this is what I ended up with…. (I’ve changed a few details to protect the innocent so if it doesn’t quite work it’s only due to my own typo 🙂 )

I won’t explain each bit as that would spoil the fun – but the general gist is that FTPReportsForDate is normally run without parameters and it finds files in a specific folder and renames them on the local host. The process also does other stuff I didn’t have time to go into….

FTPReportsForDate.cmd (which accepts a parameter or gets current date if not supplied)

This is a really simple one, but if you want to build a solution in a specific ‘Configuration’ and you have a big solution open, it can sometimes be an awfully long time to switch configurations – e.g. Debug -> Release. You may find yourself needing to do this when you’re needing to build and rebuild setup projects whilst testing.

You could of course run a command line compile, but a simple way to stay in the IDE and Debug configuration whilst running a Release build is the Batch Build facility.

Just go to Build –> Batch Build, then select all the Release versions of the projects you want to build (or whatever you want to build), and then click Build. The settings will be remembered for the next time you go in too…