RE: New Paper: Microsoft SQL Server Passwords

From: Pauli Porkka (pauli.porkka@prettybit.fi)
Date: 07/10/02 

From: "Pauli Porkka" <pauli.porkka@prettybit.fi>
To: "Toni Lassila" <toni.lassila@mc-europe.com>
Date: Wed, 10 Jul 2002 12:00:45 +0300

Unfortunately changing the collation to case sensitive means that all the
stored procedures and direct sql queries need to be case sensitive as well.
Meaning that for example creating a table "Customer" and referencing to it
with "customer" in sql query will produce an error. So when changing to case
sensitive collation make sure that all the procedures and sql queries
(including any sql scripts which create a database and its tables and so
forth.) are checked on non-production database before proceeding.

Pauli Porkka/PrettyBit Software Oy
pauli.porkka@prettybit.fi
www.prettybit.fi

-----Original Message-----
From: Toni Lassila [mailto:toni.lassila@mc-europe.com]
Sent: 9. heinäkuuta 2002 8:55
To: NGSSoftware Insight Security Research
Cc: bugtraq@securityfocus.com
Subject: RE: New Paper: Microsoft SQL Server Passwords

> -----Original Message-----
> From: NGSSoftware Insight Security Research
> [mailto:nisr@nextgenss.com]
> Sent: Monday, July 08, 2002 17:33
> To: bugtraq@securityfocus.com
> Subject: New Paper: Microsoft SQL Server Passwords
>
>
> Hi all, I've written a paper on how users' passwords, or
> rather their hashes, are stored in Microsoft's SQL Server.
> The paper discusses the manner in which they are hashed and
> how they can be more easily brute forced as two hashes
> are stored: a case sensitive password hash and an upper case
> password hash are produced. Needless to say, when auditing
> password strength, it is far easier to go after the UPPER cased
> version.

An added weakness that has not been widely noted:

If you select a case-insensitive collation for your SQL Server
installation, the user accounts and passwords will be case
insensitive as well. This means there is a good chance any
given SQL Server will have very weak passwords.

You can verify if you are operating with case-insensitive
passwords by running this query:

Select SERVERPROPERTY(N'Collation')

If the name of the collation setting contains 'CI' instead
of 'CS', all your SQL login passwords are case-insensitive.
To remedy this is not a simple task, though. I quote from BOL:

"After a collation has been assigned to any object other
than a column or database, you cannot change the collation
except by dropping and re-creating the object. This can be
a complex operation. To change the default collation for an
instance of Microsoft® SQL Server(tm) 2000 you must:

Make sure you have all of the information or scripts needed
to re-create your user databases and all of the objects in them.

Export all of your data using a tool such as bulk copy.

Drop all of the user databases.

Rebuild the master database specifying the new collation.

Create all of the databases and all of the objects in them.

Import all of your data."

> this. (With a Windows account people have access to other
> operating system services as well as SQL Server, but with just
> an SQL login they should only be able to access the SQL
> Services. The latter is the 'more safe' option in the author's
> opinion)

With this I simply disagree. 

--
Toni Lassila        toni.lassila@mc-europe.com
Operations Engineer           +358 9 5655 1882

Relevant Pages

  • Re: Database collation error after moving to a remote sql server
    ... I think I'm going to try plan B: Install a second instance of SQL 2000 onto ... the remote server and set its default collation to the same as my SMS ... database, then move my db to the new instance. ... The char, varchar, text, nchar, nvarchar, or ntext system data types, ...
    (microsoft.public.sms.setup)
  • Re: Case Insensitive lookup in DB
    ... Is your SQL Server installed with a case sensitive collation? ... And while you might be able to reset the collation of a database by using ...
    (microsoft.public.inetserver.asp.general)
  • RE: Install SQL Server 2000 with different COLLATION
    ... When you upgrade an existing SQL 7.0 instance, ... always inherits the 7.0 instance's collation. ... If you want to install SQL ... Microsoft SQL Server Support ...
    (microsoft.public.sqlserver.server)
  • Re: To unicode or not?
    ... Then again, since SQL Server stores Unicode data in the UCS-2 encoding, ... use them with varchar, you simply work with a subset of the characers, ... an SQL collation is just a Windows collation ...
    (comp.databases.ms-sqlserver)
  • Re: Passwords case sensitivity
    ... To change the instance default collation after installation, ... I think the Earlier I used all the SQL ... >> SQL Server MVP ... >>> server's login passwords are case sensitive. ...
    (microsoft.public.sqlserver.security)
Loading