Testing Access Services

This is part of my series on testing SharePoint service applications: http://matthewchurilla.blogspot.com/2012/02/testing-service-applications-in.html

There is a good article on MSDN for setting up a basic web database using Microsoft Access: http://msdn.microsoft.com/en-us/library/ff402351.aspx.  This article will walk you through the basics of creating a few access tables, publishing your database to SharePoint and then adding data into the database.  The site even has a sample database you can use to do your testing(http://archive.msdn.microsoft.com/Release/ProjectReleases.aspx?ProjectName=odcac2010h2&ReleaseId=3921).  I like to use this sample database as a starting point for my testing once I have started with this database I like to perform the following actions on the stock database just to get a better test of the available functionality.

1. Use web form to add a supplier: Microsoft
2. Use web form to add a supplier: Apple
3. Use web form to add a supplier: Google
4. Use web form to add a part for supplier: SharePoint, Microsoft
5. Use web form to add a part for supplier: Office, Microsoft
6. Use web form to add a part for supplier: Windows, Microsoft
7. Use web form to add a part for supplier: iPhone, Apple
8. Use web form to add a part for supplier: iPad, Apple
9. Use web form to add a part for supplier: GSA, Google
10. Use web form to add a part for supplier: AdSense, Google
11. Use web form to add a part for supplier: Blogger, Google
12. Switch back to Access and ensure that data is populated back into the database.
13. Create a new report to test reporting.

1. Show all fields from all tables
2. Add "Supplier Name" to the report
3. Remove Column "Supplier"
4. Remove Column "ID"
5. Add a Group on "Supplier Name"
6. Your final report should look like this.
7. Synchronize the report back to the web
14. View the report in SharePoint

At this point you have performed a basic test of Access Services.  If everything outlined here worked you can be fairly confident your service is installed and working properly.  We will keep the database site published for now we might use it in future articles.  

Document Management with SharePoint and CRM

Microsoft Dynamics CRM is a great application for managing customer, sales, and marketing data but it is not a very good document management system.  Fortunately Microsoft has another product named SharePoint that is a great document management and storage system and now with the new version of CRM you can link entities to SharePoint so you can store related documents in SharePoint and utilize the great document management features provided by that software platform.  In this blog posting I am going to give an overview of the integration possibilities between Microsoft Dynamics CRM and Microsoft SharePoint.

First off let us take a brief look at the Document Management section of the Microsoft Dynamics CRM Settings to see what is available to us. 

1. Document Management Settings – This option allows you to do the base configuration for Document Management.  This is where you go to configure which entities in the system document management is enabled for and where you can specify the primary document management location if you choose to have one. 
2. Install List Component – This option points you to instructions for downloading and installing the SharePoint list component that you can install as a sandboxed solution for sites that you wish to enable enhanced document management on.
3. SharePoint Sites – This section lists all of the sites that are linked to CRM but unfortunately it is only useful for sites that have the SharePoint List Component installed on them.   If you are not using the list component adding sites to this section will not have any effect in other parts of the system.
4. SharePoint Document Locations – This choice is a great place for administrators to reference.  Listed in this section you will find every location that is linked to from CRM.

Starting at the top let’s examine the document management settings section.   The primary listing on this page allows you specify which entities you would like to enable document management on.  A number of these are enabled by default however you can enable document management for any stock or custom entity that exists in the system.  There is a second option present on this screen and that is an option that lets you specify a SharePoint 2010 site URL to enable automatic folder creation; this provides you the capability to automate your document management with a SharePoint 2010 site that has the List Component installed on it.  We will re-visit this later when we review the 2010 only features. 

Next we are going to take a quick look at how things look once you have document management enabled so you have a good feel for how linked document libraries will behave.  So without further ado...

Here is a sample view of how a site will look when linked via an absolute URL.  Notice how this is just an embedded view of the SharePoint Document Library, because of this, you are capable of doing anything in this embedded view you could do normally in SharePoint.  The downside is it makes for a busy screen because there is a lot going on visually and the two different UI styles clash a little bit.

And here is a sample screenshot of how linked document libraries look for sites that have the SharePoint List Component installed on them, notice how the document library has a more integrated look and feel to it this exposes all of the document management capabilities of SharePoint to the user through a more seamless CRM user interface.

The last thing I want to point out is that you are able to link more than one document location to each entity.  You are capable of having many locations which will allows you to separate documents into different locations based on similar properties.

Next let’s move on to linking CRM entities to SharePoint document libraries.  For those of you with SharePoint 2007 this will be the only integration option available to you.  However this functionality is not only available to just 2007 users; if you are operating in an environment where you cannot install the SharePoint 2010 List Component you can still use Document Locations to connect SharePoint 2010 and CRM.  Document Locations are rather primitive because they just embed an HTML viewer in the CRM page that simulates browsing the linked document library in a web browser.

When you add a Document Location and don’t have any Site Locations defined that have the SharePoint 2010 List Component installed you will be presented with an “Add Document Location” dialog box that will only be capable of linked via an absolute URL to a target document location.  In order to do these just give your document location a name and enter the full SharePoint URL you wish to link as a document location. 

Now let’s look at the SharePoint 2010 List Component; this item allows you to directly link document libraries from SharePoint 2010 into CRM.  I will not go over installing this component on a SharePoint site because this process is well documented elsewhere (http://www.microsoft.com/download/en/details.aspx?id=5283).  Once you have installed the list component on a site you will have a few new capabilities available to you. 

First you will now be able to set an automatic folder creation site.  When you enable this feature you will be prompted on whether you want to structure your folders by accounts or contacts.  Whenever you enable this feature entities that are related to the based on entity will have all of their documentation consolidated in the same folder structure within SharePoint.  For example if you had an account named Account 1 that had an opportunity named Opportunity 1 then you would end up with a folder structure that was something like “Account 1\opportunity\Opportunity 1” similarly if there was a quote named Quote 1 the folder would be named “Account 1\quote\Quote 1”.  If you don’t select to group items based on entity it will group everything within a new document library named after the entities that are contained within it.  Once you have enabled automatic folder creation the first time someone visits the document section of an entity they will be prompted to create a new folder in the appropriate location.  Personally I don't like to enable automatic document library creation, I feel that this removes some of the flexability of the system but this could be very convient for some organizations.  Below are some screen shots of how this appears in the CRM interface.

The second thing you will now be able to do is add sites into the SharePoint sites list.  Adding sites here is a good way to allow users to create folders.  When you have sites listed in the SharePoint Sites list users will now have a second option when adding a new document location.  This option allows the user creating a new document location to specifiy a new folder under an existing site that you have added to the SharePoint Sites List. 

The last section we need to visit is Document Locations and it is a very good section.  The Document Locations option enables you to view every link between CRM and SharePoint so it is a great place to review for duplicate connections and to ensure that the integration is being used properly.  You will notice in this final screen shot the difference between how absolute URL links are displayed compared to Document Locations that are setup based on a base SharePoint Site and relative references from there.

And that completes this posting on Document Management for Microsoft Dynamics CRM.  I hope you are a little more informed now. 

Configuring Secure Store

This is part of my series on testing service applications: http://matthewchurilla.blogspot.com/2012/02/testing-service-applications-in.html

We will be working on testing service applications and in order to do this properly we will need to configure the secure store service.  We are configuring this service to test the ability of service applications to access external data sources.  If you don't care about accessing external data sources from your service applications then you can skip configuring the secure store service and any future tests that involve it.

In order to not reinvent the wheel Microsoft already has some documentation on configuring most service applications and I will link to it when possible.  Here is the document for configuring the secure store service application: http://technet.microsoft.com/en-us/library/ee806866.aspx.  The steps you will want to perform in order to have a properly configured service application.

1. Generate a New Key
2. Create a new Target Application.  I used the parameters to the right.  Be aware setting the members to All Authenticated Users is not recommended this will allow any user to authenticate to external data sources that use the target application ID.  But since this is for testing purposes we are going to use it to make things easy.
3. Set credentials on the target application.

Once you have a target application setup you will be able to use this application in other service applications to test data access to external data sources.

Testing Service Applications in SharePoint 2010

So you've taken time and built a farm, but you still wonder how do I test all of this?  Well stay tuned this week I  am going begin writing a series on how to test each and every service application that ships with SharePoint 2010.  I hope that this will become a good resource for those of you, who like myself, would like to be able to test their farm and ensure it is running properly before releasing it to end users.  I will update this post as I release new articles in this series with links to each article.

Before testing applications setup a secure store: http://matthewchurilla.blogspot.com/2012/03/configuring-secure-store.html
Testing Access Services: http://matthewchurilla.blogspot.com/2012/03/testing-access-services.html

Central Administration Prompts for Credentials

I stumbled across a quirky authentication issue when we enabled Kerberos on our Central Administration Web Applications for SharePoint 2010. Every time we launched Central Administration using the link provided on the start menu we would be prompted for credential entry, and everytime credentials were entered they would fail even if they were entered correctly. After double checking the SPNs it was obvious they were setup properly and Kerberos authentication should have been logging us in automatically.

 It turns out the problem here is somewhat related to another issue I posted about with our CRM environment (http://matthewchurilla.blogspot.com/2012/01/outlook-plugin-is-nice-feature-of.html). At first I looked at the command that was getting executed by the menu link and it was pointing to psconfigui.exe so it was hard to tell what was happening there. Next I opened internet explorer and found the Central Administration URL had been added to trusted sites and trusted sites does not automatically login users, only the intranet zone does this. It occured to me that psconfigui.exe must do something similar to what I discovered the CRM Outlook plugin doing.  In an attempt to be helpful it is adding the Central Administration URL to trusted sites but unfortunately when the URL is in trusted sites kerberos integrated authentication does not work. The solution to this problem is as simple as it was for CRM you just need to follow this process:

• Find the URL that got added to trusted sites and remove it.
• Internet Options -> Security -> Trusted Sites -> Sites
• Open Local Intranet configuration and add the URL you just removed from trusted sites.  Make sure the URL matches exactly.  If you removed http://machine from trusted sites then add http://machine to local intranet sites.  If you removed http://machine.fqdn from trusted sites than add http://machine.fqdn to local intranet sites.
• Internet Options -> Security -> Local Intranet -> Sites -> Advanced
• Launch Central Administration using the start menu link; everything should work fine.

Error Installing KB976462 during SharePoint Pre-Requisites Installer

I was running the SharePoint Pre-Requisites installer this week. Upon competition it failed to install KB976462 and then skipped the rest of the installers. It turns out a second running of the installer then successfully installed the rest of the pre-requisites. I could have just continued on my merry way, but having a weird issue like this hanging out there can usually lead to some very strange issues further along in the install process so I dug in to figure out what was wrong.

The first step was to figure out what the KB was and this lead me to the Microsoft kb article for it:
http://support.microsoft.com/kb/976462. It turns out this is a patch to the .NET framework 3.5 SP1, this seems like a crucial thing to have installed properly. Next I logged into one of the other servers provided to me and checked to see what versions of .NET was installed. In order to determine the .NET versions I navigated to the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\NET Framework Setup\NDP\ upon viewing the versions installed in this key I noticed that only version 2 or .NET was installed.

On this other machine I then used the proper method to activate .NET v3.5.1 by adding the windows feature from the server management console. After the feature was activated windows update was run to make sure that all of the current patches for .NET were downloaded and installed. After the feature was activated and windows update was run the pre-requisite installer was executed and it ran without any errors.

To be on the safe side I had the first server rebuilt because installing SharePoint on a server with an improperly installed .NET v3.5.1 did not seem like a good path to go down.

The username is invalid. The account must be a valid domain account

Today while configuring a farm I stumbled across a weird error I have never seen before. Upon entering the credentials for our farm account and error message popped up stating: "The username is invalid. The account must be a valid domain account."

As part of our deployment we wanted to make our account names very descriptive of what they were used for. This policy led us to use a very descriptive and lengthy account name that was similar to SP2010_ServerFarmAccount. This seemed like a great idea at the time because it is very obvious what this account was used for. But as it turns out lengthy user names might be a bad idea in a Windows environment.

Those people out there who are Active Directory administrators might already know what is coming up as the root of this problem. It turns out that Active Directory will let you make very lengthy user names; unfortunately there is a second field that is kept around for legacy purposes that truncates the user name to 20 characters. So using domain\SP2010_ServerFarmAccount was not the proper thing to do because the account gets truncated for authentication purposes to domain\SP2010_ServerFarmAcc.

The solution to this problem is pretty easy. Login using either the legacy form domain\SP2010_ServerFarmAcc or if you prefer to use the whole user name you can use the more modern format SP2010_ServerFarmAccount@domain.fqdn.

Couple Installation Issues This Week

Lately I have been doing a lot of SharePoint 2010 installs and this has lead to a couple of unusual problems that others might run into so I will document them in the next few posts so if anyone else runs into these issues they can be a little bit closer to fixing them.

Authentication issues with the Microsoft CRM Outlook Plugin

The outlook plugin is a nice feature of Microsoft Dynamics CRM that enables users to view CRM information from within outlook. I have been a longtime user of the plugin but came across a significant problem with authentication once CRM Online became popular. The problem was that when running in IE everything seemed to work fine for the users but once they switched to the outlook plugin they will constantly get asked for a username and password. The root cause of this is because in some Rollup Microsoft decided that the plugin would add the URL it uses to access CRM to the trusted sites list upon startup and this is the root of the problem.

Adding the URL automatically to the trusted sites list is great for users of CRM Online or other third party hosted CRM. Back in the early days of CRM Online one of the most asked questions was why is CRM not loading properly in my environment and the most common answer was that CRM was not in the trusted sites list of IE. Unfortunately for those of us who use the on-premise version having CRM in the trusted sites list is a little less than convenient. One downside of having the site in the trusted sites list is that integrated authentication does not happen in the trusted sites zone, I don't condone enabling integrated authentication in the trusted sites zone just to get this working there is a better solution below. As you may know integrated authentication only happens in the Intranet zone with the IE default settings. So in order to get the plugin to stop asking for a username and password you will need to add the site to the Local Intranet Sites list.

Internet Options -> Security -> Local Intranet -> Sites -> Advanced

Be aware when adding your site to this list that adding a wildcard will not work in this case it needs to be the exact URL that you use to access CRM. So if I work at foo.com having *.foo.com in the local intranet list will not work, if this is done the plugin will still add the specific site to the trusted hosts and authentication will still be broken. You need to add the exact CRM host ex. https://crm.foo.com/ when the outlook plugin sees this in the local intranet list it will skip the step of adding the URL to the trusted sites list and authentication will work successfully. As an easy solution I suggest just pushing this change out to all hosts via group policy, if you do not use group policy then you will need to manually go and add this URL to the local intranet sites.

If you have yet to install the outlook plugin then you should be good and everything should work fine.  However if the outlook plugin is installed on the machine you need to uninstall (and double check to make sure the site is still in the local intranet zone) and then re-install the plugin.

I wish there was a better solution to not have the plugin add its URL to the trusted sites list but unfortunately this is the only solution I have been able to find that works.

HTTP 503: Service Unavailable message from SharePoint 2010

I have been upgrading a number of SharePoint 2010 farms to the December 2012 cumulative update. During the install process I have experienced repetitive failures when running the SharePoint 2010 Products Configuration Tool. The tool would advance to some percentage of step 9/10 and then it will just mysteriously fail with no reason given in the dialog box or in the configuration log files. Fortunately recovering from this failure is easy, all you need to do is just re-run the configuration wizard. While I have yet to confirm this, and I'm not sure I can confirm it in any way, I believe this issue might be related to us running SharePoint 2010 on servers that only meet the minimum suggested requirements for memory and processor.

Unfortunately after successfully running the SharePoint 2010 Productions Configuration Wizard and starting Central Administration I was presented with an HTTP Error 503 Service Unavailable. Thinking that this might have just been an issue that affected our Central Administration site I next attempted to browse to a normal site and still got a 503 Service Unavailable message. So I started to scour the internet looking for an answer but couldn't seem to find any reported problems that matched the problems we were having, and there were no signs of a solution that seemed to make sense given the symptoms of our problem. So I began the task of trying to figure out the problem myself.

I started my search at the bottom and opened up the windows service console. Checking the related IIS services they all seemed to be started and running without errors. This lead me to start up the IIS Manager and search for something within it that would suggest it was the cause of these issues. Eventually I was looking at our Application Pools for SharePoint and noticed that the Application Pool for our Central Administration was stopped and in addition a number of other Application Pools related to SharePoint were stopped (You can see this next to #1 in the image below). I started up all of the stopped application pools and then went back and tried the Central Administration site and it was there and working. Just to ensure that this problem did not arise after a reboot I double checked the advanced settings for each Application Pool (#2 below) and ensured that the Start Automatically property was set to true (Pictured below).

The short version of the above for people like me to who hate reading and are just here for the solution:

Received HTTP Error 503 Service Unavailable when accessing SharePoint Central Administration and other sites after a failed execution of the SharePoint 2010 Products Configuration Wizard.

Found the problem was because our SharePoint IIS application pools were not started (1).

Started the application pools and double checked the advanced settings (2) to ensure that the application pool was set to automatically start. 

SharePoint was running fine after the application pools were started and all SharePoint requests were processed successfully.