Re: SPN creation

If SQL runs under a different account, then the SPN for SQL with the host name associated with that SQL must be assigned to the fixed service account. If the SPN you want to use is already set on computer account, you will need to remove it from there and add it to the service account.

The rules for SPNs are actually pretty simple:

When a client wishes to authenticate to a service via Kerberos, it does this by requesting a service ticket from the KDC for a specific SPN. This is usually formed by a combination of the service type, host name and port. For a SQL connection using a DNS name sql.domain.com on the default port, the SPN would be MSSQLSvc/sql.domain.com:1433.

Thus, there must be an SPN listed in AD (one and only one!) with the value MSSQLSvc/sql.domain.com:1433. If there is no SPN matching that, Kerb is not possible. If there is more than one, Kerb will be attempted and fail.

The SPN that is found MUST be set on the account in AD that is actually running the service as well. If not, the service ticket presented by the client to the server will not be decryptable by the service and will result in a different error.

If you follow the simple rules, it tends to become much easier. The difficulty here is usually that you need to know how to perform the searches in AD to see if a specific SPN exists, if it is duplicated and what account it is set on. It can also be confusing when you need to set port-specific SPNs as some service types seem to use the port (SQL for example) while others don't seem to (HTTP, LDAP).

--
Joe Kaplan-MS MVP Directory Services Programming
Co-author of "The .NET Developer's Guide to Directory Services Programming"
http://www.directoryprogramming.net
"phill" <phill@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:282D1257-16FD-43E9-8F54-A94178EC6DDB@xxxxxxxxxxxxxxxx
will i need to create an spn for the live sql server service account in order
for the website to access sql using kerberos? This is running under a
specific service account

thanks


"Joe Kaplan" wrote:

Are you needing to have Kerb auth to get Kerb delegation to the SQL backend?
If that's the case, then it is not actually needed to get Kerb auth on the
front end website to get Kerb delegation to the backend if your AD is 2003
native mode or higher. You can use "protocol transition" logon which will
allow users authenticated via NTLM (or something else) to the front end to
use Kerb to access the backend. In the delegation tab you end "allowed to
delegate with any protocol" and configure the service to delegate to SQL.

That said, you can't have two service accounts that use the same host name
for an HTTP service and differ only by port number. The browser doesn't
form port-specific SPNs for HTTP, so this won't work. You'll need an
alternate host name for the second service account or will need to run all
the app pools as the same service account. The SPNs you would want created
on the service account in question would just be HTTP/FQDNofWebSite and
HTTP/shortnameofsite. Note that you really only need SPNs for the addresses
you use.

Kerb auth to SQL should work with the SPNs that get created when SQL is
installed if you run SQL as network service or SYSTEM. I would not
recommend using SYSTEM unless you absolutely have to. Try to run services
under least privilege accounts. SQL itself works fine under Network
Service. In any event, either of those local IDs will translate to the
computer account in the domain for Kerb purposes, so as long as the SQL SPNs
are on that account, Kerb should be fine.

--
Joe Kaplan-MS MVP Directory Services Programming
Co-author of "The .NET Developer's Guide to Directory Services Programming"
http://www.directoryprogramming.net
"phill" <phill@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F185038A-38AA-42D0-83FB-B27B75DC5476@xxxxxxxxxxxxxxxx
> Hi, Here is hopefully more information:
>
> 1 server running 4 websites all running on different ports. 80, 1000, > 1001
> &
> 1002
>
> 2 of the websites will be connecting to the test sql backend server. 2
> websites will be connecting to the live sql server. The developer > informs
> me
> that we need to use kerberos.
>
> only 2 websites are running at the moment. They are both running under > 2
> seperate service accounts. 1 website connects to the test sql & 1
> connects
> to the live sql.
>
> what i have done so far:
> created 2 SPN's for test service account - HTTP/servername.fqdn &
> HTTP/servername
>
> on the test sql i have created the sql service & SPN details are
> MSSQLvc/servername.fqdn:port
> the sql is running under local system on the test server.
>
> Is what i have done above correct? it seems to work but I also need to > do
> the for the live website on the same server. This is using a different
> service account so if i created the bove bot for a different service
> account
> would it be classed as a duplicate?
>
> Thanks
>
> Phill
>
> "DaveMo" wrote:
>
>> On Feb 10, 4:42 am, phill <ph...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
>> > "phill" wrote:
>> > > Hi,
>> >
>> > > I need to use windows integrated security. The scenario is as
>> > > follows:
>> >
>> > > webserver named server1. On this web server a user has two >> > > service
>> > > accounts. service account1 & 2. He wants to run the test website >> > > on
>> > > port
>> > > 1000, the live wbsite on port 80. these websites also connect to >> > > sql
>> > > backend
>> > > servers. running on port 1199 & 1198.
>> >
>> > > The backend sql is running as local system account.
>> >
>> > > I have read the articles on how to set this up but it does not >> > > seem
>> > > to work.
>> > > could anybody help please.
>> >
>> > i have managed to get kerberos working when connecting to the test
>> > website,
>> > after when i check the sql backend i get a success audit but the
>> > authentication package is Negotiate. is this correct?
>> >
>> >
>> >
>> > - Hide quoted text -
>> >
>> > - Show quoted text -
>>
>> phill,
>>
>>
>> There are lots and lots of details missing here, and setting something
>> up like what you are doing is by neccessity somewhat complicated.
>> Negotiate is the package for "Windows Integrated Authentication" but
>> it provides the functionality for both Kerb and NTLM. It depends on
>> what you are trying to do to know whether you need to know more about
>> what is happening.
>>
>> HTH,
>> Dave
>>



.

Relevant Pages

  • Re: Delegation problems
    ... This sounds like an SPN problem. ... as a service account, did you add an SPN to that service account in AD that ... delegate from my web server to the SQL service on the DB server when I ...
    (microsoft.public.dotnet.framework.aspnet.security)
  • Re: Delegation problems
    ... There are no SPNs on the machine account. ... did you add an SPN to that service account in AD ... delegate from my web server to the SQL service on the DB server when I ...
    (microsoft.public.dotnet.framework.aspnet.security)
  • Re: KDC_ERR_S_PRINCIPAL_UNKNOWN
    ... You have recently changed the account that the SQL service starts up as. ... The SQL server you ... can use ADSIedit to remove the SPN from the First account. ...
    (microsoft.public.win2000.security)
  • Re: AcceptSecurityContext and Service Accounts
    ... Note that on a domain box, the NETWORK SERVICE account IS a domain account as it uses the network credentials of the computer account in AD whenever network credentials are needed, so you don't actually NEED A fixed domain service account just to be able to authenticate with the domain or use Kerb. ... the SPN for your service needs to be set on the account in AD that runs the service. ...
    (microsoft.public.windows.server.active_directory)
  • Re: sql problem
    ... Account # is the link field on ... The requirement is I have to come up with a report comparing the two ... customer tables to see if they are in sync. ... what I wanted to do was to get the SQL to return the "like" names ...
    (comp.databases.oracle.server)
Loading