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.
