Post reply

migrated to sql 2008 R2 from an old sql 2000 database using compatibility mode, some passwords don't work anymore

  • February 15, 2017 at 8:49 am

    #321673

    so here's the situation. 

    We use an old proprietary piece of software made in vb.net from a long time ago.  We don't have the source code.  We need this service for a few more months but we migrated to a new hosting provider who won't support sql 2000, so we put it on sql 2008 R2 with compatibility mode on for sql 2000.  Everything seems fine except, the login from the app (using odbc) will fail if we put regular LOWER case characters (a,b,c,d,e,f...etc) in there.  If we use numbers only and/or some non-ascii characters (I know - and ! and @ work fine) or even all upper case it works.  I'm trying to figure out what's going on here, it just uses the SQL server login credential for access, any Ideas?  I know the coalition is the same, I'm assuming it's something in the way sql 2008 is interpreting the connection.  The ODBC connection using the same credentials itself works fine.  Is there any way for me to capture the vb.net login when it passes the password?

    I'm not a developer, I was a SQL dba in the past, but not for a few years, so my skills are extremely rusty.  Please bear with me as I remember all my long forgotten techniques.  I've tried all letters and they all fail.

    any ideas would be great.  If you need me to put any info in here, just let me know what.

    thanks,

    -Steve Hare

  • February 15, 2017 at 9:06 am

    #1928643

    stephen.hare - Wednesday, February 15, 2017 8:49 AM

    Everything seems fine except, the login from the app (using odbc) will fail if we put regular LOWER case characters (a,b,c,d,e,f...etc) in there.

    Steve

    In where?  I don't really understand. Is the login and/or password hard-coded in the application?  Are you saying you don't have access to either or both of them?  Is the login set up on the new server?

    If I remember correctly, SQL login passwords in SQL Server 2000 were only case-sensitive if the server collation was case-sensitive.

    John

  • February 15, 2017 at 9:17 am

    #1928651

    HI John, 

    thanks for replying.  the Login isn't hardcoded, when you open the .exe it first prompts for a user and password, then passes those on (assuming through the ODBC) and authenticates against the sql server login.

    I know the sql server login itself is fine, as I've tested outside the app, one quirk, when I cut and paste a password (even lower case letters) it works fine, it's only when you try to type the password and it contains lower case letters.  Very odd.  And this exact same app. works fine when authenticating against a copy of the db living on sql 2000.

  • February 15, 2017 at 9:22 am

    #1928656

    At a guess, I'd say the application is converting the password to upper case, which wouldn't have mattered on SQL Server 2000 (depending on the server collation) but definitely does matter on all later versions.  Looks like you're going to need to choose a password with numeric, special and upper case only.

    John

  • February 15, 2017 at 9:46 am

    #1928666

    I can confirm what John says about the password. On SQL 2000, passwords were case-insensitive, if the server collation was case-insensitive. This was changed in SQL 2005.

    If the application uppercasess all letters, that sounds a little crazy, but I have no alternative explantion to offer.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

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

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