I’m always amazed that issues usually come in batches. I was looped into a few cases that had the following symptoms. They were running SharePoint 2010 and Reporting Services 2012 SP1. When they went to use a data source with Windows Authentication, they were seeing the following error:
System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
This caused me to raise an eyebrow (visions of Spock as the new Star Trek movie is opening today <g>). A lot of thoughts were floating in my head that all told me that this error didn’t make sense, for a bunch of reasons.
- The default protocol order for connecting to SQL from a client is TCP and then Named Pipes. So, because we failed with a Named Pipes error, that meant something was either wrong with TCP or someone changed the Protocol order (which I have never seen in a customer case – so very unlikely)
- This is RS 2012, which means we are a Shared Service and rely on the Claims to Windows Token Service (C2WTS). This forces Constrained Delegation. Pretty sure most people would not have created the delegation requirements for the Named Pipes SQL SPN as most people go down the TCP route. You can read more about SQL’s SPNs being Protocol based here . Also more on this related aspect in a later post as I found some interesting things about this as well.
- This error tells me that we couldn’t establish a connection to SQL via Named Pipes. Think of this as a “Server Not Found” type error. I immediately tossed out any Kerberos/Claims related issue due to that thinking – again more on the kerb piece of this in a later post.
- This is really the first time I’ve had someone hit me up with a Named Pipes connection failure from an RS/SharePoint Integration perspective ever. And I just got hit with 3 of them within the same week. Something is up.
Being this told me we had an actual connection issue via Named Pipes, I started down the normal connectivity troubleshooting path. With any connectivity issue, I started with a UDL (Universal Data Link) file. Basically just a text file renamed with an extension of UDL. It’s important to run this from the same machine that is hitting the SqlException. In my case it was my SharePoint App server, not the WFE server.
You’ll notice the “np:” in front of the server name. This forces the Named Pipes Protocol and ignores the default protocol order. And this worked. I also tried “tcp:” to force TCP in the UDL and this worked to. I went back to my data source and tried forcing TCP there.
System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - The requested name is valid, but no data of the requested type was found.)
This made no sense. I even made sure I was logged in as the RS Service Account as that is the context in which we would have been connecting to SQL. Same result. Also, within a network trace, I saw nothing on either the TCP or Named Pipes side of the house in the trace that related to this connection attempt. Which meant we never hit the wire.
As I was going to collect some additional diagnostic logging (Kerberos ETW tracing and LSASS Logging) I ended up doing an IISRESET and a recycle of the C2WTS service. We went to reproduce the issue, but got a different error this time.
System.IO.FileLoadException: Could not load file or assembly 'System.EnterpriseServices, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. Either a required impersonation level was not provided, or the provided impersonation level is invalid. (Exception from HRESULT: 0x80070542) File name: 'System.EnterpriseServices, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' ---> System.Runtime.InteropServices.COMException (0x80070542): Either a required impersonation level was not provided, or the provided impersonation level is invalid. (Exception from HRESULT: 0x80070542)
This error I did know and can work with. I had blogged about this error last July here . Checking the “Act as part of the operating system” showed that the C2WTS service account in fact was not given that right. Adding that account to that policy right and restarting the C2WTS Windows Service and performing an IISRESET then yielded the following:
The connectivity errors were clearly related to the lack of the Policy Setting. It was unexpected and didn’t line up with normal connectivity related issues and also wasn’t very helpful with regards of where to go look for more information as all of the normal paths didn’t show anything useful.
Of note, I tried reproducing this on SharePoint 2013, but only got the FileLoadException. I think this is partly a timing issue with how IIS AppPools are started and the C2WTS service is started. Doesn’t mean you won’t see this on SharePoint 2013 necessarily. Even on SharePoint 2010, the first time I hit the FileLoadException.
Adam W. Saxton | Microsoft Escalation Services
https://twitter.com/awsaxton