SQL Server High Availability – Avoid Direct User Login to AlwaysOn Primary

availability-groupshigh-availabilitysql server

I have setup an AlwaysOn High Availability in Sql Server 2014.
my IP addresses are according to this :

192.168.1.189  -- Listener
192.168.1.180  -- Replica
192.168.1.181  -- Primary

Right now users can login to my Primary directly and I want to avoid this.
Is it possible ?

Best Answer

There are a few questions that are similar to what you are asking, may be board-line duplicate but either way:

That second question listed is probably the duplicate of this one.

Overall if you are looking at ensuring logins can only access via the listener you are going to have problems trying to use a login trigger. I am not sure you can pick up DNS or IP address it used to actually connect to SQL Server. If it can then it would be a viable option.

I would opt to use your firewall or network infrastructure. A firewall can be much more efficient and easier to manage with blocking access to either the IP or access from an IP range to a specific IP.