March 2007 - Posts

• SharePoint 2007 development exams: the results

  Last week I got the e-mails I was waiting for since January; I passed both beta SharePoint 2007 exams that I took :-). So I am now a Microsoft Certified Solution Specialist in: Microsoft  Windows SharePoint Services 3.0: Application Development Microsoft Office SharePoint Server 2007 Application Development Renee Hulsman, who is one of my collegues, also passed his MOSS 2007 Application Development exam. Nice! Posted Mar 27 2007, 07:48 PM by tonstegeman

• Document templates and SharePoint 2007 content types

  One of the most powerful new features in WSSv3 (and of course MOSS 2007) are content types. One of the benefits of having content types is that we now can have multiple document templates in one single document library. Each content type can have a document template. By attaching multiple content types to a library, we can offer multiple templates to our users. Just as in the previous version of SharePoint we can still set a document template for a specific document library. Document templates can be set in the SharePoint user interface, but it can also be done in code. This post will show some examples that will help you to get started. Example 1 – Template for a document library This example shows how you can upload a document to SharePoint and use that as the document template for your document library. First the code checks if a document library called ‘MyDocuments’ is available. If not it is created. If the document is not yet uploaded to the Forms folder in the library, it is uploaded. The last step in this codesample sets the DocumentTemplateUrl property of the library.     string libraryName = "MyDocuments";     string templateName = "MyTemplate.doc";       SPSite site = new SPSite("http://office2007");     SPWeb web = site.OpenWeb();     SPDocumentLibrary doclib = null;     foreach (SPList list in web.Lists)     {         if (list.Title == libraryName)         {             doclib = (SPDocumentLibrary)list;             break;         }     }     if (doclib == null)     {         System.Guid doclibid = web.Lists.Add(libraryName, string.Empty, web.ListTemplates["Document Library"]);         doclib = (SPDocumentLibrary)web.Lists[doclibid];     }     SPFolder formsFolder = doclib.RootFolder.SubFolders["Forms"];     SPFile template = null;     foreach (SPFile file in formsFolder.Files)     {         if (file.Name == templateName)         {             template = file;             break;         }     }     if (template == null)     {         byte[] doc = System.IO.File.ReadAllBytes(string.Format(@"c:\{0}", templateName));         template = formsFolder.Files.Add(templateName, doc);     }     string templateUrl = string.Format("{0}/Forms/{1}", libraryName, templateName);     if (doclib.DocumentTemplateUrl != templateUrl)     {         doclib.DocumentTemplateUrl = templateUrl;         doclib.Update();     } The document (MyTemplate.doc) is uploaded to the ‘Forms’ folder that is available in every document library. After running this code your Forms folder and advanced options will look like this:         Example 2 – Template for a content type The second example basically does the same thing as the first example, but now for a content type. This sample requires a content type called ‘Proposal’ to be present in your site before it will run.     string contentTypeName = "Proposal";     string proposalTemplateName = "MyProposal.doc";     SPContentType proposal = web.ContentTypes[contentTypeName];     SPFolder cts = web.Folders["_cts"];     SPFolder proposalFolder = cts.SubFolders[contentTypeName];     SPFile proposalTemplate = null;     foreach (SPFile file in proposalFolder.Files)     {         if (file.Name==proposalTemplateName)         {             proposalTemplate = file;             break;         }     }     if (proposalTemplate == null)     {         byte[] proposalDoc = System.IO.File.ReadAllBytes(string.Format(@"c:\{0}", proposalTemplateName));         proposalTemplate = proposalFolder.Files.Add(proposalTemplateName, proposalDoc);     }     if (proposal.DocumentTemplate != proposalTemplateName)     {         proposal.DocumentTemplate = proposalTemplateName;         proposal.Update();     } This code will upload a template document for the proposal content type and then set the DocumentTemplate property of the SPContentType. When you use the SharePoint interface to upload a new template document, this document is uploaded to a subfolder called ‘_cts’. All content types in a SPWeb have their own subfolder in _cts. Our ‘’MyProposal.doc’ is therefore uploaded to the folder ‘/_cts/proposal’. My content type is in the root site, so therefore this _cts folder is a subfolder of the rootsite. If you create your content type in a site deeper down in the hierarchy it will be a sub folder of this sub site. The screenshot below shows SharePoint Designer showing this folder. The screenshot below that shows the advanced settings for our content type. Please note the difference between the document library and the content type. The content type expects just the filename, the document library expects you the set the full relative url to the template.           And after writing this post I should now return to finishing my article on custom policies for the Dutch .NET Magazine…. Posted Mar 27 2007, 07:35 PM by tonstegeman with 18 comment(s)

• Using Analysis Services data in Excel Services part 3 - Create and test an Excel sheet

  This is the third and last part in a small series on my adventures to get Excel Services working with SQL Server 2005 Analysis Services. In this part we will create and test an Excel sheet. Part 1 – Preparing the AD for Kerberos Part 2 – Preparing the MOSS server Part 3 – Create and test an Excel sheet Part 4 – Overview and updates Step 1 – Set permissions The first thing to do is make sure that your user account has acces to the Analysis Services cube. Step 2 – Create a data connection file The first step is to create an ODC file the holds the connection to the Analyses Services cube. In my test environment I used the Microsoft Adventure Works sample. Go to the ‘My Data Sources’ folder in My Documents and double click ‘+Connect to New Data Source’      When the Data Connection Wizard starts in Excel, select Microsoft SQL Server Analysis Services and click Next.                 Or start Excel 2007, select the Data tab and click ‘From Other Sources’ and then select ‘From Analysis Services’.      In the next screen of the wizard, select the server– and instance name of the Analysis Services server. Set the log on credentials to Use Windows Authentication.      In the next screen select your cube:      In the last screen of the wizard, enter a filename for the ODC files and also add a friendly name and description. Make sure that you check the box ‘Always use this file to attempt to refresh data’. Click the button ‘Authentication Settings’ and select ‘Windows Authentication’      Your ODC file is now saved. Navigate to the Data Connection Library (you created this in step 2) and upload your ODC file.       Step 2 – Create the Excel sheet Start Excel and on the Data tab click Existing Connections. Click the button ‘Browse for more’ and navigate to the data connection library in your SharePoint site. Select the ODC file.      Now create the report you want.       Step 3 – Publish the Excel Sheet After you finished creating your report, click on the Office button (you need the Enterprise version of Excel for this) and select ‘Publish’. Enter the url to your document library (see step 2) that was marked as a trusted file location and save the Excel sheet. After you have done this, Excel Web Access will open (if you checked the box) and load the Excel sheet.      If you succesfully configured all elements that I have discussed, you will be able to drilldown into this pivottable. If you did not, you will end up with this message:      Unable to retrieve external data for the following connections:      Adventure Works      The data sources may be unreachable, may not be responding, or may have denied you access.      Verify that data refresh is enabled for the trusted file location and that the workbook data authentication is correctly set. This is shown as in the screenshot below.      If you did it all well, you should be able to drilldown into your pivot table, or refresh the data from the server:      Posted Mar 13 2007, 07:58 PM by tonstegeman with 6 comment(s)

• Using Analysis Services data in Excel Services part 2 - Preparing the MOSS server

  This is the second part in a small series on my adventures to get Excel Services working with SQL Server 2005 Analysis Services. The first part describes the changes to the Active Directory to setup the Kerberos authentication for delegation of user accounts. In this part we will prepare the SharePoint server. Part 1 – Preparing the AD for Kerberos Part 2 – Preparing the MOSS server Part 3 – Create and test an Excel sheet Part 4 – Overview and updates Step 1 – Set the Access Model The first thing to do is set the Access Model on the server. After installing MOSS in a farm with multiple servers, the access model is default set to ‘Trusted subsystem’. This has to be set to ‘Delegation’. To do this you have to run these STSADM commands: stsadm -o set-ecssecurity -ssp SharedServices1 -accessmodel delegation stsadm -o execadmsvcjobs In the first command, ‘SharedServices1’ is the name of my Shared Services provider. Step 2 – Create a Data Connection Library A Data Connection Library is a special type of document library that can hold ODC files. These are the data connection files that you use in your Excel sheets to connect to external data sources. Go to your SharePoint site and create a Data Connection Library (DCL) where you want it to be.       After creating the DCL, you need to trust it. By doing this, you tell SharePoint the the ODC files in this library are safe and can be used to get data from external data sources. To do this, go to the admin site for your shared services provider, and select ‘Trusted data connection libraries’ in the ‘Excel services settings’ section. Click ‘Add Trusted Data Connection Library’and enter the url to the DCL you just created. Step 3 – Create a document library Anywhere in your SharePoint site, create a document library that you will use to publish you Excel sheets to. After creating this library, you have to tell Excel Services that it can trust this location for loading excel sheets. Navigate to the Shared Services admin site and select ‘Trusted file locations’ in the ‘Excel services settings’ section. Click ‘Add Trusted File Location’ and enter the url to the document library you just created. In the External Data section on the same page, make sure that you set the setting ‘Allow External Data’ to ‘Trusted Data Connection Libraries only’.      Step 4 – switch your web application to Kerberos authentication Start the Central Administration and navigate to the Application Management tab. In the ‘Application Security’ section, click ‘Authentication Providers’. Make sure that the web application of your site is selected in the dropdown on the topright of the page. Click on the ‘’Default’’zone. Change the IIS Authentication Settings from ‘NTLM’ to ‘Negotiate (Kerberos)’.      Reset IIS Log off your client and log back on again At this stage all server are ready for us to test the solution, which we will do in the next (and last) post. Posted Mar 12 2007, 08:35 PM by tonstegeman with 4 comment(s)

• Using Analysis Services data in Excel Services part 1 - Preparing the AD for Kerberos

  I started to work with Excel Services in combination with data from Analysis Services. First I configured this all in 1 virtual machine, which was easy, because it just works. The next step was to get it working in a real world scenario at one of our customers. Here we have a SharePoint server which also runs Excel Services. Databases and the Analysis Services cubes are on a different machine. The requirement is that we need to impersonate the user to Analysis Services. To be able to do this you have to implement delegation of user account using Kerberos authentication (or SSO). NTLM is not enough, because the web server cannot delegate the current user to the SQL Server. This is also known as the double hop. In this small series I will descibe all steps that I took to get it working in a virtual test environment. First I will describe the setup of my environment and after that I will describe the changes to the Active Directory. Part 1 – Preparing the AD for Kerberos Part 2 – Preparing the MOSS server Part 3 – Create and test an Excel sheet Part 4 – Overview and updates In my test lab I have 3 machines: Active Directory and SQL Server 2005 Machine name: office2007 FQDN: office2007.tst.intra Domain: tst Roles: Active Directory, SQL Server 2005 SP2, Analysis Services SQL services run under a domain account (tst\sqlservice) Domain functional level: Windows Server 2003 SharePoint and Excel Services Machine name: tstmossdev FQDN: tstmossdev.tst.intra MOSS installed in a small server farm topology Separate domain accounts for all application pools and services Web Application on http://intranet (application pool identity: tst\intranetapppool) Client machine Windows XP SP2 Excel 2007 Username: tst\ton tst\ton is a member of the site running on the SharePoint server tst\ton has reader access to the Analysis Services cube. Below you will find all the steps for configuring the AD, to work with Kerberos authentication. Step 1 – SPN for SQL Server account The account that runs the SQL Server services (in my case TST\sqlservice) must have a Service Principal Registration in the AD. To register this SPN, we use SetSPN.exe. This tool is part of the Windows 2003 Support tools. More information can be found in this kb-article. You have to be a domain admin to run these commands. The commands for my environment are: setspn –a MSOLAPSvc.3/office2007:tstdev05 tst\sqlservice setspn –a MSOLAPSvc.3/office2007.tst.intra:tstdev05 tst\sqlservice You have to run both commands. In this command, ‘office2007’ is the servername of the SQL server and ’tstdev05’ is the SQLinstance. For more information on the exact syntax of the commands, please read this blog post by Mosha Pasumansky. If you want to check what SPNs your account has, you can use setspn -l; in my case setspn -l tst\sqlservice. After running the commands above, this returns:      Step 2 – SPN for Application pool account for the SharePoint/Excel server Just as the SQL Service account, the account that runs the application pool of your web application also needs to have a SPN. The commands that we need to run to create the required SPN for this account: setspn –a http/tstmossdev tst\intranetapppool setspn –a http/tstmossdev.tst.intra tst\intranetapppool In this example, ‘tstmossdev’is my SharePoint/Excel services server. After running these commands, the spn’s should look like this:      Step 3 – Enable delegation for the application account The account we created a SPN for in step 2, needs to be enabled for delegation. To do this, find the service account in your AD and go the the properties. Navigatie to the Delegation tab and select “Trust this user for delegation to any service”:      Some remarks: This screenshot is based on a domain running in Windows Server 2003 mode. If you are using the “Windows 2000 mixed” domain functional level, things look a bit different. If there is no Delegation tab, check your SPNs, they are probably not correct. I couldn’t get it to work using constrained delegation. I suspect it has something to do with the fact that I am using named instances in my SQL Server setup. Therefore I trust the account to delegate to any service.   Step 4 – User accounts Make sure that your users that connect to the cube through Analysis Service have the checkbox ‘Account is sensitive and cannot be delegated’ on the account page cleared:       Step 5 – Enable delegation for the SharePoint server The server that is running Excel services (in my case SharePoint and Excel Services) must be trusted for delegation as well. Find the server in your Active Directory, navigate to the Delegation tab and select “Trust this user for delegation to any service”:      In my sample setup this is the TSTMOSSDEV machine. In part 2 of this series we will configure Excel Services and SharePoint to use the Kerberos authentication. Posted Mar 11 2007, 02:33 PM by tonstegeman with 9 comment(s)

