Get -SPServiceApplication not working

One day I open my InfoPath form associated with a list in SharePoint 2010 and I get the following error – An error occurred querying a data source.

How to resolve this issue-

Step 1 – Using Correlation ID, I found that there is problem with User profile Service Application. I went to Powershell and check Get -SPServiceApplication. It is not working and gave me error

Get -SPServiceApplication : The specified user or domain group was not found.
At line:1 char:25 ………………..

To cross check it, I went to Central Administration -> Security -> Configure Service Account. It is also not working and giving same error “ The specified user or domain group was not found ”

Step 2 –  Open Sql Management studio, go to Sharepoint_Config database and write the following query in query analyzer

1 2 3

SELECT [Name], [Version], CAST([Properties] as xml) as PropertiesXML From [SharePoint_Config].[dbo].[Objects] with (nolock) Where [Name] LIKE '%Profile%'

You will find multiple rows with “User Profile Service Application” under column “name”.

Open the PropertiesXml column value for all the above rows and search for keyword “m_SerializedAcl”.

The row which has the above value in the PropertyXML is the required row. Copy its ID, lets say “ID_of_Identified_row”

Step 3 – Copy the xml content of the PropertXML which has the keyword  “m_SerializedAcl” in notepad and save it as text file on your SharePoint Database server drive e.g. F:\XMLChanged.txt.

Step 4 – Open XMLChanged.txt and search for node containing  “m_SerializedAcl”. Now concentrate on the value of this node

1 2 3 4 5 6 7

<sFld type="String" name="m_SerializedAcl"><acl> <ace identityName="i:0#.w|TechilaDomain\domainname1" displayName="Domain Name 1" sid="" allowRights="18446744073709551615" denyRights="0" /> <ace identityName="i:0#.w|TechilaDomain\domainname2" displayName="Domain Name 2" sid="" allowRights="18446744073709551615" denyRights="0" /> <ace identityName="i:0#.w|TechilaDomain\domainname3" displayName="Domain Name 3" sid="" allowRights="18446744073709551615" denyRights="0" /></acl></sFld>

The problem is that one of the user, Domainname1 has left the companyand has been deleted from the Active directory. However, his/her entry still exists in this column which is stopping User profile service from running properly.

So delete the following(for example) from the above string and save the XMLChanged.txt file

1 2	<ace identityName="i:0#.w|TechilaDomain\domainname2" displayName="Domain Name 2" sid="" allowRights="18446744073709551615" denyRights="0" />

Step 5 –  Open query analyzer in management studio under SharePoint_Config database and run the following command

1 2 3 4 5 6 7 8

Declare @abc xml select @abc = cast(c1 as xml) from OPENROWSET (BULK ' F:\XMLChanged.txt',SINGLE_BLOB) as T1(c1)   update  dbo.objects set  Properties = Convert(nvarchar(max),@abc) where  Id ='ID_of_Identified_row '

Step 6 – Restart the SharePoint website, Central administration website and App pools  containing these websites.

Congrats, Its done.  Infopath form associated in SharePoint should be opening now without any error. Also if you now run Get –SPServiceApplication in Powershell, you will received list of services and type.

Disclaimer: I have stated my experience here. This approach might not be the recommended by Microsoft. Please check with Microsoft before using this. This should be used as the last resort and should be used judiciously with utmost care and all backups taken before starting this process