ASP.NET 2.0 SqlDataSource and SQL 2005 Express Application Role

[Antony Lai]

Dear All,

I am not sure I shall post this here. If not, please let me know.

I am building a web application using asp.net 2.0 using c# (IIS 5 under XP)
with the web developer express edition and Sql 2005 express edition. I love
the SqlDataSource control, and want to use it to access my database. I am
planning my database to use "application role", but SqlDataSource only acept
a connection string. That means I cannot use SQL Application Roles. Am I
mis-understand something or I need to use a SQL Server Login instead?

Thank you.

 

[Pavel Minaev]

Dear All,

I am not sure I shall post this here. If not, please let me know.

I am building a web application using asp.net 2.0 using c# (IIS 5 under XP)
with the web developer express edition and Sql 2005 express edition. I love
the SqlDataSource control, and want to use it to access my database. I am
planning my database to use "application role", but SqlDataSource only acept
a connection string. That means I cannot use SQL Application Roles. Am I
mis-understand something or I need to use a SQL Server Login instead?

You could probably do that by registering handlers for Selecting/
Updating/Deleting/Inserting events of SqlDataSources, and within them,
calling sp_setapprole. However, app roles also don't play nice with
ADO.NET connection pool (it's not aware of them, so it will happily
take your connection post-sp_setapprole and try to recycle it; if you
use more than one approle in your application - including any 3rd-
party components that you might be using - that won't work well), so
you may need to disable pooling as well.