x

Adding AD group to SQL Server

Hi everyone. I know the topic has been covered, but I couldnt find solution to my problem.

The client used to grant permissions to database through single AD accounts and everything worked just fine. But the logical solution when there is a lot of users is to use groups. Client defined groups in Active Directory. Groups exist and members are assigned. The group has been added as SQL Server login(we can find the group in AD from SSMS, and add it as a login), which should grant group members access to server(we are talking about server access level, not database access). But it doesnt work. Accounts from group have no access.

At the login properties in Securables -> Effective tab there is an error message: Cannot execute as the server principal because the principal does not exist, this type of principal cannot be impresonated or you do not have the permission.

Such error does not appear for single accounts that has access to database. I'm running out of ideas here - what am I missing?

more ▼

asked Feb 17, 2016 at 10:08 AM in Default

avatar image

lukjak
101 1 1 8

You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.

Feb 22, 2016 at 11:21 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Give this a shot, to at least gather more information about why the group members cannot connect.

Regarding the error "Cannot execute as the server principal because the principal does not exist, this type of principal cannot be impersonated or you do not have the permission." -- That looks a lot like the error you get if you try to use TSQL to EXECUTE AS a windows group. Per BOL, you cannot impersonate a group.

However, you CAN impersonate a user IN that group, even if they don't have a server login for their own account. So, if YourDomain\\Bob is a member of YourDomain\\SQLPeople, and YourDomain\\SQLPeople is a windows authenticated login in SQL Server, then you can EXECUTE AS Bob, then check permissions from there. To wit:

 USE master
 GO
 
 EXECUTE AS LOGIN = 'YourDomain\\AUserInTheADGroup'
 
 SELECT * FROM fn_my_permissions(NULL, 'Server')
 
 REVERT

At minimum, you should see the permission CONNECT SQL. If not, start troubleshooting here: http://dba.stackexchange.com/questions/4380/minimum-user-rights-for-connecting-ms-sql-database-remotedly

More information on fn_my_permissions: https://msdn.microsoft.com/en-us/library/ms176097.aspx

Longer description of impersonating a user to check their permissions: http://www.kendalvandyke.com/2008/12/hey-mr-dba-what-permissions-do-i-have.html

Point of clarification: I am assuming that when you say "SQL Server Login" you mean a login inside SQL Server that authenticates through Windows -- i.e., you did not specify a password. Logins with passwords are authenticated through SQL. For more, see: https://msdn.microsoft.com/en-us/library/ms144284.aspx

more ▼

answered Feb 18, 2016 at 11:04 AM

avatar image

sdoubleday
639 3 8

(comments are locked)
10|1200 characters needed characters left

Check and see if the AD group has been added to a server group. It is possible to grant access to the SQL server without giving them access to the physical server itself.

If you want to give the group access to the physical server, then they need to either be added to an existing server group.

more ▼

answered Feb 17, 2016 at 02:44 PM

avatar image

sjimmo
1.6k 1 3 5

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x119
x7
x2

asked: Feb 17, 2016 at 10:08 AM

Seen: 211 times

Last Updated: Feb 22, 2016 at 11:21 AM

Copyright 2017 Redgate Software. Privacy Policy