Trusted connections??

[Paul M]

Hi folks,

Just want to make sure I've got this right.

I've got an application I want to authenticate to the domain i.e. an
intranet app. I've set this up (all the web.config items are in place and
everythings tickety boo) and I can get the username and various other bits
of info from a WindowsIdentity object. So far so good.

When I try and connect to my SQL 2K server however using a trusted
connection, the system will either use the account that ASP.NET runs under
in IIS or I have to switch impersonation on and use a user at my discretion.

Either way, it doesn't matter who's using the application, each individual
session will always use the one account to make a trusted SQL server
connection. Correct?? So I'll always have to replicate some security
structure in the database i.e. create and maintain and User table if I want
to use a trusted connection.

Is this something that's going to get fixed (it's not a feature) in Whidbey?
Having read a number of books about this, none of the authors seem to find
this inconsistency odd, which I find odd. It's as if it's perfectly natural
for the framework to be able to pick up the domain user data, but not let
SQL server use it. To me it's the king of mildly annoying and screamingly
obvious bug I normally associate with the Unix crowd.

I'm tempted just to use Forms authentication and not bother with integrated
security until they sort this out. Would that be a better move?

Any comments and advice would be much appreciated...

Cheers...Paul

 

[DalePres]

Paul,

To use impersonization you would have to get the client's domain password
and username over the network. Asking the client to enter their domain
password is not a good idea. If you want to validate the client and grant
appropriate database access based on user, you'll have to institute
something like form authentication.

But, if you need that sort of security, your question seems to imply that
your app is giving more control to the user than he/she should have. In
other words, if I allow the user to create custom queries, I have to
implement role or user based security at the SQL Server.

If the ASP.Net app controls what the user can request of the database then I
can use application security. This is really the default in ASP. In this
case, I implement user authentication at the application and the application
controls what features the user can use and, thereby, what access the user
can have to the database. This is where your domain login can be safely
tested by using Integrated Windows Authentication for your ASP application.
If a user can add records, you give him NTFS access to the AddRecords.aspx;
if he can only read, he only gets access to ReadRecords.aspx at the NTFS
permissions level.

Hope this helps,

Dale

 

[Mike Kruchten]

Are you trying to connect from your web server to a different machine
running SQL server and want it to use the domain credentials of the client?
I don't know if the ASP.NET stuff is different but with regular ASP this
will only work if you're using Active Directory (Kerberos). I think it's
called account delegation or something.

HTH,
Mike Kruchten