SQL App roles and intranet

[Martin de vroom]

Hi,

I have been charged with redesigning my companys intranet.
The Intranet uses sql server as the backend db. Currently the intanet uses
windows authentication.
Access to the tables from the asp code is via a combination of sp's and
direct "select" "update" "delete" and update statements.
Obviously this means that permissions are granted directly to users to
execute the sp's and direct permissions on tables is given.
I am pretty sure that I will rewrite all of the direct queries from the asp
code to use sp's, and remove direct permissions from the users.

It is a requirement, from management that users should not be able to access
the database using tools such as EM.
Obviously, putting all of the deirect access into sp's solves security up to
a point but would the use of application roles to better in this situation?

As the connections in an internet/intranet application should be open as
short as possible this would mean.

Connecting to the database.
issuing a call to the sp to invoke the app role.
doing the needed database access
closing the connection.

is this a viable thing to do in an intranet app. The current intranet app
uses purely sql authentication to access the database.
I also need to grab the user name for auditing purposes so I was thinking of
connectng with windows authentication, giving the user access to only
connect to the database and call the one sp to invoke the app role, I
believe that the user name will still be stored in the SUSER_SNAME variable,
and the just apply.

has anybody experience a situation like this before, and could offer advice
on how to handle it.

cheers

martin

[Manohar Kamath [MVP]]

You can use trusted connections (in your conn string) and accomplish exactly
the thing you are looking for. Just make sure the anonymous/Basic
authentications for the app are turned off in IIS settings.

--
Manohar Kamath
Editor, .netBooks
[url]www.dotnetbooks.com[/url]


"Martin de vroom" <martindevroom@hotmail.com> wrote in message
news:e#wDqd6QDHA.2176@TK2MSFTNGP12.phx.gbl...

> Hi,
>
> I have been charged with redesigning my companys intranet.
> The Intranet uses sql server as the backend db. Currently the intanet uses
> windows authentication.
> Access to the tables from the asp code is via a combination of sp's and
> direct "select" "update" "delete" and update statements.
> Obviously this means that permissions are granted directly to users to
> execute the sp's and direct permissions on tables is given.
> I am pretty sure that I will rewrite all of the direct queries from the

asp

> code to use sp's, and remove direct permissions from the users.
>
> It is a requirement, from management that users should not be able to

access

> the database using tools such as EM.
> Obviously, putting all of the deirect access into sp's solves security up

to

> a point but would the use of application roles to better in this

situation?

>
> As the connections in an internet/intranet application should be open as
> short as possible this would mean.
>
> Connecting to the database.
> issuing a call to the sp to invoke the app role.
> doing the needed database access
> closing the connection.
>
> is this a viable thing to do in an intranet app. The current intranet app
> uses purely sql authentication to access the database.
> I also need to grab the user name for auditing purposes so I was thinking

of

> connectng with windows authentication, giving the user access to only
> connect to the database and call the one sp to invoke the app role, I
> believe that the user name will still be stored in the SUSER_SNAME

variable,

> and the just apply.
>
> has anybody experience a situation like this before, and could offer

advice

> on how to handle it.
>
> cheers
>
> martin
>
>

[martin de vroom]

Hi Manohar,

Thanks for replying, I'm not sure I follow you. If I use NTLM
authentication from the web app then I can pick up the users login and use a
trusted connection to sql server. That is cool.

However I do NOT want to grant permissions to that particular person in the
db. But I want the db to know the user name so that I can audit in the
database.

So in order to let the db know the user name I must use a trusted login but
ONLY to connect, as soon as I connect I wish to activate an app role.

I guess my question is.

Is it a good idea to use application roles in web apps?

cheers

martin.

"Manohar Kamath [MVP]" <mkamath@TAKETHISOUTkamath.com> wrote in message
news:OmCFX0GRDHA.704@tk2msftngp13.phx.gbl...

> You can use trusted connections (in your conn string) and accomplish

exactly

> the thing you are looking for. Just make sure the anonymous/Basic
> authentications for the app are turned off in IIS settings.
>
> --
> Manohar Kamath
> Editor, .netBooks
> [url]www.dotnetbooks.com[/url]
>
>
> "Martin de vroom" <martindevroom@hotmail.com> wrote in message
> news:e#wDqd6QDHA.2176@TK2MSFTNGP12.phx.gbl...

> > Hi,
> >
> > I have been charged with redesigning my companys intranet.
> > The Intranet uses sql server as the backend db. Currently the intanet

uses

> > windows authentication.
> > Access to the tables from the asp code is via a combination of sp's and
> > direct "select" "update" "delete" and update statements.
> > Obviously this means that permissions are granted directly to users to
> > execute the sp's and direct permissions on tables is given.
> > I am pretty sure that I will rewrite all of the direct queries from the

> asp

> > code to use sp's, and remove direct permissions from the users.
> >
> > It is a requirement, from management that users should not be able to

> access

> > the database using tools such as EM.
> > Obviously, putting all of the deirect access into sp's solves security

up

> to

> > a point but would the use of application roles to better in this

> situation?

> >
> > As the connections in an internet/intranet application should be open as
> > short as possible this would mean.
> >
> > Connecting to the database.
> > issuing a call to the sp to invoke the app role.
> > doing the needed database access
> > closing the connection.
> >
> > is this a viable thing to do in an intranet app. The current intranet

app

> > uses purely sql authentication to access the database.
> > I also need to grab the user name for auditing purposes so I was

thinking

> of

> > connectng with windows authentication, giving the user access to only
> > connect to the database and call the one sp to invoke the app role, I
> > believe that the user name will still be stored in the SUSER_SNAME

> variable,

> > and the just apply.
> >
> > has anybody experience a situation like this before, and could offer

> advice

> > on how to handle it.
> >
> > cheers
> >
> > martin
> >
> >

>
>