SharePoint 2013: Cannot open database requested by the login

Problem

You have changed the database of a service application.  You did this by changing the database name in the service application's Properties page (in Central Administration).  After changing the database, the service application is restored without any issues and runs successfully.  You are able to interact successfully with the service application.  Some time later, during routine checks, you find the following error event messages appearing in the server application log of one of your farm's Application servers:

Critical Event 3760
SQL Database '[Service Application Database]' on SQL Server instance  '...' not found. Additional error information from SQL Server is included below.
Cannot open database "[Service Application Database]" requested by the login. The login failed.
Login failed for user 'DOMAIN\[farm service account]'.

Similar messages are seen in the ULS log.  Curiously, the user associated with these events is your own administrator account.  Checking the Manage Databases Upgrade Status page and find the old service application database still listed. Launching SQL Server Management Studio and then connecting to the farm's database server instance, you verify that the old database is still attached.

The cause of the problem is that the old database has not been formally removed from the farm.

Solution

1. First, review all farm databases and their status.  You can do this by executing this command in an elevated SharePoint shell:

   Get-SPDatabase | Sort-Object Name | ft Name, ID, IsAttachedToFarm, ExistsInFarm,Exists,Status -auto

   The Exists status of the orphaned database will be False.
2. Note down the ID of the database having Exists status of False.
3. Next, get an instance of the orphaned database by executing this command:

   $orphanDB = Get-SPDatabase -Identity [ID]

4. Verify that an instance was returned by simply executing:

   $orphanDB

5. Now remove the database from farm configuration by using this command:

   $orphanDB.Delete()

   This removes the database from the farm, but not from SQL Server nor does it close any open connections. To clean up, you have one more command to run
6. Execute this command to clean up the farm and detach the database from SQL Server:

   $orphanDB.Unprovision()

7. Lastly, rerun this command to verify that the farm no longer retains any reference to the orphaned database:

   Get-SPDatabase | Sort-Object Name | ft Name, ID, IsAttachedToFarm, ExistsInFarm,Exists,Status -auto

   And then check the Manage Databases Upgrade Status page again.
   

References

• Get-SPDatabase
• Cleaning up deleted databases in SharePoint
• Deleting Orphaned SharePoint Databases
• SecureStoreServiceDatabase.Unprovision method
• Manage databases in SharePoint 2013
• Rename service application databases in SharePoint 2013

Notes

• This farm has one application server.
• Central Administration > Upgrade and Migration > Review database status > Manage Databases Upgrade Status
• I encountered this problem, originally, by restoring to the development farm the managed metadata database of the production farm.

SharePoint 2013: There was no endpoint listening at net.pipe...

Problem

You click on a document in a document library in order to view it in Office Web Apps (OWA). Instead, the browser presents "Sorry, something went wrong" and the usual correlation ID.  Testing further, you find that you cannot open any Microsoft Office document in OWA.

Troubleshooting

1. Executed Get-OfficeWebAppsMachine on the farm's OWA server, and it returned Healthy.  
2. Checked OWA discovery, by connecting to http:/[OwaServername]/hosting/discovery, and found that it returns the appropriate XML file.  
3. On a SharePoint server, executed Get-SPWOPIBinding and this returned all bindings appropriately configured.
4. On the primary WFE, checked ULS logs using the time and correlation ID seen earlier, and found the following entries:

   Trusted provider is missing. Provider: '00000003-0000-0ff1-ce00-000000000000' and WOPIFrame
   
   Unhandled exception: System.ServiceModel.EndpointNotFoundException: There was no endpoint listening at net.pipe://localhost/SecurityTokenServiceApplication/appsts.svc that could accept the message. This is often caused by an incorrect address or SOAP action. See InnerException, if present, for more details. ---> System.IO.PipeException: The pipe endpoint 'net.pipe://localhost/SecurityTokenServiceApplication/appsts.svc' could not be found on your local machine....

   among 17 entries associated with this correlation ID. 
5. Checked server event logs on the APP and WFE servers, and found no System or Application events associated with this issue.
6. Noting the SecurityTokenService item in this ULS entry, checked the SecurityTokenServiceApplicationPool on all WFE and APP servers and found that it was started.
7. Opened browser, and then connected to:

   http://localhost:32843/SecurityTokenServiceApplication/appsts.svc

   This displayed a normal return:
   
8. Restarted the SecurityTokenServiceApplicationPool, but found that this didn't resolve the problem.
9. Performed search on ULS log entry terms, as noted in step 4 above.  Found articles on Net.Pipe Listener Adapter.
10. Checked the Net.Pipe Listener Adapter service on the WFEs and found that it was stopped on the primary WFE (there are two WFEs in Windows NLB configuration):
    

Solution

1. Check the server's Net.Pipe Listener Adapter service on all SharePoint servers.
2. If it's stopped, start it.

References

• Get-OfficeWebAppsMachine
• Get-SPWOPIBinding
• SharePoint 2013 Error on Office Web Apps
• Listener Adapter API Reference
• WAS Activation Architecture
• There was no endpoint listening at net.pipe://localhost/

Notes

• SharePoint Server 2013 Enterprise, two WFEs.
• Problem occurred immediately after successful patching.
• Thanks to Waqas Sarwar for presenting the original solution to this problem.

SharePoint 2013 TIP: how to create a no-code document upload hyper-link

This TIP shows you how to create a document upload hyperlink on any page in the web (i.e., website) and without having to write any JavaScript.  It uses the existing document library's Upload.aspx page, and the page will be opened as a standard page and not as a modal popup (as would normally be the case).  This method builds upon and simplifies a method developed by Jenny Hersko originally for SharePoint 2010.

Procedure

1. Create the document library that you want to use.  Any document library will do.
2. On any web part or wiki page, insert the App for this document library.  The App will have the same name as the document library.  By default, this App will be inserted onto the web page with Toolbar Type set to Full Toolbar.
3. Hover the cursor over the "+ new document" hyperlink on this App.  Note the URL.
4. Right-click on this hyperlink and choose Copy shortcut (in IE).
5. Paste this into NotePad.  Note its structure:

   [your site URL]/_layouts/15/Upload.aspx?List={[List GUID]}&RootFolder=

   Upload.aspx is the default form created automatically for every document library.  By itself, this is all you need, and you can copy and insert this as a hyperlink.  However, after executing the upload, the user's browser is navigated to the item itself.  If you don't want this behavior but want the user's browser to be navigated back to its origin (i.e., the page that had the hyperlink), you need to the Source parameter.
6. Build the Source parameter by replacing special characters with their appropriate ASCII code. Thus, if the page URL was "http://MyFarmUrl.com/MySite/MyPage.aspx, you would encode this as:

   http%3A%2F%2FMyFarmUrl.com%2FMySite%2FMyPage.aspx

   and the hyperlink becomes

   [your site URL]/_layouts/15/Upload.aspx?Source=
   http%3A%2F%2FMyFarmUrl.com%2FMySite%2FMyPage.aspx&List={[List GUID]}&RootFolder=

7. Now just create a new hyperlink and set HREF= to this text.

References

• Create a link to “New Document” template link
• Add “New document” button to home page
• Launch Document Content Type Templates From Any Web Page with a Link
• HTML URL Encoding Reference

Notes

• You can easily implement your own, custom uploading form: if this custom form is saved in the same web folder as the default forms, just replace Upload.aspx with the name of your custom form and you're set to go.
• A similar approach works for adding a new list item hyperlink to a page.  Insert the app for this list onto any page.  Right-click on the "+ new item" hyperlink on this App, and then select Copy shortcut.  In this shortcut, not that now the page is listform.aspx.  

SharePoint 2013 TIP: cancel multiple workflows using PowerShell

When I update the development farm content from the production farm, there will usually be a number of list workflows still running that are being brought over from production.  It makes no sense to have these workflows still running on the development farm, so, I like to cancel them all out.  here is a simple PowerShell scripts that will do just that.  This script was originally developed for SharePoint 2010, but also works on SharePoint 2013 just fine.  It was originally developed by Justin Brink based upon a script written by Serge Luca.

#Cancel all workflows in a specific list
$web = Get-SPWeb "[site URL]";
$web.AllowUnsafeUpdates = $true;
$list = $web.Lists["[list name]"];
foreach ($item in $list.Items) {
  foreach ($wf in $item.Workflows) {
    [Microsoft.SharePoint.Workflow.SPWorkflowManager]::CancelWorkflow($wf);      
  }
}
$web.Dispose();

References

• Cancel multiple SharePoint Workflows using PowerShell
• Powershell - stop all running workflows
• Terminating a SharePoint Workflow Programatically
• SPWorkflowManager members
• Locating active workflows within a site
• SPWorkflowManager.CountWorkflows method