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

dualybing posted on Monday, August 28, 2006

Hello, I am attempting to use "Windows" authentication with my implementation of the CSLA Framework.  For testing, I have a separate machine for UI, Application Server and Database.

I have added the CslaAuthentication = "Windows" key to my UI machine as well as the IIS machine handles the remoting.  I have also created the identity impersonate="true" key on the IIS server and disabled anonymous access under the website config.

Everything works great when the UI + Business logic reside on the same machine... however, everytime I attempt to use a remote dataportal I get:

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

When I look in the EventLog I can see that my user account has been successfully authenticated by Windows on my web server, but when the data call goes out to my SQL Server (third and separate machine) the credentials are not making it there.

Will someone be awesome enough to explain what I have missed?

Thanks,

-Rob

RockfordLhotka replied on Monday, August 28, 2006

I am not a security expert, so I can't give a full explanation. However, I do know that there are limits to how far you can impersonate, and I think you are hitting that limit. In other words, the app server can impersonate the identity of the client's user, but it can't then pass those credentials to the database server to do the impersonation there. I'm sure there's a good reason why this is the case, but I'm not enough of a security expert to explain it...

figuerres replied on Monday, August 28, 2006

OMG.... it's been a while but....

if you have a domain and several servers you may use AD with Keberos to pass a Kebros TGT all the way back to the SQL server.

but first I'd check "WHY" you want to do that.

generally if you go to deep with impersonation you incurr some problems like number of CAL's you have to have and number of sql connections you are opening.

say you have 3 servers:  front web, midle app, back end SQL

then let the mid-tier have an account to connect to SQL

then sql gets one user, and you can use connection pooling to keep connections llow and CAL's low

and perf high.

If I dug out the details there are sveral reasons why that is generaly the way to go aside from the first few....

build checks in your front and mid servers and then "trust" that mid teir box to do only what you tell it to do.

do not let any users inputs go all the way back w/o having been transformed and sanitised so to speak :-)

HTH

figuerres replied on Monday, August 28, 2006

here is a blog item that relates:

http://blogs.msdn.com/mjeelani/archive/2004/12/07/275921.aspx

dualybing replied on Tuesday, August 29, 2006

ok, i'll just hard code the middle tier impersonation to take advantage of connection pooling (that worked when I tested it previously).

Thanks.