I am facing an issue with SQL Server 2016, we have an application in windows forms to block multiple logins with the same username.
The logic that we implemented to prevent this is: the first time a user logs in to the application a DB connection will be opened and the connection will not be closed until he logs out.
When the second user connects the application using the same username, we will kill the first connection using the KILL command (this logic is implemented inside a stored procedure) after this when the first user does some DB operation the connection StateChange event will be fired and the application will be closed.
This was working perfectly up to ms SQL Server 2012. Recently we have migrated our application to framework 4.7 and SQL Server 2016 after that we are having this issue. In SQL Server 2016 when we kill the first user connection it will disappear from the SYSPROCESSES table but if the user performs any DB fetching the connection will be reopened automatically (with the same connection id) and the StateChange event will not fire. We are not using any connection pooling. I have tried different combinations of .NET framework and SQL Server (not checked in SQL Server 2014)
Up to SQL Server 2012, it will work in all dot net frameworks
In SQL Server 2016, this will work only if the framework is 3.5.
Connection string used:
Data Source=DBServer;Network Library=DBMSSOCN;Initial Catalog=DBName; User ID=DBUserName; Password=DBPassword; POOLING=false;Trusted_Connection=false;APPLICATION NAME = STATECHANGE_ISSUE
SQL Server 2016 version
Microsoft SQL Server 2016 (SP1-GDR) (KB4019089) - 13.0.4206.0 (X64) Standard Edition (64-bit)
Did anyone face this issue before? Is there any workaround for this in .Net framework 4.7.
