none
Login failed for user 'SA' - Need ideas why RRS feed

  • Question

  • Hi,
    I've got a click once application that connects to a sql server database using Sql Authentication. I've released an update that is supposed to change the SA password when the application starts up, but it fails 9 out of 10 times with the SqlException Login failed for user 'SA'.

    Here are the steps I'm taking to connect.
        1. Get the application's connection string
        2. Use a regular expression to swap out the normal user with "SA" so that I end up with uid=SA in the connection string.
        3. Use a regular expression to swap out the normal password with the SA password so that I end up with pwd=password.
        4. Create a connection and a command object with the command text = "ALTER LOGIN [sa] WITH PASSWORD=N'newP@ssword' old_password='password'".
       5. Open the connection and execute the command.

    You would think I'm just using the wrong password but we're able to log in using Management Studio, SA, and the same password so it has to be correct. One theory was that the user name might be case sensitive in the connection string but I don't think it's that because it works perfectly in my development environment. What else could be wrong?

    Tuesday, September 1, 2009 4:01 PM

Answers

  • I've reproduced it. You're right, the default server installation isn't case sensitive (thanks for checking), but if you change the collation to be case sensitive, then the login username becomes case sensitive. Now I just have to determine if this is the case on the client databases.
    • Marked as answer by Yichun_Feng Tuesday, September 8, 2009 2:05 AM
    Thursday, September 3, 2009 3:05 PM

All replies

  • It sounds like your regex is not working as you expect, thus changing the password to be incorrect depending on what you feed the regex with.
    Subsequently the login fails.

    I do not fully understand this approach. But if you are prepared to have the SA password in cleartext, why not do it directly rather than with regex?


    This posting is provided "AS IS" with no warranties.
    Wednesday, September 2, 2009 7:29 AM
  • Try to run SQL Profiler on server to see what is going on . It might provide some information
    Val Mazur (MVP) http://www.xporttools.net
    Wednesday, September 2, 2009 10:39 AM
    Moderator
  • Thanks for your responses!

    Michael, I'm using a regex to replace the username and password of the existing connection string because I don't necessarily know the name of the server. I could parse out the server name instead, but this way seemed the most straight forward. I've verified that the regex part works as expected in my development environment, our test environment, and using the config file/connection string from one of the applications receiving the error. Here are the expressions: "uid=[^;]*" and "pwd=[^;]*" .

    Since it works sometimes and not others, are there any server configuration settings that could be causing this behavior? Oh shoot, I forgot to include that this is using SQL Server Express.

    Val, I didn't know SQL Profiler would show login attempts. I'll see if someone will let us remote it in to debug.

    Wednesday, September 2, 2009 2:20 PM
  • Are there any server or database settings that would cause the user ID in a connection string to be case sensitive?
    Thursday, September 3, 2009 12:00 AM
  • I'm not very good on regex so I can't comment on that.

    Did a quick test with a default server installation.
    Servername, instancename and username doesn't seem to be case sensitive.
    Password is case sensitive and, of course, if the user doesn't exist this error will show.

    //Michael
    This posting is provided "AS IS" with no warranties.
    • Marked as answer by Yichun_Feng Tuesday, September 8, 2009 2:05 AM
    • Unmarked as answer by _xr280xr_ Tuesday, September 8, 2009 1:37 PM
    Thursday, September 3, 2009 7:41 AM
  • I've reproduced it. You're right, the default server installation isn't case sensitive (thanks for checking), but if you change the collation to be case sensitive, then the login username becomes case sensitive. Now I just have to determine if this is the case on the client databases.
    • Marked as answer by Yichun_Feng Tuesday, September 8, 2009 2:05 AM
    Thursday, September 3, 2009 3:05 PM