Membership & BDC import status hangs with infinite items (SharePoint 2007)

This was a bit of an odd one I came across, caused by a transaction log filling up. Take the following scenario

1. The log file for SharePoint_SSP_Search (the SSP Search database) becomes full, and cannot grow further. The database stops responding
2. The Office Search Service , then the SharePoint timer service throws an error as it cannot contact the database. This causes an error when the crawler tries to pause, and the object cache becomes corrupted (slight speculation here).
3. The SharePoint services continually attempt to communicate with the SQL Server (every second), the net result being that the server is too busy to serve RDP requests (not ideal if doing remote support!)
4. Someone “resolves” the database log size, and restarts all the SharePoint services, including IIS, the Timer Service and Office Search (or server reset)
5. Office Search and Timer resumes, persisting the corrupted object cache data to the database. This has a knock on impact on the “Membership and BDC Import”, which gets stuck in some form of infinite loop (last count, over “56,000” AD records had been imported, in an AD of less than 2,000!)
6. This fills up the transaction log at the rate of a few 100MB a minute, quickly reaching the max log file size again, causing SharePoint to stop responding once more.

The route cause of all this was that the transaction log for the search database was full. In this scenario the reasons was:

1. The log file had been capped at 5GB. This is not necessarily unreasonable, but if it does reach the limit, this triggers the issue.
2. The database is in simple recovery mode
3. The log file will grow with each SharePoint content crawl, or user profile import.
4. The database backup for SharePoint_SSP_Search appears to not be on a daily schedule (last backup was more than 10 days previous). This means that the log file has significantly more time to grow, and shrinking the log nightly has no impact, due to the fact that the database (despite being in simple mode recovery) has not been backed up, and the transaction log space has not been freed for deletion.

To resolve the problem, you can take the following steps:

1. Manually backup/shrink the search database. Note, if your database is maxed on the log file, you may need to allocate more log file space before you can succeed in a manual backup/shrink
2. Created a particular maintenance plan for this database, which backs up nightly, then shrinks the log file. (this stops the trigger for the issue from occurring under "normal" conditions.
3. Stop the troublesome “Membership and BDC Import”
4. Reset all crawled content, which clears out the corrupted data from the search database
5. Started a full user profile import and confirm success.
6. Initiated a full crawl on “Local Office SharePoint Server Sites”, and confirm success

I hope this helps someone else out. If you find a more definitive route cause or fix, please let me know.

Using Trusted Connections in Web Applications under SharePoint Server

Microsoft really try to hammer home the concept of using trusted connections in web.config files, such as

<connectionStrings>
<add name="MyDbConn1" 
connectionString="Server=MyServer;Database=MyDb;Trusted_Connection=Yes;"/>
<add name="MyDbConn2" 
connectionString="Initial Catalog=MyDb;Data Source=MyServer;Integrated Security=SSPI;"/>
</connectionStrings> 

They will also mention that you do not need impersonation on for this to work, only that the identity account for the application pool has the required access to the SQL server (normally making this account a managed domain service account). This is despite many inccorrect postings on news groups saying you must have impersonation on (people never read the scenario)

However, there is one gotcha when playing your web application underneath a currently existing .NET application. If your top level website has

<identity impersonate="true">

then you will need to put

<identity impersonate="false">

into your own web.config to override (or override in another way, this worked best for me). This is the scenario you will face with WSS or MOSS, as all the sites attempt to impersonate the current user.

SSRS 2008 SharePoint Integrated Mode Issues -

I've finally found time to look at SSRS 2008 and how it improves the integration mode for SharePoint. As usual, I had a new pain setting it up.

My setup:

• MOSS 2007 and SSRS 2008 installed on vmsp-01
• SQL Server 2008 installed on vmsql-01
  

There are several reasons for getting the following message:

An unexpected error occurred while connecting to the report server. Verify
that the report server is available and configured for SharePoint integrated
mode

The main reason for getting this error will be that you have configured Reporting Services in Central Administration to point at a localhost URL. However you might have checked all your settings (as I had) and been fairly sure they were right. Mine were as below





Now, as this was testing, the url is not in DNS (and to be honest, I've no intention of cluttering DNS with another record). I'd modified the hosts file to point at this.

What you might find at this point is that you can access the URL from external machines (with the right host headers in the hosts file), but you cannot access the url from the server itself. You've fallen foul of "as designed. If you cannot connect to any *windows authenticated* URL hosted on the server from the server itself, and you are receiving the error message above, then it is the loopback adapter that is at fault. As the SharePoint server makes the second request to retrieve data from the reporting server it makes no difference if you can access the reporting server url correctly from a client machine


Disabling the loopback adapter solves the problem. I think that having a DNS record probably fixes this issue too, but I've not checked for sure.


Installing SSRS for SharePoint Integrated Mode
Disabling Loopback Adapter in Windows Server 2003 R2 and Windows Server 2008/Vista

Selecting Content Types from the "New" menu gets the wrong content type

The scenario

• Create 2 breand new content types/ Provide each one with a word 2007 document as the template (each with some different text to test success).
• Attach both content types to a document library.
• From the "new" dropdown on the doc library select the last content type on the list.
• Add some text add save back. The content type attributed to the document will be the first one in the list (the default) *even though* we selected the correct type.
• saving to disk and inspecting "item4.xml" inside the docx shows the wrongly selected content type. Correct the documents content type in sharepoint, download to disk and inspect item4.xml and you will see the correct content type.
  

This is a bit of a weird one. I'm going to do a bit of investigation and report back through my blog. It's rather frustrating when you have a different workflow that kicks off on the creation of a document of each content type

The B2B upgrader timer job failed

I found a great article on getting WSS 3.0 SP1 working after receiving the worrying "The B2B upgrader timer job failed" error message. I'm only linking it here in order to try and bump it up good so others can benefit.

Search Server 2008 Express RC

Today Microsoft released a bombshell: Search Server 2008 Express

In summary:

• It's free
• Standalone product
• All the features of MOSS search
• Limited to one query server – so not scalable for the very large enterprise look at the other variations if you're after this

This is going to really change the game for the low end users.

It's in RC now, so I'd think the end of Q1 will be the target launch. I'll post something in the next day or so relating my experiences with it

MOSS - Targeting content to Sites not Audiences

Audience targeting is a great way to push information to individual users from a central source - but what about when you want to push information to particular sites? My example is a series of department sites. Sounds easy - content query web part (CQWP) filtered on a choice column on your list of announcements/documents/links etc to say which department the item is relevant to. Job done? well until you need to say that this item is for multiple departments...

Try changing the choice from dropdown to "checkbox/multiple". The text that appears in a list view is great "choice1;choice2;choice3" etc. Great! lets filter using "contains" in the CQWP. Not really - while you see the value in the interface as a string, th CQWP won't when filtering - so you end up with no results? The answer? Content Types and Workflow. For the rest of this article I'm working with a document library with a "Relevant Department" column

Create a new Content Type

(mine is at the top portal level for reuse). I called mine "My Choices Content Type", inherited it from "Document" and added it to "Test Content Types" to keep it seperate. I added 2 new site columns

• MyChoices - a checkbox based choice column


• MyChoices_Text - a single line text column

Go to your document library/annoucements list/ etc and change it to allow content types, add your new content type and remove any others from the list. Don't upload any documents or create any items yet.

Create a Workflow in SharePoint Designer

Open up the site containing your document library in SP Designer. From the file menu select New > Workflow, configure yours similar to mine below:






This workflow will copy the (non-text) multiple choices into a text column, thus exposing it as text to the CQWP.

Ammend CQWP to Filter based on our content type

Now that we have this property create a CQWP on your "department" site. Configure it as below to point at our list, our content type and then filter it based on the Site Column "RelevantDepartment_Text" and use the filter operator "Contains". Enter a string matching one of your choice values. OK these choices.



Congratulations, you should now have a list of documents that can be relevant to more than one department, pushed through to the site of your choosing.

MOSS 2007 - The Great Hard Disk Consumer

Recently I've been performing a bit of a review on som MOSS servers that are a little "under-specified" on the hard disk front. While you probably shouldn't have anything with as little as 10GB as your only drive on a SharePoint server, it has got me looking at some of the places that SharePoint just eats needless space if you let it.

The Databases

Watch out for the SharePoint_Config database particularly - the log file can grow and grow in "default" SQL configurations. A sensible maintenance plan, or at the very least setting databases to backup in "simple" mode will dramatically reduce the size of the logs you are working with (GB down to about 20MB). You should understand the impact of changing your backup modes before you make any changes though. You have been warned™

IIS Logs from Searching

IIS will by default be generating loads of log file data - particularly when the content crawling is happening. You probably want to keep IIS logs for "normal" use, but not for content crawling. If you create yourself an alternative access mapping you can index all your content of one web application url (eg internal.enterprise.com), and turn off logging in IIS, while users access the other one (eg live.enterprise.com) , with logging switched on.

Diagnostic Logging

SharePoint puts its diagnostic logs in:

C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\LOGS

and by default logs very verbosely. You can change these settings to only log the more "interesting" severities through Central Admin > Operations > Diagnostic Logging

WSS_AdminService.log

You will find a log file called WSS_AdminService.log held in

C:\Documents and Settings\Default User\Local Settings\Temp

The file is being written to almost constantly and becomes very large (900Mb by the time I found it).Worse still, because it's in the

C:\Documents and Settings\Default User

folder it gets duplicated when a new user logs onto the server console. Deleting it resolves the situation temporarily (i didn't notice any errors), but it will appear again and start growing.I'm not sure how to "solve" this one, as it seems completely unknown to the internet aprt from this one link (no solution).

http://www.tek-tips.com/viewthread.cfm?qid=1363719&page=6

If and when I find the answer to this and other similar capacity issues I'll ammend this entry. For the meantime, careful monitoring and deletion should keep things right.

Configuring MySites under your main portal

If, like the rest of the work, you'd prefer people to access their MOSS 2007 Mysites as

https://portal.enterprise.com/personal/username

rather than

https://mysites.enterprise.com

(which requires a different url, possibly new certificates, and probably mass confusion), then you can do no better than look to this blog entry from Chris Johnson.

http://blogs.msdn.com/cjohnson/archive/2006/09/15/754902.aspx

Site Collection Administrators and Adding Workflows to Sites with Broken Inheritance

Another gotcha in the SharePoint world. You would think that Site Collection Administrators could do anything on any site inside the collection. Being an administrator and all. Not so. If you've broken inheritance you will find that you no longer have the correct options in SharePoint Designer to add a new workflow. For the meantime I have solved this by explicitly adding the "Site Collection Administrator" account into the "SiteName Owners" group - which was created when we broke inheritance. Not exactly the functionality I was expecting.

Fear and Surprise and Permissions in MOSS Central Admin

Access denied when selecting "User profiles and properties"? Can't access your shared services provider? Can't get to the "Import Application Definition" page of the BDC?

No one expects the MOSS administration, and certainly - if you are looking at the Central Administration tool for the first time, you would certainly be forgiven for suffering from fear and surprise, the 2 weapons of the Administration.

Yes 2 are their weapons; fear, surprise and permissions...oh, no wait, that's 3. Anyway.....

As an external consultant, assisting with someone elses MOSS install, you can't always fall back on knowing the domain admin password, or being privvy to the setup accounts and passwords used for setting up the search, app pools etc. But you may need, like I do, to have access to administer everything about Central Admin after the initial setup, using a normal user account created for you by the IT team. I generally make sure this is all sorted out before I leave site the first time, along with any remote connection details required.

Giving this user account access sounds easy. The "original" administrator needs to go to Central Admin -> Operations -> Update Farm Administrator's Group, and simply add the user in. Easy! well....you'd think. Launch Central Admin with this new account and yes, you'll get in, but you'll quickly find you have no access to mysites, shared services providers or to any other site collections. However - you now have all the boot strapping you require.

First make sure you are a site collection administrator for mysites, the SSP, and any site collections you are interested in using the following procedure:

• Go to Application Management -> SharePoint Site Management -> Site Collection Administrators
• Select "change site collection" and select the correct web application using the over complex interface
• Add yourself as the secondary site collection administrator

Great - I'm a site admin of the SSP - now I can do everything I need? Sadly not...

You may have got yourself into the SSP, but you'll find that you can't access several expected key features, notable being:

• User Profiles and Properties
• Import Application Definition

To do this, you'll have to assign yourself the permissions using the links:
Personalization services permisions
Business Data Catalog Permissions

Nothing complex here - just a step that I bet you wont be expecting already being as "admin" as you can get

Access 2007 is useful! Stop Press!!!

Access has generally been the whipping boy of database products, mostly scorned by "real database developers" due to the fact that it allows users to think they can create databases. However, for the first time, I think I have seen a glimpse of something truely useful for Access 2007.

Where else might Access be useful but in MOSS 2007! Tired of your old views? bored with datasheets? Management requesting graphed reporting information from a list you wish you'd stored in SQL? "Access View" to the rescue! New in 2007 (along with Gant views - another favourite) you can produce any Access report - and better, link it directly to the list of views available from the SharePoint list.

1 - Create your view

Simply select "Access View" and it will launch Access 2007, with your list information already marked as a linked table. For this example I selected the "Pivot Table" option - surely the best demonstration of why you'd want an Access view. A quick bit of playing and I was done.

2 - Publish the result to SharePoint.

As simple as selecting the "Publish to SharePoint" option. Save the file in a document library (best make a hidden one for the purpose of access views). Make sure that when the dialog appears you say yes to adding this view to the drop down list of views available in the list.

3 - Select your view

Woohoo! Look - there is my new view. Sadly it loads Access 2007, rather than say, displaying the pivot chart directly in a web page, but I guess it is still a big step.

4 - Enjoy the results

And here is my completed pivot chart - 10mins from start to finish, no development, no reporting services, and Management can play to their hearts content.

Forms Authentication in MOSS 2007 / WSS 3.0 - Not all that...

One of the great heralded features of the latest SharePoint release has been the integration of forms authentication Membership/role providers from ASP.NET 2.0, essentially allowing seamless integration of any list of users from any datasource to Authenticate to SharePoint/MOSS - the default one being through aspnetdb. I don't want to cover that, as it's been more than detailed elsewhere. I just thought I'd reveal a few of the important gotchas that you'll experience after getting it working.

• You cannot create lists from excel spreadsheets, or make any use of a DataSheet mode for lists
• You cannot (easily) publish an InfoPath form to a form library. (Our workaround was a second web application set to basic auth)
• Give up hope of seeing any SharePoint plugins in Office documents
• You won't get a "Edit in Word" style option for office documents of the context menus
• As with all forms authentication you won't be able to download files directly using code means like WebClient() in C#
• RSS Readers won't be able to get past forms authentication to subscribe
• I'm sure there are other features similar that won't work

I think all this is a bit of a shame, as all the user creation user controls etc are provided for you, and it's quicker than Account Creation Mode for WSS 3.0, and works in MOSS 2007. It may still be the solution for you in a non-active directory world, but be very sure that the features that you are losing can afford to be lost