OK - so I'm attempting to restrict access to SQL Server on a per instance basis based on source hostname. The environment:
Windows Server 2008 R2 Enterprise 64bit
SQL Server 2008 R2 Enterprise 64bit
Multiple layers of NAT'ing (which means I can't use source IP most likely although I'll try it if possible)
So the routing and firewalling in the vCloud UI leaves a lot to be desired and I am unable to control access that way. Each SQL Server has multiple instances (2 Primaries and 1 Mirror that serves both with each Primary serving as Witness for the other). I have multiple App/IIS Servers that each connect to their own assigned SQL instance. The goal is to restrict access per instance so that only its assigned App Server can connect to it (thereby eliminating the possibility of cross connection and someone doing something stupid by making changes in the wrong instance/database(s)).
Someone on another forum suggested firewalling/routing however that is not possible on a virtual platform where those pieces don't function the way they are supposed to so I have to find another option. I could use some sort of blacklist or whitelist in SQL but that appears to not be ideal and requires a lot of T-SQL so it would be difficult for me to do (I'm not a DBA - I'm an Engineer who is a DBA by accident although I will be taking the training and certification next year). Someone else suggested IPSec however that presents its own issues when you try and use Windows Firewall on SQL Servers (and is really unnecessary plus WF just gets in the way half of the time so I avoid it like Bubonic plague).
Is there someone out there who has done this previously or someone who may have a workable option? If it's possible I want to do this to prevent mistakes by Support personnel but if not then I'll have to make sure they all understand that if they screw up it's on their head and I'll be screaming it from the mountain top that they are an idiot (which may be a deterrent unto itself). Any help is VERY much appreciated!