Page 1 of 1 If you are paying attention to security in your online applications, then you are likely encouraging your users to select strong passwords that contain numbers, special characters and letters that are both upper and lower case. Without special attention to your database settings, however, SQL Server will treat upper and lower case characters as equivalent to one another, causing you to lose a significant part of the strength of your passwords.
The problem stems from the fact that, by default, SQL Server treats upper and lower case characters as if they are the same. This can be very handy when you are looking for information and you do not really care about whether it was entered in the database in UPPER or lower case characters. The title of a book, or the name of an author is not treated as distinct just because of case. So if you search for shark, SQL Server would return records that read:
Shark
shArk
ShArK
In most cases that is a good thing, but not in a field where you want upper and lower cases to be treated as different characters such as in a password field. In a password, if a person enters 1N3rt46, that input should NOT match:
1n3rt46
1N3Rt46
or
1N3RT46
Treating cases this way significantly expands the number of distinct characters that are available, making a brute force attack much less likely to succeed.
In order to get SQL Server to treat your data this way, you need to set the collation of the field that you use to store passwords. In general, collation refers to a set of rules that determine how data is sorted and compared. Rules are employed that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width. There are a number of different collation options, but just one simple setting can be used to provide case-sensitivity in a password field.
The default SQL Server setting for any field is dictionary order and case-insensitive. You can change this very simply. When defining your database table, in the field that you will use to store passwords, under the Table Designer section find the Collation setting. Click the ... button and a pop-up box will appear. In that box you will be provided a number of collation settings. Choose SQL_Latin1_General_CP1_CS_AS, which will enforce case-sensitivity for that field.
Visit the Microsoft site for more detail about the various collation options available to you.
Page 1 of 1 1
Keywords
