ado.net 2.0 and MSSQL AppRoles

[Jeff Jarrell]

I recall reading that in the ado.net 1.1 that connection pooling should be
disabled when using app roles. I have avoided the issue so far.

Is this still the case in Ado.net 2.0? The ERP system work with that I work
with has an APP role and it takes time to verify\authenticate the user and
then get and decrypt he password necessary to activate the application role.
It would seem to be very painful to do this EVERY time we need to open a
connection. Now if we have to maintain an open connection, then we are kind
of back to the old ways.

Comments?

Thanks,
jeff

 

[William \(Bill\) Vaughn]

Question: Is the application ASP.NET-based or a Smart Client (Windows forms)
application?
If it's the latter, connection pooling brings very little to the party.
There is nothing wrong with opening a connection and leaving it open for the
life of the application if your server can handle the number of connections.
SQL Server can support hundreds of connections (or more if you're careful).

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Jeff Jarrell]

At this point it happens to be Winforms but in the future there will be
Asp.net based clients. At the data layer I am trying to code in a style
that is client agnostic. Just trying to learn and code with best practices
in mind. Maintaining an open connection is a comprise to what the ideal was
that the developers of ado.net had in mind.

For the question, lets assume it is ASP.net. Do approles kill the built in
connection pooling? If it does then the obvious next step would be to build
your own connection pool.

jeff

 

[William \(Bill\) Vaughn]

Building a generic application for both architectures means an application
that does not really address the special issues and benefits of either.
These two architectures are very different in many respects.
Just consider that anytime you use SSPI security, the credentials must be
revalidated each time the connection is opened. I don't see how role-based
security is any different in this respect than any other SSPI approach. Only
SQL Server security does not carry this burden. The other consideration is
that when building
ASP applications, unless you're using impersonation (which adds more
overhead), the connection is made by IIS with its credentials, not by the
"user".

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Jeff Jarrell]

Bill,

In my context the "role-based" security is a fair amount of overhead in
addition to the SSPI. From the incoming SSPI the application looks up to
verify if the user is "entitled", then an encrypted version of approle
password. These steps are calls to database via "public" entitled stored
procedures. Next, a com object is instantiated to decrypt the approle
password. And finally the approle can be activated.

It's painful at best. I ended up going to an approach like asp where I use
a system type account for access. Basically, I switch to that user if
necessary.

Thank you for your comments.
jeff

 

[William \(Bill\) Vaughn]

There are as many security schemes as there are scandals in Washington. One
approach that I've seen work uses an application-based credentials to gain
access to the server. Once connected the application validates the user by
other means (perhaps by determining the Windows logon) which can be done
during the first round-trip. This means the connection pool (which is only
interesting for ASP apps) can maintain the same ConnectionString and be
easily scaled since no SSPI authentication is required. Your application
might benefit from a CLR-coded validator function if it has to perform
complex decryption.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________