Configure isolated access to a Hyperscale named replica using AAD groups

Question

Configure isolated access to a Hyperscale named replica using AAD groups

Krzysztof Żelazny 20

I'm traing to configure isolated access in azure sql database base on article https://learn.microsoft.com/en-us/azure/azure-sql/database/hyperscale-named-replica-security-configure?view=azuresql&tabs=SQL-Authentication to a Named replica using AAD groups, unfortunately when I use AAD groups I can login to primary replica indicating user database in SSMS options (Connect to database) Anybody can help me to prevent login users to primary replica user database ?

Accepted answer

0 additional answers

Your answer

Answer 1

Oury Ba-MSFT 20,931 Microsoft Employee Moderator

Krzysztof Żelazny Thank you for reaching out. To prevent users from being able to connect to the user database on the primary replica, you can configure firewall rules on the primary replica to restrict access to the user database. Please try the following steps

Go to your Azure SQL Database instance in the Azure Portal.
Go to networking tab.
Under "Firewall rules", click "Add client IP" to add your current IP address to the list of allowed IP addresses.
Under "Virtual network service endpoints", select the virtual network and subnet that you want to use for the named replica.
Click "Save" to apply the changes.
Once you are connected to the named replica, you can create firewall rules to restrict access to the user database on the primary replica. You can do this by adding a firewall rule that blocks all traffic to the IP address of the primary replica. This will only prevent users from connecting to the user database on the primary replica. It will not prevent them from connecting to the primary replica itself, or to other databases on the primary replica. If you need to restrict access to the primary replica itself, you can follow the instruction in this doc Configure isolated access to a Hyperscale named replica. I Please comment below if you face any issues. Regards, Oury

Oury Ba-MSFT 20,931 Reputation points Microsoft Employee Moderator

2023-04-24T16:46:38.76+00:00

Krzysztof Żelazny Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. If the answer was not helpful, please do comment below so we can continue to work on the same. Regards, Oury
Krzysztof Żelazny 20 Reputation points

2023-05-02T09:44:23.3233333+00:00

Thank you for your suggestions, this is however a cumbersome solution.

Shouldn't access via AAD groups work the same way as for SQL and domain accounts ?
Oury Ba-MSFT 20,931 Reputation points Microsoft Employee Moderator

2023-05-03T19:17:08.7533333+00:00

Krzysztof Żelazny

I would say this to improve my answer above.

You can create an AAD login on the primary, not an AAD user in the primary database. Then, create a user for that login in the database. Then, drop this login on the primary but create it on a named replica.

Create and utilize Azure Active Directory server logins - Azure SQL Database | Microsoft Learn

It happened that AAD logins only became available recently (they are still in public preview).

Please do not forget to mark as accept answer if the reply was helpful.

Regards,

Oury
Krzysztof Żelazny 20 Reputation points

2023-05-08T08:17:14.1833333+00:00

If I understand correctly, the only thing I can do at the moment is to try to restrict access to the primary replica using a firewall.

Regards,

Krzysztof
Oury Ba-MSFT 20,931 Reputation points Microsoft Employee Moderator

2023-05-10T17:26:33.4966667+00:00

Krzysztof Żelazny

We noticed your feedback that the above answer was not helpful. Thank you for taking the time to share your feedback.

Kindly let us know what we could have done better to improve the answer and make your engagement experience good.

The AAD logins are still in a public preview with some unsupported AAD area related to SQL/AAD groups that is being worked on. We plan to complete the public preview for this feature including AAD groups very soon.

You can create an AAD login and an AAD user from AAD login on the primary, as well as a login on the secondary (the user will be replicated). You can Deny CONNCT to the login on the primary which will deny logins/users access to the primary but will allow it on the secondary. Unfortunately, the DENY CONNET is not supported for AAD groups as well as for AD groups. Ony users can be part of the DENY CONNECT command.

Firewall will help. Server level firewall rules work regardless of where you implement them i.e., primary vs secondary.

We are looking forward to your reply. Muh appreciate your feedback.!

Regards,

Oury
Oury Ba-MSFT 20,931 Reputation points Microsoft Employee Moderator

2023-05-10T21:14:21.95+00:00

Krzysztof Żelazny

Adding on top of my comment above. This article - Configure named replicas security to allow isolated access - Azure SQL Database | Microsoft Learn now has steps to configure isolated access with Azure AD Authentication updated on 29 March. Please follow the doc again and let me know what challenge you are facing.

Regards,

Oury

Share via

Configure isolated access to a Hyperscale named replica using AAD groups

0 additional answers

Your answer