Does linked server local login value support a windows AD group based sql login?

All we need is an easy explanation of the problem, so here it is.

Link: https://docs.microsoft.com/en-us/sql/relational-databases/linked-servers/create-linked-servers-sql-server-database-engine?view=sql-server-ver15#to-create-a-linked-server-to-another-instance-of-sql-server-using-sql-server-management-studio

I have a windows group configured as sql login.

While configuring linked server, under local login, I can see the windows group sql login listed in the dropdown.

The above link doesn’t mention anything about windows group. Does linked server local login support windows group (with/without impersonation)? If not then why is that option showing up windows group in the dropdown?

This link says its not possible, but it’s very old: https://www.sqlservercentral.com/forums/topic/linked-server-and-windows-group-local-login

I am looking for documentation mentioning about this.

Does linked server local login value support a windows AD group based sql login?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

No, groups are not allowed; if you try you will get this error xxxx\groupname is not a valid login or you do not have permission even if the group is valid.
Does linked server local login value support a windows AD group based sql login?
This is by design.

To use groups, you have to work with AD groups in the target server, set proper permission and use kerberos autentication. To do that you have to set connection made with "login’s current security context".

Does linked server local login value support a windows AD group based sql login?

With kerberos authentication you can authorize in target server a group and have your user use linked server with their windows logon on the remote server, based on group membership. But you have to setup kerberos constrained delegation. link

But you can do this only if the remote server is in the same or trusted domain and is using windows authentication.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply