Restrict login to SQL availability groups

Is it possible to restrict the databases available to a user based on an availability group in SQL ent 2012/2014.

Want to have two databases where a domain user has access right on these databases. Put one in one availability group and the other in another availability group. Have listeners on each with separate ip addresses. Then use a firewall to restrict access to a given IP address based on where the end user is connecting from and hopefully restrict what database they can access based on if they are connecting from one network another ?? Is this possible, my understanding is the default behaviour is the listener will allow the end user to connect to which ever database they have access to and not to be related to the availability group database associated with this listener.

Any guidance on how to achieve this ..

Thanks

Solution 1:

So basically:

User has access to Database A when connected to network A and access to Database B when connected to network B. User should not be able to connect to database B when in network A and vice versa.

When you connect to an AG Listener, you are connected to the SQL instance where the primary location for the AG at this given time is. The security that is applied is the one active on that instance.

The only way I would see this working, is to give the user the rights on both databases (and instances) and than use a firewall to block off access from Network A to the SQL in network B (AG Listener and SQL Listener) and vide versa.

This would work when everything is good, but when your AG fails over, you are not going to be able to access it.

I wouldn't recommend implementing this. My main question is still: Why would you?

I think Availability Groups are not the correct solution here. Just set up 2 SQL Servers, give the appropriate rights and configure your firewall. If you wanted the AGs for DRP/HA, you'll need to refrain from blocking it off.