SharePoint alerts stop working when you add a new web front-end server to the farm

SharePoint alerts is a very useful user notification service. It is heavily relying on out-going email configuration. There are many on-line articles talk about how to setup out-going email services and troubleshoot alerts issues. Today, I would like to share a different scenario I had when alerts were not working properly.

We have a SharePoint farm and alerts have been working properly for years. In the past few months, we were in the process to add a new web front-end server to replace the current one, not doing load balancing (regarding this decision, it is another story).

It took sometime to install and configure the new server to make it ready to join the farm. After I ran the configuration wizard, I noticed that some of the immediate alerts stopped working. However, I still received daily and weekly summary alerts and emails sent by workflows were fine.

Obviously, out-going email service and configuration was working OK, otherwise I would not receive any emails from SharePoint. Alerts did not stop working entirely, only immediate alerts were stopped. Nevertheless, it did show that this problem has something to do with the new server.

When I checked the server events, I did see errors regarding "cannot connect to the SMTP mail server". At the time I saw these errors, the new server were not opening to end users and I was the only one using local Hosts file to gain access to SharePoint. The in-coming email service was running fine on the old web front-end server and I did not install SMTP components to the new server. There was only one service running on the new server, which is the SharePoint foundation web application service to allow users to access SharePoint sites and content.

No changes were made to either email, Exchange or alerts configuration and this service has been running fine for years from the existing web front-end one. I knew the new one could not connect to the email host, but the old one should and why alerts stopped working?

After some research, I thought perhaps SharePoint needed everyone on the same page, otherwise services would stop working even though one of the servers in the farm was capable doing that. In order to eliminate this possibility, I added the new server to the Exchange anonymous relay list. I added the old web front-end server to it when I established the farm. Few minutes after the new server is added, immediate alerts started working. The email host connection errors were gone and everything went back to normal.

So, it did not occurred to me that SharePoint is working like a gang: you don't go and we don't go. :) Even though other servers could handle alerts, SharePoint did not want leave one of them behind and decided not to functioning as it should be. 

Lessons learned.

SharePoint Managed Metadata values did not show up after record was saved

In SharePoint 2010 and 2013, you can configure Managed Metadata service to provide a enterprise-level metadata source and management interface. I ran into this scenario when I have the term set and terms ready to use:

1. As a site administrator, I was able to have a column ready the term set from the service.
2. I was able to create a new or update an existing record and select terms from the term set.
3. I had no problem to expand any sub groups and pick any values.
4. I had verified that all values were added to the field before I save the record.

However, when I reviewed the update either from the item form or list/library view, no values showed up as they should be. If I logged on to the site with a site collection administration account, I was able to see those values.


This scenario told me that the updates did saved correctly but some where the permission settings may be not correct. After some research on line, I found out there is a hidden list at the top level site of every site collection. This hidden list has all the metadata values used by any list or library in the same site collection. All users who can access any place within this site collection should have at least read-only access tot he hidden list. The generic URL of this hidden list is at:


http://Root/ManagedPath/SiteCollectionName/Lists/TaxonomyHiddenList/


The Root is the root URL of the web application. It could be the Host Header of the IIS virtual site or the server name with a port number, depends how you created it.


The ManagedPath is the one used in your environment. One of the common default value is Sites.


You need to make sure or adjust permission settings to this list to allow all users with Read-Only permission.


Once I changed it, all values did show up. I went through all site collection to make appropriate changes.


Problem Solved.


I hope this information would help you when you need.

SharePoint 2010 list suddenly could not export to Excel

Few weeks ago, a site administrator called me for help . He has a list with only 350 rows and he could not export data recently. When I did investigation and compare to other lists, I could export another list with more than 4000 rows. When I tested this particular one, I could export up to 100, but not 101 or more. When this happened, Excel would freeze for a while and then display a pretty vague error message.

There was no errors logs on the server side, nor on the local machine. Since I was able to export another list with lot more rows, this has nothing to do with SharePoint or Excel.

