I ran into a new Kerberos Scenario that I hadn’t hit before when I was working on the cases related to this blog post . It’s rare that I actually see a case related to the Named Pipes protocol. When I do, it is usually a customer trying to get it setup with a Cluster deployment. I have never had a Named Pipes case related to Kerberos. On top of that, I’ve never had a SQL related Kerberos issue that looked like an actual network related issue. I usually see a traditional “Login failed for user” type error from the SQL Server itself.
As part of my troubleshooting for the other blog post with the Claims configuration, I stumbled upon some information and theories about how Named Pipes responds when Kerberos is in the picture that I hadn’t ever seen or dealt with before. I love when I see new things! It is very humbling and always reminds me there are a lot of things that I don’t know. And, if you have read my other blog posts, or have seen me present at conferences like PASS , you know I have a passion for Kerberos!
Here is what I saw from an error perspective using SharePoint 2013 and Reporting Services 2012 SP1.
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) ---> System.ComponentModel.Win32Exception: Access is denied
This is a typical error if we can’t connect to SQL. Think of this like a “Server doesn’t exist” type error. We didn’t get the normal “Login failed for user” error that would possibly point towards Kerberos. In this error, we didn’t even make it to SQL. The interesting piece here though is the “Access is denied” inner exception. That does possibly point to a permission issue.
I had talked in the last Blog Post about protocol order with connecting to SQL and that the default was TCP. In this case, I was forcing Named Pipes, so the fact that the error is a Named Pipes error is expected.
I dropped down to a network trace to see how far we actually got and to see if that revealed any other information. One thing to keep in mind here is that we are in a Claims to Windows Token Service (C2WTS) scenario with the SharePoint/RS 2012 integration. So, Kerberos/Constrained Delegation will be in the picture here. A lot of people aren’t necessarily familiar with how Named Pipes actually works. Named Pipes actually uses the SMB (simple message block) protocol from a network perspective. This is the same protocol used for file shares and you’ll see the traffic on port 445. It can be a little confusing because SMB sits on top of TCP, but we aren’t actually using the TCP 1433 port. It is just a different way to connect to SQL Server. The IP 10.0.0.20 was the SharePoint Server hosting the Reporting Services Service.
300 9:04:40 AM 5/17/2013 10.0.0.20 captthrace.battlestar.local SMB SMB:C; Negotiate, Dialect = PC NETWORK PROGRAM 1.0, LANMAN1.0, Windows for Workgroups 3.1a, LM1.2X002, LANMAN2.1, NT LM 0.12, SMB 2.002, SMB 2.??? {SMBOverTCP:42, TCP:41, IPv4:1}
302 9:04:40 AM 5/17/2013 captthrace.battlestar.local 10.0.0.20 SMB2 SMB2:R NEGOTIATE (0x0), Revision: (0x2ff) - SMB2 wildcard revision number., ServerGUID={97B805C2-296C-477B-82B4-DEB6170A2A01} Authentication Method: GSSAPI, {SMBOverTCP:42, TCP:41, IPv4:1}
303 9:04:40 AM 5/17/2013 10.0.0.20 captthrace.battlestar.local SMB2 SMB2:C NEGOTIATE (0x0), ClientGUID= {9CB563F9-BEF4-11E2-9403-00155D4CB97B}, {SMBOverTCP:42, TCP:41, IPv4:1}
304 9:04:40 AM 5/17/2013 captthrace.battlestar.local 10.0.0.20 SMB2 SMB2:R NEGOTIATE (0x0), Revision: (0x300) - SMB 3.0 dialect revision number., ServerGUID={97B805C2-296C-477B-82B4-DEB6170A2A01} Authentication Method: GSSAPI, {SMBOverTCP:42, TCP:41, IPv4:1}
323 9:04:40 AM 5/17/2013 10.0.0.20 captthrace.battlestar.local SMB2 SMB2:C SESSION SETUP (0x1) Authentication Method: GSSAPI, {SMBOverTCP:42, TCP:41, IPv4:1}
326 9:04:40 AM 5/17/2013 captthrace.battlestar.local 10.0.0.20 SMB2 SMB2:R - NT Status: System - Error, Code = (22) STATUS_MORE_PROCESSING_REQUIRED SESSION SETUP (0x1), SessionFlags=0x0 Authentication Method: GSSAPI, {SMBOverTCP:42, TCP:41, IPv4:1}
327 9:04:40 AM 5/17/2013 10.0.0.20 captthrace.battlestar.local SMB2 SMB2:C SESSION SETUP (0x1) Authentication Method: GSSAPI, {SMBOverTCP:42, TCP:41, IPv4:1}
- ResponseToken: NTLM AUTHENTICATE MESSAGE Version:NTLM v2, Workstation: CAPTHELO
Signature: NTLMSSP328 9:04:40 AM 5/17/2013 captthrace.battlestar.local 10.0.0.20 SMB2 SMB2:R - NT Status: System - Error, Code = (34) STATUS_ACCESS_DENIED SESSION SETUP (0x1) , {SMBOverTCP:42, TCP:41, IPv4:1}
329 9:04:40 AM 5/17/2013 10.0.0.20 captthrace.battlestar.local TCP TCP:Flags=...A.R.., SrcPort=49665, DstPort=Microsoft-DS(445), PayloadLen=0, Seq=2945236632, Ack=2852397926, Win=0 (scale factor 0x8) = 0 {TCP:41, IPv4:1}
In the Network Trace we can see that we were trying to connect via NTLM. I already know that that will be a problem as we have to go Kerberos. We started supporting Kerberos with Named Pipes starting in SQL 2008, so it should work. At this point, I’m thinking we actually have a Kerberos issue even though it looked like a network issue from the original error message. So, lets go see if we can validate that. I already had Kerberos Event Logging enabled. These entries will be located in the System Event Log. You can ignore errors that show “KDC_ERR_PREAUTH_REQUIRED”. That is just noise and expected. Also realize that errors may be cached and if they are, you will not see them in the Event Log or a Network Trace. It may require an IISRESET, a reset of the C2WTS Windows Service, or even a reboot of the box to get the items to show in the Event log or Network Trace. See this Blog Post .
Log Name: System
Source: Microsoft-Windows-Security-Kerberos
Date: 5/17/2013 9:04:40 AM
Event ID: 3
Task Category: None
Level: Error
Keywords: Classic
User: N/A
Computer: CaptHelo.battlestar.local
Description:
A Kerberos error message was received:
on logon session
Client Time:
Server Time: 14:4:40.0000 5/17/2013 Z
Error Code: 0xd KDC_ERR_BADOPTION
Extended Error: 0xc0000225 KLIN(0)
Client Realm:
Client Name:
Server Realm: BATTLESTAR.LOCAL
Server Name: cifs/captthrace.battlestar.local
Target Name: cifs/captthrace.battlestar.local@BATTLESTAR.LOCAL
Error Text:
File: 9
Line: 12be
Error Data is in record data.
This entry was the only non-PREAUTH_REQUIRED error. Two things that were interesting about this. First was KDC_ERR_BADOPTION. When I see this, especially in a Claims type configuration, it tells me we have a Constrained Delegation issue. The other item that was interesting was the CIFS SPN. CIFS is used for File Sharing. It stands for “Common Internet File System”. This was our SMB traffic. We can also see this in the Network Trace.
319 9:04:40 AM 5/17/2013 10.0.0.20 10.0.0.1 KerberosV5 KerberosV5:TGS Request Realm: BATTLESTAR.LOCAL Sname: cifs/captthrace.battlestar.local {TCP:44, IPv4:14}
321 9:04:40 AM 5/17/2013 10.0.0.1 10.0.0.20 KerberosV5 KerberosV5:KRB_ERROR - KDC_ERR_BADOPTION (13) {TCP:44, IPv4:14}
This was interesting, because I never gave Constrained Delegation rights to CIFS for the C2WTS or the Computer Account. When we talk about SPN’s and Delegation and placement, we talk about that the SPN should be on the account that is running the servers. For CIFS, it will be the system itself and therefore on the machine account of the SQL Server that we are trying to connect to.
CIFS is one of those special Service Classes, similar to HTTP. It is covered by the HOST SPN on the Machine Account and we won’t see an actual CIFS SPN defined, but when we go to the delegation side of things you will see it.
I added this to both the Claims Service account and the Computer Account. I say computer account, because the actual SMB request will come from the machine and not directly from the RS Process. Under the hoods, it is affectively making a call to the CreateFile Windows API.
After resetting IIS and cycling the C2WTS Service, I still saw the same exact error. This was one of those reboot moments. After rebooting the server, I then got the following:
I didn’t necessarily expect this as I expected to fail on the Kerb side to SQL. So, I ran a report and stuck a WAITFOR DELAY in there so I could see the connection. had a look at dm_exec_connections on the SQL Server and saw that we had connected with NTLM:
For our purposes this will work as I’m not going further than SQL. This is technically a single hop between the SharePoint Server System context and the SQL Server. You can configure it for Kerberos if you really want that auth_scheme by creating the appropriate Named Pipes SPN and configuring the appropriate Delegation for the C2WTS Service Account and the Machine Account for where the SMB request is originating from. Also realize that if you have a misplaced Named Pipes SQL SPN, you will encounter a “Cannot Generate SSPI Context” similar to the following:
Adam W. Saxton | Microsoft Escalation Services
https://twitter.com/awsaxton