Tuesday, 20 December 2011

Deploying Databases with Object-Level Permissions

Cross-posted from Jason Lee's Blog

As I mentioned in my last post, we’re currently creating some guidance on deploying enterprise-scale applications. As we go along, I plan to blog about a few of the things that I find particularly tricky to figure out.
This time I want to look at database deployment. When you build a web application project in Visual Studio 2010, the Web Publishing Pipeline features allow you to hook into the IIS Web Deployment Tool (commonly known as “Web Deploy”) to package and optionally deploy your web application. As part of this process you can also deploy local databases to a target server environment. This is all nice and easy to configure through the project property pages in Visual Studio 2010, as shown below.

I don’t want to describe this process in any detail, you can find that elsewhere on the web (for example here).

Deploying databases in this way has advantages and disadvantages. On the plus side:
  • It’s easy.
  • It’s UI-driven.
  • It figures out most of the settings for you.
On the downside:
  • There’s no support for differential updates. In other words, the destination database is destroyed and recreated every time you deploy, so you’ll lose any data.
  • Some of the default database deployment settings are unsuitable for many real-world scenarios – this is the issue I want to focus on here.
In many cases, you’ll want to avoid the Web Deploy approach altogether and use VSDBCMD.exe to deploy and update your databases, but that’s a conversation for another day. In this post I want to focus on how you can change some of the default behaviours for database deployment using Web Deploy. In particular, Web Deploy omits object-level permissions by default. This causes problems if your database (a) contains stored procedures and (b) grants execute permissions on the stored procedures to database roles.

Suppose you’re using the Visual Studio 2010/Web Deploy approach to deploy an ASP.NET membership database from a local development machine to a destination server environment. (NB you’d typically only deploy a membership database if you’ve modified the schema, otherwise it’s easier just to run ASPNET_REGSQL.exe and create the database from scratch on the destination server). You opt for a full deployment, including schema and data from the source database. On the source database, you can see that various database roles are granted execute permissions on stored procedures:

However, when the database is recreated on the destination database server, these permissions are missing:

This can be mystifying at first—essentially, you add users to the built-in database roles such as aspnet_Membership_BasicAccess and aspnet_Membership_FullAccess, but membership of these roles has no effect. They’re basically just empty roles that aren’t mapped to any permissions.

The problem is that stored procedures are “objects” in database terms (don’t ask me, I’m not a DBA), and by default Web Deploy does not include object-level permissions when it scripts the database. To change this behaviour, you need to modify the project file for your web application project.

1. In the Solution Explorer window, right-click your web application project node, and then click Unload Project.

2. Right-click the project node again, and click Edit [project file].

3. Locate the PropertyGroup element that corresponds to your build configuration (for example Release|AnyCPU).

    Condition=" '$(Configuration)|$(Platform)' == 'Release|AnyCPU' ">

3. Within this element, locate the PreSource element and add a Permissions=”True” attribute, as shown below. The Permissions attribute indicates that the database script should include all permissions, including object-level permissions, which are defined in the source database.

    <ObjectGroup Name="ApplicationServices-Deployment" Order="1">
      <Destination Path="[Destination Database Connection String]" />
      <Object Type="dbFullSql">
        <PreSource Path="[Source Database Connection String]"
                   DriDefaults="True" />
        <Source Path="[Where to save a copy of the script]"
                Transacted="True" />

4. Save and close the project file.

There’s a whole host of settings you can add to the PreSource element to configure how your database is deployed. For example, if you want to deploy a database that already exists on the destination server, you need to add a ScriptDropsFirst=”True” attribute to the PreSource element – otherwise Web Deploy will complain that you’re trying to create objects that already exist. The full list of properties that you can set as PreSource attibutes can be tricky to track down unless you know how the database deployment process works:
  • Web Deploy uses the dbFullSql provider to deploy databases (the link includes some properties you can use as PreSource attributes).
  • Under the covers, the dbFullSql provider uses SQL Server Management Objects (SMO) to generate database scripts. The ScriptingOptions Properties page describes some SMO properties you can specify as PreSource attributes.

Alternatively, to get a full list of properties, you can run the following Web Deploy command:
msdeploy.exe –verb:sync –source:dbFullSql /?

There’s much more to database deployment than I can cover in a quick blog post, and we’ll cover these kinds of issues in much more detail when we publish to MSDN. For now though I hope this helps to shed some light on the intricacies of database deployment.

Deploying Web Packages as a Non-Administrator User

Cross-posted from Jason Lee's Blog

Regular readers (all six of you ;-)) will have noticed that I haven’t posted about SharePoint for a while. For the last couple of months I’ve been working with the Developer Guidance team at Microsoft to write some MSDN content on enterprise-scale web deployment and application lifecycle management. I’ll let you know when the content is available, and I don’t plan to duplicate it here. What I want to do is just to draw attention to a couple of areas that I found particularly tricky to figure out.

The first area involves the IIS Web Deployment Tool (commonly known as “Web Deploy”), and a gotcha around deploying web packages as a non-administrator user. For brevity I’ll have to assume that you’re broadly familiar with:

One of the big advantages of Web Deploy 2.0, on IIS 7 or later, is that non-administrator users can deploy web packages to specific IIS web sites. This is generally useful in two scenarios:

  • Hosted environments, where tenants need control over specific sites but do not have server-level administrator privileges.
  • Enterprise environments, where members of a development team may need to deploy specific sites but do not typically have server-level administrator privileges.

If you want to enable non-administrator users to deploy web packages, you need to configure the Web Deploy Handler on the target IIS web server. The other deployment approaches (the remote agent and the temp agent) don’t allow users who aren’t server administrators to deploy packages. I’ll assume that you’ve configured the Web Deployment Handler to allow a non-administrator user (FABRIKAM\User) to deploy content to a specific IIS website, as described here.

By default, the Web Deploy Handler exposes an HTTPS endpoint at the following address:

https://[server name]:8172/MSDeploy.axd

For example:


However, when a non-administrator user deploys a web package to the Web Deploy Handler, they need to add the IIS website name to the endpoint address as a query string:

https://[server name]:8172/MSDeploy.axd?site=[site name]

For example:


Why the difference? In a word, authorization. Your non-administrator user doesn’t have server-level access to IIS, they only have access to specific IIS websites. If they attempt to connect to the server-level endpoint, Web Deploy will an ERROR_USER_UNAUTHORIZED error. The event log on the destination server will show an IISWMSVC_AUTHORIZATION_SERVER_NOT_ALLOWED error like this:

So you’ve got to use the site query string. Now for the gotcha.

Due to an open bug in the current version of Web Deploy (2.1), you can’t specify a query string in the endpoint address if you use the .deploy.cmd file generated by Visual Studio to deploy your web package. In other words, this won’t work:

DemoProject.deploy.cmd /Y /M:https://TESTWEB1/MSDeploy.axd?site=DemoSite /U:FABRIKAM\User /P:Pa$$w0rd A/:Basic -allowUntrusted

I’ve seen some fairly bizarre “workarounds” for this—for example, drop the query string and use an administrator account—this works, but it kind of defeats the object when the whole point of the exercise was to use a non-administrator user to deploy the web package. What you need to do is to use Web Deploy (MSDeploy.exe) directly rather than running the .deploy.cmd file.

All the .deploy.cmd file contains is a bunch of parameterized Web Deploy commands. This is put together by the build process to take some of the work out of the deployment. For example, you don’t need to specify the location of the web package, the Web Deploy providers to use for the source and destination, the Web Deploy verb, or the location of the .SetParameters.xml file, because the .deploy.cmd file knows this already. However, there’s nothing to stop you using the raw Web Deploy commands directly. The easiest way to do this is to look at the output when you run the .deploy.cmd file – you’ll see the actual MSDeploy.exe commands written to the console window. You should see something like this (ignore the line breaks):


Run this command directly from the command line, using your non-administrator user credentials, and the deployment should succeed.

Packaging and deploying web applications is a fairly broad and complex topic, and I’ve had to gloss over many of the details in this blog post. The content we’re developing for MSDN will cover these kinds of issues in much more detail, and I’ll link to the content as soon as it’s available.

Thanks to Tom Dykstra at Microsoft for helping me troubleshoot the issue and pointing out the bug.