Re: sql server authentication with named domain account

Hi,

Microsoft has a great ebook available on writing secure .NET applications. =
This covers all your various options (sql auth, integrated auth where crede=
ntials flow to SQL Server, trusted subsystem model etc)

The benefit of integrated security (at least prior to SQL Server 2005) is t=
hat you got to take advantage of Windows authN infrastructure (account lock=
out, password complexity policies, lots of available auditing tools etc).

So, if you want to maximise:
a) connection pooling
b) still use the benefits of integrated auth connecting to SQL Server
then you probably want to implement some kind of trusted subsystem model. Y=
ou authenticate users at your application level, then use a single Windows =
security principal to connect to the DB. This could be the worker process i=
dentity (if your w3wp.exe is connecting to SQL Server) or some identity for=
 your middle tier application.

Cheers
Ken

--
M.BT (UNSW), B.Com (UNSW)
MCITP x3, MCTS x6
MCSE+Security (2003), MCBDA (2000)
Microsoft MVP - Windows Server (IIS)


-----Original Message-----
From: Discussion of building .NET applications targeted for the Web [mailto=
:[email protected]] On Behalf Of Mark Aurit
Sent: Monday, 23 June 2008 5:45 AM
To: [email protected]
Subject: Re: [DOTNET-WEB] sql server authentication with named domain accou=
nt

Unless the "dirty little secret" of integrated security is that behind
the scenes a seperate connection string is
being created per user.  Its a whole lot easier, when creating 2 of what
appear to be different authentication
mechanisms, to merely abstract one up a level and use it to call the second=
.

Shawn Wildermuth wrote:
> Pooling is supposed to be per Connection String (which would suppose that
> Integrated security would not cause fragmentation).  If you want to test
> this, make sure you don't do it under the debugger as under the debugger
> pooling is disabled.  I'd use Profiler and a test app to test it.  I did
> this years ago when I wrote my book and it worked as expected, but that w=
as
> under 1.0/1.1 not 2.0 or later.
>
> Thanks,
>
> Shawn Wildermuth
> http://adoguy.com
> http://wildermuthconsulting.com
> http://www.silverlight-tour.com
> Microsoft MVP (C#), MCSD.NET, Author and Speaker
>
> The Silverlight Tour is coming to a city near you!
>
>
> -----Original Message-----
> From: Discussion of building .NET applications targeted for the Web
> [mailto:[email protected]] On Behalf Of Mark Aurit
> Sent: Saturday, June 21, 2008 9:10 AM
> To: [email protected]
> Subject: Re: [DOTNET-WEB] sql server authentication with named domain
> account
>
> Which is what Id hoped would be the case.  But the below MSDN article, un=
der
> "Pool Fragmentation Due to Integrated Security", would seem to say the
> opposite; that the pool is at the individual user level and therefore not
> conducive to scalibility.  In other words, the ado.net version of running
> under
> the user identity, which as you say its not a good thing.
> http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx
>
> On Fri, Jun 13, 2008 at 7:01 AM, Mark Aurit <[email protected]> wrote:
>
>
>> (I posted this yesterday but it didnt come back.  Since its of important
>> to me right now Im going to post it
>> again, sorry if you are getting it twice)
>>
>> Im creating an asp.net/sql server app; Id prefer to use a sql server
>> account to maximize connection pooling, our security guy wants
>> integrated security.  We've reached an agreement where I can use a
>> single account as long as its a domain account, but Ive been unable to
>> figure out how to do so (if its even possible). Im thinking I either
>> need to use integrated security but force sql server to use a single
>> account, or use a regular connection string, naming the domain account
>> and password with Integrated Security=3Dfalse.
>> Does want I want to do sound possible?
>> Thanks, Mark
>>
>> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>> This list is hosted by DevelopMentor(R)  http://www.develop.com
>>
>> View archives and manage your subscription(s) at
>> http://discuss.develop.com
>>
>>
>
>
>
> --
>
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> [email protected]
>
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> This list is hosted by DevelopMentorR  http://www.develop.com
>
> View archives and manage your subscription(s) at http://discuss.develop.c=
om
>
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> This list is hosted by DevelopMentor(r)  http://www.develop.com
>
> View archives and manage your subscription(s) at http://discuss.develop.c=
om
>
>

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
This list is hosted by DevelopMentor(r)  http://www.develop.com

View archives and manage your subscription(s) at http://discuss.develop.com

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
This list is hosted by DevelopMentor=AE  http://www.develop.com

View archives and manage your subscription(s) at http://discuss.develop.com