Installation of SQL Server 2012 PowerPivot for SharePoint 2013

Ahamed Fazil Buhari
 
Senior Developer
January 2, 2017
 
Rate this article
 
Views
3602

Hello everyone, in this article,let us see how to Install SQL Server PowerPivot for SharePoint 2013. The Business Intelligence features of SharePoint 2013 have a lots of moving parts, PowerPivot is one among that. Well, SQL Server PowerPivot for SharePoint is available in SQL Server 2012 SP1 disk or ISO file either Enterprise or the Business Intelligence edition. Please make sure you are having Service Pack 1.

SQL Server PowerPivot for SharePoint is not a SharePoint component, it’s actually a SQL component. It’s one of the modes of the SQL Server Analysis Services. This is why we’re not going to find it on our SharePoint disk, we’re going to find it on the SQL disk. We can install this on either SharePoint or SQL Server, it doesn’t matter. Here I’m going to install this in my SharePoint Server.

It is going to be a lengthy process. Let’s go ahead and install SQL Server PowerPivot for SharePoint,

Step 1: Go to you SQLServer 2012 Service Pack 1 setup folder and open the setup application

image

Step 2: It will open up the regular SQL Server Installation center and remember that we’re in SharePoint server and we have no instance of SQL on here. So, select Installation on left side panel and select New SQL Server stand-alone installation or add features to an existing installation

image

 

 

Step 3: It will open and Setup Support Rules and please make sure everything has passed. Click on OK.

image

 

 

Step 4: In next window, we’re going to specify the Product Key. Here, I’ve selected the free edition and picked Evaluation version and click on Next.

image

Step 5: Accept the license terms and click on Next.

image

 

 

Step 6: Include SQL server product updates, if you want and click on Next.

image

 

 

Step 7: It will download if there’s any update and install it. It will take few minutes and after everything has setup then click on Install.

image

Step 8: It will go for Rule Check in Setup Support Rules. Here, we’ve got a warning message for “Windows Firewall” and to resolve that we need to add Inbound Rule in our Windows Firewall settings. You can refer my other article “Windows Firewall settings for SQL Server Analysis Service” to add Inbound Rule (port – 2383 is used for PowerPivot for SharePoint, because it’s a SQL Server Analysis Service mode) and click on Next
image

 

 

Step 9: In Setup Role, select ‘SQL Server PowerPivot for SharePoint’ and click on Next.

image

Step 10: In Feature Selection, click on Next.

image

Step 11: Check that the Installations Rules are passed and click on Next.

image

Step 12: In Instance Configuration, please make a note of Instance ID it should be POWERPIVOT and do not change its name and we cannot have two instance of PowerPivot with same Instance ID. We can have only one Instance ID, one instance for PowerPivot and it has to be named as POWERPIVOT, this is default. Click on Next

image

 

Step 13: Click on next for Disk Space Requirements and in Server Configuration step, specify the Service account for SQL Server Analysis Service. For your information – PowerPivot for SharePoint is a SQL Server Analysis Service mode (it’s better to use the service account which is used for SQL Server Analysis Service.)

image

 

Step 14: In Analysis Services Configuration step, we need add few admin accounts. Here, I’m adding the current user and other accounts like application pool account, business intelligence account, and farm account as per the need. Go ahead and choose Next.

image

Step 15: Click on next in Error Reporting step and in Installation Configuration Rules check whether the status of all the rules are passed and choose Next.

image

 

 

Step 16: Click on Install in Ready to Install step and it is going to take a little bit of time to complete the installation.

image

It may ask for system restart after successful installation of SQL PowerPivot.

image

PowerPivot for SharePoint has been installed successfully.

Happy Coding

Ahamed

Category : SharePoint

Author Info

Ahamed Fazil Buhari
 
Senior Developer
 
Rate this article
 
Ahamed is a Senior Developer and he has very good experience in the field of Microsoft Technologies, especially SharePoint, Azure, M365, SPFx, .NET and client side scripting - JavaScript, TypeScript, ...read more
 

Installation of SQL Server Analysis Service (SSAS) – SQL Server 2012

Ahamed Fazil Buhari
 
Senior Developer
November 18, 2016
 
Rate this article
 
Views
8961

In this article, we’ll look into the step by step approach to install SQL Server Analysis. To take full advantage of the Business Intelligence options of SharePoint 2013, we need to have SQL Server Analysis Services running on our SQL Server.

We’ve installed this on our existing SQL Server as an added feature. It doesn’t make much difference if you go ahead with New SQL Server stand-alone installation. Ok, let go step by step on installation process.

1. Open your SQL Server 2012 setup file and it will open up the below window,

