NT authentication vs. SQL auth

NT authentication vs. SQL auth

Post by Henri » Mon, 24 Feb 2003 03:00:16



Hi

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). It makes it
pretty difficult identifying the users in Enterprise manager.
We have talked about changing authentication to NT - but someone told me
that NT auth is more demanding for the SQL server !!???

Specs. 200 simultanious users, db 15gb
Hardware Cluster with 4x Xeon 700 mhz and 4gb ram and fibre disk system.

Does anyone have experience wiht this issue ???

Thank you in advance

Henrik Nefling

 
 
 

NT authentication vs. SQL auth

Post by Dejan Sark » Mon, 24 Feb 2003 03:13:23


Quote:> 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.

Quote:> 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

 
 
 

NT authentication vs. SQL auth

Post by Henri » Mon, 24 Feb 2003 04:57:30




Quote:> > 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

 
 
 

NT authentication vs. SQL auth

Post by BP Margoli » Mon, 24 Feb 2003 09:18:07


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.



en

> > > 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

 
 
 

NT authentication vs. SQL auth

Post by Montu Mistr » Mon, 24 Feb 2003 17:28:40


What do you mean by more demanding?

NT Authentication is in fact more secured and MS is recommending NT
Authentication over Mixed Mode

Hope this would help


Quote:> Hi

> 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). It makes
it
> pretty difficult identifying the users in Enterprise manager.
> We have talked about changing authentication to NT - but someone told me
> that NT auth is more demanding for the SQL server !!???

> Specs. 200 simultanious users, db 15gb
> Hardware Cluster with 4x Xeon 700 mhz and 4gb ram and fibre disk system.

> Does anyone have experience wiht this issue ???

> Thank you in advance

> Henrik Nefling

 
 
 

NT authentication vs. SQL auth

Post by MB » Sun, 02 Mar 2003 01:38:53


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!

Quote:>-----Original Message-----
>Henrik,

>I haven't done formal testing, but it makes logical

sense, at least to me,
Quote:>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
Quote:>cached) internal tables, has to pass to Windows the

connection information,
Quote:>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
Quote:>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
Quote:>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
Quote:>seriously consider using it.

>The performance of an application is almost never

associated with things
Quote:>like Windows Authentication vs. SQL Server

Authentication. Almost always a
Quote:>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
Quote:>worrying whether the Titanic is going to strike the

iceberg on the port side
Quote:>or the starboard side ... heck, it just doesn't

matter ... the thing is to
Quote:>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,
Quote:>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
Quote:>a performance hit, provided that your application is

better shielded from
Quote:>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.



>> "Dejan Sarka"


>en

>> > > We are currently developing a new application,

running SQL2000sp2 on a
Quote:>> > > 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
Quote:>> > 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

>.

 
 
 

1. NT Authentication -vs- SQL Authentication

Hi All!

I'm having a difficult time connecting to SQL 7.0 through my ODBC data
source.  I am on a domain at MSU-Billings.  I attempt to create a file DSN.
I have no problem finding the SQL server.  The problem comes when I am asked
:
How should SQL server verify the authenticity of the login ID?

With Windows NT authentication using the network login ID.
With SQL Server authentication using a login ID and password entered by the
user.

If I use the NT authentication the connection works perfectly, but if I
attempt to use the SQL authentication the connection fails every time.

Does anyone have any ideas?

Please Help Thanks,

Jacob Rome

2. Server side Processing question

3. NT authentication logins vs. SQL server authentication

4. OPINION WANTED: 6.4/05 on Alpha OpenVMS

5. NT auth works, SQL Server auth doesn't

6. How should I insert a file to Database?

7. Accessing SQL 7.0 through ASP using SQL authentication rather than NT authentication

8. SP's and SQLERRD ???

9. SQL Server Authentication vs. Windows Authentication

10. Login under SQL Authentication reverts to NT Authentication

11. NT Authentication instead of SQL Authentication

12. Switching from SQL Authentication to NT Authentication

13. SQL Authentication and NT Authentication