Ben McIntyre (ben_spam@mailcity.com) writes:
> My query is that now, depending on the article I read, I am told to
> use conflicting values for LoginMode.
> Some articles say 0=NT, 1=Mixed, other say 1=NT, 2=Mixed.
> A list of links to articles is at the end of this post.

I checked some SQL Servers I had around, and I found:
2 - SQL2000 running mixed mode.
1 - SQL2000 running Windows authentication only.
0 - SQL 6.5 running SQL authenication only.

I also tested to manually change the setting to 0 for SQL2000 and that
gave me mixed mode.

None of these servers are MSDE, but I see no reason why MSDE should be
different. I note that the article you gave a link to, is not a KB
article, and somewhat old. I'll pass it to my contacts at MS to have
it pulled or updated.

> As an additional question, to save me some time, does anyone know if
> SQL(password) Mode Authentication requires specific ports open in a
> firewall, or does it just ride on the back of NetBIOS ?

SQL authentication uses the same port as the rest of the communication.
That is, port 1433 in the default case, but which can be changed per
server.

> MS are making it harder by the day it seems to use password-based
> authentication.

Yes, and there is a good reason for it. SQL authentication is not
very safe, as there is no lockout policy to protect against brute-
force attacks.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Erland Sommarskog <sommar@algonet.se> wrote in message news:<Xns93E7DE88CD14FYazorman@127.0.0.1>...
> Ben McIntyre (ben_spam@mailcity.com) writes:
> > My query is that now, depending on the article I read, I am told to
> > use conflicting values for LoginMode.
> > Some articles say 0=NT, 1=Mixed, other say 1=NT, 2=Mixed.
> > A list of links to articles is at the end of this post.
>
> I checked some SQL Servers I had around, and I found:
> 2 - SQL2000 running mixed mode.
> 1 - SQL2000 running Windows authentication only.
> 0 - SQL 6.5 running SQL authenication only.
>
> I also tested to manually change the setting to 0 for SQL2000 and that
> gave me mixed mode.
>
> None of these servers are MSDE, but I see no reason why MSDE should be
> different. I note that the article you gave a link to, is not a KB
> article, and somewhat old. I'll pass it to my contacts at MS to have
> it pulled or updated.
>

Thanks on behalf of all of us, that would be great.

Note that I have checked three separate installs of MSDE 2000/8 and
all have LoginMode=0 as a registry setting.
My concern was that this is essentially an undocumented value
(confusing when combined with above article).
This may be specific to MSDE 2000 (probably an MSI issue). I am
installing the latest SP3'd MSDE.

I get the same behaviour, 0 or 2=mixed, 1=windows only

> > As an additional question, to save me some time, does anyone know if
> > SQL(password) Mode Authentication requires specific ports open in a
> > firewall, or does it just ride on the back of NetBIOS ?
>
> SQL authentication uses the same port as the rest of the communication.
> That is, port 1433 in the default case, but which can be changed per
> server.
>
> > MS are making it harder by the day it seems to use password-based
> > authentication.
>
> Yes, and there is a good reason for it. SQL authentication is not
> very safe, as there is no lockout policy to protect against brute-
> force attacks.

True, but we are forced to offer password based login for as long as
Win98 and ME clients remain around for.
And as mentioned, MSDE forces you to log in as Administrator under NT
security, surely a huge security risk (so much so that I refuse to use
it).

It would seem a much better approach to me to make the password based
system more secure by offering some sort of key based encrypted login
and additional features such as optional time-based lockout periods.
The motivation for encouraging NT security seems to be more political
than a design decision - to extend the windows authentication
envelope. (And I'm _not_ a *nix conspiracy theorist, I'd just like to
add).

MS are heading towards the 'hard shell, soft centre' approach, where
it may be very hard to get in, but once you're in you have access to
practically every application and all data on the machine.
The database file-level security in the server is a good example of
this - if you have the NT admin login, you can detach, copy and have
full ownership over any database you like.

I believe it is a big mistake to force us down this road, especially
when it would be easy to offer optional strong password-based
protection for a database file, or for password based login, and then
leave it up the adminstrator to decide whether to implement a
monolithic or scaled security model.

I know everyone has their opinion on this, but as the old equation
goes, 'security = 1 / convenience'.

Thanks again,

Ben McIntyre
Horticulture Software Solutions

Ben McIntyre (ben_spam@mailcity.com) writes:
> True, but we are forced to offer password based login for as long as
> Win98 and ME clients remain around for.

I guess the answer to that statement is that MS prefers to see these
legacy clients to be phased out.

> And as mentioned, MSDE forces you to log in as Administrator under NT
> security, surely a huge security risk (so much so that I refuse to use
> it).

All and all, that account *is* safer than the "sa" account on SQL Server.

> It would seem a much better approach to me to make the password based
> system more secure by offering some sort of key based encrypted login
> and additional features such as optional time-based lockout periods.
> The motivation for encouraging NT security seems to be more political
> than a design decision - to extend the windows authentication
> envelope. (And I'm _not_ a *nix conspiracy theorist, I'd just like to
> add).

Yes, it could be worthwhile. I tell you what, there is an address
sqlwish@microsoft.com which is good for this type of suggestions.

Note however, that with SQL2000 Windows authentication is safer
and is going to continue to be. If your wish is convincing enough,
next version of SQL Server may offer something different.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp