How do I connect to SQL database using applicaiton roles and ado.net?

[Ken Varn]

My MSDE database uses Application Roles. When I run my asp.net application
I cannot open the database, I get a Logon Failed exception. I need to be
able to open the DB to run the sp_setapprole stored procedure for my role
access. Is there some other way that I can do this? So far the only way I
can get this to work is to give the ASP.NET process SYSTEM rights. I would
prefer to use the ASPNET user rights.

My connection string is as follows:

"Pooling=false;Server=(local);Integrated Security=SSPI;Database=MyDb"


--
-----------------------------------
Ken Varn
Senior Software Engineer
Diebold Inc.

EmailID = varnk
Domain = Diebold.com
-----------------------------------

 

[William \(Bill\) Vaughn]

Did you grant rights to the ASPNET account in SQL Server? (I assume you're
using IIS 5.0). IIS 6.0 uses IUSR_<computername>.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
_________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)


INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Ken Varn]

I just tried that and it seemed to fix part of the problem. However I am
still having some permission issues at times. I think it may have something
to do with user impersonation. There are some areas in the ASP.NET code
that impersonate an admin and the revert back. Would this mess up an open
connection to a DB to cause my current application role to get reset?

--
-----------------------------------
Ken Varn
Senior Software Engineer
Diebold Inc.

EmailID = varnk
Domain = Diebold.com
-----------------------------------
Did you grant rights to the ASPNET account in SQL Server? (I assume you're
using IIS 5.0). IIS 6.0 uses IUSR_<computername>.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
_________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)


INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[William \(Bill\) Vaughn]

Running SQL Server on one server and IIS on another can be problematic. It
requires a second level of security redirection that's especially hard to
configure. I would research "impersonation" on MSDN and elsewhere... I for
one, avoid it. I think you're on the right track though.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
_________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)


INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

I just tried that and it seemed to fix part of the problem. However I am
still having some permission issues at times. I think it may have
something
to do with user impersonation. There are some areas in the ASP.NET code
that impersonate an admin and the revert back. Would this mess up an open
connection to a DB to cause my current application role to get reset?

--
-----------------------------------
Ken Varn
Senior Software Engineer
Diebold Inc.

EmailID = varnk
Domain = Diebold.com
-----------------------------------
Did you grant rights to the ASPNET account in SQL Server? (I assume you're
using IIS 5.0). IIS 6.0 uses IUSR_<computername>.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
_________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)


INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________