clip_image002

2. It will do the Rule Check. In this case everything is passed. Click OK.

clip_image004

3. Now, it will go ahead and check for updates and click on next to install that updates.

clip_image006

 

4. After installing the updates, it will again do the Rule Check as shown below. Everything is passed except Windows Firewall which is in warning status. Anyway we will deal with this windows firewall configuration in another article. As of now, let’s go and click on next.

clip_image008

 

5. As I said earlier, we are adding feature to an existing instance of SQL Server 2012 and select the server name in the dropdown and click on next.

clip_image010

 

6. This is an important step, in this step we need to choose the Features that we want to install. We need Analysis Services, SQL Server Data Tools and Management Tool – Basic.

clip_image012

7. Click on next in Installation Rules and Disk Space Requirements.

8. In Server Configuration step, provide the account for the SQL Server Analysis Services (Microsoft recommends that you use a separate account for each SQL Server service.) and click on next.

clip_image014

9. In Analysis Service Configuration, select the mode. We want to make sure that Multidimensional and Data Mining Mode should be selected. Also we need to specify which user have administrative permission for the Analysis Services. So I clicked on Add Current User

clip_image016

 

10. Click next.

clip_image018

 

11. Click on Install and it will take few minutes to install all the components.

clip_image020

12. Everything is completed and successful, so simply click on close.

clip_image022

 

Go to SQL Server Management Studio to check the connectivity of our Analysis Services. Select the Server type as: Analysis Services and provide the Server name and give authentication details.

clip_image024

Well, our SQL Analysis Service is up and running.

clip_image026

Currently there are no Databases inside it, in my next article I will explain on ‘How to Attach Database and Give Access to SSAS Service Account in SQL Server 2012’.

 

Happy Coding

Ahamed

Category : SQL

Author Info

Ahamed Fazil Buhari
 
Senior Developer
 
Rate this article
 
Ahamed is a Senior Developer and he has very good experience in the field of Microsoft Technologies, especially SharePoint, Azure, M365, SPFx, .NET and client side scripting - JavaScript, TypeScript, ...read more
 

SharePoint 2016 Single FARM installation In AZURE with SQL server 2014 – PART 2

Sriram Varadarajan
 
Solution Architect
August 28, 2016
 
Rate this article
 
Views
4715

In Part 1 of SharePoint 2016 installation in Azure, we started with installing SQL Server 2014. In this part we can see the common SQL Server mistakes while installing SharePoint 2016

In SharePoint 2016, there is no ‘standalone’ option during installation which installs SQL Express – SQL must be installed and running prior to installation.

SQL server didn’t complete successfully as the services wasn’t starting up automatically. Part 1 of this series can be found here

SQL Setup failed due to below error

(01) 2016-08-23 09:36:14 Slp: The configuration failure category of current exception is ConfigurationFailure

(01) 2016-08-23 09:36:14 Slp: Configuration action failed for feature SQL_Engine_Core_Inst during timing ConfigRC and scenario ConfigRC.

(01) 2016-08-23 09:36:14 Slp: Microsoft.SqlServer.Configuration.SqlEngine.SqlEngineConfigException: Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.

(01) 2016-08-23 09:36:14 Slp:    at Microsoft.SqlServer.Configuration.SqlEngine.SqlServerServiceBase.WaitSqlServerStart(Process processSql, Boolean withFailpoint)

(01) 2016-08-23 09:36:14 Slp:    at Microsoft.SqlServer.Configuration.SqlEngine.SqlServerServiceSCM.StartSqlServer(String[] parameters, Boolean withFailpoint)

(01) 2016-08-23 09:36:14 Slp:    at Microsoft.SqlServer.Configuration.SqlEngine.SqlServerStartup.StartSQLServerForInstall(String sqlCollation, String masterFullPath, Boolean isConfiguringTemplateDBs, Boolean isSlipstreamRunning)

(01) 2016-08-23 09:36:14 Slp:    at Microsoft.SqlServer.Configuration.SqlEngine.SqlEngineDBStartConfig.ConfigSQLServerSystemDatabases(EffectiveProperties properties, Boolean isConfiguringTemplateDBs, Boolean useInstallInputs)

(01) 2016-08-23 09:36:14 Slp:    at Microsoft.SqlServer.Configuration.SqlEngine.SqlEngineDBStartConfig.DoCommonDBStartConfig(ConfigActionTiming timing)

(01) 2016-08-23 09:36:14 Slp:    at Microsoft.SqlServer.Configuration.SqlConfigBase.SlpConfigAction.ExecuteAction(String actionId)

(01) 2016-08-23 09:36:14 Slp:    at Microsoft.SqlServer.Configuration.SqlConfigBase.SlpConfigAction.Execute(String actionId, TextWriter errorStream)

(01) 2016-08-23 09:36:14 Slp: The following is an exception stack listing the exceptions in outermost to innermost order

(01) 2016-08-23 09:36:14 Slp: Inner exceptions are being indented

(01) 2016-08-23 09:36:14 Slp:

(01) 2016-08-23 09:36:14 Slp: Exception type: Microsoft.SqlServer.Configuration.SqlEngine.SqlEngineConfigException

(01) 2016-08-23 09:36:14 Slp:     Message:

(01) 2016-08-23 09:36:14 Slp:         Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.

(01) 2016-08-23 09:36:14 Slp:     HResult : 0x851a001a

· Checked ERRORLOG and found that Setup was unable to start SQL Services

· Tried below and SQL was able to start.

sqlservr.exe -mSqlSetup -Q -qSQL_Latin1_General_CP1_CI_AS -T4022 -T4010 -T3659 -T3610 -T8015 -T902

· Tried Net Start

net start mssqlserver /mSqlSetup /Q /qSQL_Latin1_General_CP1_CI_AS /T4022 /T4010 /T3659 /T3610 /T8015 /T902

· Above failed with Certificate error.

· This was because of the fact that Service SID don’t work on domain controller

· So, I changed service account to “LocalSystem”

· After that I was able to start SQL Services

· Agent was not starting. Below was the error in SQLAgent.out file.

2016-08-23 10:00:28 – ? [100] Microsoft SQLServerAgent version 12.0.5000.0 (X64 unicode retail build) : Process ID 5052

2016-08-23 10:00:28 – ? [495] The SQL Server Agent startup service account is DCC\SHAREPOINT2016$.

2016-08-23 10:00:28 – ! [150] SQL Server does not accept the connection (error: 18456). Waiting for Sql Server to allow connections. Operation attempted was: Verify Connection On Start.

2016-08-23 10:00:28 – ! [000] Unable to connect to server ‘(local)’; SQLServerAgent cannot start

2016-08-23 10:00:28 – ! [298] SQLServer Error: 18456, Login failed for user ‘DCC\SHAREPOINT2016$’. [SQLSTATE 28000]

2016-08-23 10:00:28 – ! [382] Logon to server ‘(local)’ failed (DisableAgentXPs)

2016-08-23 10:00:28 – ? [098] SQLServerAgent terminated (normally)

· To fix this, I need to add that account as SQL Login

· I was not able to do so because dcc\dcc was not there in SQL Logins

· Followed https://sqlserver-help.com/2012/02/08/help-i-lost-sa-password-and-no-one-has-system-administrator-sysadmin-permission-what-should-i-do/

· Started SQL with /mSQLCMD parameter

· Ran  below command

 USE [master]
 GO
 CREATE LOGIN [dccdcc] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
 GO
 EXEC master..sp_addsrvrolemember @loginame = N'dccdcc', @rolename = N'sysadmin'
 GO

· Stopped SQL and logged in via SSMS.

· Added ‘DCC\SHAREPOINT2016$ account as Sysadmin

· Now I was able to start SQL Agent

· Performed Repair of SQL

Overall summary:

  Final result:                  Failed: see details below

  Exit code (Decimal):           -2068578302

  Start time:                    2016-08-23 10:06:13

  End time:                      2016-08-23 10:08:53

  Requested action:              Repair

Setup completed with required actions for features.

Troubleshooting information for those features:

  Next step for SQLEngine:       Use the following information to resolve the error, and then try the setup process again.

  Next step for FullText:        Use the following information to resolve the error, and then try the setup process again.

  Next step for Replication:     Use the following information to resolve the error, and then try the setup process again.

Feature:                       Database Engine Services

  Status:                        Failed: see logs for details

  Reason for failure:            An error occurred during the setup process of the feature.

  Next Step:                     Use the following information to resolve the error, and then try the setup process again.

  Component name:                SQL Server Database Engine Services Instance Features

  Component error code:          0x84B40002

  Error description:             The SQL Server feature ‘SQL_Engine_Core_Inst’ is not in a supported state for repair, as it was never successfully configured. Only features from successful installations can be repaired. To continue, remove the specified SQL Server feature.

  Error help link: http://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft SQL Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=12.0.5000.0&EvtType=0x2841E06E@1204@2&EvtType=0x2841E06E@1204@2

· Modified registry

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\ConfigurationState

· Changed values from 2 (failed) to 1 (success)

· Repair again and it worked

· SQL is installed successfully.

Category : Azure, SQL

Author Info

Sriram Varadarajan
 
Solution Architect
 
Rate this article
 
Sriram is a Technology Evangelist with 15+ years experience in Microsoft Technologies. He is an enterprise architect working for large pharmaceutical organization which has presence globally with largest Microsoft implementation ...read more
 

SharePoint 2016 Single FARM installation In AZURE with SQL server 2014 – PART 1

Sriram Varadarajan
 
Solution Architect
 
Rate this article
 
Views
5416

We have got SharePoint 2016 (Enterprise) available in AZURE (IAAS) for trail and here we can see how to configure it. First let’s see at a very high level on how to set a Virtual Machine in AZURE for setting up a SharePoint Environment

· Login to PORTAL.AZURE.COM

You should end up seeing this page: (The look and feel of the page might different based on what you have pinned to your home page)

clip_image002

· Select Virtual Machine (Not classic) from the left side tree view.

· Click ADD

clip_image004

· And select the correct subscription (Provided you have multiple)

· Search for SharePoint 2016 (in our case)

clip_image006

· Select the VM which appears.

· Select the Resource manager:

clip_image008

Provide the respective details one by one

clip_image010

You’re final screen might look something like this (this might different based on the options you have selected)

clip_image012

The VM would be provisioned once you click OK.

To connect to VM

· Come to the home page

clip_image013

· Click ALL resource

· Select the correct subscription

· Use the search box if you can’t find it.

· Select the machine

clip_image015

· Click connect (a RDP file would get downloaded to your local machine)

· Now you’re all set to use the machine.

NOTE:

The D drive the machine is a temp, any file/folder created in that drive would get erased when your restart.

· You can increase the DISK at run time with out any down time by select the option DISK under settings.

 

 

http://msdn.microsoft.com/en-us/library/windowsazure/jj672979.aspx

Category : Azure, SQL

Author Info

Sriram Varadarajan
 
Solution Architect
 
Rate this article
 
Sriram is a Technology Evangelist with 15+ years experience in Microsoft Technologies. He is an enterprise architect working for large pharmaceutical organization which has presence globally with largest Microsoft implementation ...read more
 

How to setup Active Directory Rights Management Service Role on Windows Server 2012

Sathish Nadarajan
 
Solution Architect
April 17, 2015
 
Rate this article
 
Views
11319

In this article, let us see how to setup AD RMS (Active Directory Rights Management Service) Role & Feature on Windows Server 2012.

1. Go to Server Manager.

2. On the top right corner, under Manage Click “Add Roles and Features”.

clip_image002

3. Click on Next

clip_image004

4. Select the Options as below.

clip_image006

clip_image008

5. Select the Check Box, “Active Directory Rights Management Service”

clip_image010

6. The corresponding Feature will also automatically selected. Click on Add Features.

clip_image012

7. Click on Next

clip_image014

8. Make sure that the Feature “Active Directory Rights Management Services” is selected. It would be selected by default. Even then, we are double checking here.

clip_image016

9. Click on Next

clip_image018

10. In this screen also, no need to change anything.

clip_image020

11. Restart if required.

clip_image022

12. Installation is in Progress.

clip_image024

13. After the successful installation, we need to configure the AD RMS.

clip_image026

14. The Configuration section let us see in a separate article.

Happy Coding,

Sathish Nadarajan.

Category : Windows

Author Info

Sathish Nadarajan
 
Solution Architect
 
Rate this article
 
Sathish is a Microsoft MVP for SharePoint (Office Servers and Services) having 15+ years of experience in Microsoft Technologies. He holds a Masters Degree in Computer Aided Design and Business ...read more
 

Installing and Configuring Office Web Apps for SharePoint 2013

Sathish Nadarajan
 
Solution Architect
June 2, 2014
 
Rate this article
 
Views
34939

In this article, let us see how to install and configure Office Web Apps for SharePoint 2013.

Office Web Apps Server – It is used to open the Office Applications, which is stored inside the SharePoint Server on the Browser itself. i.e., within an iFrame on a Layouts Page. This will be very much useful, if the end user doesn’t have Office Applications installed on his machine.

Prerequisites For Office Web App Server

1. The first point is, don’t install the Office Web Apps Server on the machine on which the SharePoint 2013 has been installed.

2. The minimum Software requirement is

a. Windows Server 2008 R2 With Service Pack 1 (or)

b. Windows Server 2012.

3. There is no specific Hardware requirement is specified. If the machine is capable enough to install the above mentioned OS s then it is enough.

Versions of Office Web Apps Server

There are 2 flavors of Installers

1. Free Version which can be downloaded from the Microsoft Download Center. Click here to download. This version is used to view only. i.e., the End user cannot edit the document within the SharePoint.

2. Licensed Version – Which allows the users to check out, edit and check-in to the SharePoint.

With the above background information, let us see how to Install and Configure Office Web Apps.

In my case, I used Windows Server 2012 as the WACServer. (WACServer is nothing but the machine on which we are going to install the Office Web Apps)

The Steps are as follows.

1. Download the Img file and install the Apps on the Windows Server 2012 Machine.

2. Make sure the following services are running. For that, execute the following command on PowerShell. (Open the PowerShell ISE as Administrator)

 Add-WindowsFeature Web-Server,Web-Mgmt-Tools,Web-Mgmt-Console,Web-WebServer,Web-Common-Http,Web-Default-Doc,Web-Static-Content,
 Web-Performance,Web-Stat-Compression,Web-Dyn-Compression,Web-Security,Web-Filtering,Web-Windows-Auth,Web-App-Dev,Web-Net-Ext45,
 Web-Asp-Net45,Web-ISAPI-Ext,Web-ISAPI-Filter,Web-Includes,InkandHandwritingServices
 

3. This will get executed without any problem.

4. Then the next step would be Make sure that the Windows Firewall Services are running. To verify that, you can go to Services.msc and verify.

image

5. Now, we need to make a decision. I.e., Whether we are going to have a HTTP app or a HTTPS App. I would suggest always go with HTTPS.

6. For HTTPS Sites, we require a Self Signed Certificate. Please refer here. In our case, I named the certificate as OfficeWebAppCertificate.

7. Once the certificate has been created, execute the below command.

 New-OfficeWebAppsFarm -Verbose -InternalUrl https://C6723468661 -ExternalUrl https://C6723468661.DC07.Loc -CertificateName OfficeWebAppCertificate -ClipartEnabled -TranslationEnable

8. C6723468661 – This is the machine on which I am installing the Office Web Apps. This is a cloudshare machine and the domain is DC07.Loc

9. With this, we are done with the WACServer.

10. To verify whether the installations are correct, open the browser and enter the URL

http:// C6723468661.dc07.loc/hosting/discovery

a.image

11. I think this is so simple right. Let us see the steps to be done on the SharePoint Server Now.

12. I came back to my SharePoint Server and going to execute the following command.

 New-SPWOPIBinding -ServerName C6723468661.DC07.Loc

13. That’s it. We are done with the SharePoint Server too. Now, if you go to the Documents Library and hover any of the document, then the popup should come and on which the document will be displayed.

The screen shot would be,

image

The installation and configuration steps may be very simple. But the following are the exceptions and the solution for them while doing the configuration steps. They are the key points which I wanted to make a note of.

Exception 1:

something is preventing us from opening the workbook. it might be blocked for security reasons

Solution :

Make sure that the Firewall of the WACSever is not blocking. For that, better we can Turn Off the FireWall Settings.

To do that, we can go to the Server Manager and Change the settings.

image

Exception 2:

While trying to execute the New-SPWOPIBinding, I am not able to reach the WACServer. I was keep on getting the exception like

WARNING: The Server did not respond. Trying again (attempt 1 of 5)

Solution:

After struggling for more than an hour, I found that there something needs to be done on the Local Security Policy.

a. On the SharePoint Server, go to Local Security Policy. For that, Press Windows+Q and type “Local Security Policy”.

b. The following window will Open.

image

c. On which go to the Public Key Policies.

image

d. Select the Certificate Path Validation Settings. On the Network Retrieval Tab, make sure that the following check boxes were selected.

image

e. Do a Re-Start of the SharePoint Server.

f. That’s it. The exception gone.

Exception 3:

sorry there was a problem and we can’t open this document office web apps 2013

Solution :

a. Open the WACServer and open the SharePoint URL

b. You will be seeing the Certificate Error on the Browser.

c. Click the Certificate and Install the Certificate under the Trusted Root.

d. Restart the IE.

Observation with Content Search WebPart.

The Office Web Apps Server opens the documents in Browser from the AllItems.aspx as well as the Search Results Page. But when I try opening from a content Search WebPart, it is not able to open in the Browser. That I am working for a workaround. Let us see, If I get any workaround, will post to the community.

Thanks for the patience and hope this makes your installation easy.

Happy Coding.

Sathish Nadarajan.

Author Info

Sathish Nadarajan
 
Solution Architect
 
Rate this article
 
Sathish is a Microsoft MVP for SharePoint (Office Servers and Services) having 15+ years of experience in Microsoft Technologies. He holds a Masters Degree in Computer Aided Design and Business ...read more
 

Leave a comment