Hanley

Hi guys

I've been banging my head with this problem for a couple of days now so I thought I would see if anyone has any ideas

A quick description of the problem.

Our development team have created a website which is hosted on IIS6 on a 2003 server, they've removed Anonymous access and want to use only Integrated Windows Authentication, the site accesses a SQL Server database using a trusted connection.

When you access the link and click on 'Launch' you should get a new window with a few list boxes etc but all you get is the following error

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

After a little troubleshooting I realised the IIS Server wasn't configured for kerberos delegation to the SQL Server. I've initially configured it to be trusted for delegation to any service on the SQL Server.

I then tried again and got the same error, couldn't figure it out at all.

I then realised that the IIS box and the SQL box are in our dev domain and I was logged onto a pc in the production domain. I logged onto a pc with an id from the dev domain and I got the following error:

"Login failed for domain\userid"

Success, error was expected as the ID did not have a SQL login, once I granted it a login it worked as expected.

Now my question is, why won't it work across domains??

The dev domain is not a child domain of our production domain, they don't share the same namespace so I'm assuming this is causing the problem.

I've trawled the net for info on kerberos delegation across domains but haven't had any luck yet.

Any ideas from the many gurus on here???

KiwiGTI

What's the authentication mode of SQL set to? Mixed?

Hanley

Yeah it's set to mixed.

Don't think that's a problem though as it works in a single domain.

I found some info on Technet that says constrained delegation (which I think is what I'm attempting) cannot cross the domain boundry and also Kerberos Protocol Transition needs a 2-way trust between forests (ours is only 1-way)

So either way it seems like a no go.

Gigsy

You could try using local accounts on the IIS and SQL boxes - both with the same UID and PWD. This solved a similar SQL/IIS authentication issue I had a while back.

Hanley

But thats not the real issue.

When I browse the site from the IIS box it works okay as it uses NTLM to authenticate me on the SQL box, as it's only one hop from IIS > SQL NTLM is okay.

When I use a client pc the IIS box is the middle tier and the SQL boxis the back-end, if NTLM is used the IIS box won't hold my password and so the 'null' user gets passed to SQL.

The answer is to authenticate using kerberos and configure the IIS computer account for delegation, we set-up constrained delegation so it could only delegate to the MSSQLSvc SPN on the SQL box, works okay within the same domain.

Problem I'm having is our client pc's are in a different domain, kerberos uses something called SSPI to allow delegation over any protocol and, unfortunately, unless there is a 2-way trust between the domains it won't work. Also constrained delegation can't cross the domain boundry so it's never going to work.

Not really an issue in prod as all clients, IIS and SQL are in the same domain, it's only for testng purposes we'll need to change things slightly.

My head hurts

Gigsy

Mine too... whatever happened to DOS! ;0)