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.

Application Role

I am confused . What is considered an application and how SQL would know ?

If I have a web site accessing SQL VIA IIS will SQL Server treat it as an application ? How about MS Excel ?

Also , If I was to use the application of Power builder , using app role , how do control which user can use the app ?

ThanksAn application role is activated for a connection by running the sp_setapprole stored procedure. The idea is you can activate the application role by putting the password in the compiled code of your application, while people using MS Access, and Query Analyzer can not get the same permissions because they do not know the password for the application role. Look up sp_setapprole in BOL.

Application On Start And Database connection

Hello,
I was wondering if someone could help me answer this question.

If I had a site like Yahoo, that constantly had users accessing it. Would it be a good or bad idea to automatically open a database connection to SQL Server on Application Start? What would you recommend? Would opening and closing the DB connection on every page save alot of overhead and bottleneck? Thank You.> Would it be a good or bad idea to automatically open a database connection to SQL Server on Application Start?

BAD idea. horrible in fact.

open a database connection immediately before you need it and close it immediately after.

remember, connection pooling is handled for you.. so it's hardly a huge overhead.|||Thank You for your help.

Anyway to Stop DSN & DSNless connections to SQL Server?

I'm trying to setup a new server but need to stop people accessing the database via Access, Excel etc

Is there any way to set SQL server up to stop connections from such applications?

Is there any way to allow only certain users to connect via a DSN

We are not using windows authentication on the server

Cheers

No, you cannot restrict connections based on the application that made them. But you can restrict connections based on the account they were made with. You can try to have your application connect to SQL Server using a specific account and not grant any connection rights to the users - then they would only be able to access the database through the application. Of course, if they would debug the application, they could figure out how it connects to SQL Server (the account and the password), but this would at least prevent accidental connections from other applications.

Thanks
Laurentiu