Re: NT authentication vs. SQL auth
From: MB (dhha_dba@yahoo.com)
Date: 02/28/03
- Next message: dkriege: "ODBC Driver SQL Server to AS/400"
- Previous message: Steve Thompson: "Re: Stop SQL from listening on external IP"
- In reply to: BP Margolin: "Re: NT authentication vs. SQL auth"
- Next in thread: BP Margolin: "Re: NT authentication vs. SQL auth"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
From: "MB" <dhha_dba@yahoo.com> Date: Fri, 28 Feb 2003 08:38:53 -0800
During a recent data migration from a SQL 6.5 db to a SQL
2000 db (using DTS), we noticed that the time to complete
the package went from ~3 hours using NT authentication to
about 1 hour and 10 minutes using SQL authentication.
Since it was a 1 time migration, we used SQL
authentication.
There does appear to be a severe performance hit using NT
authentication in some instances.
Cheers!
>-----Original Message-----
>Henrik,
>
>I haven't done formal testing, but it makes logical
sense, at least to me,
>that Windows Authentication is more "demanding" than is
SQL Server
>Authentication.
>
>I would think that SQL Server, rather than just looking
at its own (probably
>cached) internal tables, has to pass to Windows the
connection information,
>at which point Windows uses its own "security database"
to retrieve
>information and pass it back to SQL Server. So, I would
think that it makes
>sense that Windows Authentication is more demanding, just
as if a security
>guard at the front desk would have to phone someone
everytime that a visitor
>appears, rather than just looking at a security badge
carried by the
>visitor.
>
>But I do think that you might be missing Dejan's point.
Yes, Windows
>Authentication is more "demanding", but (a) how much more
demanding, and (b)
>do the benefits outweigh the "loss in performance".
Windows Authentication
>can be more secure than SQL Server Authentication. If you
aren't worried
>about security at all, then just drop all your logins
except one, and let
>everybody log in via the single one, and enable the guest
account for the
>databases. Since you have logins, I have to assume that
you are concerned
>about security ... and Windows Authentication can be
significantly more
>secure than is SQL Server Authentication ... and I don't
believe that the
>extra performance hit of Windows Authentication is
significant enough to not
>seriously consider using it.
>
>The performance of an application is almost never
associated with things
>like Windows Authentication vs. SQL Server
Authentication. Almost always a
>poorly performing application can be traced to a poor
database design or
>poor coding (e.g. cursors rather than set-oriented SQL)
or a non-optimal
>indexing strategy.
>
>Please don't take this the wrong way, but concentrating
on the performance
>of Windows Authentication vs. SQL Server Authentication
is, to my mind, like
>worrying whether the Titanic is going to strike the
iceberg on the port side
>or the starboard side ... heck, it just doesn't
matter ... the thing is to
>get the ship out of the way of the iceberg! Concentrate
on the important
>performance issues, not the ones that will not make a
measurable difference,
>or at best a measurable difference of way under 1%.
>
>And, again to Dejan's point ... what the heck does it
matter if you do take
>a performance hit, provided that your application is
better shielded from
>hackers. If a hacker gets in because you used SQL Server
Authentication
>rather than Windows Authentication, and your data is
destroyed or
>compromised, do you really care about the performance of
Windows
>Authentication vs. SQL Server Authentication?
>
>-------------------------------------------
>BP Margolin
>Please reply only to the newsgroups.
>When posting, inclusion of SQL (CREATE TABLE ...,
INSERT ..., etc.) which
>can be cut and pasted into Query Analyzer is appreciated.
>
>"Henrik" <mail@nefling.dk> wrote in message
>news:KCQ5a.86$O24.77@news.get2net.dk...
>>
>> "Dejan Sarka"
<dejan_please_reply_to_newsgroups.sarka@reproms.si> skrev i
>en
>> meddelelse news:uIwwrFq2CHA.2176@TK2MSFTNGP09.phx.gbl...
>> > > We are currently developing a new application,
running SQL2000sp2 on a
>> > > Win2000 sp3 Cluster. At the moment
>> > > everyone is connected to the database though user
SA (in ODBC).
>> >
>> > This is really bad practice, specially considering
security. Everyone is
>> > administrator of your SQL Server.
>> >
>> > > We have talked about changing authentication to NT -
but someone told
>me
>> > > that NT auth is more demanding for the SQL
server !!???
>> >
>> > More demanding for what? It is more demanding for
possible attackers.
>You
>> > should go for it.
>> >
>> > --
>> > Dejan Sarka, SQL Server MVP
>> > FAQ from Neil & others at: http://www.sqlserverfaq.com
>> > Please reply only to the newsgroups.
>> > PASS - the definitive, global community
>> > for SQL Server professionals - http://www.sqlpass.org
>> >
>> >
>>
>> Hi
>>
>> Thank you for your reply.
>> Ill try to clarify what i mean. Demanding as in eating
up more
>ressources -
>> eg. ram and processor.
>> Actually I dont know, but the company who is developing
the application
>told
>> us that there was
>> a performance issue, when changing fra SQL to NT
authentication
>>
>> Henrik Nefling
>>
>>
>>
>>
>>
>>
>
>
>.
>
- Next message: dkriege: "ODBC Driver SQL Server to AS/400"
- Previous message: Steve Thompson: "Re: Stop SQL from listening on external IP"
- In reply to: BP Margolin: "Re: NT authentication vs. SQL auth"
- Next in thread: BP Margolin: "Re: NT authentication vs. SQL auth"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
