SharePoint 2013, InfoPath and External Data Connections

I will be brief today, busy day, however I wanted to get this one out there.

Here is the list…

Broken Infopath Form (You do not have permission to access a database that contains data required for this form the function correctly) – Check!
External Data Connection, SQL – Check!
SharePoint 2013 – Check!
Data Connection Library Created – Check!
Secure Store Service Application Setup – Check!

I have run into this quite a few times. SharePoint 2013, Web Applications have kerberos enabled and the form breaks. This usually doesn’t happen “just because” unless you have a residual form that someone found recently… Either way, it’s broken.

Find the UDCX file in the data connection library of the site the form resides in. Generally under site contents. Make a copy of it and edit, notepad works.

Here is the change that is needed.

Before. Uncomment and set the Application ID.

</udc:UpdateCommand>
<!–udc:Authentication><udc:SSO AppId=” CredentialType=” /></udc:Authentication–>
</udc:ConnectionInfo>

After. Enter in the App ID and Cred Type.

</udc:UpdateCommand>
<udc:Authentication><udc:SSO AppId=’InfoPathForms‘ CredentialType=’NTLM‘ /></udc:Authentication>
</udc:ConnectionInfo>

This has fixed it most of the time.

 

 

SharePoint: The INSERT statement conflicted with the FOREIGN KEY SAME TABLE constraint “FK_Objects_Objects”. The conflict occurred in database “SP_Config”, table “dbo.Objects”, column ‘Id’

SharePoint issues… meh

I was provided a standard license for a SharePoint farm in lieu of needing enterprise for Access and Excel services. After the farm build, I attempted to make this right…

I attempted to convert the license, however this option was not available.

sharepoint-the-insert-statement-conflicted-with-the-foreign-key-same-table-constraint-fk_objects_objects-the-conflict-occurred-in-database-sp_config-table-dbo-objects-column-id-a

Attempting to Enable Enterprise Features, the timer job runs which eventually fails to this..

Unknown SQL Exception 547 occurred. Additional error information from SQL Server is included below.

The INSERT statement conflicted with the FOREIGN KEY SAME TABLE constraint “FK_Objects_Objects”. The conflict occurred in database “SP_Config”, table “dbo.Objects”, column ‘Id’.
Table ‘LastUpdate’. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Objects’. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Classes’. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Objects’. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
The statement has been terminated.

While I found a few posts to fix this, either manipulating the webpage by enabling the disabled functions on the convert license type page or of course the always, not recommended, manual update to the database.

In the end, since this was production, I opted for dropping the attached servers from the farm, deleting the necessary databases (it’s new, so no need to save most), and creating another farm.

After getting central admin online, I now see my farm license is all ok and I can continue on. A more official method would be to uninstall/reinstall the SharePoint bits to properly assign the license, however considering the farm was showing me the correct license and there isn’t any higher to go, I am good with this.

sharepoint-the-insert-statement-conflicted-with-the-foreign-key-same-table-constraint-fk_objects_objects-the-conflict-occurred-in-database-sp_config-table-dbo-objects-column-id-b

 

SharePoint 2013: Error: ID3204: WS-Federation SignIn request must specify a ‘wtrealm’ or ‘wreply’

As you may know, I work with Microsoft products, SharePoint specifically lately. I ran into an issue with the setup of utilizing ADFS as a claim token provider for authentication on a specific URL.

The issue was…

Error: ID3204: WS-Federation SignIn request must specify a ‘wtrealm’ or ‘wreply’

There was more to this with custom errrors on. I like it when I get the full spiel from .NET.

While I care very much for wreply in my situation, I didn’t get initially what was broken… the setup of a token issuer is quite simple, especially ADFS, it’s less fuss and a Microsoft product. Plus my scripts usually don’t fail me much.

After a quick working environment comparison and running a few powershell scripts, I saw that my realm providers did not have the URN attached.

Key                                        Value
—-                                      —–
https://URL1/
https://URL2/
https://URL3/
https://URL4/
https://URL5/
https://URL6/
https://URL7/

Bah Humbug…

A quick clear, of the values.

$ap = Get-SPTrustedIdentityTokenIssuer
$ap.ProviderRealms.Clear()
$ap.Update()

And re-run my script… and voila…

Key                                        Value
—-                                      —–
https://URL1/                     urn:fancy1:fancy1
https://URL2/                     urn:fancy1:fancy1
https://URL3/                     urn:fancy1:fancy1
https://URL4/                     urn:fancy1:fancy1
https://URL5/                     urn:fancy1:fancy1
https://URL6/                     urn:fancy1:fancy1
https://URL7/                    urn:fancy1:fancy1

The urn:fancy1:fancy1 provider realm matches the ADFS relying party I had setup before. Now my site works…

 

 

 

 

Unable to open Word, Excel, PowerPoint documents in Office Web Apps 2013 Office

I am working with SharePoint 2013 and Office Web Apps 2013. Microsoft decided to move this server / service away from a SharePoint integrated model to a dedicated server model., yah!

Issue: Unable to open Word, Excel, PowerPoint documents in Office Web Apps 2013. Documents spin on open, fail with “Server Busy” message or a message “Can’t Open the Word Document”. I can create new documents in Word, Excel and PowerPoint and edit OneNote documents.

The following were found in the WAC server logs (C:\ProgramData\Microsoft\OfficeWebApps\Data\Logs\ULS) and Event Viewer > Applications and Services Logs > Microsoft Office Web Apps

HostingServiceWatchdog reported status for HostingService in category ‘CheckDiscoveryResponse’. Reported status: The Discovery request failed with an exception: The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.

BroadcastServicesWatchdog_Wfe reported status for BroadcastServices_Host in category ‘4’. Reported status: Contacting Present_2_0.asmx failed with an exception: Could not establish trust relationship for the SSL/TLS secure channel with authority ‘OWAServer.domain.int’.

ServerSession.ProcessWebException: A Web exception during ExecuteWebMethod has occurred for server: http://WINSHRPTOD1:809/ecs/ExcelService*.asmx, method: GetHealthScore, ex: System.Net.WebException: The remote server returned an error: (401) Unauthorized.

Resolution:

The error “The remote server returned an error: (401) Unauthorized” was specific to installation on a the non-system drive. Office Web Apps does not like to be installed on another drive, I had installed on E:\ and moved back to C:\.

Second, the TLS error, I utilized a wildcard certificate. The friendly name started with a “*”. Updated to just the domain, domain1.com. It should work, instead I requested a SAN certificate with the servername and Internal load balanced URL. Either certificate should work but I stuck with the SAN cert.

Notes:

The WOPI keys that are created when SharePoint is connected to OWA server enable a trust between. If a change to OWA or SP is made after the initial configuration and connection, running the command “Update-SPWOPIProofKey” resolves this issue by updating the security keys and trust between environments.

  • Update WOPI Key – Update-SPWOPIProofKey

If it doesn’t take, removing all bindings and adding again will also refresh the key.

  • Remove – Remove-SPWOPIBindings -All:$True
  • Add – New-SPWOPIBinding -ServerName owaURL.FQDN.com

Last Thoughts:

Remember, Office web apps is a dummy service. Set it and forget it. Just make sure you set it right the first time.