Re: application roles

From: Mary Chipman (mchip_at_online.microsoft.com)
Date: 12/15/04 

Date: Wed, 15 Dec 2004 11:00:19 -0500

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" <belgie@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" <mchip@online.microsoft.com> wrote in message
>news:bkttr099vg0rijg4fuk86o4o3bmtvrp7s7@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" <belgie@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" <mchip@online.microsoft.com> wrote in message
>> >news:7o4sr0tctd2ep62l4h8666ha605e60kckl@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.aspx?scid=KB;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" <belgie@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
>> >> >
>> >>
>> >
>>
>