How to add Active Directory user group as login in SQL Server Remotely using SSMS

Question

Good day all,

I've created an AD group called SQL Admins to allow users admin access to the SQL server. The users are able to RDP into the servers and have admin access to the SQL server.

However, we don't the users to RDP into the server. SSMS was installed on users' computers and they are unable to connect.

Thus far we have done the following:

• Opened port 1433 on the Windows firewall
• Enabled SQL Server and Windows Authentication mode
• Enabled Allow remote connections to this server

We are able to connect to the server remotely with SSMS using the sa account.

When we try to log in remotely with an AD account that is SQL Admins group we get "Login failed for user '<username>'. (Microsoft SQL Server, Error: 18456)". The event is logged in event viewer as "Login failed for user '<username>'. Reason: Could not find a login matching the name provided. "

If the same user added directly to Logins with the same permissions as the AD group, only then are they able to log in remotely with SSMS.

Is there a way to get assign users to an AD Group and then add persons who need access to that group rather than having to create a new SQL login for each person?

Answer 1

Hi @Vishard Birusingh ,

To add Active Directory user group as login, please go to Security > Logins and right-click New Login.
In the Login - New dialog box, on the General page, click Search... to open the Select User or Group dialog box.

In the Select User or Group dialog box, click Object Types... to open the Object Types dialog box and select Groups, and click OK.

Then you can enter the group name that you want to find under Enter the object name to select (examples).
Please refer to Create a Login which might help.
Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Had the same issue when using SSMS against SQLSever2019. Turns out I have to run SSMS as administrator (right click icon and select Run as administrator) in order for the AD Group to be authenticated using my user ID.

Answer 3

Yes, that is possible and that is what everyone is doing.

It seems to me that you have set up the SQL Admins group so that users only get this token when log on to the SQL Server machine. That could for instance be that is not an AD group, but only a local group on the machine. Or there is some funny tweak in your AD creation. (I'm an SQL Server guy and I haven't even seen an AD on a postcard.)

You will have to take a closer look at what you actually did. But going back to your question: it is perfectly possible to add an AD group as a login to SQL Server, and I would suggest that this by far the most common way to handle access and permissions in SQL Server. You work with AD groups in SQL Server, and the user administration is in the AD only.

Answer 4

Thank you very folks!!

Just a note, when testing the access from the remote client machine, log off and log back in as you more persons in and out of the AD groups. This cost me couple hours.