New Excel Services Articles and Code Samples

Two (2) new articles have been published centered on Excel Services programmability with accompanying sample solutions available in the Microsoft Download Center.  The first article demonstrates how to extend List and query tables on Excel Services and includes content detailing external workbook references, SQL write-back and user-defined functions.  The second article provides a more in-depth approach to developing UDF’s for Excel Services.


Extending the Excel Services Programmability Framework

Accompanying Solution:

SharePoint Server 2007 Sample: Extending Excel Services Programmability Framework Samples



Developing User-Defined Functions for Excel 2007 and Excel Services

Accompanying Solution:

SharePoint 2007 Sample: User-Defined Functions for Excel 2007 and Excel Services Sample Solution

Securing SharePoint Products and Technologies for the Extranet

When preparing to deploy an Internet accessible Microsoft Office SharePoint Server 2007 web farm security is the forefront of discussion and planning. To provide a brief insight into securing SharePoint Products and Technologies, I’ve decided to finally consolidate, compile and make available some of my notes. Hopefully this will be of benefit in helping others understand the method by which SharePoint Products and Technologies communicate and a high-level security overview. Internet Information Services (IIS) is the obvious first candidate for preparation and discussion –we’ve concluded IIS will be configured to use both basic and Integrated Windows authentication methods, basic allows credentials to be transmitted unencrypted and secured by SSL. The benefit of this is twofold, one it can be easily used in the extranet environment and two; basic authentication is part of the HTTP 1.1 protocol, supported by virtually any browser. Integrated windows authentication is provided to our intranet users and implemented as NTLM. SSL/TLS Transport Layer Security secures a channel between the browser and web server and IPSEC secures the communication between the web servers and the SQL backend and an additional IPSEC policy configured and applied to handle Search – Index server relationships when indexes are propagated from the index management server to the search server. In SharePoint Portal Server/Windows SharePoint Services user authentication is based on Windows security accounts, ASP.NET is configured to use Windows authentication for SharePoint Site Collections meaning ASP.NET relies on IIS to perform the required authentication of client(s). IIS will then authenticate the user against Windows security accounts and pass the identity to ASP.NET.

Transaction Paths

Often overlooked is the communication which occurs inside the environment itself. Communication in SharePoint Products and Technologies occurs in several distinct manners to include changes to the configuration where a web front-end (WFE) will communicate with the configuration database to relay changes to the deployment, change requests which include typical user transactions occurring in the content database submitted by the WFE such as updating/adding/deleting List items, documents, etc. Another transaction occurring nearly as often; however, more complex in the nature of the transaction are search requests – the user submits the request, the WFE then communicates with the query server to generate the results at which point the WFE will provide the content based on the previous transaction through communication with the content databases/content database server. Indexing transactions and requests must occur to both provide search results and build indexes through a separate communication channel with the content database/content database server. A proper IPSEC implementation and policy definition and application can secure these transactions to provide a high level of communication security within the datacenter and remain transparent to the consumers of the technologies. Microsoft Office SharePoint Server 2007 now supports configuration of the Shared Service Provider to leverage Secure Sockets Layers to secure a channel between the server machine(s) hosting the SSP and the Shared Services database(s), providing an additional layer of security within the web farm. Joel Oleson has a great post on this topic (25 Tips to Lockdown Your SharePoint Environment), covering high-level ISA, Kerberos, and Firewall considerations.


Common Extranet Design

Upgrading a Windows SharePoint Services 2.0 farm to Microsoft Office SharePoint Server 2007, supported?

I’ve recently been asked whether Windows SharePoint Services 2.0 can be upgraded to Microsoft Office SharePoint Server 2007; while this is possible, there are several important constraints that should be taken into consideration.  Foremost, you should not upgrade Windows SharePoint Services 2.0 to Microsoft Office SharePoint Server 2007 bypassing the Windows SharePoint Services 3.0 upgrade – by attempting to upgrading Windows SharePoint Services 2.0 to Microsoft Office SharePoint Server 2007 you are combining two upgrade methodologies; version to version (WSS 2.0 -> O12) and SKU to SKU (WSS 3.0 -> O12).  While possible in practice to achieve success by implementing this upgrade framework, it is both untested and unsupported, which should be taken into consideration when applying future QFE’s and service packs and what impact will result as a result of the state of your environment.  An example of this statement is that setup sets a Registry key indicating sku2sku upgrade, PSConfig recognizes the key and subsequently runs all of the Microsoft Office SharePoint Server 2007 upgraders on the farm which provides a true SKU to SKU upgrade.  In a Windows SharePoint Services 2.0 to Microsoft Office SharePoint Server 2007 upgrade, it is untested in whether or not those upgraders are run.  In the event the upgraders fail to run, Microsoft Office SharePoint Server 2007 version information will be missing on the Site Collections in their associated Content Databases impacting future upgrades and patching.  With that information in mind, the supported and tested upgrade path is Windows SharePoint Services 2.0 to Windows SharePoint Services 3.0 and then Microsoft Office SharePoint Server 2007.  This upgrade method will ensure the required upgraders are run as intended.  One of the most common applications of this upgrade method is an in-place upgrade of Windows SharePoint Services 2.0 to Windows SharePoint Services 3.0 followed by the installation of Microsoft Office SharePoint Server 2007 over the Windows SharePoint Services 3.0 installation – at this point the databases can be upgraded using the supported and tested SKU – SKU method.