Skip to content

SharePoint 2010, Claims-based authentication and delegation

This is just a short walkthrough on how to configure impersonation/delegation for a WCF service hosted inside SharePoint 2010. The site uses claims based-authentication. Our goal is to make a service call from a SharePoint-hosted page (or Control or WebPart) to a WCF service, and to impersonate the identity of the original caller in the WCF service.

(We will do some of the steps manually where prebuilt stuff exists just for the sake of better understanding)

Create the wcf service

… this is just your ordinary WCF [ServiceContract] stuff. Just hack the interface and service implementation together.


Create the svc file and configure the Factory

We will configure the .svc file using the well-known Service-attribute (which points to our implementation of the service). Additionally, we will use the Factory-attribute to configure a custom-built factory (we also could as well go with one of the prebuilt factories which ship in the SharePoint-assemblies).

.svc file


ServiceHostFactory and ServiceHost

The factory is required and will configure the service host for Claims authentication when the service is activated. This is done using a call to SPIisWebServiceApplication.ConfigureServiceHost(appHost, SPServiceAuthenticationMode.Claims);


Deploy .svc to anonsvc-folder

The .svc file will be deployed to the _vti_bin/anonsvc folder in SharePoint 2010, as I want to have a web service which runs in the context of a SharePoint site. The _vti_bin folder will be mapped by SharePoint under the Site collections just like the _layouts folder.

The anonsvc folder maps to {SharePointRoot}\ISAPI\anonsvc, which will be surfaced as _vti_bin/anonsvc.


Configure the bindings for the service

I will use a customBinding which works with http. For https, just duplicate the endpoint and reference a bindingConfiguration with httpsTransport instead of httpTransport. The authenticationMode attribute under the security-element will be configured using the IssuedTokenOverTransport value. The transport uses the authenticationScheme “Anonymous”.


Create the client.config under SharePoints WebClients-folder …

…. and make sure it has matching bindings.  For the WCF client, we will create a folder under the WebClients and put a client.config there. The client.config contains configuration information for the WCF client/ChannelFactory.

Create the client code to call the service.

I always use the ChannelFactory model instead of using the generated clients. The first step here is to load the client configuration …


With the client configuration from the client.config file, which contains the WCF serviceModel, client and binding information, we fire up the ChannelFactory. We use ConfigurationChannelFactory<T> which we can pass a reference to the serviceModel-configuration from the client.config file.

Two things are of note here … first of all, we configure the ChannelFactory to use Claims authentication (ConfigureCredentials is an extension method from SharePoint-land, SPChannelFactoryOperations).

Then we create the Channel using the SPChannelFactoryOperations.CreateChannelActingAsLoggedOnUser (which will call ChannelFactoryOperations.CreateChannelWithIssuedToken from the Microsoft.IdentityModel WSTrust extensions.)


Authentication? Why anonymous? And pay attention to the anonsvc-folder …

The authentication process with IIS, ASP.NET, and WCF can be configured in many different ways, and it is important to understand which component performs authentication and authorization. When hosting a claims-based service, the WCF component must perform the authentication, and as we say “den letzten beißen die Hunde”. Since the WCF service is hosted in IIS (and in our case, SharePoints SPRequestModule class plays a heavy role), we need to make sure the message does in fact reach the WCF stack. Since the WCF stack is hosted in IIS (IIS sees the message before it gets to WCF) and in case for the _vti_bin-Folder, SharePoint also processes the request before it gets to the WCF-stack, we need to make sure that neither IIS nor SharePoint generate an authentication or authorization error. If either IIS or SharePoint generate an error, WCF would never see the request and hence could not perform any authentication on it.

For IIS, we need to enable Anonymous authentication (and disable Windows/Basic/Forms) on the application or virtual directory. When deploying to SharePoints _vti_bin folder, this is already set up correctly.

For SharePoint and services hosted under the _vti_bin-Folder, the only way to truly get anonymous access is to put them under the anonsvc-Folder. This path is hardcoded in the SharePoint request processing pipeline. In case you don’t put the service under the anonsvc folder, you will likely see HTTP 401 Unauhorized errors. The 401 is part of the challenge process for both NTLM and Kerberos authentication, so all SharePoint tries to say here is that it wants the client to authenticate using either NTLM or Kerberos (depending on how the server is configured) and sends its part of the authentication handshake back to the client. So it merely asks the client to pass the authentication information in the next request.

