sp_setapprole use wiithin ASP 
Author Message
 sp_setapprole use wiithin ASP

We are trying to tighten security within databases and are in need of
implementing the use of sp_setapprole, in order to allow access to a
specific table only through a web page on our intranet.  I've tried
implemeting this in ASP, but it only seems to work 30% of the time, all
other times, returning an "access to <table> is denied" or "sp_setapprole
was not invoked correctly. Refer to the documentation for more
information.", as if it had been called twice.

Any suggestions for implementing a successful use of sp_setapprole?

Thanks



Tue, 27 Sep 2005 16:03:24 GMT
 sp_setapprole use wiithin ASP

Most probably connection pooling. You can turn that off in the connection string, or you can
work the SESSION_USER function to determine whether the connection you get already is in the
role or not.

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...

Quote:

> We are trying to tighten security within databases and are in need of
> implementing the use of sp_setapprole, in order to allow access to a
> specific table only through a web page on our intranet.  I've tried
> implemeting this in ASP, but it only seems to work 30% of the time, all
> other times, returning an "access to <table> is denied" or "sp_setapprole
> was not invoked correctly. Refer to the documentation for more
> information.", as if it had been called twice.

> Any suggestions for implementing a successful use of sp_setapprole?

> Thanks



Tue, 27 Sep 2005 16:15:42 GMT
 sp_setapprole use wiithin ASP
I'll have a look into that one.

Thanks



Quote:
> Most probably connection pooling. You can turn that off in the connection
string, or you can
> work the SESSION_USER function to determine whether the connection you get
already is in the
> role or not.

> --
> Tibor Karaszi, SQL Server MVP
> Archive at:

http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...
Quote:




Quote:
> > We are trying to tighten security within databases and are in need of
> > implementing the use of sp_setapprole, in order to allow access to a
> > specific table only through a web page on our intranet.  I've tried
> > implemeting this in ASP, but it only seems to work 30% of the time, all
> > other times, returning an "access to <table> is denied" or
"sp_setapprole
> > was not invoked correctly. Refer to the documentation for more
> > information.", as if it had been called twice.

> > Any suggestions for implementing a successful use of sp_setapprole?

> > Thanks



Tue, 27 Sep 2005 16:59:50 GMT
 sp_setapprole use wiithin ASP
The pooling idea doesn't seem to have affected the problem. I've added the
"pooling=false" to the connection string.

The first time the page loads, an "permissions denied on table..." message
is returned.
When you go to the page the second time, it displays correctly.
Any attempt to access the page after this, returns the "sp_setapprole was
not invoked correctly. Refer to the documentation for more information."
error message.

I notice that two users NT_AUTHORITY/ANONYMOUS LOGON are still logged onto
the database, even though the connection to the database is closed within
the ASP code.  Both the processes status's are "sleeping".  Killing these
two  processes starts the above cycle again.

Any suggestions?


Quote:
> I'll have a look into that one.

> Thanks

> "Tibor Karaszi"


Quote:

> > Most probably connection pooling. You can turn that off in the
connection
> string, or you can
> > work the SESSION_USER function to determine whether the connection you
get
> already is in the
> > role or not.

> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:

http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...
Quote:



> > > We are trying to tighten security within databases and are in need of
> > > implementing the use of sp_setapprole, in order to allow access to a
> > > specific table only through a web page on our intranet.  I've tried
> > > implemeting this in ASP, but it only seems to work 30% of the time,
all
> > > other times, returning an "access to <table> is denied" or
> "sp_setapprole
> > > was not invoked correctly. Refer to the documentation for more
> > > information.", as if it had been called twice.

> > > Any suggestions for implementing a successful use of sp_setapprole?

> > > Thanks



Tue, 27 Sep 2005 20:15:25 GMT
 sp_setapprole use wiithin ASP
Just found an article stating (http://www.sqlteam.com/item.asp?ItemID=864):

You should note that the use of AppRoles with ADO is problematic since the
approles is strictly a SQL Server concoction that ADO is apparently not
aware of or programmed to.

In some cases ADO will create additional connections behind the scenes
(especially when connections are 'busied') and since it doesnt know about
approles they are not propogated automatically to the cloned connections. As
a result, the new conenctions cannot access the database through the approle
and would rely on whatever other access permisions the user had.

This seems to be the problems I'm having.


Quote:
> The pooling idea doesn't seem to have affected the problem. I've added the
> "pooling=false" to the connection string.

> The first time the page loads, an "permissions denied on table..." message
> is returned.
> When you go to the page the second time, it displays correctly.
> Any attempt to access the page after this, returns the "sp_setapprole was
> not invoked correctly. Refer to the documentation for more information."
> error message.

> I notice that two users NT_AUTHORITY/ANONYMOUS LOGON are still logged onto
> the database, even though the connection to the database is closed within
> the ASP code.  Both the processes status's are "sleeping".  Killing these
> two  processes starts the above cycle again.

> Any suggestions?



> > I'll have a look into that one.

> > Thanks

> > "Tibor Karaszi"


> > > Most probably connection pooling. You can turn that off in the
> connection
> > string, or you can
> > > work the SESSION_USER function to determine whether the connection you
> get
> > already is in the
> > > role or not.

> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at:

http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...

- Show quoted text -

Quote:



> > > > We are trying to tighten security within databases and are in need
of
> > > > implementing the use of sp_setapprole, in order to allow access to a
> > > > specific table only through a web page on our intranet.  I've tried
> > > > implemeting this in ASP, but it only seems to work 30% of the time,
> all
> > > > other times, returning an "access to <table> is denied" or
> > "sp_setapprole
> > > > was not invoked correctly. Refer to the documentation for more
> > > > information.", as if it had been called twice.

> > > > Any suggestions for implementing a successful use of sp_setapprole?

> > > > Thanks



Tue, 27 Sep 2005 20:50:25 GMT
 sp_setapprole use wiithin ASP
secondary connections are a "feature" that exhibits only in multi-threaded
conditions. if you insist on using an approle then you should open/close
your connection on each page access instead of storing an opened one in
session/application/SPM etc. don't know how conn pooling will affect you
though.

cheers.
</wqw>


Quote:
> Just found an article stating

(http://www.sqlteam.com/item.asp?ItemID=864):
Quote:

> You should note that the use of AppRoles with ADO is problematic since the
> approles is strictly a SQL Server concoction that ADO is apparently not
> aware of or programmed to.

> In some cases ADO will create additional connections behind the scenes
> (especially when connections are 'busied') and since it doesnt know about
> approles they are not propogated automatically to the cloned connections.
As
> a result, the new conenctions cannot access the database through the
approle
> and would rely on whatever other access permisions the user had.

> This seems to be the problems I'm having.



> > The pooling idea doesn't seem to have affected the problem. I've added
the
> > "pooling=false" to the connection string.

> > The first time the page loads, an "permissions denied on table..."
message
> > is returned.
> > When you go to the page the second time, it displays correctly.
> > Any attempt to access the page after this, returns the "sp_setapprole
was
> > not invoked correctly. Refer to the documentation for more information."
> > error message.

> > I notice that two users NT_AUTHORITY/ANONYMOUS LOGON are still logged
onto
> > the database, even though the connection to the database is closed
within
> > the ASP code.  Both the processes status's are "sleeping".  Killing
these
> > two  processes starts the above cycle again.

> > Any suggestions?



> > > I'll have a look into that one.

> > > Thanks

> > > "Tibor Karaszi"


> > > > Most probably connection pooling. You can turn that off in the
> > connection
> > > string, or you can
> > > > work the SESSION_USER function to determine whether the connection
you
> > get
> > > already is in the
> > > > role or not.

> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > Archive at:

http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...

- Show quoted text -

Quote:



> > > > > We are trying to tighten security within databases and are in need
> of
> > > > > implementing the use of sp_setapprole, in order to allow access to
a
> > > > > specific table only through a web page on our intranet.  I've
tried
> > > > > implemeting this in ASP, but it only seems to work 30% of the
time,
> > all
> > > > > other times, returning an "access to <table> is denied" or
> > > "sp_setapprole
> > > > > was not invoked correctly. Refer to the documentation for more
> > > > > information.", as if it had been called twice.

> > > > > Any suggestions for implementing a successful use of
sp_setapprole?

> > > > > Thanks



Tue, 27 Sep 2005 22:17:09 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. Connection trouble- sp_setapprole used.

2. Calling sp_setapprole using ODBC API

3. sp_setapprole with third party program using ODBC

4. Calling sp_setapprole using ODBC API

5. calling sp_setapprole using the ODBC AP

6. Problem: SQL Server 7 using ASP - Using wildcards with Field Names

7. Problem using VB to retrieve ASP/XML using ADO recordset

8. Application Roles - sp_setapprole

9. sp_setapprole in ADO

10. sp_setapprole and connection pooling

11. sp_setapprole error!

12. Application Role, invoking sp_setapprole from a Client Access MDE


 
Powered by phpBB® Forum Software