• Managing Content Type order and visiblility on a SharePoint list in code

  In SharePoint 2007 and WSS v3 it is possible to add multiple content types to a single list or document library. After adding the content types to your list, you can use the option “Change new button order and default content type” to change the order of the content types. If you uncheck the Visible checkbox, this content type will not appear in the New menu. The first content type in the list is considered the default content type. When a user clicks the New button instead of opening the New menu, the default content type will be selected for the new list item or document. Besides setting these options in the user interface, you can also set them from code. In my example, I have a document library with 3 custom content types added in the wrong order as you can see below. This example will work on any SharePoint list.      In the code example we will re-order the content types and change the visibility. We only want content types 1 and 2 to appear on the new menu. While investigating how to do this, I found that these properties are stored at a folder (SPFolder) level. This means that you can sort your content types in a different way for each folder. And you can also let each folder have a specific set of content types. To see this in the user interface, create a folder, open the drop down menu and select “”.      The code snippet below sets the options for the list itself. This uses the RootFolder property of the list to find the correct folder. The sample assumes that you have a list or document library with some extra content types assigned.     SPSite site = new SPSite("http://office2007:300");     SPWeb web = site.AllWebs["intro/beheer"];       SPList list = web.Lists["Docs"];     list.ContentTypesEnabled = true;     SPContentType listCt1 = list.ContentTypes["ContentType1"];     SPContentType listCt2 = list.ContentTypes["ContentType2"];     SPContentType listCt3 = list.ContentTypes["ContentType3"];       SPFolder folder = list.RootFolder;     SPContentType[] orderedContentTypes = new SPContentType[2];     orderedContentTypes[0] = listCt1;     orderedContentTypes[1] = listCt2;     folder.UniqueContentTypeOrder = orderedContentTypes;     folder.Update();   Each SPFolder object has a property called UniqueContentTypeOrder. This is an IList of SPContentType objects. Content types that are not in this list, are not visible. After running the code, the content types at the document library level now look like this:        Posted Mar 02 2007, 02:22 PM by tonstegeman with 12 comment(s)

• Navigation options in a SharePoint Publishing Site

  As with almost anything in SharePoint 2007, it is possible to set the navigation options for a publishing site in code. To be able to do this, you need to reference the Microsoft.SharePoint.Publishing assembly and add these using directives. using Microsoft.SharePoint; using Microsoft.SharePoint.Publishing; using Microsoft.SharePoint.Navigation; Initialize The following examples all use this initialization snippet:     SPSite site = new SPSite("http://office2007:300");     SPWeb web = site.AllWebs["intro/webcms"];     string pagename = "IntroductieCMS.aspx";       PublishingWeb pw = Microsoft.SharePoint.Publishing.PublishingWeb.GetPublishingWeb(web);     PublishingPageCollection webpages = pw.GetPublishingPages(); Example 1 – Switch “Show Pages” on This example switches the “Show Pages” option for a publishing site on. This setting is found in SharePoint on the Modify Navigation page.              if (!pw.IncludePagesInNavigation)     {         pw.IncludePagesInNavigation = true;         pw.Update();     } Example 2 – Exclude a page from the navigation This sample excludes the page called “IntroductieCMS.aspx” from the navigation. This is a normal page in the Pages document library.              PublishingPage page = webpages[string.Format("Pages/{0}", pagename)];     if ((page != null) && (page.IncludeInCurrentNavigation))     {         page.CheckOut();         page.IncludeInCurrentNavigation = false;         page.Update();         page.CheckIn("changed the navigation options");     } In this sample the page is in a Pages library that has versioning with the Require Check Out switched on. There we need to to a checkout of the item, before we can change it. Example 3 – Add a link to the navigation This example adds a custom link to the current navigation. This is the navigation on the left side of the page.              SPNavigationNodeCollection navNodes = pw.CurrentNavigationNodes;     SPNavigationNode newNavNode = new SPNavigationNode("e-office", "http://www.e-office.com", true);     navNodes.AddAsLast(newNavNode);     newNavNode.Properties.Add("NodeType", NodeTypes.AuthoredLinkPlain.ToString());     newNavNode.Update(); By using the NodeType property, you can choose the type of link. You can also use this to add a heading to the navigation. Please make sure that you first add the node to the collection and the set the properties in the HashTable. Otherwise the Properties value will be null and your code will fail. To add a link to the navigation on top of the page, use the GlobalNavigationNodes property of the PublishingWeb. These are just a few very basic examples, but they should be enough to get you started. Posted Mar 01 2007, 06:15 AM by tonstegeman with 1 comment(s)