John C. Kirk (johnckirk) wrote,
John C. Kirk


I've been looking at OpenID, and I'd like to start using it on one of my websites. In technical terms, I would be an RP (Relying Party), accepting logins from OPs (OpenID Providers). I've decided to use DotNetOpenAuth for this (formerly known as DotNetOpenID), so I've been trying to get some sample code working. This has turned out to be surprisingly tricky, and it's taken me a few days to figure it all out, so I've documented the process; hopefully this will be useful to anyone else who's in the same situation.

Getting into a bit more detail, I'm using the "Express" versions of Visual Studio and SQL Server at home, i.e. the free versions. This has wound up with me chasing my tail a bit, particularly when you compare them to the paid versions. At work, I'm using the Visual Studio 2008 Team System; when I came to install SQL 2008 Developer Edition it complained because I didn't have SP1 installed for VS 2008. (Normally I'm up to date with patches, but for some reason this service pack wasn't distributed through Windows Update, so I had to download it separately.) Anyway, that was simple enough to resolve: I installed the service pack, then installed SQL. At home, I already had Visual Basic 2008 Express (including SP1) and SQL Server 2005 Express. To do web development, I needed Visual Web Developer 2008 Express, but when I tried to install that it complained about SQL 2005, so I had to upgrade that to SQL 2008 Express first. It all worked out ok, but it amuses me that you need the latest version of Visual Studio to install SQL Server and you need the latest version of SQL Server to install Visual Studio!

Anyway, I then downloaded DotNetOpenAuth from this page. There are actually three downloads listed on that page: "DotNetOpenAuth", "DotNetOpenAuth project", and "DotNetOpenAuth-Tools". I assumed that the project would be sample code, so I downloaded it and unzipped it, but then it wouldn't build; it kept complaining that $safeprojectname$ wasn't defined. Later on, I came across this blog post (written by the guy who developed the software), which explained a few things: this is actually a project template. This is similar to the difference between a Word template and a Word document: you copy the template as a starting point for your own project, then it will replace $safeprojectname$ with the project name you specified. So, I shouldn't have unzipped it; instead, it needed to be copied to my profile folder. In my case, that's:
C:\Users\John\Documents\Visual Studio 2008\Templates\ProjectTemplates\Visual Web Developer
This was my mistake, but I think it would be more intuitive to use a vsi file to publish the template, as described at MSDN.

Once I'd sorted that out, I created my own project based on the template, but then I hit another snag. As I mentioned above, I had to upgrade SQL 2005 to SQL 2008 before I could install Visual Web Developer. However, this project has a couple of references to SQL 2005 assemblies, so I had to replace them with the corresponding SQL 2008 assemblies:
It also then needed an additional reference:

(I left a comment with this info on the blog post, but it hasn't appeared yet, so I'm putting it here so that it's somewhere public.)

I could then build the application and run it, using the ASP.NET Development Server (i.e. the web server that's built into Visual Web Developer). This displayed the setup page, prompting me to create a database:

Setup page

However, when I entered my LiveJournal OpenID, I got an error:

Login failed: No OpenID endpoint found.

The basic idea of OpenID is that you get bounced back and forth between websites. For instance, suppose that you want to log in with your LiveJournal account: my website will redirect you to LiveJournal, they get your password (if you're not automatically logged in), then they redirect you back to my site. The key point is that you should only give your LiveJournal password to LiveJournal, not to me. Similarly, you only give your Google password to Google, not to me. I don't need to know it, and I don't want to know it! All I need to know is that Site X has vouched for you. (As I've mentioned before, I don't like the Facebook method, i.e. "Give us your password for other websites, then we'll log in as you.")

Anyway, that means that the test website has to be publically accessible, in order for the OP to send you back to it. This makes it a bit more tricky to do development on my desktop PC, so I've copied the test app over to my server. There are ways around this, particularly if you have spare public IP addresses, but forwarding traffic makes it harder to work out what's not working if there are any problems. Also, I'll need to sort out the server configuration eventually, so I may as well get it over and done with. (I'm using IIS 6 on Windows 2003; the instructions will probably be slightly different for IIS 7 on Windows 2008.) This mean that the server will need to have SQL 2008 Express installed too, using the "SQLEXPRESS" named instance. I also recommend installing Management Studio Express on both machines.

So, I ran IIS Manager, and created a virtual directory for this application. The alias can be anything you like: I chose "WebRPApplication1", to match the project name. The path should be an existing folder on the server, where you've copied the entire project; I prefer to put this on a separate drive, rather the C drive. When you assign permissions, tick the boxes for "Read" and "Execute". Once IIS has created the new virtual directory, go to its properties, and look at the "Virtual Directory" page. Change "Execute permissions" from "Scripts and Executables" to "Scripts only", and click "Apply". The property page should then look like this:

Virtual Directory property page

I then used my web browser to connect to this new website, but I got an error instead of the setup page:
"Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed."

So, it failed due to a failure. Gosh, thanks, that's very helpful! Digging on the web, this seems to be quite a common problem, and various people have suggested solutions. However, I think that some of these suggestions are actually quite dangerous, so proceed with caution!

For instance, this page says that you should change the credentials of the SQL Server service to run as "Local System". That means that it will have full control over your computer, so it goes against the principle of least privilege. (Digressing a bit, this is why the Slammer worm did so much damage: if someone could connect to the SQL Server and run xp_cmdshell, they could execute any code that they liked on that machine with full admin rights.) Quoting from the MSDN article Security Considerations for a SQL Server Installation: "Run separate SQL Server services under separate Windows accounts. Whenever possible, use separate, low-rights Windows or Local user accounts for each SQL Server service." That's the way I do it: I have various "service" accounts in my domain, e.g. "Service-SQL", and they only get granted the permissions that they actually need. There's a similar issue in this discussion, where Dennis Alzoubi suggests changing the IIS application pool to run as "Local System"; if someone was able to exploit a bug in IIS (e.g. a buffer overrun), they would be able to seize full control of your server, which is bad. So, I think it's useful to actually understand what's going on.

When you connect to the website, IIS will try to load the page you requested, or the default document (Default.aspx) if you just specified the folder name. All of the webpages are based on site.master, which includes your "logged in" status. It achieves this by referring to Global.DataContext, which accesses the Global.asax.cs file. The "DataContext" property procedure in there includes the line:
dataContext = new DatabaseEntities();
If it can connect to the database, great, and then you'll see the relevant page. If not, it redirects you to Setup.aspx, and that prompts you to create a new database, as seen earlier.

"DatabaseEntities" is defined in the web.config file:

<add name="DatabaseEntities" connectionString="metadata=res://*/Model.csdl|res://*/Model.ssdl|res://*/Model.msl;provider=System.Data.SqlClient;provider connection string="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True;MultipleActiveResultSets=True"" providerName="System.Data.EntityClient" />

I've put the most important part in bold. Integrated Security means that the code in this application will connect to the SQL Server database using the Windows username and password rather than explicitly providing credentials.

Quoting from MSDN: "When you run a page using the ASP.NET Development Server, the page runs in the context of your current user account." So, when I ran the website locally on my PC, it connected to the local instance of SQL Server as me, and my account has the "sysadmin" server role on that instance. However, IIS does it a bit differently. Each application is linked to an application pool; in this case I've left it as "DefaultAppPool". If you look at the list of application pools, then go to their property pages and look at the "Identity" tab, each pool uses a particular security account. Here's what it looks like for DefaultAppPool on my server:

DefaultAppPool Identity

So, this application pool runs as "Network Service". Using SQL Server Management Studio, I've confirmed that there is a login for this account, which has the "public" server role:

Server Roles

Looking back at the connection string, it also says:

|DataDirectory| is a bit like an environment variable, and it gets replaced by the path to the "App_Data" subfolder at runtime. In my case, that's:
This folder is initially empty, but the application will create a database in there. In fact, it will create two files: Database.mdf and Database_log.LDF. So, that means that it needs the appropriate folder permissions, which it probably doesn't have by default. As I said, I'm using "Network Service" for this application's pool, so I've given it Full Control over that specific folder. (Modify isn't enough, because it changes the permissions for the files that it creates.) Again, this wasn't a problem on my PC because I created the project in my source code folder, so I have all the necessary permissions.

That might be all you need to do, but I still got the same error message on my server, so there was an extra step involved.

The other important part of the connection string was:
"User Instance=True;"

User instances were introduced in SQL 2005, and there are a couple of articles about them in the MSDN library:
SQL Server 2005 Express Edition User Instances
User Instances for Non-Administrators (SQL 2008)

The SQL 2008 article explains how to enable/disable user instances; they were already enabled on my server (possibly because I'd upgraded from 2005), but you might find that they're disabled by default on a brand new installation.

The basic idea is that you have a "parent instance" (in this case SQLEXPRESS), and then this can generate a new instance for each user, which runs under their credentials. Normally you need admin privileges to create a new database, but each user is an admin on their own instance. The trade-off is that these databases are specific to each user, so nobody else can connect to them, but that's not really a problem here because all the people who want to use this database will use the same website, so they'll effectively be sharing an account (in terms of database access).

Normally, each instance of SQL server has a set of system databases, which are stored in an appropriate location, e.g.
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
These databases are master, model, msdb, and tempdb, and each database has two files (data and log). In particular, the master database has a list of all the other databases, so you need to update it each time you add a new database to the instance.

In order for user instances to work, each user needs to have their own set of system databases, and these are stored in each user's profile folder. In particular, for "Network Service", the files are stored here:
C:\Documents and Settings\NetworkService\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS

Since I upgraded from SQL 2005 to SQL 2008, I had some old versions of these files left over, which didn't work properly. The solution was to delete this folder, then it would automatically get recreated. That's a bit easier said than done, because the files were in use. In the end, I wound up stopping most of the services on the server, including SQL and IIS, and killing a process via Task Manager. I then had to reboot the server to get rid of the error message; just restarting SQL and IIS wasn't enough.

The first time I tried to access the website after the reboot, I had to refresh the page a few times before it worked. Looking at the log, it has to copy files across and then apply various updates, so it can take a few minutes, particularly on an older machine. (My server has two Pentium 3 800Mhz CPUs, which is pretty puny by modern standards!) Anyway, I eventually got the setup screen, prompting me for an OpenID login.

Woo! This took a while, but I'd now reached the same point I was at when I hit F5 in Visual Web Developer. However, the story isn't quite over. I typed in my LiveJournal ID, and clicked the "Create database" button, but then I got a different error:
"CREATE DATABASE permission denied in database 'master'."

This was a bit strange, because the whole point of user instances is to give the user full control. The solution here involved digging through the source code in a bit more detail, in particular the Setup.aspx.cs file. The "CreateDatabase" method doesn't use the connection string from web.config; instead, it establishes its own connection to ".\\sqlexpress", uses the Admin/CreateDatabase.sql file to create a new database in the relevant location ("~/App_Data/Database.mdf"), then detaches this new database. In other words, it isn't using the user instance! Instead, it's using the main "SQLEXPRESS" instance to create the database, then detaching it so that the files can be reattached to the user instance. This means that "Network Service" needs the "sysadmin" server role, at least temporarily.
NB Assigning the "dbcreator" role isn't sufficient; that allows the application to create the database, but not to detach it afterwards, so then you wind up with a whole bunch of other errors.

So, I assigned the extra server role, tried the web page again, and this time it reported success:

Database initialized

However, when I clicked through to the home page, it timed out. So, don't delete the Setup files just yet!

I tried to reload the main page, but unfortunately this took me back to the setup screen again, i.e. the application seemed to think that it needed to create a database, so it didn't "recognise" the one it had just created. I had a look at the error logfile on the server:
C:\Documents and Settings\NetworkService\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\error.log
This had the same pair of lines repeated several times, once for each time I'd loaded a webpage:
2009-11-21 18:55:21.27 Logon Error: 15350, Severity: 16, State: 1.
2009-11-21 18:55:21.27 Logon An attempt to attach an auto-named database for file D:\IIS\WebRPApplication1\App_Data\Database.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

I knew that the file existed, and that it wasn't on a UNC share. I deleted the SQLEXPRESS profile folder again, just to make sure that I was starting out with a "blank" copy of the master database when it was recreated, but the error persisted. Eventually I figured out that the problem was folder permissions. Although I'd given "NETWORK SERVICE" Full Control over the "App_Data" folder, and it had Full Control over the two new database files, it couldn't actually work its way down the tree to get that far. So, the solution was to give that account read-only permission to the intermediate folders:
I recommend editing the permissions so that they apply to "This folder only" rather than "This folder, subfolders, and files" (the default). That way, you reduce the risk of accidental exposure.

After that, I could actually view the home page properly:

Home page

You can now delete the three setup files from the server:

Also, this would be a good time to remove the "sysadmin" server role from the "NETWORK SERVICE" login.

In the long run, I'll probably use a "permanent" database instead of user instances. However, this has been instructive (if frustrating), since it's forced me to learn more about the underlying technologies in order to solve the problems that occurred.

Back in my web browser, I can now login to the website using my LiveJournal account (which is already stored in the database). I then get this screen:

Identity Validation

I'm surprised that I didn't get this screen before, when I created the database. That step failed on my local PC, which implies that the test website needed to be able to communicate with the LiveJournal server. Ah well, no matter. I clicked "Yes; always.", and it took me back to the test site:

Members Only area

Note that it's displaying my OpenID in the top-right corner. In this case it's the same thing that I typed in, but if I log in with Google/Yahoo then I get a long alphanumeric string that bears no relation to my account name. Anyway, this OpenID is what I need to store in the database to recognise people.

If I log out, and log back in, I still have to type in my LiveJournal OpenID, but it no longer (visibly) redirects me: it just takes me straight in without needing a password. You can control this behaviour the first time you log in, but I can see it being quite convenient.

If you'd like to play with the site, it's here:
It doesn't work properly with Yahoo! at the moment (I'll elaborate on that in my next post), but other OpenIDs should be ok, so please let me know if you find any that don't work.
Tags: iis, openid, sql

  • Full moon swims

    As I've mentioned before, I like outdoor swimming when the sun's out: the water looks very inviting when I can see the sunlight reflecting off it.…

  • UK CWSC 2015

    A few weeks ago (Sat 24th January), I took part in the UK Cold Water Swimming Championships at Tooting Bec Lido. This runs every 2 years…

  • 2013/2014 in review

    I didn't get round to doing a review post last year, so here's a bumper double-edition. (Hey, if it's good enough for TV guides then it's good enough…

  • Post a new comment


    Anonymous comments are disabled in this journal

    default userpic

    Your reply will be screened

    Your IP address will be recorded 

  • 1 comment