|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
ado.net 2.0 and MSSQL AppRolesI 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 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 -- Show quote____________________________________ 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" <jjarrel_NOSPAM@yahoo.com> wrote in message news:ODwYdRtHGHA.1388@TK2MSFTNGP11.phx.gbl... >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 > 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 Show quote "William (Bill) Vaughn" <billvaRemoveT***@nwlink.com> wrote in message news:uMUzgtvHGHA.2460@TK2MSFTNGP10.phx.gbl... > 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" <jjarrel_NOSPAM@yahoo.com> wrote in message > news:ODwYdRtHGHA.1388@TK2MSFTNGP11.phx.gbl... >>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 >> > > 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 -- Show quote____________________________________ 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" <jjarrel_NOSPAM@yahoo.com> wrote in message news:e5mmHq2HGHA.2696@TK2MSFTNGP14.phx.gbl... > 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" <billvaRemoveT***@nwlink.com> wrote in message > news:uMUzgtvHGHA.2460@TK2MSFTNGP10.phx.gbl... >> 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" <jjarrel_NOSPAM@yahoo.com> wrote in message >> news:ODwYdRtHGHA.1388@TK2MSFTNGP11.phx.gbl... >>>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 >>> >> >> > > 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 Show quote "William (Bill) Vaughn" <billvaRemoveT***@nwlink.com> wrote in message news:unHrk94HGHA.2460@TK2MSFTNGP10.phx.gbl... > 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" <jjarrel_NOSPAM@yahoo.com> wrote in message > news:e5mmHq2HGHA.2696@TK2MSFTNGP14.phx.gbl... >> 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" <billvaRemoveT***@nwlink.com> wrote in message >> news:uMUzgtvHGHA.2460@TK2MSFTNGP10.phx.gbl... >>> 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" <jjarrel_NOSPAM@yahoo.com> wrote in message >>> news:ODwYdRtHGHA.1388@TK2MSFTNGP11.phx.gbl... >>>>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 >>>> >>> >>> >> >> > > 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 -- Show quote____________________________________ 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" <jjarrel_NOSPAM@yahoo.com> wrote in message news:%23U5Q4hHIGHA.604@TK2MSFTNGP14.phx.gbl... > 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" <billvaRemoveT***@nwlink.com> wrote in message > news:unHrk94HGHA.2460@TK2MSFTNGP10.phx.gbl... >> 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" <jjarrel_NOSPAM@yahoo.com> wrote in message >> news:e5mmHq2HGHA.2696@TK2MSFTNGP14.phx.gbl... >>> 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" <billvaRemoveT***@nwlink.com> wrote in message >>> news:uMUzgtvHGHA.2460@TK2MSFTNGP10.phx.gbl... >>>> 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" <jjarrel_NOSPAM@yahoo.com> wrote in message >>>> news:ODwYdRtHGHA.1388@TK2MSFTNGP11.phx.gbl... >>>>>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 >>>>> >>>> >>>> >>> >>> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics