Re: Delegation problems

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) wrote:
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.

--
Joe Kaplan-MS MVP Directory Services Programming
Co-author of "The .NET Developer's Guide to Directory Services Programming"
http://www.directoryprogramming.net
--
"Mike Rasmussen" <michael.rasmussen@xxxxxxxxxxx> wrote in message
news:1149877782.456744.102710@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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) wrote:
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.

--
Joe Kaplan-MS MVP Directory Services Programming
Co-author of "The .NET Developer's Guide to Directory Services
Programming"
http://www.directoryprogramming.net
--
<michael.rasmussen@xxxxxxxxxxx> wrote in message
news:1149867559.859982.177090@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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.



.

Relevant Pages

  • Re: SQL account rights
    ... Please advice what is the best, suitable rights rather than domain admin ... issues, such as a server that might have IIS running on the same machine, ... applicable to SQL 2000 environment, ... files, or backups, make sure that the service account has Full ...
    (microsoft.public.sqlserver.security)
  • RE: MP Install issue
    ... Where in the installation are you talking about specifying the account rather ... > MPDB ERROR - CONNECTION PARAMETERS ... > SQL Server Name: servername ... > with a trusted SQL Server connection. ...
    (microsoft.public.sms.setup)
  • Re: SQL Express Fails with Hardware Error
    ... The LocalSystem account is a built-in account, ... which the SQL Service runs. ... MCSE, CCEA, Microsoft MVP - Terminal Server ... Minimum Hardware Requirement (Warning) ...
    (microsoft.public.sqlserver.setup)
  • Re: SCCM with a remote SQL instance problems (IT IS NOT A WARNING)
    ... PreReq check is not a WARNING it is a FAILURE. ... account the run the SQL Server Service on the server, Domain Memberships, AD ...
    (microsoft.public.sms.installer)
  • Re: Distributed Query - Linked Servers?
    ... In the case of SQL style accounts, it doesn't seem very secure to me, because the password will be transmitted over the network. ... it should also be able to work by setting up a credential and proxy account on Server1. ... In this case it could work with NTLM authentication. ... we just need to verify that the 2 sql server instances are under sql server and windows authentication mode and the sql account you use has the permission on the 2 sql server instances. ...
    (microsoft.public.sqlserver.server)