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
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
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
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?
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.
> > > 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
> > > that NT auth is more demanding for the SQL server !!???
> > More demanding for what? It is more demanding for possible attackers.
> > 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
> Thank you for your reply.
> Ill try to clarify what i mean. Demanding as in eating up more
> eg. ram and processor.
> Actually I dont know, but the company who is developing the application
> us that there was
> a performance issue, when changing fra SQL to NT authentication
> Henrik Nefling