Saturday, November 19, 2011

0 How to solve Error: 18456, Severity: 14, State: 12. Login-based server access validation failed with an infrastructure error. Check for previous errors

Recently, I have encounter this login failure for my sql server account.

image

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.

  1. GRANT CONNECT SQL TO "mytest"
  2.  
  3. GRANT CONNECT ON ENDPOINT::"TSQL Default TCP" TO "mytest"

After I grant my login have proper permission for the endpoint. It works like a charm. Smile

To check your login permission, you can use below query

  1. select name, principal_id from sys.server_principals where name = 'mytest'
  2.  
  3. select* from sys.server_permissions where grantee_principal_id = suser_id('mytest')

image

Update:

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

  1. CREATE ENDPOINT [SG1USERENDPOINT]
  2. STATE = STARTED
  3. AS TCP
  4.    (LISTENER_PORT = 4536, LISTENER_IP =ALL)
  5. FOR TSQL() ;
  6. GO

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

  1. GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [public]

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates