How SharePoint manages web.config via SPWebConfigModification
You probably will be very surprised to know that SharePoint 2007 doesn’t manage web.config via direct access to the actual file. The reason for this is that updating the web.config directly may not always work (you don’t have permissions, or file is locked, or context is not ready yet and etc) and you need a way to propagate all your changes to WFE web.configs.
So, what SharePoint does is manage web.Config via …. Content DB. Yep, right via Content DB, it’s not a joke :) The actual records of web.config are stored in [SharePoint_Config].[dbo].[Objects] as XML entries. You can query them easily via the following script
SELECT Id, ClassId, ParentId, Name, Status, Version, Properties
FROM Objects
WHERE (Name LIKE '%WebConfig%')
To modify web.config programmatically you need to use SPWebConfigModification - it updates columns in the Object table with your values and SharePoint modify web.config itself across all servers. Unfortunately, this method is really poor documented, so you need to google for the samples.
However, that’s not an end of the story. I wouldn’t write this post if the situation was not so tricky. Due to the fact, that web.config records are XML entries we do not have a bullet-proof storage of our entities, because SharePoint doesn't validate that XML. You can put unnecessary stoke (‘) or other symbols that will break the XML. SharePoint doesn’t provide any validation and you end up with the broken SPWebConfigModification :) Whatever you do, function throws exceptions and you can’t rollback your changes :(
There is a solution for this – manually update the XML records in [SharePoint_Config].[dbo].[Objects] table :) .Some SharePoint people will have the heart attack at this exact moment :) The solution is described there http://blog.thekid.me.uk/archive/2007/03/23/corrupt-webconfigmodifications-in-sharepoint.aspx. What you actually need to do is to find your broken the XML nodes with your web.config content and delete them.
It’s absolutely unsupported and not recommended solution, but sometimes your need “to cut the corners” :)
In some cases this modification is insufficient :) You need to update the [Title] row where column “[Name] = SharePoint - <yourSite>” as well, because web.config settings are stored in two places.
Additional story is how to update the XML rows. SQL Server Management Studio doesn’t allow you to update XML entries in editor, and UPDATE SET doesn’t work for XML as well. The only tool I know for such operations is Altova DatabaseSpy that provides you all functionality to update the XML
PS: to find the second location I spend about 2 hours, wearing a “bloodhound” hat, scrutinizing DB with profiler and other diagnostic tools, because SPWebConfigModification thrown exception all time. After I removed all custom entries everything started to work really smoothly.