application roles

[bill]

I am looking for examples and assistance in configuring application roles
using SQL Server 2000 and VB.NET, both web forms and windows forms.

Are there any suggestions?

Thanks
Bill

[Mary Chipman]

You probably won't find much because application roles are not widely
used, especially in Web applications because you have to sacrifice
connection pooling to get them to work. See:

PRB: SQL Application Role Errors with OLE DB Resource Pooling
http://support.microsoft.com/default...;EN-US;Q229564

This was written for ADO, but still applies to ADO.NET. Even if they
worked, you would still not want to use them even in a .NET Winforms
application because the application role password must be supplied by
your client code. Reading the IL of a compiled assembly is fairly
straightforward using the disassembler tool (ildasm.exe). Even if it's
not embedded in your code, the password must be stored *somewhere* on
the client, which makes it vulnerable.

--Mary

On Mon, 13 Dec 2004 08:42:34 -0500, "bill" <be****@datamti.com> wrote:

I am looking for examples and assistance in configuring application roles
using SQL Server 2000 and VB.NET, both web forms and windows forms.

Are there any suggestions?

Thanks
Bill

[bill]

Thanks for the input.

What is the recommended approach to prevent users from accessing database
resources independently of the user interface? Users have database
permissions and can access the database using MSAccess or whatever.

I appreciate your help.

-Bill
"Mary Chipman" <mc***@online.microsoft.com> wrote in message
news:7o********************************@4ax.com...

You probably won't find much because application roles are not widely
used, especially in Web applications because you have to sacrifice
connection pooling to get them to work. See:

PRB: SQL Application Role Errors with OLE DB Resource Pooling
http://support.microsoft.com/default...;EN-US;Q229564

This was written for ADO, but still applies to ADO.NET. Even if they
worked, you would still not want to use them even in a .NET Winforms
application because the application role password must be supplied by
your client code. Reading the IL of a compiled assembly is fairly
straightforward using the disassembler tool (ildasm.exe). Even if it's
not embedded in your code, the password must be stored *somewhere* on
the client, which makes it vulnerable.

--Mary

On Mon, 13 Dec 2004 08:42:34 -0500, "bill" <be****@datamti.com> wrote:

I am looking for examples and assistance in configuring application roles
using SQL Server 2000 and VB.NET, both web forms and windows forms.

Are there any suggestions?

Thanks
Bill

[Mary Chipman]

The best way is to take advantage of parameterized stored procedures,
granting only execute permissions for database roles to selected
stored procedures and denying all permissions to the base tables to
public. Users might be able to connect due to their Windows logins
being enabled on the server, but they would be prevented from reading
or modifying data using other query tools. Access won't let you link
to tables you don't have permissions on. It's more work, but worth it
if your goal is increased security.

--Mary

On Tue, 14 Dec 2004 07:56:58 -0500, "bill" <be****@datamti.com> wrote:

Thanks for the input.

What is the recommended approach to prevent users from accessing database
resources independently of the user interface? Users have database
permissions and can access the database using MSAccess or whatever.

I appreciate your help.

-Bill
"Mary Chipman" <mc***@online.microsoft.com> wrote in message
news:7o********************************@4ax.com.. .

You probably won't find much because application roles are not widely
used, especially in Web applications because you have to sacrifice
connection pooling to get them to work. See:

PRB: SQL Application Role Errors with OLE DB Resource Pooling
http://support.microsoft.com/default...;EN-US;Q229564

This was written for ADO, but still applies to ADO.NET. Even if they
worked, you would still not want to use them even in a .NET Winforms
application because the application role password must be supplied by
your client code. Reading the IL of a compiled assembly is fairly
straightforward using the disassembler tool (ildasm.exe). Even if it's
not embedded in your code, the password must be stored *somewhere* on
the client, which makes it vulnerable.

--Mary

On Mon, 13 Dec 2004 08:42:34 -0500, "bill" <be****@datamti.com> wrote:

>I am looking for examples and assistance in configuring application roles
>using SQL Server 2000 and VB.NET, both web forms and windows forms.
>
>Are there any suggestions?
>
>Thanks
>Bill
>

[bill]

I see. Do you think application roles will be abandoned, or will the
problem with connection pooling be resolved in a later version? It's too
bad, because it seems like such a good way to handle database access
otherwise.

Thanks!
Bill

"Mary Chipman" <mc***@online.microsoft.com> wrote in message
news:bk********************************@4ax.com...

The best way is to take advantage of parameterized stored procedures,
granting only execute permissions for database roles to selected
stored procedures and denying all permissions to the base tables to
public. Users might be able to connect due to their Windows logins
being enabled on the server, but they would be prevented from reading
or modifying data using other query tools. Access won't let you link
to tables you don't have permissions on. It's more work, but worth it
if your goal is increased security.

--Mary

On Tue, 14 Dec 2004 07:56:58 -0500, "bill" <be****@datamti.com> wrote:

Thanks for the input.

What is the recommended approach to prevent users from accessing database
resources independently of the user interface? Users have database
permissions and can access the database using MSAccess or whatever.

I appreciate your help.

-Bill
"Mary Chipman" <mc***@online.microsoft.com> wrote in message
news:7o********************************@4ax.com.. .

You probably won't find much because application roles are not widely
used, especially in Web applications because you have to sacrifice
connection pooling to get them to work. See:

PRB: SQL Application Role Errors with OLE DB Resource Pooling
http://support.microsoft.com/default...;EN-US;Q229564

This was written for ADO, but still applies to ADO.NET. Even if they
worked, you would still not want to use them even in a .NET Winforms
application because the application role password must be supplied by
your client code. Reading the IL of a compiled assembly is fairly
straightforward using the disassembler tool (ildasm.exe). Even if it's
not embedded in your code, the password must be stored *somewhere* on
the client, which makes it vulnerable.

--Mary

On Mon, 13 Dec 2004 08:42:34 -0500, "bill" <be****@datamti.com> wrote:

>I am looking for examples and assistance in configuring application roles >using SQL Server 2000 and VB.NET, both web forms and windows forms.
>
>Are there any suggestions?
>
>Thanks
>Bill
>

[Mary Chipman]

They probably won't be abandoned for backwards compatibility issues,
but IMO they sound better than they actually are in reality. Aside
from poolin, the big issue is that they represent a giant security
hole because the application must pass the application role password
to the server in order to activate it. Secrets stored on the client
are *always* vulnerable to being hacked, especially in .NET where
strings are immutable.

--Mary

On Tue, 14 Dec 2004 09:51:42 -0500, "bill" <be****@datamti.com> wrote:

I see. Do you think application roles will be abandoned, or will the
problem with connection pooling be resolved in a later version? It's too
bad, because it seems like such a good way to handle database access
otherwise.

Thanks!
Bill

"Mary Chipman" <mc***@online.microsoft.com> wrote in message
news:bk********************************@4ax.com.. .

The best way is to take advantage of parameterized stored procedures,
granting only execute permissions for database roles to selected
stored procedures and denying all permissions to the base tables to
public. Users might be able to connect due to their Windows logins
being enabled on the server, but they would be prevented from reading
or modifying data using other query tools. Access won't let you link
to tables you don't have permissions on. It's more work, but worth it
if your goal is increased security.

--Mary

On Tue, 14 Dec 2004 07:56:58 -0500, "bill" <be****@datamti.com> wrote:

>Thanks for the input.
>
>What is the recommended approach to prevent users from accessing database
>resources independently of the user interface? Users have database
>permissions and can access the database using MSAccess or whatever.
>
>I appreciate your help.
>
>-Bill
>
>
>"Mary Chipman" <mc***@online.microsoft.com> wrote in message
>news:7o********************************@4ax.com.. .
>> You probably won't find much because application roles are not widely
>> used, especially in Web applications because you have to sacrifice
>> connection pooling to get them to work. See:
>>
>> PRB: SQL Application Role Errors with OLE DB Resource Pooling
>> http://support.microsoft.com/default...;EN-US;Q229564
>>
>> This was written for ADO, but still applies to ADO.NET. Even if they
>> worked, you would still not want to use them even in a .NET Winforms
>> application because the application role password must be supplied by
>> your client code. Reading the IL of a compiled assembly is fairly
>> straightforward using the disassembler tool (ildasm.exe). Even if it's
>> not embedded in your code, the password must be stored *somewhere* on
>> the client, which makes it vulnerable.
>>
>> --Mary
>>
>> On Mon, 13 Dec 2004 08:42:34 -0500, "bill" <be****@datamti.com> wrote:
>>
>> >I am looking for examples and assistance in configuring applicationroles >> >using SQL Server 2000 and VB.NET, both web forms and windows forms.
>> >
>> >Are there any suggestions?
>> >
>> >Thanks
>> >Bill
>> >
>>
>