Wednesday, July 26, 2017

Businesss Data Connectivity - Access denied!

I recently stood up a new SharePoint 2016 environment complete with patching and configuring to ensure it is as healthy as possible.  Once complete, one of the first actions I wanted to do was create an External Content Type and connect to a MSSQL database.  Done it before, should be easy enough...Nope.

Following Microsoft's steps to configure an External Content Type, I ran into the access denied issue right after trying to Add Connection.  The message returned was 'Access denied by Business Data Connectivity'.

Quick search shows I did not have permission to create new connections in the Business Data Connectivity service.  Alright, simple enough to add myself.  I launch Central Administration, click on Application Management, click on Business Data Connectivity Service and in the ribbon I click on Set Metadata Store Permissions.  New permission window, find my name and add to the group box, check the permissions and click the OK button just to be greeted by "Something went wrong..." message that once again tells me 'Access denied by Business Data Connectivity'.  This makes no sense as I am a farm admin, I should be able to make this change.

A few hours of searching and I tried several suggestions, all to no avail.  At this time I am really thinking outside the box and a thought occurred to me.  I was attempting to make these changes on my local client.  I have experienced some wackiness where the client browser and machine does not always play nicely with Central Administration, mostly not showing all the links.  So, I decided to RDP into the App server (using an install account that has the same permissions as my account) and add my user account to BCS Metadata Store Permissions, sure enough it worked.  In retrospect, I should have tried running the browser with administrative rights to see if that would have worked but hey, this way worked as well.

So, if you are having trouble adding a user or a group to the BCS Metadata Store Permissions, either RDP into one of the farm servers or try running your local browser as administrator and see if that works.

Tuesday, June 13, 2017

InfoPath Force Input Format aka Mask

I had a need to force the format of a text input field to a pattern of 12 digits, three sets of four separated by dash i.e. XXXX-XXXX-XXXX.  Also, I wanted the user to be able to paste or type in their entry and the form automatically apply the format.  So if users typed in XXXXXXXXXXXX, the form would automatically apply dashes where needed.  Conversely, if a user typed XXXX XXXX XXXX, I need the spaces stripped out and dashes applied.

Research showed common solutions of creating a validation rule which would force the user to retype the data.  I did not agree with this solution as the form should really be smart enough to handle this simple task.  After research failed to provide an answer, I started experimenting. Long story short, I found a solution that works well enough for me.

First, create an action rule that removes spaces.
  1. FIELD is not blank
  1. Set a field's value
  2. Field, should be the same field to which you are applying the rule
  3. Value is a formula: translate(FIELD, " ","")
This will remove any unwanted spaces

Second, create an action rule that adds dashes.
  1. FIELD does not contain "-"
  2. FIELD is not blank
  1. Set a field's value
  2. Field, should be the same field to which you are applying the rule
  3. Value is a formula: concat(substring(FIELD, 1, 4), "-", substring(FIELD, 5, 4), "-", substring(FIELD, 9, 4))
With these two rules, if a user types or pastes XXXXXXXX or XXXX XXXX XXXX or XXXX-XXXX-XXXX the field will be reset to the desired format of XXXX-XXXX-XXXX when focus moves to another field.  The only thing to note is any characters past 12 digits in this solution will be truncated.

This solution should be scalable to any need where you require a specific format to text.

Tuesday, February 7, 2017

Outgoing Email Fail

Recently stood up a SharePoint 2016 farm and configured outgoing email with data from a working SharePoint 2010 farm however during testing, alerts were not being received.

  • Verified the 'Immediate Alert' job was running every five minutes successfully.
  • Double checked the config information for outgoing email in Central Admin.
  • Verified the SMTP server was configured to allow emails from the Web and App server.
  • ULS was not helpful
  • Restarted SharePoint Timer service
  • Restarted IIS
  • Even restarted all servers in the farm
Before enabling verbose logging and slogging through all that data, I decided to broaden my testing scope and I created a simple workflow that sent me an email upon trigger.  The workflow completed with an error "The e-mail message cannot be sent. Make sure the outgoing e-mail settings for the server are configured correctly. For more information, please read this article:"

This information was not immediately helpful but it did confirm that the issue was within the SharePoint farm and not with Exchange or some other outside service.

