Why does killing a SQL Connection allow a reconnect with SSMS

Question

I would like to kill connections for individuals using certain sql auth logins in SSMS that are reserved for our apps. They should have to go out of their way to reauthenticate into sql and at least feel a little pain for doing something they shouldn't be.

This is the query similar to one I use to identify spids that should be killed. I would take those spids and iterate through and KILL them in the same fashion I have seen in many other posts. I believe my method actually uses a cursor, but that is neither here nor there.

SELECT sp.spid 
FROM sys.sysprocesses sp
JOIN sys.server_principals sl ON sl.sid = sp.sid
WHERE sl.type='S'
AND sp.Program_Name LIKE 'Microsoft SQL Server Management Studio%'
and sp.loginame NOT IN ('pilon','validlogin1','validlogin2') 

I have tried to kill spids that fit this criteria and then all they have to do is run a query and it reconnects. I found this MS Documentation on the ALTER LOGIN command, that would lead me to believe that is the expected behavior, but I have found the opposite.

Is there a different way to go about this than 'KILL 1234'? Is there an additional 'FORCE' command or something that I am not aware of? I have considered using the Resource Governor, but don't want to incur the overhead necessary to make that work.

I have no idea if this is even possible. Any suggestions are welcome. We have considered a shame email as well, but that is a little more in your face than I am after.

Answer 1

I have tried to kill spids that fit this criteria and then all they have to do is run a query and it reconnects.

If a client is allowed to connect, it's allowed to connect. Killing their session or connection won't stop them, as you've found, to be able to connect right back again.

Is there a different way to go about this than 'KILL 1234'?

J.D has posted one way, which is semi-popular in SQL Server but there are better ways with less treacherous pitfalls.

1. Logon Triggers - Already mentioned. Incorrectly done will lock everyone out of the instance until you back-door it and disable the trigger. Best case, your application is chatty/microservice and your performance tanks from the overhead of logon triggers firing. It's also easily to defeat as the comment by SergeyA has stated.
2. Firewalls - No need to even touch SQL Server. You know your application servers, they are probably on a different subnet than the individuals you're seeking to stop. Setup rules to block them. What if they still need to connect and you can't firewall block them to oblivion? See #3.
3. Stop using SQL Logins - Go to using Windows Authentication. Change the applications to use Managed Service Accounts, the individuals won't be able to impersonate them.
4. Multiples - Do #2 and #3, which is arguably baseline security these days.

Answer 2

IMO, the only safe way to avoid individuals to connect to some SQL Server using SSMS is to make sure that they cannot connect in the first place. I.e., don't expose a login name / password combo to those individuals.

If they should be able to connect to the server, but you want them to have less privileges compared to when running your "app", then again don't expose your app's login name / password combo. Use a different login name (possibly using Windows authentication) for them to use when using SSMS.

Reducing or revoking privileges might not be popular. Also, it might require you to re-think your security model and deployment process. For instance only allowing devs access to dev and test environments, but not CT (?) or prod. CT and similar (like performance test environments) might not be clear-cut, where one deciding factor can be whether it contains sensitive data (where as dev and test would have the data through a "washing machine").

Anyhow, it is easy to spoof when you're connecting, since the application name is a connection string attribute.

Below you find screen shots from me connecting to my SQL Server using SSMS, specifying the application name "CRM".

Answer 3

I don't think Resource Governor is relevant here, but you could implement a Logon Trigger that checks the Program_Name for the session that logged in and kill it accordingly, so it's automaticly killed on login, no matter how many times they reconnect:

Logon triggers fire stored procedures in response to a LOGON event. This event is raised when a user session is established with an instance of SQL Server. Logon triggers fire after the authentication phase of logging in finishes, but before the user session is established. Therefore, all messages originating inside the trigger that would typically reach the user, such as error messages and messages from the PRINT statement, are diverted to the SQL Server error log. Logon triggers don't fire if authentication fails.

You can use logon triggers to audit and control server sessions, such as by tracking login activity, restricting logins to SQL Server, or limiting the number of sessions for a specific login.