none
Error: 18456, Severity: 14, State: 146. for Non-sysadmins

    Pertanyaan

  • Here is the SQL Server info
    SQL_Server_Edition        : Express Edition (64-bit)
    SQL_Server_Build_No     : 14.0.3026.27
    Server authentication     : Mixed mode

    I came across SQL connection error that is not going away.

    Error messages:
    Login failed for user 'DOMAIN\User1'. Reason: Token-based server access validation failed with an infrastructure error. Login lacks Connect SQL permission. [CLIENT: <local machine>]
    Error: 18456, Severity: 14, State: 146.

    Login failed for user 'DOMAIN\User1'. Reason: Token-based server access validation failed with an infrastructure error. Login lacks Connect SQL permission. [CLIENT: <IP address>]
    Error: 18456, Severity: 14, State: 146.

    State Id: 146 refers to token-based server access validation error.


    Security setup for 'DOMAIN\User1':
    Server role         : public
    Database role    : db_owner, public

    Note: The default permissions for 'public' role are not altered. This means it has 'CONNECT SQL' (server) and 'CONNECT' (endpoint) permissions.

    I checked in sys.login_token and do see a token for that login.

    These are my troubleshooting steps for domain user:
    1. Granted 'CONNECT SQL' and 'CONNECT' permissions explicitly to make sure (did not work).
    2. Tried connecting remotely and by rdping, both as an administrator and regular(did not work).
    3. Granted 'securityadmin' server role (did not work).
    4. Granted 'sysadmin' server role (worked!).
    5. Removed 'sysadmin' and kept 'public' role.


    To rule out token issue, created a SQL authenticated user with 'public' server role and 'public' & 'db_datareader' database roles. Connection worked!

    This means there is no connectivity issue for SQL authenticated user, but do have for domain user. There is an option for the application to use sql authentication, but we will loose the accountability. Additionally, using Windows authentication will restrict the access to an AD group, preventing unauthorized access.

    Is this a limitation of Express edition?

    Additionally,wanted to see if this occurs in on SQL Server 2016 Standard edition (13.0.5149.0). Created a login for same domain user 'DOMAIN\User1' with 'public' server and db role and 'db_datareader' db role. No connectivity issue and worked!


    Am I doing anything wrong?
    Meantime, I am applying Standard edition key for MSSQL 2017 to try same tests.

    Thank you in advance!
    Any help is greatly appreciated.


    Puja



    • Diedit oleh PShah109 Rabu, 13 Juni 2018 19.27
    Rabu, 13 Juni 2018 15.59

Semua Balasan

  • Upgraded the edition from SQL Server 2017 Express to Standard, still same error.

    Cannot grant 'sysadmin' for ever and grant more access than what is needed.


    Puja

    Rabu, 13 Juni 2018 16.16
  • Obviously, the account is missing CONNECT SQL permission or CONNECT permission on the endpoint.

    Run this query:

    SELECT * FROM sys.server_permissions WHERE type = 'COSQ' OR class = 105

    Pay particular attention to whether there is any DENY in the mix.

    Also check sys.server_endpoints to see that the endpoints listed are the ones expected.

    Rabu, 13 Juni 2018 21.31
  • Reason: Token-based server access validation failed with an infrastructure error.

    See SQL SERVER – Login failed for user . Reason: Token-based server access validation failed

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Kamis, 14 Juni 2018 06.53
  • Hi Puja,

    Let's analyze your error message, first the token-based message is generated for the Windows authentication logins. 

    Then the important piece of information from that message is the part about server access validation failed. During the login process, the database engine has to perform several checks regarding the login and its various attributes before letting the application connect to the SQL Server instance. Out of that big list, related to this error condition, there are two checks to find out if this login is authorized to access this server instance. There are two permissions that come into play while performing these authorization checks:

    1. Does the login have the SERVER class permission named CONNECT SQL for this server instance?

    2. Does the login have the ENDPOINT class permission named CONNECT for the specific endpoint on which the client application established the connection?

    So to resolve your error, you can refer to this article: Why do I get the infrastructure error for login failures? and verify the authorization checks.

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Kamis, 14 Juni 2018 08.03
    Moderator
  • Hi Erland,

    Thank you for responding.

    I ran about query and not see 'DOMAIN\User1' in the list. Only 'Builtin\Users' had DENY, which I believe should not impact 'user1'?

    There are no new endpoints, besides the defaults. For existing endpoints, user1 has public role which has that permission. But I still wanted to confirm and explicitly did grant CONNECT SQL and CONNECT permissions, they did not work.

    I had granted permissions before posting a question as well, but in vain.


    Puja


    • Diedit oleh PShah109 Kamis, 14 Juni 2018 12.35
    Kamis, 14 Juni 2018 12.29
  • Hi Olaf Helper,

    Thank you for your reply.

    I did come across Pinal Dave's blog post and did run the query, got only 'Builtin\users' windows group. 'domain\user1' is not part of 'Builtin\users' group.

    Running SSMS as an administrator locally on server did not help either.

    SELECT sp.[name],sp.type_desc 
    FROM sys.server_principals sp 
    INNER JOIN sys.server_permissions PERM ON sp.principal_id = PERM.grantee_principal_id 
    WHERE PERM.state_desc = 'DENY'


    Puja

    Kamis, 14 Juni 2018 12.46
  • Hi Xi Jin,

    Thank you for your reply. Both permissions were granted explicitly and being part of 'public' server and database roles, 'domain\user1' had them and is not a member of 'BUILTIN\Users' Windows group.


    Puja

    Kamis, 14 Juni 2018 12.58
  • I ran about query and not see 'DOMAIN\User1' in the list. Only 'Builtin\Users' had DENY, which I believe should not impact 'user1'?

    Don't really know why you think that DOMAIN\user1 is not part of Builtin\USERS. My guess is that this is the case. In any case, why not simply test by revoking CONNECT SQL from BUILTIN\Users? I am quite sure that that DENY is the culprit.

    Kamis, 14 Juni 2018 21.55