Taking another look at Outgoing E-mail Settings, I see one setting that is available for SharePoint 2016 that was not available in SharePoint 2010; "Use TLS connection encryption" which is defaulted to 'Yes'.  I Set it to 'No', restarted IIS an the SharePoint Timer service.  Resetting IIS and Timer was probably unnecessary but I wanted to be sure.

I was now able to receive alerts and workflow emails.  Turns out our email server was not using a compatible version of TLS.  It would have been nice to find an error with these details.

So, if you are not receiving alerts and system emails but you are sure your environment is configured correctly, disable TLS and see what happens.  If you need encryption, work with your Exchange admin to ensure compatible versions are available.

Friday, January 13, 2017

Feed Cache Repopulation Timer Job(s) fail

For the past few days, I have been combatting an issue with failing timer jobs on two SharePoint Farms and I believe I have finally resolved the issue.

Specifically, the following timer jobs were failing with the below message.

Failed Timer Jobs
  • User Profile Service Application - Feed Cache Full Repopulation Job
  • User Profile Service Application - Feed Cache Repopulation Job
And the message was:
Unexpected exception in FeedCacheService.IsRepopulationNeeded: Failed to Decrypt data... (Correlation=XXX...)

Failed to decrypt data?  Do I have a permission issue?  ULS provided no extra leads so I ramped up logging to verbose but unfortunately, that also provided no new leads. Quite a bit of research on the Internet gave me a few suggestions such as restart IIS which worked for 20 to 40 minutes before the errors returned.  Unsurprisingly, error returned after rebooting the farm.

I also verified...
  • Permission for the UPS service account
  • Permission AppFabric service account (since this is a caching issue and AppFabric handles it)
  • UPS and AppFabric were using the same service account (Not required)
  • UPS and AppFabric listed the same cache host server
  • Version of AppFabric.  I am running 1.1 CU 7
  • Version of .NET Framework.  I am running 4.6.2
  • Firewall was configure properly
In my searching I found a nice set of monitoring scripts by Filip Bosmans, found here and while enlightening, did not give me an immediate answer.

During my search, I came across this page authored by Wictor WilĂ©n which gave me a lead!

"The three queues, or rather the three Timer Jobs are created per Web Application..."

This is when I noticed in Central Admin, Job Definition page that I had two web apps (mysites and portal) and the following jobs for both we are scheduled to run every minute.
  • My Site Instantiation Interactive Request Queue
  • My Site Instantiation Non-Interactive Request Queue
  • My Site Second Instantiation Interactive Request Queue
What is the connection you may ask?  These jobs create the Newsfeed and Microblog for new My Site profiles. SharePoint 2013 and 2016 cache Newsfeeds and Microblogs.

Another section of Wictor's page gave me my next lead.

"The Timer Jobs are based on the SPWorkItemJobDefinition Job Definition Type. This is a really nice timer job implementation that has a queue per content database"

This is significant as I have approximately 30 content databases and a new My Sites web app so only a dozen or so profiles.  For a large company, this could mean that every minute, these three jobs run against every web application and touches ever content database.  Frankly, that is insane.  Why do services that create My Sites content need to run against the Portal Web app?  Again, searching the Internet yielded no satisfying answer.  If you, the reader knows please feel free to comment below.  As a test, I disabled the three jobs that were running on the Portal Web Application.  The Feed Cache jobs took longer to fail but ultimately, they did fail.

With this information I start to suspect the cache was filling up but that did not make sense because even if the service was only running on MySites Web Application, a large company would have dozens and maybe hundreds of MySite content databases to fill the cache.  This is when Filip's monitoring scripts provided the potential solution.  On his "How to read the results page", one of the first issues discussed is Background Garbage Collection which is a feature provided in AppFabric 1.1 CU3 but it is not turned on by default.  You have to manually change a related config file for the feature to actually work.  So I did.

"After you apply this cumulative update, AppFabric uses a nonblocking garbage collection (background server garbage collection). Nonblocking garbage collection is a new feature in the .NET Framework 4.5.
To apply this fix, follow these steps:
1. Upgrade the servers to the .NET Framework 4.5.
2. Install the cumulative update package.
3. Enable the fix by using the following setting <appSettings><add key="backgroundGC" value="true"/></appSettings> in the DistributedCacheService.exe.config file between
   <appSettings><add key="backgroundGC" value="true"/></appSettings>
4. Restart the AppFabric Caching service for the update to take effect.
Note By default, the DistributedCacheService.exe.config file is located under the following directory:
%ProgramFiles%\AppFabric 1.1 for Windows Server"

My working theory is the cache was filling up and the 'failed to decrypt' message was not related to permission issues but to space issues.  Unfortunately I forgot to leave one of the farms in the original configuration as a control.  *shakes head in disappointment*  Warrants further research but I wanted to at least get this information out in the event it may assist someone else.

Wednesday, December 2, 2015

Display People Picker value.

Recently had to dive back into Infopath 2010 and I had an issue where I wanted to display the value of a people picker for a record but essentially have it read only.  Most of the solutions I found said to create a Text Box and assign it a value of the people picker.  I hated this solution because it creates another column in the SharePoint list, forcing me to administer duplicate data.  Blehg.

I found a better way.  Much like the above solution but instead of a Text Box, use the Calculated Value control.  All the same steps but instead the value is calculated at runtime instead of stored in a SharePoint list.


  1. Under the Home tab, look for Controls and click on Calculated Value.  This will bring up the Insert Calculated Value dialog box.
  2. Click on the function button (fx) bringing up the Insert Formula dialog box.
  3. Click on the Insert Field or Group button bringing up the Select a Field or Group dialog box.
  4. People pickers will not be displayed in the fields list.  You have to click on the Show advance view link at the bottom of the dialog box to change the fields view.
  5. What you should now see is a tree view of your data, most likely collapsed. 
    1. Expand dataFields
    2. Expand my:SharePointListItem_RW and find your people picker field.
    3. Expand your people picker field.
    4. Expand pc:Person.
    5. Click on DisplayName.
    6. Click the Ok buttons until you are back to your form.
You now have a calculated value control which populates with that records people picker value without having to store or administer unnecessary data.

Monday, January 19, 2015

No Provider Schema

Scenario:  You have a SharePoint 2013 list and have just created a custom New/Edit/Display form and you want to connect a couple of webparts together but SharePoint is reporting a "No Provider Schema" error.  Ok, what is that?  How do I fix it?  You spend several hours searching for an answer, most of which do not apply or are useless.  Finally you stumble upon this post.  I hope it helps.

I compared an out of the box form with connected webparts with a custom form to see the difference.  I noticed two lines of code encapsulating the webpart connections so I decided to copy just those lines (omitting the connections since they will not apply on the custom form) and pasted them into the new form, inside the table and just before SPDataSource if you have one.  Example below.  Code in question is highlighted.

 <table class="ms-core-tableNoSpace" id="onetIDListForm">
     <WebPartPages:SPProxyWebPartManager runat="server" ID="__ProxyWebPartManagerForConnections__">

   <WebPartPages:DataFormWebPart runat="server" ......

Once the code was in place, I was able to use the SharePoint web interface to connect webparts together.  I was also able to use SharePoint Designer to do the same.

Correction, I was only able to establish the connection between webparts using SharePoint Designer.  The web interface still reported "No Provider Schema" error.  Hmm...

This solution has only been tested with SharePoint 2013.  I assume it would be the same for 2010 however I cannot guess about 2007.

Monday, February 24, 2014

Center Top Navigation for SharePoint 2013

I had a need to center the top navigation on a SharePoint 2013 site and it needed to adjust depending on screen resolution and browser size.  I could have hard coded the margins but I wanted something that scaled.

Researching this issue took some time to come to a conclusion that is deceptively simple.  Most everyone was suggesting JQuery but I felt there had to be a simple way and it turns out, there is.

First, on your custom master page, wrap the top navigation control in a <div> with an ID or class of your choosing.  See highlight in my code below:

  <div id="nav">
            SkipLinkText="" />-->

Then in your custom CSS file, create a selector update that will auto adjust the left and right margin and use a percentage width for the navigation menu.  This will set the navigation window and allow it to float depending on the resolution and browser size.  You may need to update the width for your needs.

#nav {

No JQuery or complex coding required.