In case you are debugging things like 401 error codes with IIS, ASP.NET and WCF involved, check out the fabulous Failed Request Tracing in IIS. Dunno why it’s called Failed Request Tracing though;)

Posted in SharePoint, SharePoint 2010.

Tagged with , , , , , , , , , , .

SharePoint 2010 Service Applications

David Taylor from Microsoft has a very good series on how to build Service Applications for SharePoint 2010 in his blog. When trying to get a better overview about this topic, the other good option is to look at the code Microsoft ships with reflector. Besides some stuff (for example the Claims based authentication model with WIF) the new architecture is quite straightforward. I have been mostly looking at Word Automation Services … unfortunately as it looks, there are next to none extensibility hooks. And the implementation which ships is quite bare-metal …

Posted in SharePoint 2010.

Tagged with , , .

Visual Studio 2010 Pro Power Tools: Must-have download

Download the Visual Studio 2010 Pro Power Tools from here. This is a must-have extension for VS2010. The default tabbing behavior in VS 2010 is shite suboptimal, as it was in 2008. And in 2005. (can’t remember, but probably: and in 2003. And in 2002). And the registry hack for 2005/2008 doesn’t work in 2010 (google “visual studio tab ears steam” if you still use 2008 or 2005. My condolences). And this fixes it a bit.

It also fixes the Add Reference dialog (broken ever since it existed), but I don’t know whether I like that. Would probably be too good to be true …


Now, please Microsoft build a Marketing/Product Management Power Tool which gets rid of these ridiculously looooooooooooooooong product names. Visual Studio Team System 2008 Database Edition General Distribution Release, anyone?

Posted in Uncategorized.

Psscor2.dll (superset of SOS.dll) extension for WinDbg available

If you have ever used WinDbg to debug managed processes or dumps, you certainly know the Son of Strike sos.dll extension.

Microsoft (finally) released a more feature rich extension for Windbg, psscor2.dll. See here and here.

Download from here.

Nice work!

Posted in Productivity, Utilities.

Tagged with , , , , .

Fun with expression trees (.NET v4 RC)

I had some fun with writing unit tests for APM style calls the other day, got interested in the new expression tree-features in .NET v4, and this is basically the fallout.

The task is simple: for a delegate instance of type TDelegate create a new delegate of type TDelegate, which calls the original delegate plus does some other stuff (in my case adding/wrapping the delegate call with exception handling code). The method should be able to work on delegates of any given type (System.AsyncCallback, System.Action<string>, System.Func<string, out int> …), and should treat input, output and return values properly. Note that this is unit testing code so we will not pay a lot of attention on the performance (which will suffer a bit).

In v4, courtesy to the integration of the Dynamic Language Runtime into the framework, expression trees have been pimped quite a bit. For example, you can now create full method bodies.

We want to create a method which takes a delegate instance as parameter and returns a new delegate instance of the same type. Hence we say


(Delegates are always classes.)

From the delegate passed to our method, we query the parameters and convert them to ParameterExpression using Expression.Parameter (the second parameter _.Name is somewhat optional and just for debugging purposes). This allows us to access the parameters in the expression tree and pass as parameters when we invoke the original delegate.


We want to declare a try/catch/finally block and store the Exception we catch in the catch clause. We declare a ParameterExpression to store the exception


Next, we create the expression tree for a try/catch/finally-block (some Expression.Block are redundant … I have other code in there which I removed from the sample).

Please note that the method is declared as Expression.TryCatchFinally(Expression body, Expression finally, params CatchBlock[] catch), so the finally comes before the catch handlers.


Code does next-to-nothing:

  • In the try-block, we invoke the original delegate (which has been passed as a parameter to our method), and we reference the array of ParameterExpression we created in step 2.
  • In the finally-block, we invoke an instance method using a MethodCallExpression mcexpDecrement. This method just performs some internal cleanup
  • The catch block references the variable we declared in step 3. It will then call a instance method in the calss using a MethodCallExpression mcexpStore, which takes a single parameter of type Exception, and is passed the variable we declared, xcptnParameterDef. Hence, we pass the Exception instance we just caught (and swallowed) to another method in our class.
  • At the end of the catch-block, using the Expression.Default(…), we return a value from our method. In case our delegate is of type AsyncCallback, its ReturnType would be void. In case our delegate if of type Func<string, out int>, Expression.Default(inputDel.Method.ReturnType) would translate to return default(int) in C#.


In the last step, we create a lamdba expression. You can create lambda expressions from delegate types (inputDel.GetType() will return System.AsyncCallback in our sample). As the second parameter we pass the try/catch/finally-block as body of the lambda expression. We give it a random name, and pass the array of ParameterExpression we created in step 2 as the last parameter.

In the second step, we compile the lambda expression to a delegate. Since we created the lambda expression based on our delegate type T (in our sample, AsyncCallback), we can cast it using “as T” syntax back to the delegate type passed to our method.

The expression tree for the lambda method will look similar to this in the debug viewer

.Lambda Fooo<System.AsyncCallback>(System.IAsyncResult $asyncResult) {
    .Try {
        .Block() {
            .Invoke .Constant<System.AsyncCallback>(System.AsyncCallback)($asyncResult) // this will invoke our “logic” for the callback
    } .Catch (System.Exception $xcptn) {
        .Block() {
            .Call .Constant<VisualStudio.TestTools.UnitTesting.Extensions.TestExceptionManager>(VisualStudio.TestTools.UnitTesting.Extensions.TestExceptionManager).StoreException($xcptn)
    } .Finally {
        .Block() {
            .Call .Constant<VisualStudio.TestTools.UnitTesting.Extensions.TestExceptionManager>(VisualStudio.TestTools.UnitTesting.Extensions.TestExceptionManager).DecrementCounter()

We can use the code with delegates of any type. Also works with multicast delegates.


Posted in .NET 4.0, Productivity, Testing, Utilities.

Tagged with , , , , .

Unit testing async calls, multithreading, and rewriting delegates with expression trees

I am currently developing extensions for WCF. When you extend WCF, for example by writing a custom transport, you will find yourself writing a lot of APM-style methods (BeginXxx, EndXxx) in C#. Now, since I am also writing a lot of unit tests to test these async implementations, I run into the issue that multi-threaded tests are really not supported well in Visual Studio. The default Visual Studio test host will only handle exceptions/Assert.Xxx-calls on the main thread.

So when doing an Assert-Call on a method/delegate/lambda you pass as a callback to a BeginXxx-method, this code will execute as a callback on a different thread. If the Assert gets executed and fails (a failed call to Assert.Xxx raises an exception), the test will not report the failed assertion properly. An [ExpectedException(…)] attribute on your test will not work either. In the best case, the test will result in an error … other cases include the test host process crashing. The test won’t terminate properly though.

The best/most straightforward option you have to circumvent this is to catch any exception from a background/callback thread, and rethrow it on the main thread.


For example if you want to test the following code (just a sample! I am not going into discussions whether you should be testing against a sql database like this). We call SqlCommand.BeginExecuteReader, passing a callback-method as a lambda expression (the delegate is of type System.AsyncCallback). This screenshot contains only the logic I want to test (note: this uses an extension method public static SqlCommand CreateCommand(this SqlConnection conn, string commandText)). The SQL statement will wait for five seconds and then do a simple select on the sys.objects system view.

The highlighted part shows the lamdba expression for the callback logic we want to test.


This test code has a number of issues:

a) the code in the lambda will execute on a callback thread, none of the Assert.Xxx-statements will work properly.

b) the test won’t wait for the async callback to be completed, it would just exit the [TestMethod] TestAsyncSqlCommand before the callback actually executed. It would be handy if it could do an automatic blocking wait before the Assert.IsTrue(callbackExecuted) to wait for the callback to complete – which in this example would take at least five seconds (due to the waitfor delay N’00:00:05’ in the SQL query).

To make this test work, we would

a) need to enclose the callback code in the lamdba with a artificial try/catch-block, to catch any exception thrown by a failed Assert.Xxx call

b) add a artificial synchronization object (for example ManualResetEvent), which we declare at the top of the test method, then signal during the callback, and then perform a blocking wait before the Assert.IsTrue(callbackExecuted).

It would look like this … we have two threads in action, one is our main test thread which calls the BeginExecuteReader, then continues to the manualResetEvent.WaitOne-call and blocks there. The second thread will execute the callback method, everything in the lambda, and then signal the synchronization directive to unblock the main thread.


First of all, this is the same logic we want to test as in the sample before. It simply contains much more noise/clutter/infrastructure code we need just to make the test work. And for subsequent unit tests for other APM-implementations, we would need to replicate it over and over again.

What do we do, and how can we factor it out?

  • at the top of the test we declare the synchronization directive (ManualResetEvent) and a variable to store an exception
  • the lamdba expression is now wrapped in a try/catch/finally-block which a) collects the first exception thrown on the callback thread and b) signals the ManualResetEvent
  • does an explicit WaitOne (specifying a maximum timeout of 10.000 milliseconds) to wait for the callback to complete
  • if an exception has been collected on the background thread during the callback, we will rethrow it on the main thread. The test host will then report the failed Assert.Xxx properly

First things first: It is pretty easy to factor out the synchronization/WaitOne and the rethrow of the exception. We’ll (admittingly, I do have a serious IDisposable fetish) just create a class TestExceptionManager : IDisposable, add this class in a using() statement in the test and collect and rethrow the exceptions in the Dispose method. This class contains the synchronization directive (on which we will wait in the Dispose method), the exception-rethrow.

But we still need to inject code into the callback. We simply should not need to write the try/catch/finally for each callback method we use in our tests. This code should be automatically added by our testing infrastructure. Any delegate we pass as a callback needs to be automatically wrapped with a try/catch/finally block. And this solution needs to work for delegates of any type (not only for AsyncCallback, as in this sample). The parameter we pass as a callback method (lambda in our case) is always a delegate of a type determined by the BeginXxx/EndXxx method pair we are testing.

.NET 4.0 comes with the Dynamic Language Runtime and vastly enhanced support for expression trees. It is now possible to generate full method bodies using expression trees. We will use this to dynamically create our interception code. We will generate

  • a new method which has the same signature as the original delegate (mandated by the BeginXxx-method). For example, AsyncCallback is declared as delegate void AsyncCallback(IAsyncResult asyncResult)
  • a body containing a try/catch/finally block
  • in the try-block, invoke the “original” delegate (our lambda method from the first sample, with the correct parameters)
  • in the catch-block, swallow any exception and store it, so we can rethrow it later on the main thread (let’s call it void StoreException(Exception xcptn))
  • in the finally-block, signal the synchronization directive (let’s call it void DecrementCounter())
  • if the delegate type declares a return type other than void, return the correctly typed result (in case of a catch we may need to return the default value for this return type)

See this posting how I implemented a method which dynamically generates a new method body and delegate.

It is implemented in the

TDelegate TestExecptionManager.Wrap<TDelegate>(TDelegate del) where TDelegate : class


The expression tree for the dynamically generated code will look similar to the following (for AsyncCallback).

