locked
SQL Server Error 18456 RRS feed

  • Question

  • When I try to connect to a server via Microsoft SQL Server Management Studio using SQL Server Authentication with the server domain name in the “server name field”, I get an error (Error:18456).

    To resolve this error and connect via the server domain name, and do the following steps:

    1-      1- I switch the authentication to “Windows Authentication” and replace the domain name with the sever (machine) name that can be accessed from another machine within the same network, this connects me to the server properly.

    2-      2- I change the password of the system administrator account (sa) and set it as the old one, for example if the old one is 123, I change it to 123, and apply these changes.

    3-     3-  After changing the sa password, I can connect to the server using the domain name with no errors.

    This is better illustrated with the screenshots below

     My problem is: After performing these steps, the error is resolved temporarily and after a certain period (almost 2 hours) the 18456-error appears again, and I have to repeat the same procedure!

    What might be the issue here?

    Any help is appreciated,

    Hani


    Hani Draidi GIS Engineer



    • Edited by Hani Draidi Tuesday, February 17, 2015 9:35 AM
    Sunday, February 15, 2015 8:28 PM

Answers

  • Here is an example. I've put the log table directly in the master database, but since this is only a test server, that is not too much of an issue:

    Note that the example below cleans everything up at the end.

    USE master
    go
    CREATE TABLE eventlog (logid  int IDENTITY,
                           atwhattime datetime2(3) NOT NULL
                              CONSTRAINT def_eventlog_atwhattime DEFAULT sysdatetime(),
                           bywhom     sysname
                              CONSTRAINT def_eventlog_bywhom DEFAULT original_login(),
                           fromwhat   sysname
                              CONSTRAINT def_eventlog_fromwhat DEFAULT host_name(),
                           fromwhere  sysname
                              CONSTRAINT def_eventlog_fromwhere DEFAULT app_name(),
                           theevent   xml
                              CONSTRAINT def_eventlog_theevent DEFAULT eventdata(),
                           CONSTRAINT pk_eventlog PRIMARY KEY(logid)
    )
    go
    CREATE TRIGGER sneaky_tri ON ALL SERVER FOR ALTER_LOGIN AS
      INSERT eventlog DEFAULT VALUES
    go
    ALTER LOGIN sa WITH PASSWORD = 'thenewsecretpassword'
    go
    EXEC sp_password NULL, 'andnow the password is even more secret', sa
    go
    SELECT * FROM eventlog
    go
    DROP TRIGGER sneaky_tri ON ALL SERVER
    go
    DROP TABLE eventlog


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, February 24, 2015 10:13 PM

All replies

  • Hello,

    Try to rename the sa account.


    If you want to log when a change of password is occurring for the sa account, open SQL Server Profiler and use the following event class.

    https://technet.microsoft.com/en-us/library/ms190672(SQL.90).aspx


    The following resource may be useful.

    http://sqlserver-deep-dives.blogspot.com/2011/05/auditing-password-changes-in-sql-server.html


    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com


    Sunday, February 15, 2015 9:04 PM
  • To resolve this error and connect via the server real IP, I do the following steps:

    Real IP, but why does it say ".com" in the screenshot?

    In any case, log in the SQL Server errorlog. There you should find a message
    which matches the login failure. This message also includes an explanation why the login fails.

    From your description it seems that there is a process or a person that is pulling your legs and keps changing the password.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, February 15, 2015 9:18 PM
  • Many Thanks for the helpful resources Alberto!

    I'll try them and keep you posted


    Hani Draidi GIS Engineer

    Tuesday, February 17, 2015 9:36 AM
  • Hi,

    If you check in your SQL Error log you will find similar error message

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

    Check the severity and state and match it with the blog and you can find the real reason.

    http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Tuesday, February 17, 2015 9:42 AM
  • Erland,

    Thank you very much for your valuable input,

    I am sorry for mentioning "Real IP" in my post, actually I used this term by mistake and the correct term is "the server domain name" (that ends with .com). I've modified this in the original post.

    After Exploring the SQL Server errorlog, I found that this error happens due to change in the IP address (the error message in the log files says: Password did not match that for the login provided [CLIENT:45.31.158.87]), and in each error the IP address is different.

    This does make sense, because I am using a Dynamic DNS domain, which keeps the server domain name without reserving the IP.

    But my issue now is making the SQL Server connect using the domain name (which is the same all the time) regardless of its associated IP address (which changes frequently)

    Thank you in Advance and sorry for inconvinience

    Best Regards,


    Hani Draidi GIS Engineer

    Tuesday, February 17, 2015 9:53 AM
  • Well, your IP address does not matter for the password. Your IP address may change, but that does not change the password.

    No, what is happening is what I said in my previous post: someone or something is changing the password of sa.

    To track this down you could create a DLL trigger or set up a trace for
    the Audit Login Change Password Event.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, February 17, 2015 10:11 PM
  • Thank you again Erland,

    Could you please elaborate more how can I track the changes in the "sa" password. Is there away to secure this password so no one except me can change it?


    Hani Draidi GIS Engineer

    Monday, February 23, 2015 7:56 AM
  • Could you please elaborate more how can I track the changes in the "sa" password. Is there away to secure this password so no one except me can change it?

    Yes, make sure that you are the only one know the password. And make sure that noone else is in the sysadmin role on the server.

    I don't have the time to type up tested DDL trigger right now, but create a server-level trigger for the ALTER_LOGIN action and save the output from the eventdata() function into a table.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, February 23, 2015 10:35 PM
  • Thank you again Erland,

    That's what driving me crazy. I am working on test machine, I am the only administrator, and there is no one else has a permission to access it!

    Actually, I am not familiar with SQL Server technical details. May you please mention some resources that I can use to create a DDL trigger to track the password status?



    Hani Draidi GIS Engineer

    Tuesday, February 24, 2015 7:30 AM
  • Here is an example. I've put the log table directly in the master database, but since this is only a test server, that is not too much of an issue:

    Note that the example below cleans everything up at the end.

    USE master
    go
    CREATE TABLE eventlog (logid  int IDENTITY,
                           atwhattime datetime2(3) NOT NULL
                              CONSTRAINT def_eventlog_atwhattime DEFAULT sysdatetime(),
                           bywhom     sysname
                              CONSTRAINT def_eventlog_bywhom DEFAULT original_login(),
                           fromwhat   sysname
                              CONSTRAINT def_eventlog_fromwhat DEFAULT host_name(),
                           fromwhere  sysname
                              CONSTRAINT def_eventlog_fromwhere DEFAULT app_name(),
                           theevent   xml
                              CONSTRAINT def_eventlog_theevent DEFAULT eventdata(),
                           CONSTRAINT pk_eventlog PRIMARY KEY(logid)
    )
    go
    CREATE TRIGGER sneaky_tri ON ALL SERVER FOR ALTER_LOGIN AS
      INSERT eventlog DEFAULT VALUES
    go
    ALTER LOGIN sa WITH PASSWORD = 'thenewsecretpassword'
    go
    EXEC sp_password NULL, 'andnow the password is even more secret', sa
    go
    SELECT * FROM eventlog
    go
    DROP TRIGGER sneaky_tri ON ALL SERVER
    go
    DROP TABLE eventlog


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, February 24, 2015 10:13 PM
  • Thank you so much Erland for your prompt input,

    Your valuable time is highly appreciated!


    Hani Draidi GIS Engineer

    Friday, February 27, 2015 10:20 PM