Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP .Net Security > Membership functions connect to Sql Server as Process Identity, not user identity??

Reply
Thread Tools

Membership functions connect to Sql Server as Process Identity, not user identity??

 
 
andrew.sher@gmail.com
Guest
Posts: n/a
 
      04-16-2006
When I attempt to use any of the Membership class functions (eg
getAllUsers()) to access my db from my web app, it is my
mydomain/processidentity attempting to log in to sql server, and this
is failing as this id has not been granted access in sql server(on
purpose). I am using windows authentication (in web.config), with
impersonation on, and basic authentication turned on, anonymous access
off, in IIS. When I access the database by means other than through
the Membership class, such as creating my own sql commands,
sqldatareaders, etc., I correctly log into sql server as the
impersonated user. Looking at the audit logs, it seems that regardless
of my impersonation settings, the Membership class functions run under
the processidentity id, not the impersonated user id. Is this by design
and is there any way around this? For the life of me I can't figure out
why these Membership functions do not assume the user id like
everything else does. I'm desparate for a solution and haven't found a
solution anywhere.

 
Reply With Quote
 
 
 
 
Dominick Baier [DevelopMentor]
Guest
Posts: n/a
 
      04-16-2006
right - that's by design - and i would call it a feature..

Do you really want that every single user of your system has direct access
to your credentials database??

out of curiosity - why do you use membership when you use Windows Authentication?

- and to answer your question - there is no way around it - you could download
the sources of the SqlMembershipProvider and remove the code that checks
for impersonation if thats really what you want/need...

http://download.microsoft.com/downlo...kitSamples.msi

---------------------------------------
Dominick Baier - DevelopMentor
http://www.leastprivilege.com

> When I attempt to use any of the Membership class functions (eg
> getAllUsers()) to access my db from my web app, it is my
> mydomain/processidentity attempting to log in to sql server, and this
> is failing as this id has not been granted access in sql server(on
> purpose). I am using windows authentication (in web.config), with
> impersonation on, and basic authentication turned on, anonymous access
> off, in IIS. When I access the database by means other than through
> the Membership class, such as creating my own sql commands,
> sqldatareaders, etc., I correctly log into sql server as the
> impersonated user. Looking at the audit logs, it seems that regardless
> of my impersonation settings, the Membership class functions run under
> the processidentity id, not the impersonated user id. Is this by
> design and is there any way around this? For the life of me I can't
> figure out why these Membership functions do not assume the user id
> like everything else does. I'm desparate for a solution and haven't
> found a solution anywhere.
>



 
Reply With Quote
 
 
 
 
andrew.sher@gmail.com
Guest
Posts: n/a
 
      04-16-2006
Thank you for replying, this has been bothering me for days. To answer
your questions, the reason I want to use membership while using windows
authentication is this site serves as an administration portal for
another site, and thus every user of this system by definition is to
have direct access to the credentials db. That other site does use
forms authentication and the membership class for everything. While the
other site is open to any public user, those in charge want to have a
more secure method of gathering/displaying aggregate user data, and
thus would like to use windows authentication so that only certain
users in their domain are able to login to the admin site. As another
layer of security, they want to be able to restrict the execution of
the aggregate stored procedures by setting exec permissions on Sql
Server to specific users, thus the reason I need to be able to
impersonate the user all the way to sql server (even though
theoretically the only people who can get into the admin site in the
first place will be those that have the credentials to also run the
aggregate sp's in the database). I know the drawbacks of impersonating
to sql server, including connection pool issues, but that's the way it
has to be. The reason I wanted to use the Membership functions in my
admin app is that they provide exactly the functionality I need since
all the data in the db is modeled around Membership. I could rewrite
all of the procedure calls, but chances are it wont be as quick or
correct as the real ones, but it looks like that's what i need to do.

On a side note, thank you Dominick for providing the ShowContexts.aspx
file on your website, I've been using it this last week and it's helped
me learn a lot about impersonation, security, etc.

 
Reply With Quote
 
Dominick Baier [DevelopMentor]
Guest
Posts: n/a
 
      04-16-2006
thanks, glad it is useful

as i said - you could just use the source of the sql provider released by
MS last week and remove the impersonation/revert to self code - look out
for calls to "SqlConnectionHelper.GetConnection()"

---------------------------------------
Dominick Baier - DevelopMentor
http://www.leastprivilege.com

> Thank you for replying, this has been bothering me for days. To answer
> your questions, the reason I want to use membership while using
> windows authentication is this site serves as an administration portal
> for another site, and thus every user of this system by definition is
> to have direct access to the credentials db. That other site does use
> forms authentication and the membership class for everything. While
> the other site is open to any public user, those in charge want to
> have a more secure method of gathering/displaying aggregate user data,
> and thus would like to use windows authentication so that only certain
> users in their domain are able to login to the admin site. As another
> layer of security, they want to be able to restrict the execution of
> the aggregate stored procedures by setting exec permissions on Sql
> Server to specific users, thus the reason I need to be able to
> impersonate the user all the way to sql server (even though
> theoretically the only people who can get into the admin site in the
> first place will be those that have the credentials to also run the
> aggregate sp's in the database). I know the drawbacks of impersonating
> to sql server, including connection pool issues, but that's the way it
> has to be. The reason I wanted to use the Membership functions in my
> admin app is that they provide exactly the functionality I need since
> all the data in the db is modeled around Membership. I could rewrite
> all of the procedure calls, but chances are it wont be as quick or
> correct as the real ones, but it looks like that's what i need to do.
>
> On a side note, thank you Dominick for providing the ShowContexts.aspx
> file on your website, I've been using it this last week and it's
> helped me learn a lot about impersonation, security, etc.
>



 
Reply With Quote
 
andrew.sher@gmail.com
Guest
Posts: n/a
 
      04-16-2006
Thanks again, I was wondering where the code was to change
impersonation. Now I have to figure out how to take the
SqlMembershipProvider, make my changes, and get it to compile
correctly. I tried to just copy all the source that microsoft provided
and put it in my app_code folder and change my settings in the
web.config file, but I got a compile error saying Microsoft.Samples.SR
is inaccesible due to its protection level. I don't know what this
means, so back to googling for answers.

 
Reply With Quote
 
Dominick Baier [DevelopMentor]
Guest
Posts: n/a
 
      04-16-2006
not sure whats your problem - it compiles fine here -

SR is internal - maybe change it to public / don't put it in App_Code

the impersonation code in question is (in SqlConnectionHolder.Open)

if (revertImpersonate) {
using (HostingEnvironment.Impersonate()) {
Connection.Open();
}
}
else {
Connection.Open();
}

just change it to

Connection.Open();


(not tested)


---------------------------------------
Dominick Baier - DevelopMentor
http://www.leastprivilege.com

> Thanks again, I was wondering where the code was to change
> impersonation. Now I have to figure out how to take the
> SqlMembershipProvider, make my changes, and get it to compile
> correctly. I tried to just copy all the source that microsoft provided
> and put it in my app_code folder and change my settings in the
> web.config file, but I got a compile error saying Microsoft.Samples.SR
> is inaccesible due to its protection level. I don't know what this
> means, so back to googling for answers.
>



 
Reply With Quote
 
andrew.sher@gmail.com
Guest
Posts: n/a
 
      04-16-2006
My problem was in the web.config provider element, I tried to refer to
my custom provider without using the 'Microsoft.Samples...' prefix in
the type string. It's all working perfectly now, thanks.

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Failed to generate a user instance of SQL Server due to a failure instarting the process for the user instance. The connection will be closed Homer ASP .Net 1 05-26-2009 12:36 AM
How to remote connect to the SQL server 2005 when th ere is a‘\’ in the SQL server name, such as 192.168.0.1 1\active? Wesley Chen Ruby 9 04-14-2009 11:36 AM
How to remote connect to the SQL server 2005 when th ere is a‘\’ in the SQL server name, such as 192.168.0.1 1\active? Wesley Chen Ruby 0 04-07-2009 10:23 AM
Help. Getting a An error has occurred while establishing a connectionto the server. When connecting to SQL Server 2005, this failure may be causedby the fact that under the default settings SQL Server does not allow remote aboutjav.com@gmail.com ASP .Net 0 05-03-2008 12:43 PM
Can't connect to SQL Server, using Windows Authentication users of SQL server? help =?Utf-8?B?UmV6YQ==?= ASP .Net 3 06-07-2004 06:42 PM



Advertisments