Error: 18456, Severity: 14, State: 16.Login failed for user 'sa'. [CLIENT: XXX.XXX.XX.XXX]

  • Tanveer-700199

    Ten Centuries

    Points: 1230

    Hi,

    Since morning I have observed the following errors in sql 2005 error logs

    Error: 18456, Severity: 14, State: 16.

    Login failed for user 'sa'. [CLIENT: XXX.XXX.XX.XXX]

    (where XXX represents Ip address of client machine)

    After that the client machines are not getting connected to sql server. Actually I have 2 applications connected to the same sql server. One is working perfectly fine while the other is not able to connect. Both the applications are connected through sa.

    Can someone please help me to solve this issue.

    Note : SQL Server version is SQL Server 2005 standard Edition with SP2

  • Gail Shaw

    SSC Guru

    Points: 1004474

    State 16 means that the default DB or the DB requested by the login is not available. Check what DB the app in question wants and make sure that it's accessible (present and online)

    Why are your apps using the sa login? It's a security worst practice.

    In the future, please post SQl 2005-related questions in the SQL 2005 forums. Thanks

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Tanveer-700199

    Ten Centuries

    Points: 1230

    HI,

    The db in question is accessalable and online. Previously a different login was given but I read at several places that it might be access issue hence i logged in the application through sa. But still the same issue exist.

    Please assist.

    Thanks

  • Gail Shaw

    SSC Guru

    Points: 1004474

    Can you check the connection string that the app uses, see if it specifies an initial database. Also check what the default database is for sa.

    A properly set up account for your app won't give problems and allows you to limit what the app is allowed to do.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Tanveer-700199

    Ten Centuries

    Points: 1230

    Hi,

    Thanks for your reply..

    I have mapped the application server to sql server 2000 with just a change in database server ip address. There it's working perfect.

    Is this the issue related to sql server 2005 ?

    If yes, what is the solution ?

    Thanks..

  • Gail Shaw

    SSC Guru

    Points: 1004474

    It's not a SQL 2005-specific problem. The error (specifically the state) is saying that the database requested by the login isn't available, either because it's not there, is offline or because the login doesn't have permission.

    The login's sa which will always have permission, so that isn't the issue. Check what the default DB is for sa (should be master) and check what database is requested in the app's connection string.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Tanveer-700199

    Ten Centuries

    Points: 1230

    Since the other application is accessing the same database, there is no way that the database is not available or offline.

    The default db for 'sa' is master but this default exist in sql 2000 as well and there I have no issues. Even the other application is able to access the db with default db as master.. I guess it might be some other issue.

  • Gail Shaw

    SSC Guru

    Points: 1004474

    Tanveer (9/17/2008)


    The default db for 'sa' is master but this default exist in sql 2000 as well and there I have no issues.

    That's as it's supposed to be.

    Do you have any way of seeing the connection string that the app that's not working uses?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Tanveer-700199

    Ten Centuries

    Points: 1230

    I don't have rights to see the connection string.. As mentioned earlier, the application uses the same connection string to connect to sql server 2000 and there I don't have any issues

  • Gail Shaw

    SSC Guru

    Points: 1004474

    Then there must be something different about the databases that are on SQL 2000 and the databases that are on SQL 2005. The thing is, the error is saying there's something wrong with the DB access or the DB.

    Is there anyone you can ask about the connection string? All that I want to see from it is the requested database.

    In the meantime, can you please run the following queries?

    SQL 2000:

    select name, status, status2 from master..sysdatabases

    SQL 2005:

    select name, user_access_desc, is_auto_close_on, is_in_standby, state_desc from sys.databases

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • jerry.glasgow.ctr

    Old Hand

    Points: 372

    I had this problem and I brought the database offline and then online and that resolved the issue.

  • SequelDBA

    Ten Centuries

    Points: 1093

    Hello...Having the same issue with a vendor app, who has suggested entering fake IPs in web page config files. Will push back on that resolution, but I'm a little confused as to what the problem is. I've validated the SQL login and it's privs.

    KU

  • forsqlserver

    SSCoach

    Points: 18900

    I am getting the same problem and I am not able to login using sa

    Error is coming:Login failed for user sa Microsoft Error:18456

    Its Urgent...

    Thanks

  • jerry.glasgow.ctr

    Old Hand

    Points: 372

    Here is the deal I have found. My native SQL User accounts have the account policy so they follow the same stipulations as the active directory domain accounts. When users lock that account or a password miss match or something like that occurs you cannot just unlock. We have spent some time and testing to nail down a solution. We found that we have to drop the login account (not the user account in the database) and then re-create the account. This means you either need the password to the account or you need to create a drop script and recreate script.

    1. Unlock

    2. Get drop create login scripts

    3. run drop

    4. run create

    All mappings and permissions will be intact. This is what worked for us.

  • Conan The Canadian

    Ten Centuries

    Points: 1335

    -Removed-

    Kev -=Conan The Canadian=-
    @ConanTheCdn

Viewing 15 posts - 1 through 15 (of 15 total)

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