Re: ado.net 2.0 and MSSQL AppRoles

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.
__________________________________

"Jeff Jarrell" <jjarrel_NOSPAM@xxxxxxxxx> wrote in message
news:%23U5Q4hHIGHA.604@xxxxxxxxxxxxxxxxxxxxxxx
> 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" <billvaRemoveThis@xxxxxxxxxx> wrote in message
> news:unHrk94HGHA.2460@xxxxxxxxxxxxxxxxxxxxxxx
>> 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@xxxxxxxxx> wrote in message
>> news:e5mmHq2HGHA.2696@xxxxxxxxxxxxxxxxxxxxxxx
>>> 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" <billvaRemoveThis@xxxxxxxxxx> wrote in message
>>> news:uMUzgtvHGHA.2460@xxxxxxxxxxxxxxxxxxxxxxx
>>>> 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@xxxxxxxxx> wrote in message
>>>> news:ODwYdRtHGHA.1388@xxxxxxxxxxxxxxxxxxxxxxx
>>>>>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
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>


.

Relevant Pages

  • Re: ado.net 2.0 and MSSQL AppRoles
    ... From the incoming SSPI the application looks up to ... a com object is instantiated to decrypt the approle ... > Just consider that anytime you use SSPI security, ... > revalidated each time the connection is opened. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Windows authentication in domain
    ... "When using SSPI for authentication for connectivity, if the connection ... why doesn't SQL Server use other credentials such as those that may ... security, since realistically we would only want the current user ... credentials passed and authenticated. ...
    (microsoft.public.sqlserver.security)
  • Re: Custom Authentication
    ... >I have been able to modify the SSPI wrapper found in the following article: ... > credentials, I had to rely on the current users interactive session. ... >> I said I must use my own custom credential cache. ... >> provide my own security authority to use the SSPI sample with, ...
    (microsoft.public.dotnet.framework.remoting)
  • RE: Custom Authentication
    ... I have been able to modify the SSPI wrapper found in the following article: ... I can prompt for a users username, password and domain, pass it to ... credentials, I had to rely on the current users interactive session. ... > provide my own security authority to use the SSPI sample with, ...
    (microsoft.public.dotnet.framework.remoting)
  • Re: Windows authentication in domain
    ... the domain user must be explicitly listed a SQL login. ... > "When using SSPI for authentication for connectivity, if the connection ... why doesn't SQL Server use other credentials such as those that may ... > security, since realistically we would only want the current user ...
    (microsoft.public.sqlserver.security)
Loading