sql server programming: Application Roles in SQL Server -- sql server programming

Re: Application Roles in SQL Server

Dan Guzman
10/4/2007 6:36:58 AM

Be aware that ADO will implicitly open additional SQL connections as needed
so you need to make sure that resultsets are fully processed before
executing another command. Also, application roles and connection pooling
don't play nice together
(http://msdn2.microsoft.com/en-us/library/8xx3tyca(VS.80).aspx).

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Richard Mueller [MVP]" <rlmueller-nospam@ameritech.nospam.net> wrote in
message news:%23cRnHZjBIHA.1204@TK2MSFTNGP03.phx.gbl...
[quoted text, click to view]

Re: Application Roles in SQL Server

Ralph
10/4/2007 11:04:34 AM

[quoted text, click to view]

You're confusing ADO.Net behavior with ADO behavior. One has nothing to do
with the other.

-ralph

Re: Application Roles in SQL Server

Ralph
10/4/2007 7:29:08 PM

[quoted text, click to view]
<snipped>

It is not.

Discussing ADO.Net is OT in this group.

-ralph

Re: Application Roles in SQL Server

Erland Sommarskog
10/4/2007 10:21:40 PM

Ralph (nt_consulting64@yahoo.com) writes:
[quoted text, click to view]

I'm not sure what you are talking about, but I know Dan, and Dan is
wrong far less often than I am.

In any case, old ADO can indeed do as Dan said: open a new connection
behind your back, which can cause various sorts of confusion. ADO .Net
will never do that as far as I know. If you try to run a command with
unfetched results, you will get an exception. (Unless MARS is involved.)

Dan's remark on application pooling is also on the target. The link happens
to be an ADO .Net resource, but the issue is the same in ADO.

There is, however, an error in the article Dan pointed to: "After a SQL
Server application role has been activated by calling the sp_setapprole
system stored procedure, the security context of that connection cannot be
reset." This is not true for SQL 2005, where you can back out of an
application role with sp_unsetapprole. This requires that you requested
a cookie when you set the role.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

Re: Application Roles in SQL Server

Erland Sommarskog
10/4/2007 10:24:11 PM

Richard Mueller [MVP] (rlmueller-nospam@ameritech.nospam.net) writes:
[quoted text, click to view]

Is this a two-tier or a three-tier application? Keep in mind that in a
two-tier application there is no way you can safely hide the password.
You can scramble it, and scatter it to pieces, but the user who is dead
set on getting it, will get it.

You need to set the application role everytime you connect.

Also, you must either turn off connection pooling, or you must unset
the role each time you disconnect (Possibly only on SQL 2005). Or stay
connected all the time.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.