Authentication

Hello All,

i was wondering what is the main difference between the windows
authentication and mixed mode authentication??
according to security recommendations, we should enable windows
authentication, rather than mixed one, i don get the point why do we
refuse the mixed mode authentication, although it includes windows
authentication together with an extra layer of defense by the aid of an
extra authentication mechanism, sql authentication.

i hope i can find out why??
if anyone can recommend me some papers or books, i'll be thankfull but
i hope it explains from the security point of view.

Thanx for time and sorry for interruption(Eng.Rana@.gmail.com) writes:
> i was wondering what is the main difference between the windows
> authentication and mixed mode authentication??
> according to security recommendations, we should enable windows
> authentication, rather than mixed one, i don get the point why do we
> refuse the mixed mode authentication, although it includes windows
> authentication together with an extra layer of defense by the aid of an
> extra authentication mechanism, sql authentication.

No, mixed mode does not give you any extra layer of protection.

In the beginning, SQL Server only had one means of authetication: username
and password stored in the master database in SQL Server. To connect to SQL
Server, you needed to specify username and password. This is today known as
SQL authentication.

Later Microsoft added Windows authentication which permits you to log in
with your Windows credentials. This is known as "Windows authenticiation",
"Trusted connnection" or "Integrated Security".

In SQL 6.x you had three choices: Windows authenticaton only, SQL
authentication only or both. With SQL 7, Microsoft removed the alternative
SQL authentication only.

Windows Authentication is generally regarded as more secure in SQL 2000,
because SQL Server does not have any means to check password strength,
lock accounts with many failed logins etc. Also, it's fairly easy to
crack a password sent over the wire, as the "encryption" is just a mild
form of obfustication. Some of these issues has been resolved in SQL 2005,
provided that you use Widows 2003.

However, Windows authentication requires that both client and server are
in the same domain, or are in domains that trust each other. Mixed mode
is also conventient when you work in a development environment and need
to load stored procedures etc from a privileged account, but you need to
test the application as a low-priv user.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||thats a gr8 help, i really appreciate it :).

but i was wondering, what if the client and server were of different
domains, shall we be using only the SQL authentication only?

if yes, is there any other situation where we can't use windows
authentication?

thanx again for ur gr8 help :D|||(Eng.Rana@.gmail.com) writes:
> thats a gr8 help, i really appreciate it :).
> but i was wondering, what if the client and server were of different
> domains, shall we be using only the SQL authentication only?

Unless you can set up some trust between them you need to use SQL
authentication.

Here I need to add this is a question that requires good knowledge about
Windows networking, which I do not possess.

> if yes, is there any other situation where we can't use windows
> authentication?

If you don't have a domain at all, but only a Workgroup, Windows
authentication can be difficult. Username and password must match,
but this may not be enough. For instance, I run virtual machines on
my comnputer at home, and from the host machine I connect to SQL Server
on the virtual machines with Windows authentication, and and Windows
authentication also works between virtual machine. But it does not
work from virtual machines to the SQL Server instances on the host
machine.

If the client comes from a non-Windows OS it may be even more difficult
to use Windows authentication. Obviously.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> Unless you can set up some trust between them you need to use SQL
> authentication.

how can i only enforce sql authenticaion, although the only two options
available are Windows authentication only or mixed mode.

this means that there always has to be trust between domains or that
they be on the same domain.

else how can enforce SQL authentication only!!!!!

sorry for all these questions
i really appreciate ur help
Thanx for time|||(Eng.Rana@.gmail.com) writes:
> how can i only enforce sql authenticaion, although the only two options
> available are Windows authentication only or mixed mode.
> this means that there always has to be trust between domains or that
> they be on the same domain.
> else how can enforce SQL authentication only!!!!!

Not sure that I understand your question. You cannot configure SQL Server
to not permit Windows authentication at all. But if you put the SQL Server
machine in its own domain (or a workgroup), the only way to log in with
Windows authnetication is on the local machine.

Note that SQL authetnication is not dependent on domains trusting each
other.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On 14 Jun 2006 22:04:33 -0700, Eng.Rana@.gmail.com wrote:

>> Unless you can set up some trust between them you need to use SQL
>> authentication.
>how can i only enforce sql authenticaion, although the only two options
>available are Windows authentication only or mixed mode.
>this means that there always has to be trust between domains or that
>they be on the same domain.
>else how can enforce SQL authentication only!!!!!

Hi Eng.Rana,

Though you can't disable the mechanism for Windows authentication, you
can render it unfunctional by granting nobody the right to login with
Windows authentication.

Windows authentication doesn't mean that every domain user suuddenly has
the right to connect to SQL Server - you have to explicitly allow this
to individual Windows accounts or groups. In SQL Server 2000, you used
the sp_grantdbaccess stored pprocedure (or some graphical tool) for
this; in SQL Server 2005, this stored procedure is replaced by the
CREATE LOGIN xxx FROM WINDOWS command..

Note that by default, all windows accounts that are in the
builtin/administrators group have access to the DB using Windows
authentication. If you realy want to force everyone to use SQL
authentication, you'll have to remove these logins (using
sp_revokedbaccess or DROP LOGIN). I have never tried if this works,
because I'm scared that I'll forget my sa password and never be able to
regain access to the DB. <g
--
Hugo Kornelis, SQL Server MVP