Suggestions re restricting number of connections please

[SHelton]

Up until now, all the applications I have worked on have been for use within the company, so I have not had to worry about licensing for the app itself. I am now building a commercial application and would like to restrict the number of application users connecting to the database.

I have created a secure license file client side which contains the number of licensed users (e.g. 20), and I was using the following query to check how many users were connected each time the app started:

SELECT COUNT(*) FROM sysprocesses WHERE SUBSTRING(db_name(dbid),1,30) = 'DatabaseName'

This works well, until a user leaves their application running but not doing anything. After a period of time, their process ID drops from sysprocesses and they will not be counted when another user starts their copy of the application.

Is there a way to prevent this, other than checking the number of connected users every time a query is run from the application ? I also can't set the maximum connections value in SQL Server, as the client may have other databases on the same server.

Any thoughts/suggestions gratefully received!!!

 

[B00gyeMan]

Use a table named CLIENTS having two fields: ID (auto increment) and HOST (varchar) which initially has zero records.

Let's say the client limit is MAX_CLIENTS = 20.

Upon startup, the application will check that table to see if the machine it is running on is registered in that table. If yes, it continues normal startup. If no, it checks to see if the number of records in that table is less than MAX_CLIENTS. If yes, it registers (adds) itself into that table and continues normal startup. If the number of clients in that table is equal to MAX_CLIENTS it shows a dialog specifying that the client limit has been reached and then exits gracefully.

This will allow you to control the max number of applications that can be used (simultaneously or not).

You can write the IP of the machine in the HOST field, the fully qualified host name (helps if the company uses DHCP) or the MAC address if it suits your needs.

The actual checking of the number of clients in the table and the adding of the new client should be done inside a stored procedure and using table locks because if 2 applications have started simultaneously and both have checked the table and there are only 19 items, they will both register and you will have 21 clients .

 

[SHelton]

Thanks for the response, it's a really good suggestion.

I need to allow for the app being hosted on TS/Citrix, so I guess I should add a username field because the IP address or host name will be the same for all users. I can then compare the combination of the two fields.

The only downside I can see is that if the application fails to close correctly (not that it will ever crash, I'm talking an OS problem here ) then the user/machine combination will still be consuming a license. But I can educate the customer that in the event of a problem they only need to start and close the app to clear the 'license'.

 

[B00gyeMan]

Application crashing is a problem only if you want to limit the number of concurrent running applications. My suggestion was to limit the total number of clients which are allowed to use the application. This way you wouldn't need to remove the record when the application closes. And you shouldn't have to worry when the database connection is lost...
But the licensing issues are according to your own policies.