Discussion:
sql server authentication with named domain account
Mark Aurit
2008-06-13 14:01:39 UTC
Permalink
(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=false.
Does want I want to do sound possible?
Thanks, Mark

===================================
This list is hosted by DevelopMentor® http://www.develop.com

View archives and manage your subscription(s) at http://discuss.develop.com
Richard
2008-06-21 10:39:34 UTC
Permalink
Yes it's possible.

My first recommendation is not to bother. Unless you have configured
impersonation in the web config file - hint DON'T configure impersonation -
then the default settings will give you the connection pooling you want.

The default is for the web site to run under the account ASPNET (IIS 5) or
Network Service (IIS 6). When you use Integrated Security, as far as the
database is concerned ALL database activity happens as ASPNET/Network
Service. You create an account in SQL Server with the appropriate name,
give it access to the database you are using and away you go. Connection
pooling just works - so long as you use an identical connection string in
every call.

That's the recommended approach and it still works even if you have an web
site where users have to login.



If you really want to use a named account then, exactly as you said, you use
a connection string that specifies the account and password. This is
generally considered a 'bad thing' because it requires the password in the
connection string.


Cheers,

- Richard

***@dynamisys.co.uk
www.dynamisys.co.uk
Land line +44 1793 731225
Mobile +44 7732 971786
-----Original Message-----
From: Discussion of building .NET applications targeted for the Web
[mailto:DOTNET-***@DISCUSS.DEVELOP.COM] On Behalf Of Mark Aurit
Sent: 13 June 2008 15:02
To: DOTNET-***@DISCUSS.DEVELOP.COM
Subject: [DOTNET-WEB] sql server authentication with named domain account

(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=false.
Does want I want to do sound possible?
Thanks, Mark

===================================
This list is hosted by DevelopMentorR http://www.develop.com

View archives and manage your subscription(s) at http://discuss.develop.com
No virus found in this incoming message.
Checked by AVG.
Version: 8.0.100 / Virus Database: 270.4.1/1511 - Release Date: 6/20/2008
11:52 AM

===================================
This list is hosted by DevelopMentor� http://www.develop.com

View archives and manage your subscription(s) at http://discuss.develop.com
Mark Aurit
2008-06-21 13:09:34 UTC
Permalink
Which is what Id hoped would be the case. But the below MSDN article, under
"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
Post by Mark Aurit
(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=false.
Does want I want to do sound possible?
Thanks, Mark
===================================
This list is hosted by DevelopMentor(R) http://www.develop.com
View archives and manage your subscription(s) at
http://discuss.develop.com
--

=======================
***@gmail.com

===================================
This list is hosted by DevelopMentor® http://www.develop.com

View archives and manage your subscription(s) at http://discuss.develop.com
Shawn Wildermuth
2008-06-22 02:21:27 UTC
Permalink
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 was
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:DOTNET-***@DISCUSS.DEVELOP.COM] On Behalf Of Mark Aurit
Sent: Saturday, June 21, 2008 9:10 AM
To: DOTNET-***@DISCUSS.DEVELOP.COM
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, under
"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
Post by Mark Aurit
(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=false.
Does want I want to do sound possible?
Thanks, Mark
===================================
This list is hosted by DevelopMentor(R) http://www.develop.com
View archives and manage your subscription(s) at
http://discuss.develop.com
--

=======================
***@gmail.com

===================================
This list is hosted by DevelopMentorR http://www.develop.com

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

===================================
This list is hosted by DevelopMentor� http://www.develop.com

View archives and manage your subscription(s) at http://discuss.develop.com
Mark Aurit
2008-06-22 19:45:14 UTC
Permalink
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.
Post by Shawn Wildermuth
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 was
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
Sent: Saturday, June 21, 2008 9:10 AM
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, under
"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
Post by Mark Aurit
(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=false.
Does want I want to do sound possible?
Thanks, Mark
===================================
This list is hosted by DevelopMentor(R) http://www.develop.com
View archives and manage your subscription(s) at
http://discuss.develop.com
--
=======================
===================================
This list is hosted by DevelopMentorR http://www.develop.com
View archives and manage your subscription(s) at http://discuss.develop.com
===================================
This list is hosted by DevelopMentor® http://www.develop.com
View archives and manage your subscription(s) at http://discuss.develop.com
===================================
This list is hosted by DevelopMentor® http://www.develop.com

View archives and manage your subscription(s) at http://discuss.develop.com
Ken Schaefer
2008-06-29 09:01:32 UTC
Permalink
Hi,

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

The benefit of integrated security (at least prior to SQL Server 2005) is that you got to take advantage of Windows authN infrastructure (account lockout, 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. You authenticate users at your application level, then use a single Windows security principal to connect to the DB. This could be the worker process identity (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:DOTNET-***@DISCUSS.DEVELOP.COM] On Behalf Of Mark Aurit
Sent: Monday, 23 June 2008 5:45 AM
To: DOTNET-***@DISCUSS.DEVELOP.COM
Subject: Re: [DOTNET-WEB] sql server authentication with named domain account

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.
Post by Shawn Wildermuth
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 was
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
Sent: Saturday, June 21, 2008 9:10 AM
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, under
"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
Post by Mark Aurit
(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=false.
Does want I want to do sound possible?
Thanks, Mark
===================================
This list is hosted by DevelopMentor(R) http://www.develop.com
View archives and manage your subscription(s) at
http://discuss.develop.com
--
=======================
===================================
This list is hosted by DevelopMentorR http://www.develop.com
View archives and manage your subscription(s) at http://discuss.develop.com
===================================
This list is hosted by DevelopMentor(r) http://www.develop.com
View archives and manage your subscription(s) at http://discuss.develop.com
===================================
This list is hosted by DevelopMentor(r) http://www.develop.com

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

===================================
This list is hosted by DevelopMentor� http://www.develop.com

View archives and manage your subscription(s) at http://discuss.develop.com
Mark Aurit
2008-07-02 15:53:15 UTC
Permalink
I realize this is a little off-topic, unfortunately there isnt an ado news group. If someone can reply Im glad to take it offline.

My question is whether anyone has any experience (or knowledge) with an asp.net web app that interfaces with Oracle using windows integrated security. I believe I read somewheres that its possible, but it means running the accounts under a higher-level of privileges than a normal account does, something like that.

I have a security guy at work intrigued with sql server and integrated security, and I need to be able to tell him if the same can be done with Oracle and what the ramifications were.

Thanks, Mark

===================================
This list is hosted by DevelopMentor® http://www.develop.com

View archives and manage your subscription(s) at http://discuss.develop.com
Richard
2008-06-22 08:55:19 UTC
Permalink
Interesting. I had in mind using the ASP .Net membership system. Are you
intending to use Windows Authentication?

This how-to http://msdn.microsoft.com/en-us/library/ms998358.aspx say's:

"Windows authentication without impersonation. This is the default setting.
ASP.NET performs operations and accesses resources by using your
application's process identity, which by default is the Network Service
account on Windows Server 2003."

Which suggests to me that Integrated Security would see the Network Service
account and connection pooling would be just fine.

I think you need an experiment.

Cheers,

- Richard

***@dynamisys.co.uk
www.dynamisys.co.uk
Land line +44 1793 731225
Mobile +44 7732 971786

-----Original Message-----
From: Discussion of building .NET applications targeted for the Web
[mailto:DOTNET-***@DISCUSS.DEVELOP.COM] On Behalf Of Mark Aurit
Sent: 21 June 2008 14:10
To: DOTNET-***@DISCUSS.DEVELOP.COM
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, under
"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
Post by Mark Aurit
(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=false.
Does want I want to do sound possible?
Thanks, Mark
===================================
This list is hosted by DevelopMentor(R) http://www.develop.com
View archives and manage your subscription(s) at
http://discuss.develop.com
--

=======================
***@gmail.com

===================================
This list is hosted by DevelopMentorR http://www.develop.com

View archives and manage your subscription(s) at http://discuss.develop.com
No virus found in this incoming message.
Checked by AVG.
Version: 8.0.100 / Virus Database: 270.4.1/1512 - Release Date: 6/21/2008
9:27 AM

===================================
This list is hosted by DevelopMentor� http://www.develop.com

View archives and manage your subscription(s) at http://discuss.develop.com
Per Bolmstedt
2008-07-02 16:34:35 UTC
Permalink
Post by Mark Aurit
I realize this is a little off-topic, unfortunately there isnt an ado
news group.
I think microsoft.public.dotnet.framework.adonet would disagree:

http://groups.google.com/group/microsoft.public.dotnet.framework.adonet/topics

===================================
This list is hosted by DevelopMentor® http://www.develop.com

View archives and manage your subscription(s) at http://discuss.develop.com
Continue reading on narkive:
Loading...