Delegation problems

[michael.rasmussen]

I am trying to setup delegation from a IIS server to a SQL 2K backend
server running on a different physical server from the IIS server. I am
running into problems because when I try to delegate the MSSQL service
is not on the list of services to select for the SQL server. Has anyone
seen this before. I have several other servers running SQL and the SQL
service lists when I try to delegate to those servers. I just have two
servers which do not list the SQL service.

This is running in a Windows 2003 AD environment. All servers are
running Windows 2003. All SQL 2000 servers are running SP3 or higher.

Any insights would be appreciated.

 

[Joe Kaplan \(MVP - ADSI\)]

What account is running SQL? Is it local system, network service or a
custom service account?

If it is system or network service, then the network identity for the
service will be represented by the machine account, so it will need the
appropriate SPNs. If it is a custom service account, then it will need the
SPNs.

Once you know that, then it depends on how you are connecting to SQL. If
you are using a DNS name in your connection string, make sure the service
account has an appropriate SQL SPN for that DNS name. That would likely be
something like MSSQLSvc/yourserver.domain.com:1433 if you are using the
default TCP port and your server's DNS name in yourserver.domain.com. If
you are using NetBIOS names in your connection string, then the SPN would
use that.

I'm not actually sure why the proper SPNs wouldn't show up in ADUC, but the
first thing to do is to make sure that the SPNs you need exist in AD and are
set on the right account.

I like using a tool like ldp.exe or adfind for looking at the AD stuff in
this case as you can see what's actually going on under the hood with AD.
ADUC hides to much stuff to be useful for troubleshooting. The relevant
attributes in AD for Kerberos delegation are servicePrincipalName (SPNs for
an account), userAccountControl (sets the bit that allows the account to
delegate with Kerberos or via S4U with any protocol) and
msds-AllowedToDelegateTo (specifies the constrained delegation list where
the service can delegate to).

HTH,

Joe K.

 

[Mike Rasmussen]

The SQL Server is running under a service account. I was able to
delegate from my web server to the SQL service on the DB server when I
lookup the service account. This didn't solve the rest of my problem.
When the app on the web server attempts to connect to the DB I get the
following error.

Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection

If I change SQL Server to run under a local account and then delegate
to the SQL service using the machine account my web app works.

 

[Joe Kaplan \(MVP - ADSI\)]

This sounds like an SPN problem (most likely). When you set up SQL to run
as a service account, did you add an SPN to that service account in AD that
matched the name and port you are using in your web app connection string?
For example, if you conn string uses sqlserver.domain.com and the standard
1433 TCP port, then your service account would need an SPN set on for
MSSQLSvc/sqlserver.domain.com:1433. Additionally, if the machine account
for the box already has that SPN set on it, you would have to take it off of
there. SPNs must be unique in the forest!

When using DNS names, you also sometimes need to be careful of whether you
are using a CNAME or A record to refer to the service. In my experience,
Kerberos seems to like to resolve CNAME records back to the A record, so if
you use a CNAME DNS name like sqlserver.domain.com but that points to an A
record of server.domain.com, Kerberos will form the SPN based on
MSSQLSvc/server.domain.com:1433. That could in turn either not exist or be
associated with a different account.

Also, logon event auditing is your friend here. Enable it via local
security policy and check the event logs to see what's happening. You'll
see if you are getting Kerberos, if funny SPNs are being used, or if
Kerberos is not being used at all an NTLM is being used instead. Kerberos
also sometimes spits our errors in the system event log that can be helpful.

Joe K.

 

[Mike Rasmussen]

I checked the SPNs and they appear correct on the service account.
There are no SPNs on the machine account.

I enabled auditing and I can see the inbound connection from the web
server. It is an anonymous login attempt using NTLM.

I am still baffled. Anything else to check?

 

[Joe Kaplan \(MVP - ADSI\)]

Can you show some examples, perhaps sanitized for public consumption. For
example, what is the name you use in your SQL connection string? What is
the SPN you have on the service account?

Also, on the web side, are you authenticating with Kerberos there too?
Providing as much detail as possible would help.

I wish this Kerberos delegation stuff was easier, but it is definitely true
that when it isn't working it can be insanely frustrating. When it is
working, it is sometimes a mystery as to why.

Joe K.

 

[Dominick Baier [DevelopMentor]]

i would also recommend installing www.ethereal.com - and check if the SPNs
used for requesting tickets match excactly what you have registered.

 

[Mike Rasmussen]

The connection string uses a variable defined in the web.config.
<configuration>
<appSettings>
<add key="RTMSource" value="NBES40" />
<add key="DBMSSource" value="GTOMA-DBSQL01.NBORDER.NBP" />
</appSettings>

Here the use of the variable from the application
DBSource = ConfigurationSettings.AppSettings("DBMSSource")

SQL_Connection = "data source='" & DBSource & "';integrated
security=SSPI;persist security info=False;initial catalog=SCADA "

The SPN on the service account is:
MSSQLSvc/GTOMA-DBSQL01.NBORDER.NBP:1433

The web authentication is Kerberos as well.

 

[Mike Rasmussen]

I captured a failed attempt and it does appear that the SPN is not
being resolved properly although I cannot figure out why.

What I am seeing in is that when a Kerberos TGS-REG is submitted I am
getting a Kerberos error back. The error is
KRB5KDC_ERR_S_PRINCIPAL_UNKNOWN.

The service name in the request is the same as is defined for the SPN
in the service account running SQL.

Of course once this Kerberos error is returned the server attempts to
negotiate NTLM authentication which can't be passed to the SQL Sever.

Thanks for any ideas you might have.

 

[Joe Kaplan \(MVP - ADSI\)]

Can you do an LDAP query against your forest GC with a filter like this:

(servicePrincipalName=MSSQLSvc/GTOMA-DBSQL01.NBORDER.NBP:1433)

That should hopefully return one and only one result and it should
correspond to the service account you've created.

The other question: is GTOMA-DBSQL01.NBORDER.NBP a CName or A record in DNS?
Kerberos sometimes likes to resolve CNames back into A records, when forming
SPNs, causing confusing results.

Joe K.

 

[Mike Rasmussen]

That was it. I did a search for the SPN and it came back with two
entries. When the SQL server was initially setup (by a FORMER
administrator) he used his account as the service account for SQL
server. When we changed the SQL server to use a real service account
instead of his user account the SPN was not removed so there were
duplicate SPN entries.

Thanks for your assistance. I should have caught this sooner but I am
challenged when it comes to LDAP queries.

 

[Joe Kaplan \(MVP - ADSI\)]

Woo hoo! Glad that was it. Normally I see a different error with a
duplicate SPN so I didn't think that was it, but it seemed like one of the
only things left that might be wrong since the network trace revealed an
actual attempt to do Kerb.

Joe K.