SharePoint 2010 - Creating and using a managed account through PowerShell

I had a client that called saying they could no longer access their SharePoint site nor the Central Administration site.  The cause was related to invalid passwords to the managed accounts.  The client had changed the account passwords on the services and application pools manually outside of SharePoint.  To make matters worse, they could not tell me the passwords of the account since they are handled by an outside network vendor.   Even worse the vendor tries to remain hands off with SharePoint, so I could not send them a script for them to run to resolve the problem.  My account is a SharePoint Farm Administrator.  Therefore, my plan was to use my account to get Central Administration operating again.  Then, they could go in and change the passwords through the Central Administration interface.   

I remoted into the SharePoint server and opened the SharePoint 2010 Management Shell as Administrator.  I executed the Get-SPManagedAccount cmdlet to get a listing of the current managed accounts.  I did not see my account listed, which was to be expected.  I created a new managed account using my credentials.  Here is the powershell commands that I executed.

$svcAccount = “username”

$password = "SamplePassword"

$securePassword = ConvertTo-SecureString -String $password -AsPlainText –Force

$userName = $env:USERDOMAIN + "\" + $svcAccount

$cred = New-Object System.Management.Automation.PSCredential -ArgumentList $userName, $securePassword

New-SPManagedAccount -Credential $cred

My next step was to switch Central Administration to use this new managed account.  Here are the SharePoint commands that I used to do that:

$WebApplication = Get-SPWebApplication <ApplicationURL>;

$ManagedAccount = Get-SPManagedAccount -Identity "<Domain Name>\<UserName>";

$WebApplication.ApplicationPool.ManagedAccount = $ManagedAccount;

$WebApplication.ApplicationPool.Update();

$WebApplication.update();

Once I did that, I restarted the Central Administration application pool in IIS.  I was then able to open Central Administration.  I contacted the client to let them know they could go into Central Administration and change the managed account passwords.  Once that was done, I switched Central Administration back to the original managed account and removed mine from the managed account list.  All of the SharePoint sites and services were operating as normal.

SharePoint 2010 Foundation and SQL Server Express Reporting Services 2008 R2 in native mode

I have a client who had a need to upgrade their existing reports.  The current implementation of the “reports” was an Excel file for each report that contained connections to a local Access database file that had linked tables to the SharePoint lists.  These Excel “reports” were complex and prone to problems such as the user accidentally overwriting a column.   The data in these Excel “reports” was coming from SharePoint, so the thought was to move the reports from Excel into SQL Server Reporting Services (SSRS) and have the reports available within the SharePoint site.

As for the environment, they are using SharePoint 2010 Foundation running against SQL Server 2008 R2 Express with Advanced Services.  As it turns out, SQL Server 2008 R2 Express with Advanced Services does contain SQL Server Reporting Services (SSRS).  You can setup SharePoint 2010 Foundation to use SQL Server Reporting Services in integrated mode according to this article http://technet.microsoft.com/en-us/library/bb326356(v=sql.105).aspx.  However, this article (http://msdn.microsoft.com/en-us/library/bb283190.aspx ) states that the list of SQL Server editions “[for Reporting Services in SharePoint integrated mode include Developer, Evaluation, Standard, or Enterprise editions. There is no support for this feature in the Workgroup edition or in SQL Server Express with Advanced Services.”.  Therefore, the only option we have is to setup SSRS in native mode.

The next hurdle to overcome is getting the data from the SharePoint site so it can be used in the reports.  I used Business Intelligence Design Studio (BIDS) to create my reports.  SSRS does contain a SharePoint list data source type.  However, it only allows you to connect a SSRS dataset to a single SharePoint list so that is not an option for this implementation.  Additionally, there is a more pressing problem.  The ONLY data source type supported in SQL Server Reporting Services 2008 R2 Express with Advanced Services is the SQL Data Connection.  This means that I cannot connect to SharePoint directly to retrieve the data.  I believe, with enough time, I could find a way to create a linked server or use a SQL command like OPENROWSET to access the data in SharePoint directly.  For this client, I decided to create a job that takes the data out of the SharePoint lists and copy it into tables within a SQL database.  I was then able to retrieve the data using the SQL Data Connection, create the reports, and load them into reporting services.

The last step was to show the SSRS reports in SharePoint.  One low tech method is to add link to a SharePoint site that points to the report in Reporting Services.  There are security implications to consider.  For this implementation, I was able to grant the user access in Reporting Services to run the reports.   So, the link method worked.  However, it is not a very elegant solution. 

The next option was the use the NATIVE MODE report viewer web part.  Microsoft has an article, http://technet.microsoft.com/en-us/library/ms159772(v=sql.105).aspx, that shows you the supported version of this web part and how to install it.  I followed those steps and tried to add the web part to a web part page.  I immediately received this error “Session state has been disabled for ASP.NET. The Report Viewer control requires that session state be enabled in local mode.” There are plenty of articles on the internet that guide you through enable Session State for SharePoint 2010.   Many of the postings reference using this cmdlet Enable-SPSessionStateService which is not available in SharePoint 2010 Foundation.  I finally found a solution on this site http://netwoobie.com/blog/?p=22 .  Here are the steps from that site that I used to resolve the error.

In the <system.web> section, find the <httpModules> element and edit it like the following:

<httpModules>

<add name = “Session” type=”System.Web.SessionState.SessionStateModule” />

</httpModules>

You also need to find the element tag <pages> (also under the <system.web> element) and look for the attribute called enableSessionState and set it to true if it is currently false. Save and close the web.config file.

You should then open the IIS 7 manager, and select your web application.

Open the modules applet under the IIS section.

Click “Add Managed Module” in the right hand panel.

Enter a name for the module, I chose “SessionState”, you can pick what you like, but I suggest something related to session state and then in the lower box, select the entry for:

System.Web.SessionState.SessionStateModule, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a

 

I followed those steps and did an IISRESET.  My page came up and I was able to start using the native mode report viewer web part.

 

Final comments, the article covered using SharePoint 2010 Foundation against SQL Server 2008 R2 Express with Advanced Services.  If you are looking forward, SharePoint 2013 ONLY supports SSRS components from SQL Server 2010 SP1 (see this article http://msdn.microsoft.com/en-us/library/gg492257.aspx) and SharePoint Foundation 2013 does not support BI features  (see http://technet.microsoft.com/en-us/library/ff945791(v=office.15).aspx ).  This means you cannot implement SSRS in integrate mode (now called SharePoint mode) in SharePoint 2013 Foundation.