Restricting number of database connections

[SHelton]

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

We 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 ?

 

[SQLDenis]

you could use sp_configure and RECONFIGURE to set the max user connections
use SELECT @@MAX_CONNECTIONS to see what the number is now

This is on the server level BTW, so if the client uses several Databases then this might not be a good solution


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SHelton]

Thanks for the reply Denis. Yes, the client may well have one or more other databases on the same server so changing the connections maximum would probably not be an option.

 

[fredericofonseca]

hum... I see a issue with this approach.

A connection to your database do not mean they are using your application. So unless your license is on a "usage of the information stored on the database" you should NOT count the number of connections, as that would prevent the users from having a ad-hoc query facility(e.g. Crystal Reports or other) from querying THEIR DATA.

A better way should, if indeed you are not charging by data usage, would be to store connection information on a table (e.g. process id and others), and when entering the system check the number of processes stored on that table, and if they are still active or not. Act upon this.

Other approaches could imply searching the network for other active copies of the application and limit the number of active ones based on that.
Many methods around there for doing this, none of them absolutely bullet proof.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[SHelton]

Thanks Frederico - you are correct of course, running a report would effectively use another "license", which is incorrect in terms of the license.

I had considered keeping a log of users in a table as they started and closed the application. But as I have no control over the management of the SQL Server I was concerned about the DBA removing the entry in order to allow more users to log in. I guess I could encrypt a value in a table to prevent casual "license avoidance"!!!

 

[fredericofonseca]

by all means create a table for this purpose, encrypt the information on it (loads of methods available here), and code in such a way that if you can not access the table or if a control record is not present then you DO NOT allow the program to continue.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com