How to easily collect verbose ULS logs from a scaled out SharePoint farm using powershell

One of the more difficult aspects of troubleshooting a large SharePoint farm is looking through a multitude of logs from multiple servers.  One of the environments I support has twelve servers in the farm.  So it becomes very tedious when I have a complex issue that I need to reproduce with verbose logging enabled.

Here are some powershell commands to quickly help you turn on verbose logging and aggrigate all of the logs from all of the servers (for a specific time span) into one file for review.

Powershell command to turn on full verbose logging.

Set-SPLogLevel -TraceSeverity VerboseEx

Reproduce the issue you are having...

Powershell command to turn off verbose logging.
Clear-SPLogLevel

Powershell command to aggrigate the logs from all servers in the farm.
Merge-SPLogfile -path c:\mergedULS.log -starttime <"mm/dd/yyyy hh:mm"> -endtime <"mm/dd/yyyy hh:mm">

After performing the steps above you will have a single log file that you can analyze in a log viewer tool (such as ULSViewer) to help you more efficiently identify your issue.

How to refresh a PowerPivot Data Connection to Oracle in SharePoint 2013

Description of Issue:
There is currently an unresolved issue with SharePoint 2013, PowerPivot and the oracle client in which data refresh of the workbook fails.  The errors returned are either "excel services returned an unknown error" or "refresh failed…null password or logon denied".  I am actively researching this issue to find a permanent solution.  In the meantime, the steps outlined below will provide a workaround to enable both scheduled and interactive data refresh to an oracle data source.

Workaround:
To get around this issue you will need to leverage the SharePoint 2013 Secure Store. 

Your first step will be to get a Secure Store ID created with the oracle credentials you will use to access the oracle database.  Once the secure store id is setup and ready to use you will have to add it to each of your workbooks that connect to oracle. 

Below are the steps to accomplish this:

To configure the workbook to refresh interactively, do the following:

(Note!  Interactive data refresh is only available for workbooks that created in Excel 2013. If you try to refresh an Excel 2010 workbook, Excel Services displays an error message similar to “PowerPivot Operation Failed: The Workbook was created in an older version of Excel and PowerPivot cannot be refreshed until the file is upgraded”. )  Reference.

Open the workbook locally on your computer in Excel.
Click on the "Data" tab and select "Connections".

In the workbook connections window that opens highlight your data connection and click on “properties”.

In the Connection Properties window that pops up click on the "definition" tab, then click on "authentication settings".

In the Authentication Settings windows select the option "Use a stored account" and enter the Secure Store ID that you created.

Click "OK" through each open window and save your workbook back to SharePoint.  You should now be able to refresh the workbook interactively. 

To configure the workbook to refresh on a schedule do the following:

Open the "manage data refresh" window for the PowerPivot workbook.
Expand the data source at the bottom of the page.

Under Data Source Credentials check the option to "Connect using the credentials saved in a Secure Store Service…" and enter the Secure Store ID that you created.

Once these two steps are complete you should be able to refresh your workbook with Oracle.

Good Luck!

I can't delete a SharePoint web application! Now What?

I have recently ran into an issue where I go to delete a sharepoint web application and it fails with the error:  "Object not set to a reference of an object".  It turns out there are orphaned sites still associated to the web application in the farm configuration database.  Here is how you resolve this:

In SQL management studio:

select * from Objects (nolock) where Name like '%<name of the webapplication>%'
note the "Id" guid.
select * from SiteMap (nolock) where ApplicationId = '<Id guid from the above step>'
note the 'DatabaseId' guid.
 

Next step is to be done on a sharepoint server in the farm:

STSADM -O deleteconfigurationobject -id {DatabaseId guid from step above}

Repeat this for each row returned in the first step and then you will be able to successfully delete the web application from the farm.

 

Good luck and happy sharepoint'ing.

 

reference:  http://social.technet.microsoft.com/Forums/sharepoint/en-US/64fbf6d4-b84d-4c54-a5fb-3a421e35fae2/unable-to-delete-web-application-sharepoint-administrative-framework-error

SharePoint 2013 access denied using web services to pull data from other SharePoint lists

Since the migration from 2010 to 2013, we have noticed a number of authentication changes in regards to web services, InfoPath forms, powerpivot data connections to oracle, etc...

While many of these issues were resolved by setting up a secure store id to pull data on the users behalf, I struggled with one particular problem.  A site admin had a data view web part that was using REST and pulling data in from another SharePoint sites list using windows integrated auth.  Usually this means that you haven't setup Kerberos Constrained Delegation correctly but I knew this wasn't the case.

After hours of research I stumbled across this older post which talked about a hotfix that set the asp.net impersonation to allow anonymous. Specifically: 

“This hotfix updates the .NET 2.0 framework's System.Web assembly and makes a major change when aspnet:AllowAnonymousImpersonation is set to true in web.config. Instead of code running under the application pool account as is traditional, code now runs under NT Authority\\IUSR – anonymous. If you have any code that depends on an authenticated identity or the specific app pool identity – SQL connections that use integrated security, for example, the code will break with Access Denied errors.”

I decided to test this theory by setting the aspnet:AllowAnonymousImpersonation to false and the web service calls started working!

<appSettings>

<add key="aspnet:AllowAnonymousImpersonation" value="false" />

</appSettings>

I hope this helps others out in the community that are seeing these types of auth issues.