1

We are getting the above error on a SQL 2012 server running Windows Server 2008R2 when connecting using an application and a service account. The server also has Config Manager database and GAP policy applied

The login will work if the user is sysadmin (but the application won't)

Of course we have looked at Aaron Bertrand's post on 18456 errors, which states:

States 11 & 12 mean that SQL Server was able to authenticate you, but weren't able to validate with the underlying Windows permissions. It could be that the Windows login has no profile or that permissions could not be checked due to UAC. Try running SSMS as administrator and/or disabling UAC. Another reason could be that the domain controller could not be reached. You may need to resort to re-creating the login (see this post from Simon Sabin).

and also http://blog.sqlauthority.com/2015/08/19/sql-server-login-failed-for-user-reason-token-based-server-access-validation-failed-with-an-infrastructure-error/

So we have ensured that we have a profile on the servers by logging on with the service account.
We have run the application as administrator
We have confirmed connectivity to the DC
We have removed and re-created the login
We have confirmed there are no DENYs for the user or group

We have confirmed that the user and also the public role has connect permissions to the ConfigMgrEndpoint as well as SQL

What have we missed?
What else do we need to do to resolve this issue?

Improve this question
5
  • Are you able to SSMS the SQL server locally and remotely with the account for which you are facing an issue?
    – SQLPRODDBA
    Commented May 18, 2016 at 14:27
  • Can't try that unfortunately, run as service accounts disabled on this system
    – SQLDBAWithABeard
    Commented May 18, 2016 at 17:15
  • I think you should check errors in eventviewer logs on the box where SQL server is installed. I am sure you will get some errors in there when it logs login failure messages in SQL Server error log. The other thing that comes to my mind is, there could be a connectivity issue between domain controller and SQL server box. Sometimes group policies also causes these type of issues. Forcefully update it and see if that works. Also, you should check with your sysadmins regarding any connectivity issues with the box. As the last resort try to reboot SQL server box and see if that fixes the issue.
    – SQLPRODDBA
    Commented May 19, 2016 at 13:50
  • Thank you Event viewer errors match those in SQL Error log, nothing additional in there to explain the issue. Already confirmed connectivity to the DC Already confirmed connectivity to the SQL Server (works with sysadmin privs) SQL has been restarted and also SQL Server has been restarted
    – SQLDBAWithABeard
    Commented May 22, 2016 at 8:02
  • I've had this where the DC needed to be rebooted. The DC was reachable but not authenticating correctly.
    – Alen
    Commented Jun 26, 2018 at 15:43

1 Answer 1

Reset to default
1

Check the new endpoint name on sys.endpoints table and grant connect permission for the user. The new endpoint name should be different from "TSQL Default TCP".

Use this script for windows login:

GRANT CONNECT ON ENDPOINT::"NEW_ENDPOINT_NAME" TO [DOMAIN\firstname.lastname]

Or use this for sql authentication login:

GRANT CONNECT ON ENDPOINT::"NEW_ENDPOINT_NAME" TO "username"
Improve this answer
1
  • Nice answer. Welcome on the DBA SE!
    – peterh
    Commented Jun 26, 2018 at 17:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.