I kept looking around and found out the list was customized with InfoPath. I open the form template with client and noticed that there were changes made to the list, but has not implemented to the form template.

From my prior reading on-line, there were posts mentioned that Excel exporting feature is pretty picky. Since the list was changed, I asked the site administrator to refresh and re-publish the for template. Once he did that, he could export the whole list without any errors.

Next time when you encounter issues like this one, check whether the list is customized with InfoPath and whether the template is up to date.

SharePoint 2013 User Profile Service requires SQL 2008 R2 Native Client

User Profile Service is one of the service that needs additional attention and exact steps to plan and configure it. If you miss any steps during the configuration, you may have to delete it and start all over again. The best information on line to take you through the process is still the one from SharePoint 2010 time:Rational Guide to implementing SharePoint Server 2010 User Profile Synchronization.

Even though you may follow his and other articles precisely., you may still run into few issues. One of them I have experienced recently and took me two weeks to figure it out.

The environment I am configuring is using SQL 2014 with Windows 2012 R2. When I configured User Profile Service and started first sync, everything ran correctly. Two weeks later, I found out that the service did not run any more. I tried few times, not working. I reviewed my steps, change servers and check databases, no progress. The problem I had was: after I created User Profile Service Application, I could start User Profile Service from CA, but not Sync Service. When I reviewed the Services console, both FIM and FIM Sync service were disabled.

I tried to manually start FIM service and got an error message as following:

.Net SqlClient Data Provider: System.Data.SqlClient.SqlException: Could not find stored procedure 'RegisterService'.

I also noticed that the Sync database did not have any table or stored procedures populated. I tried to find more information regarding why this database was not created correctly when Profile and Social databases were provisioned correctly? Some articles mentioned that the Farm Admin account (the one is running CA application pool if you have separated setup and farm admin accounts) should own the DBO schema. However, this is not the case to me.

After a while, I was told that perhaps I should check my SQL native client installation. From an article regarding User Profile Service with SQL 2012, I noticed that SQL 2008 R2 native client may be required even though I am using SQL 2014. I also recalled that I have uninstalled SQL 2008 R2 native client after the User Profile Service was provisioned successfully. 

I went to re-install SQL 2008 R2 native client to all of my SharePoint 2013 servers. Finally I could create this service successfully. During the last provision, I had chance to observe how the service and databases were populated. I will included them at the end of the blog. So, even though you are using SQL 2014, you still need SQL 2008 R2 native client to create and run User Profile Service. So, do un-install it like I did.

The service provision process is here:

1. When I created the service application, all three databases were created. Sync DB was empty. No tables nor stored s[procedures.
2. When I started User Profile service from CA, nothing really changed.
3.  When I started User Profile Sync Services from CA, things changed like this:

a.      FIM Synch service changed from disabled to automatic first.

b.      FIM Synch service started.

c.      Sync DB started having tables and stored procedures populated.

d.      FIM service changed from disabled to automatic

e.      FIM service started.

Enable Windows 2012 R2 Features for SharePoint 2013 SP1

The information regarding installing SharePoint 2013 Sp1 on Windows 2012 R2 is limited, especially what do you need to do an offline installation. In our case, we have Windows 2012 R2 configured with a standard configuration from the server team and deliver to us. These servers have no internet connection at all. So we have search on line and found some good articles to start with. Here are the ones I have referenced to:

http://blogs.technet.com/b/cjrawson/archive/2014/04/06/offline_2d00_sharepoint2013sp1_2d00_prerequisites_2d00_server2012r2.aspx

http://social.technet.microsoft.com/wiki/contents/articles/14582.sharepoint-2013-install-prerequisites-offline-or-manually-on-windows-server-2012-a-comprehensive-guide.aspx

http://soussi-imed.over-blog.com/article-installing-sharepoint-2013-on-windows-server-2012-r2-120547920.html

Before you run the Prerequisite, you need to configure server roles and enable some features. If you can run the feature-enable PowerShell script from those articles, congratulations!! In our case, we could not.

So, we have to manually using UI to enable required roles and features. All of the articles I have read so far mentioned following features are needed:

Roles

• Application Server
• Web Server (IIS)
• IIS 6 Management Console

Features   

• ASP.NET 4.5
• Windows Identity Foundation 3.5

During the process, you also need to enable IIS Support (separate item to check the check box). There are few required features needed that would install together.

We followed and enabled all of them in one of our servers. However, Prerequisite returned en error regarding it could not configure IIS. After looking into the error log, we found out Prerequisite was looking .NET 3.5, which was not mentioned in any of the articles we found.

Once we have .NET 3.5 enabled, it went through like a charm.

I believe sometime when we put down some information to share, we may accidentally assumed something was there or available. In this case. .NET 3.5 was needed but not mentioned.

Before you start your journey, make sure .NET 3.5 is also enabled with your Windows 2012 R2 servers.

Good luck!

SharePoint User Profile Services and Different Domain Names

If your environment has User Profile Service, have you noticed that whether all users are under the same domain? In our case, we were not and it created other issues when we want to use profile data. For example, when we want to query a user profile data, we don't know which domain this user was mapping to and it is hard to predict. In order to fix this problem, we have to fix this problem and have all users under the same Net BIOS domain name.

There are articles to show you how to do it. For example, you can find steps from this blog: http://tremblayse.wordpress.com/2011/10/17/ups-and-netbios-domain-name-different-then-fqdn-domain-name/. Since there are enough information on-line already, I would like to just mention our experience when we perform this change.

1. Make sure you have tested it in a non-production environment and document all your steps. This is very important to help you when you implement it in your production environment.
2. We have User Profile Service been running for a while. Some articles mentioned that you may want to delete it first. From my opinion, you don't have to, especially if your environment has large number of users or they have updated/added information to their profiles.
3. Before you start making changes, make sure both services are running. If you are not sure their status, you may want to go to the Central Administration site and restart both of them, one at a time.
4. After using PowerShell to enable NetBiosDomainNamesEnabled attribute, run the command with "True" to confirm it is up and running.
5. Reset IIS and restart timer job services. I did not do it in the test environment but had to do it in production one.
6. Some article mentioned that you can delete the profile sync connection before you executing the PowerSheel command. I did not do this first and you may want consider to delete it first. However, make sure you have a screen shot of the connection settings page before you delete it.
7. When you delete the connection and failed, don't panic and just restart both profile services again from the Central Administration site. Try to delete the connection again. If it is not there after you restart both services, you are fine.
8. After the new connection is created, run full sync once and give it several night to verify the number of users are imported. If the number does not seem to be right, make sure you have selected all OUs that have users. Rerun full sync if necessary.

The whole process may take over night to import and re-process all profile data. When it is done, you should be able to find all users are mapping to the Net BIOS domain name.

Hope this will help you to go through the changes smoother.

Account Permissions Needed to Integrate SSRS with SharePoint 2010 in Separated Machines

When Microsoft released SharePoint 2010 and SSRS 2008 R2, the integration process is lot smoother and easier then before. There are tons of information on-line to show you how to do it. I can easily find how to plan the environment, what kind of service accounts and permissions I need, how do I configure in different scenarios.

So, with so much information, I should be able to do it easily without too much trouble. Well, not exactly. One of the problems I have experienced in the past years with SharePoint configuration information is, a high percentage of them out there have assumed something but were not described clearly. For example, when I tried to configure MOSS 2007 to integrate with SSRS 2005, most of the articles I found at the time assumed that your environment was using Kerberos. However, we did not use Kerberos so it's hard to find out what should be done. This is no one's fault and it's almost impossible to put every possible scenarios into an articles. We just need to keep this in mind when we do our research.

The environment I was trying to work on was:
One WFE
One SSRS without database
One database server with all SharePoint databases

The service accounts I have are:
One Farm setup and admin account, which has DB Creator and Security Admin role. It does not have any other roles to the database system and it could not drop any databases.
One SSRS service account, which has no other rights in the database or farm.

The farm setup and admin account had created databases for web and service applications. When I logged on to the SSRS server using the farm admin account and started the SSRS Configuration tool, I had no problems to add SSRS service account and create web application for Report Server. I came to an error messages when I executed script to generate two databases for SSRS service. Though the databases were created but the whole process stopped and returned Insufficient Permissions errors.

I tried to add the SSRS service account with DBO role to the two newly created databases, I was not able to connect them through the SSRS configuration tool again. I got different error message said these two databases were not in the correct format.
I have followed the articles I can find regarding service account requirements and still not able to figure out why. Then I recalled that when we configured MOSS and SSRS 2005 integration, we needed farm admin account to have database System Admin role to the instance. Usually this is only assigned to the service accounts used by DBA team and not service accounts used by applications. I asked for this role in our instance and everything went through without another errors.

As far as I could recall, I haven't read an article mentioned this role or I may not do a good job when I researched it. Any way, this is my experience and if you run into errors when integrate SSRS with SharePoint 2010, give this a try.

Install SQL 2008 Express Management Studio

If you are using standalone instance of SharePoint 2010 and want to add SQL Express 2008 Management Studio to the machine, you may encounter errors during installation. The option looks valid is to add feature to current instance. however, it is not the correct one.

After some search on line, I found this blog with detail information Turns out you need to select New Instance option (I have no idea why) to continue. So if you were not able to install management studio for SQL Express 2008, try this article first.

Where is the Datasheet View in SharePoint 2010

If you have installed Office 2010 64-bit to a 64-bit OS machine, you may find out you are not able to use Datasheet view in a library or list. This will happen to users who is working on a single machine or VM environment with SharePoint 2010 and Office 2010 installed together.

So, what can you do?

According to Microsoft, you can install Office 2010 32-bit instead. The other option is mentioned in this blog, install Access Runtime for 2007 or 2010. When that blog was published, Access Runtime 2010 is still in Beta version. Now you download the full version from here.

Find closed web parts all over your portal

For most users, it's very common to close, instead delete a web part from pages, especially the landing page of a site. As an administrator, you can go to each page and open web part maintenance page to check and removed closed ones.

However, how do we find out which site has the most and deal with them first if you have hundred of sites to manage? You can use a SQL query to geerate a list. (MIcrosoft said do not touch your database, so, this is just for your own reference and risk).

select ''+dirname+'/'+ leafname+'?contents=1'
from alldocs inner join webparts on
Alldocs.id=webparts.tp_pageurlid
where tp_isincluded=0

New book: The Black Bats: CIA Spy Flights over China from Taiwan 1951-1969

I never met Chris before but Clarence Fu is one of my best friends in military aviation field. We have known each other for more than 20 years.

Clarence has published couple books regarding military aviation history of Republic Air Force, including F-100, F-104 service records with ROCAF, covert airborne missions during the early eras of cold war.

History of U-2 missions with ROCAF was rarely mentioned in the English world and I am more than happy to see books like this available. If you are living near National Museum of US Airforce, Chris is going to introduce this book. You can find the news at: http://www.nationalmuseum.af.mil/news/story.asp?id=123199296

You can find this book here: http://www.schifferbooks.com/newschiffer/book_template.php?isbn=9780764335136

THE 1958 QUEMOY CRISIS AN ORAL HISTORY

I like to read history books, especially military history. In recent years, I have chance to know or obtain some good materials that I like to introduce to you. Although this conflict happened more than 50 years ago, rarely this type of historical information was published in English.

THE 1958 QUEMOY CRISIS AN ORAL HISTORY is published by the Military History and Translation office under Ministry of National Defense of Republic of China (Taiwan is usually called by westerners). This was the last major battle between Taiwan and Communist China and some of the U.S. military personals were involved.




The team worked on this book interviewed 28 people in the both military services and some of them became higher ranking officers in both countries. Lots of photos show readers people and events during that periods from different point of views.

I first heard this book last year from a friend of mine. I got it when I went back to Taiwan to visit families. I also had chance to obtain the other two in this series also published in English and I will introduce them later.

If you would like to order a copy from the published, here is the web site. The author of this blog offers couple copies he received and I am not sure he still has any available.

Enjoy it.

SharePoint Conference in the USA in Year 2010

These are the SharePoint conferences in the USA I know so far:

The SharePoint Technology Conference
2/10 -2/12
San Francisco
http://sptechcon.com/


Microsoft Teched North America 2010
6/7 – 6/10
New Orleans, LA
http://northamerica.msteched.com/?CR_CC=100280255&WT.srch=1&CR_SCC=100280255&WT.srch=1

SharePoint.org Conference
4/18 – 4/21
Baltimore, MD
http://www.sharepointconference.org/Pages/default.aspx

The Experts Conference
4/25 – 4/28
Los Angeles
http://www.tec2010.com/

Best Practice Conference
8/24 – 8/27
Washington DC
http://www.bestpracticesconference.com/?gclid=CKGBwIKcx5QCFQ2L1QodLlYVkQ

Magic Dragon Books - Aviation, military and history

Besides IT, military aviation and history is a big part of my life and reading materials since I was in elementary school. In the past years, I wrote and translated articles either in Chinese or English through various publishing medias, digital or printed. I also get to know other people who have the same interest and expertise and become good friends.

Some of them are specialized in the Republic of China (ROC) air force history, either during the war with Japan or after 1945 with Communist China, including secret reconn missions which only been published or heard in recent years.

Magic Dragon Books is an on line book store to bring you these military aviation history unknown to most of the world in Chinese or English. I am not part of the company or business operation, neither details of the company itself. I know this due to a respectable and long time friend who dedicated himself in this field for years.

On the other hand, I always believe history has multiple faces and we can only understand more from various sources. Some of the ROC air force operations involved missions with PLA. Compare to the influence red China has to the world, it it more important than ever to bring up the stories from the other side, not to correct anything, but to provide a different angle, data sources or point of view.

If you are interested to know more about the published materials, please visit the site at: http://magicdragonbooks.com/

Find user permissions assigned in one or more site collections of all items

MOSS does not provide out-of-box interface to view assigned permissions to the same user cross site collections. Although there are tools to help you, no matter it's paid or free, if your environment does not allow free tools and you have to find it for now, what can you do?

Since everything is stored in SQL database, why not write some SQL statements to find them? That's what I have been doing before we have a nice tool to use. If you already know the structure of tables used by MOSS, then you may be able to do it in a short period of time.

If not, here is the SQL query you can use to do it:

Select 'http://yourportal/'+a.scopeurl as ObjectURL, a.title as UserID,roles.title as Permission,a.SPgroup, a.ADLogin
from roles inner join
(
select perms.scopeid, perms.scopeurl, perms.roledefwebid, alluser.title, alluser.ADLogin,alluser.SPgroup,roleassignment.roleid, roleassignment.siteid
from perms inner join
roleassignment on
perms.siteid=roleassignment.siteid
and
perms.scopeid=roleassignment.scopeid
inner join(
(
select groups.siteid, groups.id as id,userinfo.tp_login as ADLogin,userinfo.tp_title as title, groups.title as SPGroup
from groups inner join
groupmembership on groups.siteid=groupmembership.siteid
and
groups.id=groupmembership.groupid
inner join userinfo on
groupmembership.siteid=userinfo.tp_siteid
and
groupmembership.memberid=userinfo.tp_id)

union

select tp_siteid as siteid, tp_id as id,userinfo.tp_login as ADLogin, tp_title as title, 'no' as SPGroup from userinfo) as Alluser
on
roleassignment.siteid=alluser.siteid
and
roleassignment.principalid=alluser.id) as a
on
roles.siteid=a.siteid
and
roles.webid=a.roledefwebid
and
roles.roleid=a.roleid

Let me explain the columns you get:

1. frist colunmn is the whole URL of an object (site, , sub site, library/list, folder oritem) which has unique permission
2. The User ID
3. Permission assigned to this user.
4. SharePoint User Group this user is assigned to. If the permission assigned to this user individually, this column shows No.
5. User log in ID.

So, this query will tell you in a single content database, what are those objects have unique permissions, who has permission, what type permission assigned, and this permission assigned to a user group or individually.

Enjoy it.

SharePoint 2010 Preview

Microsoft released three SharePoint 2010 preview videos: Overview, For IT Professional and For Developer. You can find them here.

Work Around STSADM Import/Export Failure

We have been using STSADM Import/Export features to reorganize the entire MOSS content structure. Sometimes we saw fatal or too many error during importing process, especially when we worked on site collections with large number or size of contents.

After couple tries, we found that if we choose not to compress the exported files, it really helped to draw down the chance to have errors.

If you experienced similar situation, try this work around and let me know if it really helps you.


Enjoy.

MOSS and Reporting Services Integrated with NTLM: is it possible?

Can you integrated SQL Services Reporting Services into your MOSS farm with NTLM only?

If you search around, lots of articles do not tell you exactly which one, NTLM or Kerberos, to use. Others may tell you that you have to use Kerberos and it means you have to go through lots of troubles to just get Kerberos done. Of course, if you have someone every good with this and can help you without waiting tooo long, then you do not have to read the rest of this article.

I have been looking for this information for a year and half and finally I have the answer recently: Yes, you can use NTLM to integrate these two application together.

There are two steps never documented in any MOSS and SSRS integration information and a large portion of them just assumed you have Kerberos in place.

First, the SSRS web site has to use NTLM authentication in IIS. You can check this KB article to find out whether SSRS is using mixing or NTLM. Run the cscript adsutil.vbs set command to set it to NTLM.

Second, in the Manage integration settings page of Central Administration site, you can only use Trusted instead of Windows authentication.

With these undocumented settings, we are able to design and run reports from a MOSS site now.

Hopefully this information is able to help you if it's not too late.

Best Practice Conference August 24-26, 2009

Best Pracice Conference is back to Washignton DC this Aughst. This time it's for SharePoint and SQL Server.

You can find details here.

MOSS and High CPU Usage - a rare case

Earlier this week in the afternoon we noticed that the production MOSS was running very slow. Our team found out the CPU usage of the WFE server was reaching 100% constantly. The WFE had 8 cores with 4 GB memory and hard to believe it's normal.

I was on the way home and driving, so I could only help with my mouth bfore I could open my computer and connect to the server. We reset IIS couple times and the same situation would come back after 10 to 15 minutes. We could not reboot at the time due to the users from the west still using it.

So the next thing we did was to find out what may cause this CPU spike. When we traced the time line back, the major difference was that one site collection was restored with DocAve earlier in the afternoon. We decided to narrow down the range by restoring the same site collection to our development environment.

Then the same situation happened in the development sever and we knew that's the source. But why? By going through some emails and conversation with different users, we thought it has something to do with InfoPath form. But we still did not have anything clear.

In order to put production MOSS back to normal status, we communicated to the site owners with the situation and our work around solutions: backed up the site collection and removed it from the system until we know how to open it again without problem. Very soon we had approval from the owner and proceeded.

We backed up the site collection, removed it from production, reset IIS and restarted coupld services. Everything looked fine and CPU usage staied low for 30 to 40 minutes. We knew the crisis in production was over and we can concerated on finding the answers.

Our techinical lead took another site collection which had InfoPath from and restored it with DocAve to development enevironment. The same situation came back right away. Now we knew something wrong with DocAve and InfoPath form.

We sent our finding and a sample to the vendor and they could reproduce the same problem in their lab. So, DocAve can not deal with site which has InfoPath form?

Not really. This is a very rare case: in order to have this problem, you have to have a InfoPath 2003 form template saved in a library which was migrated from WSS 2 (not sure the same would happen to 2003 Portal). When you restore the site with DocAve, you will have the same problem. However, if you restore the entire site collection with STSADM, no problem at all.

We received a patch from the vendor and tested it. So far it looks good. So, case closed.