Recently, I have encounter this login failure for my sql server account.
Before dig into the solution, let me provide some back ground.
- SQL Server login mode is mix. ( Allot both windows authentication and sql server authentication)
- Only sysadmin group user can login to the server via sql server authentication
- SQL server account can login locally via HOSTNAME\SQLSERVERNAME but not HOSTNAME,PORTNUMBER. It does not work while connect from remote.
Here are some resource about the login failure from internet. This article http://sql-articles.com/articles/troubleshooting/troubleshooting-login-failed-error-18456/ has very detail to describe various login failure. However it does not have the solution for the 18456 state 12.
This one describe how to fix the state 11 http://blogs.msdn.com/b/sqlserverfaq/archive/2010/10/27/troubleshooting-specific-login-failed-error-messages.aspx
Other post from internet also suggest UAC or firewall issue. I have tried all of them without much of success.
I suspect it is some sort of permission issue. since once I grant the my test login as sysadmin, it can work without the issue. Properly related to the endpoint permission. Quantum John suggest in the post here http://blogs.technet.com/b/fort_sql/archive/2010/07/06/connection-error-after-removing-public-permissions.aspx also indicate the same thing.
After I grant my login have proper permission for the endpoint. It works like a charm.
To check your login permission, you can use below query
I have just figure out why I was suddenly getting this error. It is because I was playing around with user create endpoint. To reproduce this error message, simply create the user endpoint as below
Once you run above SQL, you will get the message says:
Creation of a TSQL endpoint will result in the revocation of any 'Public' connect permissions on the 'TSQL Default TCP' endpoint. If 'Public' access is desired on this endpoint, reapply this permission using 'GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [public]'.
To simply fix the issue, just run