Utter madness!

P

Paul Mason

I think i've been getting my groups mixed up.

I've been trying to get my intranet system to authenticate to SQL server
(2K) using a trusted connection for some time and have had to wait until we
upgraded to Active directory for kerberos to start working (I'm not 100%
sure it's kerberos so bear with me).

Now I've hit the final brick wall which means this isn't ever gonna happen
in the current setup. It finally twigged (dropped like a tonne of lead more
like) when I read in the help :

"If your application runs on a Windows-based intranet, you might be able to
use Windows integrated security for database access. Integrated security
requires:
a.. That SQL Server be running on the same computer as IIS...... "
I can't believe that someone from MS actually wrote this. Are they
mad?...IIS and SQL server on the same machine....hackers paradise! Appart
from being plain dangerous, it's bad networking practice, bad programming
practice...it's just bad.

Does anyone know if they are actually going to write something useful...or
are we stuck with forms authentication forever!?! Not that I'm complaining.

Cheers...P
 
J

Joe Kaplan \(MVP - ADSI\)

Lots of people run SQL on other boxes. There is no reason why you can't do
this. However, certain authentication scenarios are harder in that set up.

The issue of passing Windows credentials to SQL server can get tricky if it
is on a different box on the network. If it is your expectation that you
will log on to SQL using web logged on user's credentials and you are using
Windows Integrated Authentication, then you will need to learn some stuff
about Kerberos delegation to make this work. This is discussed ad nauseum
in this group and you will find many pointers here with a Google search.

However, there are many reasons why you would not want to use the user's
credentials to connect to SQL but instead would want to use some kind of
service account. One of the primary reasons is that you'll get better
scalability if you use one set of credentials as you can use connection
pooling. Another reason is that you can avoid the whole Kerberos delegation
thing that way. To do the service account approach, you have three typical
approaches: change the process account for ASP.NET to a domain account,
impersonate a specific domain account or put your data access code in a COM+
component and configure it to use a specific domain identity via COM+. All
have good points and bad points.

Joe K.
 
P

Paul Mason

Hi Joe,

I tried using impersonation in one application and for the amount of
connections we generate (300 max) it created a lot of extra work. I'll
stick to forms authentication for now.

I do find it odd that it's so easy to identify a domain authenticated user
(through the WindowsIdentity object) and yet it's so difficult for it to
then pass this onto SQL server.

If it's going to be "tricky" to get a trusted connection to my SQL box
working without having IIS installed on the same box, then it's not worth
doing. Most people need something that's straightforward and reliable.

I will have a root around, but if it requires the level of in-depth
knowledge of an obscure technology that you're hinting at then I doubt I'll
take it any further...

Cheers...P
 
J

Joe Kaplan \(MVP - ADSI\)

It is just Windows security stuff. Whether or not it is obscure is
debatable, but it sure helps to understand this stuff. Keith Brown has a
great online book at www.pluralsight.com called Windows Security for .NET
Developers that tells you what you need to know.

You can get a trusted connection back to SQL server. Just change your
ASP.NET account (either processModel or app pool identity depending on
version of IIS) to a domain account and make sure you have impersonation
disabled. Then you are using SSPI to connect to SQL with a specific
account.

If the requirement is to use WIA and have those credentials be used to
authenticate with SQL server on a different box on the network, then
Kerberos delegation is required. This is enabled either per machine or per
user in Active Directory. Like I said, I'd avoid using this approach unless
you absolutely need to because you are using specific per user security
features in SQL Server as it hurts scalability and makes your life much more
complicated.

Joe K.
 
P

Paul Mason

Hi,

It is, isn't it. The link is
ms-help://MS.VSCC.2003/MS.MSDNQTR.2003FEB.1033/vbcon/html/vbtskAccessingSQLS
erverUsingWindowsIntegratedSecurity.htm

Alternativelly go into Visual Studio help, then Contents, Visual Studio
..net, Visual basic and C#, Creating applications, Creating web applications
and services, Security considerations...., Accessing SQL server from a web
appliaction and finally Accessing SQL server using windows integrated
security.

That's VS1.1 by the way.

Thanks for the link. I'd done everything but the last two items covering
setting up Active directory.

Cheers...P


Where did you get that?, it is hilarious! Do you have a link?

Of course you can use integrated windows authentiction when IIS/sql server
are on different computers, you must enable delegation on your webserver for
this to work properly though.

http://support.microsoft.com/default.aspx?scid=kb;en-us;810572
--Michael
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,906
Latest member
SkinfixSkintag

Latest Threads

Top