Sql-server – MSSQL Mixed-Mode Users cant connect unless they are in the server sysadmin role

sql serverssms

SQL Server 10.0.5520 (2008 std sp3)
My goal is to enable data connections in SSMS or Visual Studio for windows users w/out them being sysadmins. Ideally this would be done through Active Directory groups, but for now I'm just targeting one use (my own) for simplicity's sake. I am using 'sa' account to make these changes.

Using either Visual Studio data connection or SSMS, my users are unable to establish a connection to the database unless they are in the server's sysadmin role.

Login failed for user 'DOMAIN\user'. 
Reason: Token-based server access validation failed with an infrastructure error. 
Check for previous errors. [CLIENT: xxx.xxx.xxx.xxx]

The previous error states:

Error: 18456, Severity: 14, State: 11.

In SSMS, a user's server roles are limited to public. If I attempt to add the server to Visual Studio. The above error occurs, and the Test connection button fails.

When I add the sysadmin server role the connection succeeds and I am able to browse the databases to select as the initial catalog, and the 'Test Connection' button succeeds.

I have confirmed that public has been granted 'connect' permission for every type of TSQL securable type.

I have given the user total control over the target database on the server.

It seems as though this is totally related to server-roles, and that somewhere something is rejecting non sysadmin accounts. (adding all roles other than sysadmin also causes a rejection).

Permission to connect to database engine is granted to server user.

What are some of the things I should check to help enable the ability to connect?

Follow-Up:

EXECUTE AS LOGIN = 'DOMAIN\user';
SELECT * FROM fn_my_permissions(NULL, 'SERVER') 
    ORDER BY subentity_name, permission_name ;  
REVERT;
GO

returned 3 records

#, entity_name, subentity_name, permission_name
1, server, , CONNECT SQL
2, server, , VIEW ANY DATABASE
3, server, , VIEW ANY DEFINITION

This may or may not be relevant. I had an AD group created and made the group sysadmin, and all the users in the group can connect… I don't think feel like AD is the culprit. There is something special about sysadmin that i am missing or that was misconfigured early on….

I was able to grant the windows user access w/out the sysadmin role by granting them CONTROL SERVER in the securables list under the server-level user properties.

Best Answer

Thanks for all your comments. I know this thread is pretty old, but I have finally resolved the issue.

The public server role had an endpoint called ConfigMgrEndpoint that had no permission to "Connect". I granted the connect privilege and things started to work for non-sa users.

Under Server Objects>Service Broker I find the ConfigMgrEndpoint.

I have to assume that at one point MS Configuration Manager was being implemented.

I'm not sure how to dis-integrate the service broker end point, and I can't really experiment because this database is in production. Our current plan is to simply deploy an instance of mssql 2012 on a fresh server install and port the database over.

Hopefully someone else gets some benefit from this, as there was never any mention about end points in the articles i read investigating this issue!

Thanks again!