Application Role

Hi there
I have a VB.NET application, that is accessing a SQL DB. I would like to
set it up to start using Application Roles, but am getting some strange
results, and would appreciate advice regarding Best Practices.
My VB.NET application is using SQL Helper class, which basically seems
to be using Connection Pooling, and creating a new connection for each
transaction (if none in the pool). I dont want to set the APplication
Role to be activated in every single call to the DB, as this will slow
down the application completely, but I would like to somehow implement
an Application Role.
I know that Application Roles dont use Connection Pooling, and I dont
mind the preformance hit there, as the application is mostly
single-user.
Also, does anyone know - if I set up an Application Role, can Any
application that has the ApplicationRole password get in and Activate
the Role, or is there a way to limit it somehow?
Thanks in advance
David Crone
*** Sent via Developersdex http://www.codecomments.com ***On Wed, 08 Jun 2005 04:56:51 -0700, David Crone wrote:

> My VB.NET application is using SQL Helper class, which basically seems
> to be using Connection Pooling, and creating a new connection for each
> transaction (if none in the pool). I dont want to set the APplication
> Role to be activated in every single call to the DB, as this will slow
> down the application completely, but I would like to somehow implement
> an Application Role.
I think you're going to have to give up the SQLHelper class (the Data
Access Application Block), or at least extend it / replace it with your own
class. Frankly I found the whole Patterns & Practices library to be bloated
and mostly useless ... come on, every connection I want to use has to store
its connection string in App.config? And I should only use app.config
through the classes? Ridiculous.
It sounds like your app would be happy with one SQLConnection. Try this:
Public Class SQLHelp
Public shared cxn as SQLConnection
Public shared ConnectionString as String = ...
Public shared Sub Init()
cxn = New SQLConnection(ConnectionString)
cxn.Open
dim pass as String = ...
dim cmd as String
sqlcmd = String.Format( _
"set_approle @.rolename='{0}', @.password='{1}'" , _
"MyRole", pass
cxn.Execute cmd
End Sub
Public shared Function ExecDataset(sqlcmd as String) as Dataset
dim ds as new Dataset
dim da as new SQLDataAdapter(sqlcmd, cxn)
da.Fill(ds)
return ds
End Function
End Module
That should get you started. It provides SQLHelp.Init() to open the
connection and set the app role, and SQLHelp.ExecDataset to run a SQL
command and return a dataset. Steal more code from the MS SQLHelper class
if you need it.

> Also, does anyone know - if I set up an Application Role, can Any
> application that has the ApplicationRole password get in and Activate
> the Role, or is there a way to limit it somehow?
The only limitation is that the application must first gain access to the
Database before it can call set_approle. In other words, a valid user in
the database has to open the connection - if Ross Presser isn't defined as
a user (or isin't a member of a windows group that is defined as a user,
etc.) then he can't run approle.
Best practice is usually to store the password encrypted in a file or
registry setting rather than hardcode it into the app, and to change it
periodically.