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,

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

• Edited by Alberto MorilloMVP, Moderator Sunday, February 15, 2015 9:05 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.

Many Thanks for the helpful resources Alberto!

I'll try them and keep you posted

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

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,

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.

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?