HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

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

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
theconnectunlessaremodecantmssqlsysadminrolethey

Problem

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 cr

Solution

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!

Context

StackExchange Database Administrators Q#76325, answer score: 4

Revisions (0)

No revisions yet.