Passwords - case sensitive in Login using SQL Server Authentication

  • Hi,

    In SQL Server 2005, Logins using SQL Server Authentication are case sensitive (even if you remove the “Enforce password policy” option in the login prosperity).

    Is there a way to turn this feature off in a Login or on all Logins on the server?

    Example:

    Login: David

    Password: BLUE

    Is there a way that Password: BLUE and Password: blue to both work?

    Thanks,

    David

  • No. While the login name was case-insensitive if your SQL Server has a case-insensitive collation, the password is always case sensitive. This is because of the hashing algorithm used.

    K. Brian Kelley
    @kbriankelley

  • Thank you for your help!

  • K. Brian Kelley (2/15/2009)


    No. While the login name was case-insensitive if your SQL Server has a case-insensitive collation, the password is always case sensitive. This is because of the hashing algorithm used.

    I understand your reply but i wonder if you can help.

    We recently migrated from Windows SBS 2000 to Windows SBS 2008 as well as SQL Server 2000 to SQL server 2008.

    We use an in house software which, when developed, didn't take into consideration the CASE SENSITIVITY for the login (user: sa pass: aBc123) since SQL server 2000 didn't care for the sesnsitivity. The developer realizes now that it was poor software design on his part and is trying to fix it.

    In the meanwhile I was wondering if we can run an SQL Query on the SQL SERVER 2008 to tell it not to care for the sensitivity of the user 'sa'.

    Something along the lines of:

    ALTER LOGIN sa PASSWORD = 'aBc123' COLLATE SQL_Latin1_General_CP1_CI_AS

    I know this is not a proper SQL query, but can you suggest anything?

  • By default SQL Server is case insensitive with respect to the login. So unless you've chosen a collation when you installed SQL Server that is case sensitive, it doesn't matter if the app logs in as sa, SA, sA, or Sa. The password is always case sensitive. If you're application doesn't take case sensitivity into account with respect to password, does it treat it as all lowercase or uppercase? If so, make the password conform to that.

    K. Brian Kelley
    @kbriankelley

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply