Wednesday, March 23, 2016

Completely remove SSRS instance from SharePoint farm

In this article I’ll show how to completely remove SSRS instance setup in SharePoint 2013 integrated mode. Before reading on the uninstallation procedure, you can read this article on installing and configuring SSRS

When we install SSRS in SharePoint integrated mode, there are two components we need to configure. They are,

  • Reporting Services Add-In for SharePoint
  • Reporting Services – SharePoint

When uninstalling, we have to remove both of those components.

1. Removing Reporting Services Add-In for SharePoint

Remember, we have to setup Reporting Services Add-In in all SharePoint servers.So we have to remove them from all SharePoint servers in the farm.

Anyway uninstallation is straight forward. First you have to navigate to Add/Remove programs and uninstall the add-in as you would do for any other program

image

2. Removing Reporting Services – SharePoint

To remove this we need to log-in to our SSRS server, the server where our “SQL Server Reporting Services” instance is running in our farm. Then we have to navigate to Add/Remove programs again and perform following actions

image

clip_image001

clip_image003

clip_image005

clip_image007

3. Remove Service Application Databases

Above actions will not remove service application databases. You have to manually remove them from your database server

Thursday, March 3, 2016

Resolving Power Query error “Specified value has invalid CRLF characters.Parameter name: value” when connecting with SharePoint on-premises site

In Microsoft Office Excel Power Query, we have an option to connect with SharePoint lists. When I tried to test the functionality with on-premises SharePoint sites it was always giving me the error “Specified value has invalid CRLF characters.Parameter name: value”.

Following were the steps I used to connect with my SharePoint site

image_thumb34

image_thumb53

image_thumb42

image_thumb40

Unfortunately this was the error I got for all my on-premises environments

image_thumb3

I changed web application settings and enable anonymous authentication for that web application, but nothing worked for me. Then I tried with a SharePoint online environment. It worked perfectly!! So was that due to a SharePoint build difference?

I checked my on-premise SharePoint patch level. they were all patched with November 2014 CU. Ok, that was pretty old !!

Then I updated one of my SharePoint environments with the latest CU, which is the March 2016. You can download the CU from this location. You can read the relevant KB article from following location

After the update I was able to successfully connect to my SharePoint environments.

image_thumb25

image_thumb28

Saturday, February 20, 2016

Prepare a SharePoint 2013 site for PowerPivot workbooks

Following are the steps I use to configure a SharePoint 2013 site to work with PowerPivot workbooks

Activate following site collection features

image

image

As a result Business Data webparts will appear in webparts collection

image

Create a Data Connection Library and add BISM content types

image

Configure Excel Services service application settings

image

image

image

image

image

That’s all we have to do. Now you can upload your workbooks.

Tuesday, February 9, 2016

Programmatically upload SharePoint app to a Site collection using feature receiver

Following is a code sample to add SharePoint App to a Site,using a feature receiver. In order to do the task, you should upload the app to the app catalog

private static string IntallApp(Guid currentSiteId)
{
    try
    {
        using (SPSite site = new SPSite(currentSiteId))
        {
            if (null == site.WebApplication.Properties["AppCatalogUrl"])
            {
                return string.Empty;
            }

            //get app catelog url from web application property
            var appCatalogSiteUrl = site.WebApplication.Properties["AppCatalogUrl"].ToString();
            using (SPSite siteApp = new SPSite(appCatalogSiteUrl))
            {
                using (SPWeb webApp = siteApp.RootWeb)
                {
                    SPList appList = webApp.Lists.TryGetList("Apps For SharePoint");
                    SPQuery query = new SPQuery
                    {
                        Query = "<Where><Eq><FieldRef Name=\"Title\" /><Value Type=\"Text\">MyApp</Value></Eq></Where>"
                    };

                    var items = appList.GetItems(query);
                    if (null != items && items.Count >= 1)
                    {
                        SPListItem appCatalogItem = items[0];

                        Guid appProductId = new Guid(appCatalogItem["AppProductID"].ToString());
                        using (SPWeb web = site.RootWeb)
                        {
                            IList<SPAppInstance> existingInstances = web.GetAppInstancesByProductId(appProductId);

                            if (existingInstances.Count > 0) throw new Exception("App is already installed in the target web");

                            //Get the permission XML
                            var appPermissionXml = appCatalogItem["AppPermissionXML"].ToString();

                            //read the client Id from the Permission XML
                            var xmlDoc = new XmlDocument();
                            xmlDoc.LoadXml(string.Format("<Approot>{0}</Approot>", appPermissionXml));
                            var root = xmlDoc.DocumentElement;
                            var clientIdNode = root.SelectSingleNode("//@ClientId");

                            //load the client id, title and stream
                            var clientId = clientIdNode.InnerText;
                            var appItemTitle = appCatalogItem.Title;
                            var appPackageStream = appCatalogItem.File.OpenBinaryStream();

                            //load the principal manager
                            SPAppPrincipalManager manager = SPAppPrincipalManager.GetManager(web);
                            //Check if the app principal is registered in this farm using client ID
                            SPAppPrincipal appPrincipal = manager.LookupAppPrincipal(SPAppPrincipalIdentityProvider.External, SPAppPrincipalName.CreateFromAppPrincipalIdentifier(clientId));
                            //if the principal not found then add
                            if (appPrincipal == null)
                            {
                                //end points  can be blank
                                List<string> endpoints = new List<string>();

                                //app  key
                                SecureString secureString = new SecureString();
                                DateTime now = DateTime.Now;
                                SPAppPrincipalCredential credential = SPAppPrincipalCredential.CreateFromSymmetricKey(secureString, now, now);

                                //create external parameters
                                SPExternalAppPrincipalCreationParameters sPExternalAppPrincipalCreationParameters = new SPExternalAppPrincipalCreationParameters(clientId, appItemTitle, endpoints, credential);
                                sPExternalAppPrincipalCreationParameters.SkipExternalDirectoryRegistration = false;

                                //register the principal
                                appPrincipal = manager.CreateAppPrincipal(sPExternalAppPrincipalCreationParameters);
                            }
                            //load it install in the target web
                            SPAppInstance appInstance = web.LoadAndInstallApp(appPackageStream);

                            //Trust the app
                            SPAppPrincipalPermissionsManager sPAppPrincipalPermissionsManager = new SPAppPrincipalPermissionsManager(web);
                            sPAppPrincipalPermissionsManager.AddAppPrincipalToWeb(appPrincipal, SPAppPrincipalPermissionKind.FullControl);

                            return appInstance.Id.ToString();
                        }
                    }
                    else
                    {
                        return string.Empty;
                    }
                }
            }
        }
    }
    catch (Exception ex)
    {
        throw ex;
    }

Tuesday, January 19, 2016

Disable warning : “Query and Refresh Data Be careful. The query to get the data might be unsafe so you should only refresh the workbook if you trust its source”. in excel service data refresh

In one of my environments, I got the warning message every time I refresh or drill down content in my workbooks. Following was the exact message

q

Following were the steps I used to resolve the error.

1. Navigate to Excel Services service application

image

2. Click on Trusted File Locations link

image

3. Click on the desired http URL, and untick the setting shown below

image

Monday, January 11, 2016

Configure Kerberos for SharePoint 2013–PowerShell Scripts

Recently I had to reconfigure an existing web application to use Kerberos authentication. The objective was to allow my Business Intelligence environment to use impersonation account for row level security.

In my environment I had a Domain Controller server, SQL Server, Analysis Server configured in Tabular mode and a SharePoint server

Following were the steps I used to perform the configuration

1. Prepare service accounts.

Following were my service accounts

  • SP\SP_C2WTS
SharePoint Claims to windows token service service account
  • SP\SP_Farm
SharePoint farm account
  • SP\SP_Services
ExcelServices app pool account
  • SP\SP_Services
PowerPivot app pool account
  • SP\SP_PortalAppPool
Web Application app pool account
  • SP\SP_Admin   
SharePoint installation account
  • SP\SP_UnattendedService
Excel services data refresh account

2. Add “SP\SP_C2WTS” Account to local administrator group in the SharePoint server

3. Set accounts in local security policy

image

image

image

image

4. Start the Claims to Windows Token Service in SharePoint

image

5. We have to change the service account for Claims to Windows Token Service.

$ct = "SP\SP_C2WTS"
$Identity = $ct
$ServiceTypeName = "Claims to Windows Token Service"

$Service = (Get-SPFarm).services | where {$_.typename -eq $ServiceTypeName}
$IdentityManagedAcct = Get-SPManagedAccount -Identity $Identity

$SvcProcessIdentity = $Service.ProcessIdentity
$SvcProcessIdentity.CurrentIdentityType = [Microsoft.SharePoint.Administration.IdentityType]::SpecificUser
$SvcProcessIdentity.Username = $IdentityManagedAcct.UserName
$SvcProcessIdentity.Update()
$SvcProcessIdentity.Deploy()

6. Create SPN Records

Setspn -S SP/C2WTS SP\SP_C2WTS
Setspn -S SP/Excel SP\SP_Services
Setspn -S SP/PowerPivot SP\SP_Services
   

7. Set SPN records for Web Applications

Setspn -S HTTP/portal.contoso.com SP_PortalAppPool
Setspn -S HTTP/my.contoso.com SP_ProfilesAppPool

8. Set SPN for SQL Server

Setspn -S MSSQLSvc/sql.contoso.com:1433 SP\SQL_Admin

9. Set SPN for Analysis Server Tabular

Setspn -S MSOLAPSvc.3/sql.contoso.com SP\SQL_OLAP_Admin

10. Set SPN for SQL browser

Setspn -S MSOLAPDisco.3/pivot.contoso.com SP\SQL_OLAP_Admin

11. Trust SharePoint Server for delegation

Navigate to Domain Controller and Active Directory Users and Computers

image[36]

image

12. Change Web Application settings

image

That’s all we have to do.

Tuesday, December 22, 2015

SharePoint 2013–Update password of a managed account using PowerShell

We can change the password of a SharePoint managed account from SharePoint central administration console as well as using PowerShell.

Fun fact here is that, we don’t need to do anything from our Domain Controller. When we update the password either from Central Administration or PowerShell, it will automatically update the user’s password in Active Directory.

Update managed account password in Central Administration

image

image

Update managed account password using PowerShell

Set-SPManagedAccount -identity SP\SP_Farm -NewPassword (Convertto-Securestring "mynewpassword" -AsPlainText -Force) -SetNewPassword