IIS and SQL2008 and Integrated Authentication and Constrained Delegation

• castaway1971

  Valued Member

  Points: 69

  More actions

  May 11, 2010 at 11:01 am

  #218793

  Hello All:-)

  As the title of this post might suggest I need some advice or to be pointed in the right direction. I have a windows IIS server located in a DMZ in it's own workgroup. I've an 2008 SQL server located in a domain that the IIS server needs to access. The SQL server has been setup using windows authentication and needs to stay like that.

  A .net application is running on the IIS server and is trying to access the SQL Server. In order to do this

  1 Ccreated an sql service account on the domain and have assigned it logon privileges to the sql server. It can access the sql server fine from within the domain.

  2 Created an sql application account on the domain and to run the iis application pool.

  3 Created the service principal names... on the sql server

  setspn -A http/test.iis.local sqlappacc

  **Actually I'm not sure what is required after the http/ most information I found seems to suggest the **fqdn of the website. but I wonder if this important.. and would just the machine name do?

  setspn -A MSSQLSvc/sql1.leon.local:1433 sqladminacc

  4 I then set the delegation for the sqlappacc in active directory, selected Trust this user for

  delegation to specified services only and the added the sqladminacc that I created the spn against

  and also selected "use any authentication protoocol"

  5 added the following two lines to the webconfig.txt

  <identity impersonate="true" />

  <authentication mode="Windows" />

  6 created an username on the IIS server of sqlappacc and then created an application pool and set it to run under this account. I then also set the .net app to use this application pool.

  When I try to run the .net app I get the following error from the iis server....

  [SqlException (0x80131904): Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.]

  On the sql side I get the following event log error messages..

  Log Name: Application

  Source: MSSQLSERVER

  Date: 11/05/2010 13:24:55

  Event ID: 18452

  Task Category: Logon

  Level: Information

  Keywords: Classic,Audit Failure

  User: N/A

  Computer: sql1.leon.local

  Description:

  Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: 192.168.130.10]

  Event Xml:

  <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">

  <System>

  <Provider Name="MSSQLSERVER" />

  <EventID Qualifiers="49152">18452</EventID>

  <Level>0</Level>

  <Task>4</Task>

  <Keywords>0x90000000000000</Keywords>

  <TimeCreated SystemTime="2010-05-11T12:24:55.000Z" />

  <EventRecordID>2448</EventRecordID>

  <Channel>Application</Channel>

  <Computer>sql1.leon.local</Computer>

  <Security />

  </System>

  <EventData>

  <Data> [CLIENT: 192.168.130.10]</Data>

  <Binary>144800000E00000005000000530051004C0031000000070000006D00610073007400650072000000</Binary>

  </EventData>

  </Event>

  Log Name: Application

  Source: MSSQLSERVER

  Date: 11/05/2010 13:24:55

  Event ID: 17806

  Task Category: Logon

  Level: Error

  Keywords: Classic

  User: N/A

  Computer: sql1.leon.local

  Description:

  SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: 192.168.130.10]

  Event Xml:

  <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">

  <System>

  <Provider Name="MSSQLSERVER" />

  <EventID Qualifiers="49152">17806</EventID>

  <Level>2</Level>

  <Task>4</Task>

  <Keywords>0x80000000000000</Keywords>

  <TimeCreated SystemTime="2010-05-11T12:24:55.000Z" />

  <EventRecordID>2447</EventRecordID>

  <Channel>Application</Channel>

  <Computer>sql1.leon.local</Computer>

  <Security />

  </System>

  <EventData>

  <Data>8009030c</Data>

  <Data> [CLIENT: 192.168.130.10]</Data>

  <Binary>8E4500001400000005000000530051004C003100000000000000</Binary>

  </EventData>

  </Event>

  I'm been trying this for some time and haven't made much progress with it.. Do I need to do anything else? I checked to see did I have multiple SPN's and I don't... I'd be grateful for any help on this one.. before I go mad.

  Thanks:w00t:

  P.S. If this is posted int he wrong place please feel free to move it...