.Lambda Fooo<System.AsyncCallback>(System.IAsyncResult $asyncResult) {
    .Try {
        .Block() {
            .Invoke .Constant<System.AsyncCallback>(System.AsyncCallback)($asyncResult) // this will invoke our “logic” for the callback
    } .Catch (System.Exception $xcptn) {
        .Block() {
            .Call .Constant<VisualStudio.TestTools.UnitTesting.Extensions.TestExceptionManager>(VisualStudio.TestTools.UnitTesting.Extensions.TestExceptionManager).StoreException($xcptn)
    } .Finally {
        .Block() {
            .Call .Constant<VisualStudio.TestTools.UnitTesting.Extensions.TestExceptionManager>(VisualStudio.TestTools.UnitTesting.Extensions.TestExceptionManager).DecrementCounter()

Now we can write our test the following way:


Note that we just need to declare the TestExceptionManager in a using() statement. Aditionally, the tem.Wrap<AsyncCallback> call will generate a dynamic method with the same signature as the AsyncCallback-delegate type mandates, then create a delegate pointing to this method, and return the newly created delegate (which then gets passed as the callback to the BeginXxx-method). Most of the stuff is the logic we want to test, and much less infrastructure clutter.


You could also combine this with Chess from Microsoft Research. After installation, Chess will be available as a test host for MS unit tests.

Posted in .NET 4.0, Productivity, Testing, Utilities.

Tagged with , , , , .

Showing some love where it’s due …

Consider me a fan of the SecAnnotate.exe tool which ships with .NET 4.0 (RC). Saved my tail end a couple of times today … Security-wise, I am also starting to like the new .NET 4.0 transparency model. See here and here.

The tool lives under %ProgramFiles(x86)%\Microsoft SDKs\Windows\v7.0A\Bin\NETFX 4.0 Tools\secannotate.exe

Posted in .NET 4.0, Utilities.

Tagged with , , .

SharePoint 2007 vs. 2010: How do my list content types, policies, customXsn look like?

Sometimes it makes sense to look at the SharePoint content database directly (note: never make any changes to the db without going through documented APIs).

Say you have a site collection policy (for example for auditing) which is attached to one or more content types. When you make subsequent changes to the site collection policy, these changes are not propagated to the lists and the list content types immediately. Instead, they are updated using a (drum roll, please) timer job, which, by default, runs once per day (Service=”PolicyConfigService”, JobDefinition=”Information management policy”). The content type information which is attached to the lists is updated only when the timer job runs. So, the site collection policy definition you will see through the UI may diverge from the policy which is active for a given list at a given time.

Information about lists is stored in the dbo.AllLists table (note: not all information is in the db. SharePoint also reads the schema.xml of the list template at run-time from the file system. The database however contains the content type information). The AllLists table has a tp_ContentTypes column, containing a XML BLOB (or is it a CLOB?) describing the content types which are effective at a given time for the list.

The data in this column may look like this:


Most of the information should look familiar. You’ll notice, that under the XmlDocuments node, there are several nodes containing Base64 encoded information data. This is an extensibility mechanism. Information about custom document information panels (xsn), event receivers, policies, forms … is stored here. See here for the possible types.

This data is surfaced through the object model in the SPContentType.XmlDocuments property, using unique NamespaceURIs as identifiers (The handling is kind of ugly in my eyes …).

The most obvious NamespaceURIs are:

office.server.policy policies PolicyDirtyBag (thanks for the consistent naming, Microsoft) document information panels. afaik, no api other than “build xml and assign to SPContentType.XmlDocuments” available. event receivers custom forms

If you want to subsequently decode the Base64 blobs, which contain XML themselves, use something like the following query. This will dump the content types and decode all the information under XmlDocuments.

— run this query against the SharePoint 2007 content database. You should change the where clause below

;with AllListsContentTypes ([ListId], [ContentTypeId], [ContentTypeName], [XmlDocumentNamespaceURI], [XmlDocument])
    select xtab.tp_Id,
            ContentTypes.x.value(N’@ID’, N’nvarchar(max)’),
            ContentTypes.x.value(N’@Name’, N’nvarchar(max)’),
            XDocs.x.value(N’@NamespaceURI’, N’nvarchar(max)’),
            cast(XDocs.x.value(N’.’, N’varbinary(max)’) as xml)
    (    select tp_Id, CAST(tp_ContentTypes as xml) CTypes
         from dbo.AllLists
         where tp_Id = N’<List Id here>’
        –Note: Add where-clause here
    ) xtab
    cross apply xtab.CTypes.nodes(N’/ContentType[@Name]’) ContentTypes(x)
    outer apply ContentTypes.x.nodes(N’./XmlDocuments/XmlDocument’) XDocs(x)
select ContentTypeName, XmlDocumentNamespaceURI, XmlDocument from AllListsContentTypes
order by ListId, ContentTypeId, ContentTypeName, XmlDocumentNamespaceURI

That query will shred the xml and display it as a nicely formatted rowset …


SharePoint 2010

Now, in 2010 (all info Beta 2) the database schema looks fairly similar to 2007. There are some extensions, but the basic schema remains roughly unchanged. However, one bigger change is that most of the columns which contained plain XML (content type definitions, views …) or BLOBs are now compressed in the database. This is a performance improvement, as such information can grow big. If you look at the 2010 content database, you will notice columns of type tCompressedBinary or tCompressedString. These two types are simply aliases for the varbinary(max) type. For SQL Server, this is just a varbinary(max) column with nothing else attached to it.


Most of the columns typed as tCompressedString still contain XML (like the tp_ContentTypes column in dbo.AllLists), but deflated using the algorithm described in RFC1950. The (de)compression is done solely in the SharePoint process (application pool, timer service et al). SQL Server knows nothing about these mechanisms.

Unfortunately, it is generally difficult to decompress such data using pure T-SQL. So instead of using a simple script like above for 2007, for 2010 another friend comes in handy: PowerShell.

I’ll post a PowerShell script later. The basic mechanism when reading tCompressedString (thanks) is skipping the first 14 bytes and then using System.IO.Compression.DeflateStream on it. That gives the XML, including the Base64 encoded parts, which you can decode using Convert.FromBase64String.

Posted in SharePoint, SharePoint 2007, SharePoint 2010.

Tagged with , , , , , .

Provisioning uncustomized files in WSS 2007 through code

For my last project I needed to provision uncustomized (aka ghosted) files to WSS document libraries through code. The scenario was that the client managed several "services" in a Sharepoint site. Any service consisted of various SharePoint artifacts (document libraries, custom landing pages for document libraries, custom views, permissions, workflows, form pages …) tied together in a certain fashion. The client also needed to be able to provision new such services through a set of custom application pages at run-time. These are aspx-pages which provision said SharePoint artifacts and configure them appropriately. For one of the document libraries, a shared landing page from a feature needed to be configured.

Uncustomized (ghosted) vs. customized (unghosted)

Just a short reminder: Content Pages (actually: pages, documents, document templates …) in SharePoint can either be uncustomized (the artist formerly known as ghosted) or customized (unghosted). The basic difference between these two is that the content stream of uncustomized artifacts is read from the file system of the WFE, whereas for customized files the content stream is read from the content database. SharePoint manages some metadata for both cases in the content database. Since a uncustomized file has an entry in the content database but no content stream, it also needs to store a reference to the file in the file system.

Here is a more in-depth explanation on MSDN by Andrew Connell: Understanding and Creating Customized and Uncustomized Files in Windows SharePoint Services 3.0. This link also shows how easy it is to deploy uncustomized files with features using the module element. This is straightforward. In our case, we cannot use features because end users should be able to create new services at run-time on their own behalf (by using custom actions and custom application pages.

In our case, uncustomized files have the following advantages:

  • Single point of maintenance for any landing page. We have a high number of document libraries, each of which references the same set of aspx-pages. We just need to service the aspx-files in the file system through a feature, and any document library will instantly pick up the modified version. There is no need to apply the changes to each document library individually.
  • Storage. Uncustomized files are only stored once in the file system and not in the content database, even if it is referenced from hundreds of document libraries. For customized files, the content stream is stored in the db for each instance of the file.
  • Security context. Uncustomized pages run in a less restricted security context since they can only be serviced by high privileged accounts (who supposedly know what they are doing).
  • The ability to still customize a file for a single document library through SharePoint Designer (or other tools). Once you do this, you have a regular customized file (which you could also revert later) with the content stream in the SharePoint content database.

Now, as anybody knows, adding ghosted files is very easy via features, but not easily possible through the UI or the object model. When adding files through the object model, you end up with customized (unghosted) files. There is no obvious way to programmatically link an item in a SharePoint list to a file to the file system (below the features folder) at all.

SharePoint API

Now, besides the .NET/COM based object model, Microsoft also has published documentation for other APIs, most notably the database API in which many of the stored procedures (and the required sequence to call them) are documented. So this solution is actually documented (and not a hack). You can find the SharePoint protocol documentation here in the Microsoft Download Center in PDF format (the contents are also available through the MSDN online version).

Files in SharePoint document libraries

Second reminder: You can add files to a document library in two ways. One is to add the file, without creating a list item. This is done for the standard views in the /Forms folder of standard document libraries. These files are used by the SharePoint infrastructure to display the list, but are not items in the list. They do not have a content type or metadata. They correspond to entries in the dbo.AllDocs/dbo.AllDocStreams tables in the content database. The second option is the first one plus creating a list item. The latter has a file plus a list item linked to it. The list item has a content type assigned to it and also stores the metadata (the values of the fields which the content type defines). This metadata ends up in the dbo.AllUserData table.

From an API point of view, in our case the stored procedures proc_AddGhostDocument, proc_AddListItem, and proc_GenerateNextId are of interest (all in the SharePoint content database). proc_AddGhostDocument does the first step, adding the file to SharePoint and storing the reference to the file system. proc_AddListItem adds the second step, the creation of the list item. proc_GenerateNextId is needed to generate the Id for the list item.

The sequence in which we call these stored procedures is as follows:

Case 1: Just add the document, without creating a list item

  1. Call proc_AddGhostDocument, which links the document in the SharePoint content database with the item in the file system (below the features hive) 

Case 2: Add a document and create a list item

  1. Allocate a new id for the document in the corresponding list using proc_GenerateNextId
  2. call proc_AddGhostDocument, which links the document in the SharePoint content database with the item in the file system (below the features hive)
  3. Write the list item information to the database using proc_AddListItem. In this step, the content type, metadata, and optionally ACL information is stored. In our case, we write two rows.

Now, how do we call the stored procedures from our code in an application page?

  1. First of all, you should make sure that you really want to allow the user making the request to your page to make the changes. Usually, this will be restricted using some kind of application role or privilege. You could leverage SPWeb.DoesUserHavePermissions combined with SPUtility.HandleAccessDenied for this.
  2. Retrieve the database connection information using SPSite.ContentDatabase.DatabaseConnectionString
  3. Elevate the security context using SPSecurity.RunWithElevatedPrivileges. I try to avoid using privilege elevation if I can, but we need it here. Just makes it more important to validate your caller and arguments in step 1! And make sure you handle all the implications that come with RunWithElevatedPrivileges correctly.
  4. Create a standard ADO.NET connection/command (SqlConnection, SqlCommand) using the connection string from step 2.
  5. Open the connection, execute the SQL (sample code below).
  6. Good practice is to put a try/finally block around your ADO.NET code, and then call sqlCommand.Cancel() and sqlConnection.Close(). The Cancel does not cancel or roll back the command. Just in case you are retrieving a TDS stream from the database which has not been processed completely, ADO.NET will cancel the stream and do the cleanup a bit quicker.

Sample code (boiler plate, not production ready) is shown below, both SQL and a C#method.

Sample walkthrough

I am testing this functionality on a sample document library labeled “42-GhostInTheShell”. We are going to provision the file LandingPage.aspx to the /Forms folder (without adding a list item), as well as the GhostedDocument.xml file to the root folder for which we also add a list item.

After provisioning the items, the GhostedDocument.xml is visible in the UI with the content type SOP (that is a custom content type).


Because we are curious we also take a look at the SharePoint content database using two very simple SELECT-statements (old German saying: nur kucken, nicht anfassen). The following screenshot from SQL Server Management Studio shows the records returned from the dbo.AllDocs and the dbo.AddUserData tables (unimportant columns omitted). You can see how the document in SharePoint links to the file under the features-folder in the file system using the SetupPath column. The AllUserData-table contains the metadata for the list item, including its content type. (TimeCreated column vs. Modified date in the UI: Content database store UTC timestamps)


For the LandingPage.aspx provisioned by us, we take a look using SharePoint Designer, which lists it as a uncustomized file. (I took a bad picture of this one. The right side actually shows the contents of library “42 – GhostInTheShell”)


Again, we take a look at the SharePoint content database (without touching anything), querying similar tables as before. Again, the entry in the dbo.AllDocs table shows how the document links to the file system using the SetupPath column. The SetupVersionPath just contains the information that the path root is the WSS v3 (aka 2007) path root, […]\Common Files\Microsoft Shared\Web Server Extensions\12.

As we did not create a list item for this file, no rows from the dbo.AllUserData-tables are returned, as expected.



For debugging SQL, the obvious tools like SQL Server Management Studio, Query Profiler, or sqlcmd come in handy. With Query Profiler you can always trace what SharePoint itself is doing. If you trust these guys, you can also happily use Quadrant.


Sample code

// sample call. passing null as the last parameter will omit the list item creation
WssFileProvisioning.ProvisionGhostedFileToList(spSite, spWeb, spDocLib, virtualPath, fileName, path12Hive, cbFileSize, "ContentTypeName");

internal static class WssFileProvisioning {

        public static void ProvisionGhostedFileToList(SPSite site, SPWeb web, SPDocumentLibrary docLib, string virtualFolderName, string virtualFileName, string filePath12Hive, int cbFileSize, string ctypeName) { 
            var webCurrentUserID = web.CurrentUser.ID;
            var webCurrentUserName = web.CurrentUser.LoginName;
            SPContentTypeId? ctypeId = ctypeName != null ? docLib.ContentTypes[ctypeName].Id : default(SPContentTypeId?);
            var siteID = site.ID;
            var webID = web.ID;
            var docLibID = docLib.ID;
            var connectionString = site.ContentDatabase.DatabaseConnectionString;

            // run as system account
            SPSecurity.RunWithElevatedPrivileges(() => { 
var newDocId = Guid.NewGuid(); 
                var builder = new SqlConnectionStringBuilder(connectionString);
                builder.ApplicationName = "da9a3306-8bc8-4e53-ba85-923d20998b33";
                connectionString = builder.ConnectionString;

                using (var conn = new SqlConnection(connectionString))
                using (var cmd = conn.CreateCommand()) {
                    cmd.CommandText = sql;
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("PSiteId", SqlDbType.UniqueIdentifier).Value = siteID;
                    cmd.Parameters.Add("PWebId", SqlDbType.UniqueIdentifier).Value = webID;
                    cmd.Parameters.Add("PListId", SqlDbType.UniqueIdentifier).Value = docLibID;
                    cmd.Parameters.Add("PDocId", SqlDbType.UniqueIdentifier).Value = newDocId;
                    cmd.Parameters.Add("PFolderPath", SqlDbType.NVarChar).Value = virtualFolderName;
                    cmd.Parameters.Add("PFileName", SqlDbType.NVarChar).Value = virtualFileName;
                    cmd.Parameters.Add("PDocSizeBytes", SqlDbType.Int).Value = cbFileSize;
                    cmd.Parameters.Add("PUserId", SqlDbType.Int).Value = webCurrentUserID;
                    cmd.Parameters.Add("PFilePath12Hive", SqlDbType.NVarChar).Value = filePath12Hive;
                    cmd.Parameters.Add("PSetupUserName", SqlDbType.NVarChar).Value = webCurrentUserName;
                    cmd.Parameters.Add("PDateTime", SqlDbType.DateTime).Value = DateTime.UtcNow;
                    cmd.Parameters.Add("P1", SqlDbType.NVarChar).Value = ctypeId.HasValue ? ctypeId.ToString() : (object)DBNull.Value;
                    cmd.Parameters.Add("P2", SqlDbType.NVarChar).Value = ctypeId.HasValue ? ctypeName : (object)DBNull.Value;
                    cmd.Parameters.Add("P3", SqlDbType.Int).Value = 0;
                    cmd.Parameters.Add("P4", SqlDbType.NVarChar).Value = Path.GetExtension(virtualFileName).Substring(1);

                    try {
                    finally {


;declare @iRet int
;declare @DoclibRowId int
;declare @Level tinyint
;declare @DocUIVersion int
;set @Level=1 — 1=checked in, 2=draft, 255=checked out;
;set @DocUIVersion=512 — 1.0 aka (1*512 + 0)
;declare @P1C varbinary(max)
;declare @S uniqueidentifier
;declare @DN nvarchar(256)
;declare @LN nvarchar(128)
;declare @ItemIdOut int

begin tran
    ;if @P1 is not null
        ;select @P1C = CONVERT(varbinary(max), @P1, 1)
        ;exec @DoclibRowId = proc_GenerateNextId @PWebId,@PListId 
    ;exec @iRet = proc_AddGhostDocument @PSiteId, @PWebId, @PDocId
                    , @PFolderPath, @PFileName
                    , @Level
                    , @DocUIVersion
                    , 0
                    , @PDocSizeBytes
                    , 268, 0, 0
                    , @PUserId
                    , 0, 3
                    , @PFilePath12Hive
                    , @PSetupUserName
                    , @PListId 
                    , @DoclibRowId
                    , 0

    ;if @iRet <> 0 goto done
    ;if @P1 is null goto done 
    ;exec @iRet = proc_AddListItem
                @WebId=@PWebId, @SiteId=@PSiteId, @ListID=@PListId

                ,@ItemId=@ItemIdOut OUTPUT
,@ItemDocType=0 — File

                ,@BaseType=1 – doclib
,@ItemDirName=@PFolderPath output
                ,@ItemLeafName=@PFileName output

    ;if @iRet<> 0 goto done;

    ;exec @iRet = proc_AddListItem
                @WebId=@PWebId, @SiteId=@PSiteId, @ListID=@PListId
                ,@ItemId=@ItemIdOut OUTPUT –@DoclibRowId
                ,@ItemDirName=@PFolderPath output
=@PFileName output

    if @iRet=0 

Posted in SharePoint, SharePoint 2007.

Tagged with , , , .

Windows Live Writer and certificates (/allowunsafecertificates)

This is a rather old one, but in case you are using Windows Live Writer for writing your blog entries and your server uses ssl certificates which Live Writer regards as unsafe (for example, if the domain does not match the url :S), you can use the /allowunsafecertificates command line option. Just right click the Live Writer entry in the start menu, select Properties, and append the options to the command line in the Target field. After that, Live Writer will (no longer refuse to) connect to your blog engine using SSL/TLS.

Here is a list of the command line options Live Writer supports (in German).

Posted in Utilities.