OK - so I'm attempting to restrict access to SQL Server on a per instance basis based on source hostname.  The environment:

First of all, if using sql authentication, the passwords should be different on each instance. That solves the problem right there. I'd use the same usernames though, in case you need to failover from one instance to another. If that will never happen, then usernames should be different too.

If you're using Windows Authentication, just don't allow certain accounts access -- unless you're already using all the same delegated accounts. In which case, have a look at logon triggers. There is some overhead associated with these, but they're easy to create. Just be sure not to lock yourself out with a logon trigger. Even if you do, you could login via DAC and remove/modify the trigger.

Yeah can't do that. Our software uses SQL accounts to connect to the database (as the intermediary for users basically - so users never touch the databases directly). So the login is the same across all instances (so is the SA pw because I can't manage 50+ different SA passwords). So with that said login triggers aren't going to solve the problem. Right now only Tech folks and Domain Admins have access to the "physical" SQL Servers - everyone else uses SSMS from each App Server to connect to the databases. So what I'm attempting to do is more for prevention of stupidity vs actual security necessity.

Why won't login triggers work?

Anyway, what you SHOULD do, in my opinion, is manage the 50+ passwords.

Also, if users are accessing the SQL Servers via SSMS then they should be using Windows Authentication. Leave SQL Authentication for applications. And that, only where necessary.

First, login triggers won't work because all of the instances are silo'd to one or two SQL Servers but provide services to 57 App Servers. Well all of the same people still need access to all of the instances - so why bother with login triggers since it's not going to do what I want which is allow them to only connect to one instance of SQL per App Server? I just want them to have to move to the respective/correct App Server to get to the associated SQL instance/databases.

Second, if I wasn't busy as the platform's architect, engineer, and administrator I would make every sa password different - however SQL Server isn't my only concern. Not to mention then I have to give that SA password out anyways so I have, yet again, defeated the purpose of setting each one different. And yes they are using SQL auth because using windows auth means I then have to closely manage SQL security and for every one of 57 instances (and more as the system continues to grow with new clients) I have to be sure to add their security groups and allow them SA-level access anyway (which then still defeats the purpose). But that still doesn't answer my original question which was how do I restrict access so that only ONE app server can access any ONE specific SQL instance (ie. forcing a one to one relationship so even if they try to access a SQL instance through SSMS that doesn't belong to the App Server they are on they will not be allowed and will be forced to move to the associated App Server - hence then my AD auditing will capture the login and record it so if someone screws up a database or an app server I'll know exactly who it was and when). I wasn't asking a security question and I have no interest in hearing about how to manage my security - what I need to know is how to control access exclusive of security because the access is to be controlled by originating source (ie. even if I could trap traffic at the firewall for port 1433 and then forward it I would need layer 7 routing in there to read the source hostname and even then I would only be able to forward the packet on to the SQL Server and not a specific instance).

Maybe this was the wrong place to ask a question like this.

I must stand corrected. Apparently there is a way to setup a logon trigger to restrict access by hostname (I finally found an article on it although the info is for blocking by app name and hostname not exactly restricting access to only one hostname source). So I need a DBA to explain how this works as I am obviously not a DBA so defer to someone who knows better.

Just check the hostname and then disallow the login. Do you need a code example? I'd be happy to provide it.

If all of your apps and users are logging in as sa, you have a giant problem. I guarentee they don't need it. You need to do a security audit as soon as possible.

I hear ya that you don't think you have time. Maybe time to bring in a short-term consultant to straighten some of these things out for you. At least to make recommendations.

57 instances is enough to require a full-time DBA. I can only begin to guess at all the other issues that are going unaddressed at this point.

EDIT: Oops, two SQL instances, 57 app servers.  My bad.

EDIT AGAIN: Wait, this means you only need to manage TWO sets of passwords...

Nope - 2 SQL Servers + 1 Mirror = 114 SQL named instances (57 App Servers each with an assigned named instance with each instance having a mirror instance) so no I do not have time to manage all of those passwords. I would have to spend all of my time managing security for SQL if I set different SA pw's and also required they use Windows auth with group security assigned SA-level rights. It's too much and they aren't going to hire a DBA - it's been a stretch to get them to pay for the training for me (CISSP, SQL, and such).

With that said yes I would WELCOME some example code that would help me plug in some info and test it with the two systems currently in test mode on the platform - then I could implement it as we go-live. Anything helpful is appreciated. Thanks.

CREATE TRIGGER denyLoginFromHost
ON ALL SERVER
FOR LOGON
AS
BEGIN
IF host_name() <> 'Allowed Host Here' 
  BEGIN
ROLLBACK;
END
END;

Be Careful. One mistake and it's easy to lock yourself out of the server.

Why does each app server connect to its own instance?

Each App Server is isolated for HIPAA reasons - we could install the app to the same server for multiple instances (and multiple websites) but then that becomes a non-standard install. Then, database naming convention is the same across the app - so if you have 50something instances of the app you will have 50something instances of the same database name by default (which, again, could be changed but then it becomes a non-standard install and a PITA to update later). So the easiest method, and most secure, is to ensure each instance is isolated from the others - that way external access is specifically per-instance and data cross-contamination is no longer possible. The final issue there is that if you don't control what SQL instance can be reached from what App Server then Support personnel could make a mistake and change data in the wrong database when assisting a customer with an issue (something that is not unheard of).

So would this work (allowing access from App Server 123456AS01 and the SQL Server where the instance is housed)?:

CREATE TRIGGER denyLoginFromHost
FOR LOGON
AS
BEGIN
IF host_name()<>'123456AS01, SQL01'
BEGIN
ROLLBACK;
END
END;

And where should this be executed? Against the Master for each instance or against each database within the instance? I assume Master but you know what happens when you assume...

THANKS for the help!

Master.

CREATE TRIGGER denyLoginFromHost
FOR LOGON
AS
BEGIN
IF host_name() not in ('123456AS01', 'SQL01')
BEGIN
ROLLBACK;
END
END;

I will test this week. Thanks!!!

You're Welcome. Glad to help.