October 20, 2002 at 6:52 pm
HI, I am new to SQL Server and would like a few questions answered:
What is the main purpose for Delegation? Do you have to have Delegation to have user access to SQL server and the databases?
What is the main purpose of inpersonation?
Does this allow easier access? Can it cause problems? Can more than one user use the same inpersonation?
October 20, 2002 at 7:59 pm
In my experience the only place that delegation really matters in SQL is when you're setting up linked servers and want to pass through the NT credentials. Impersonation is also available in COM+ so that objects on an app server can run as the calling user.
Within SQL you can connect using your NT login - which might be explicitly declared as a login, or it might be as a member of one or more NT groups. The result is the same, NT validates who you are, SQL takes it from there. Typically you don't want anyone impersonating you! There are cases where you want users to use the same login (probably more accurate to say that you want the users app to use the same login) and that is where SQL logins come in handy.
Dont know if that helps or not?
Andy
October 21, 2002 at 5:44 am
One area where this comes up frequently is with web access (and occasionally other client/server access) to a SQL database.
User X hits the web server Y. Using IIS and Explorer Y can see the access from X with NT credentials. But let's say server Y wants to hit database server Z to return data back to user X. The common choice is for Y to pass a connection string to Z that is with some internal User ID that the eventual user (X) never sees. So SQL might process the query under user Q, not knowing or caring about user X's credentials. This is very common when user X has no rights to access the database in and of themselves, but only through the web service.
Alternatively, the author may want user X to only get the data that they have access to themselves, despite passing through the web server. in that case the web server Y might pass on X's cerdentials to the SQL Server Z, so that as far as it is concerned X is connecting directly. That impersonation is useful in cases where the web server is providing generalized query tools but the database is the one authenticating who is allowed access to what.
There are a ton of quirks in impersonation and delegation. In NT4 or SQL7 I think you are pretty much limited to this working when Y and Z are the same server. W2K and SQL2K provide more options but not simple ones (if I recall AD is required and some special setup in AD for the two servers), and Dot-Net provides some additional packaging surrounding the impersonation to make it a bit easier. We've experimented with it, I need to get a specific package set up real soon now, but I don't have the details handy.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply