Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2008
    Posts
    9

    Unanswered: Error: 18456, Severity: 14, State: 16. Login failed for user 'sa'.

    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

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Someone correct me if I'm wrong - but I was taught that one of the first things to do when you get a new SQL Server is to password the sa account, disable it, and forget the password...
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My guess is that one client has the correct password for "sa" and the other client has the wrong password for "sa".

    George is right. The "sa" login is an anacronism... The "sa" login should not be used by well written software written since SQL 2000 sp3. If you need SQL Authentication, give sa a garbage password and promptly forget it. If you don't need SQL Authentication, don't enable it at all.

    -PatP

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Pat Phelan
    The "sa" login should not be used by well written software written since SQL 2000 sp3.
    Orly? I don't remember what happened that long ago. Why was it acceptable pre-sp3?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I am not sure, and I will have to check notes when I get back to work tomorrow, but I believe State 16 means the connection attempted to access a database that is offline, in recovery, or misspelled.

  6. #6
    Join Date
    Jul 2008
    Posts
    9
    Hi,

    Thanks for your reply.
    Previously I had a different login for the application but after getting the error of state 16 and checking on various websites I felt that the database might not be accessable hence I used sa to login, but again the same issue popped up.

    Now I have pointed the application to another server having sql server 2000 with same connection parameters, there it's working perfectly fine. I don't understand whether it's an issue with sql server 2005. If yes, can somebody please assist me in getting a solution.

    Thanks..

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Check the spelling of the database in the connection string or DSN.

  8. #8
    Join Date
    Dec 2002
    Posts
    1,245
    State 16 is not documented in the SQL BOL, but it means that the default database for the user is not accessible (either because it is not online, or because the user's access to that database has been disabled).

    We encounter it most frequenly in a couple of situations:

    1) When we delete a database which has been configured as the default database for a user

    2) When the default database is not accessible (such as during restore operations or when the mirror fails over to the secondary)

    Check the default database setting for the user and go from there.

    Regards,

    hmscott
    Have you hugged your backup today?

  9. #9
    Join Date
    Jul 2008
    Posts
    9
    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 in question with default db as master.. I guess it might be some other issue.

  10. #10
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by TanveerAhmed
    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 in question with default db as master.. I guess it might be some other issue.
    [shrug]
    Did you check the default database setting for the sa account on your 2005 server?

    Regards,

    hmscott
    Have you hugged your backup today?

  11. #11
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    State 16 will also be thrown if the database declared in the connection string is unavailable. Not just the default database for the login.

    For the last time in this thread, please check the spelling of the database name in the connection string or DSN.

  12. #12
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •