Using SQL Server Application Roles in a VB.NET web app 
Author Message
 Using SQL Server Application Roles in a VB.NET web app

I am working on a web application using VB.NET/ASP.NET to
a SQL Server 2000 database.  There will eventually be
three separate applications using the database with
different levels of permissions and funtionality.  In a
non-web environment I would use SQL Server application
roles to handle this issue and assign the appropriate
permissions to the app role.  With ASP.NET using the
default ASPNET user, I've been told that I cannot use the
application roles for an ASP.NET application.

Is this true?  Or is there a way to use an application
role with an ASP.NET application.



Sat, 23 Apr 2005 00:01:14 GMT  
 Using SQL Server Application Roles in a VB.NET web app
You can do it, but there's still connection pooling issues. I'd create
three regular roles (so you can take advantage of connection pooling
instead of having to turn it off as you would if you used approles)
and make the ASPNET account a member of all three. The relevant KB
article is at
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q229564

If you use stored procedures/UDF's instead of direct table access and
grant permissions to execute on the sprocs, you should be able to
manage without application roles.

-- Mary
MCW Technologies
http://www.mcwtech.com



Quote:
>I am working on a web application using VB.NET/ASP.NET to
>a SQL Server 2000 database.  There will eventually be
>three separate applications using the database with
>different levels of permissions and funtionality.  In a
>non-web environment I would use SQL Server application
>roles to handle this issue and assign the appropriate
>permissions to the app role.  With ASP.NET using the
>default ASPNET user, I've been told that I cannot use the
>application roles for an ASP.NET application.

>Is this true?  Or is there a way to use an application
>role with an ASP.NET application.



Sun, 24 Apr 2005 03:29:59 GMT  
 Using SQL Server Application Roles in a VB.NET web app
Rick,

    The role will be added to the current database application by using the
following command sp_addaprole 'joe','joe'.
In the aspnet page, run sp_setapprole 'joe','joe' after  making the
connection to the database.
Using the same connection, run 'select user'. If the last command returns
joe, then application roles can be used.

I am just curious about not being able to do the same with ASP.Net. Is it
because you are
trying to use OLEDB connection.
By the way there is an excellent reference for using Data Access Security
with ASP.Net

Thanks,
Bassel Tabbara (MS)

This posting is provided AS IS with no warranties, and confers no rights.
You assume all risk for your use.
? 2001 Microsoft Corporation. All rights reserved.

--------------------
| Content-Class: urn:content-classes:message


| Subject: Using SQL Server Application Roles in a VB.NET web app
| Date: Mon, 4 Nov 2002 08:01:14 -0800
| Lines: 12

| MIME-Version: 1.0
| Content-Type: text/plain;
|       charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Thread-Index: AcKEG2ciy6cIIpHQQyG7xXAdboWvSQ==
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Newsgroups: microsoft.public.dotnet.languages.vb
| Path: cpmsftngxa09
| Xref: cpmsftngxa09 microsoft.public.dotnet.languages.vb:73318
| NNTP-Posting-Host: TKMSFTNGXA12 10.201.226.40
| X-Tomcat-NG: microsoft.public.dotnet.languages.vb
|
| I am working on a web application using VB.NET/ASP.NET to
| a SQL Server 2000 database.  There will eventually be
| three separate applications using the database with
| different levels of permissions and funtionality.  In a
| non-web environment I would use SQL Server application
| roles to handle this issue and assign the appropriate
| permissions to the app role.  With ASP.NET using the
| default ASPNET user, I've been told that I cannot use the
| application roles for an ASP.NET application.
|
| Is this true?  Or is there a way to use an application
| role with an ASP.NET application.
|



Sun, 24 Apr 2005 03:46:08 GMT  
 Using SQL Server Application Roles in a VB.NET web app
Sorry, it's just not that simple. I would suggest you read
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q229564.
Although the paper needs to be updated for ADO.NET, the same problems
sill apply, even with SqlClient.

-- Mary
MCW Technologies
http://www.mcwtech.com


Quote:

>Rick,

>    The role will be added to the current database application by using the
>following command sp_addaprole 'joe','joe'.
>In the aspnet page, run sp_setapprole 'joe','joe' after  making the
>connection to the database.
>Using the same connection, run 'select user'. If the last command returns
>joe, then application roles can be used.

>I am just curious about not being able to do the same with ASP.Net. Is it
>because you are
>trying to use OLEDB connection.
>By the way there is an excellent reference for using Data Access Security
>with ASP.Net

>Thanks,
>Bassel Tabbara (MS)

>This posting is provided AS IS with no warranties, and confers no rights.
>You assume all risk for your use.
>? 2001 Microsoft Corporation. All rights reserved.

>--------------------
>| Content-Class: urn:content-classes:message


>| Subject: Using SQL Server Application Roles in a VB.NET web app
>| Date: Mon, 4 Nov 2002 08:01:14 -0800
>| Lines: 12

>| MIME-Version: 1.0
>| Content-Type: text/plain;
>|   charset="iso-8859-1"
>| Content-Transfer-Encoding: 7bit
>| X-Newsreader: Microsoft CDO for Windows 2000
>| Thread-Index: AcKEG2ciy6cIIpHQQyG7xXAdboWvSQ==
>| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>| Newsgroups: microsoft.public.dotnet.languages.vb
>| Path: cpmsftngxa09
>| Xref: cpmsftngxa09 microsoft.public.dotnet.languages.vb:73318
>| NNTP-Posting-Host: TKMSFTNGXA12 10.201.226.40
>| X-Tomcat-NG: microsoft.public.dotnet.languages.vb
>|
>| I am working on a web application using VB.NET/ASP.NET to
>| a SQL Server 2000 database.  There will eventually be
>| three separate applications using the database with
>| different levels of permissions and funtionality.  In a
>| non-web environment I would use SQL Server application
>| roles to handle this issue and assign the appropriate
>| permissions to the app role.  With ASP.NET using the
>| default ASPNET user, I've been told that I cannot use the
>| application roles for an ASP.NET application.
>|
>| Is this true?  Or is there a way to use an application
>| role with an ASP.NET application.
>|



Sun, 24 Apr 2005 22:32:30 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Problems using SQL Server application roles (long)

2. SQL server application role and VB connection

3. Using SQL Application Role in VB

4. VB.NET: Steps for Converting a Windows .NET Application to a Web .NET Application

5. VB Desktop Application Accessing SQL Server on Web Server

6. VB Desktop Application Accessing SQL Server on Web Server

7. VB Desktop Application Accessing SQL Server on Web Server

8. VB Desktop Application Accessing SQL Server on Web Server

9. SQL Server Integrated login & application Roles

10. SQL Server Application Roles

11. SQL Server Application roles and connections

12. SQL Server Application Role

 

 
Powered by phpBB® Forum Software