I have written a VB/SQL Server based application. The application uses stored procs and the SQL Server security model to handle logins and permissions. My partner and I want to sell the applicaiton to specific businesses and offer a rental program for smaller companies that would like to use the application to connect to an internet based database. The application uses TCP/IP with a specific port number to connect to the database so it can be used in or out of LAN.
If we rent the application, maintenance is simple. I just create a user in the database with the login/password the user would like and viola they have access to the data.
When a company wants to purchase the program to use in-house, we would like to offer two alternatives: #1 a single user or #2 a multiple user SQL Server database.
My question is if a company purchases the single-user version, how can we keep them from adding users to their database. In other words, since we have no control over the database at the client's location, how can we ensure that it remains a single-user database?
Refuse to run if the database isn't in single user mode? This is a drastic step, since it means that you can't open multiple threads (spids) into the database yourself, so you can't use most reporting tools, etc. Many SQL DBAs won't support apps like this because they effectively rendeder the server unmanagable.
A better solution might be to insist on a particular (hardcoded) username for the single user version of your application to run. When the app starts, look for any other instances of spids using that name, and fail gracefully if they are found. This would allow you to restrict one running copy of the app at a time, but still allow considerable freedom of use for your customers.
A more flexible solution would be to create a single table that could have a given set of key values in the rows. When an instance of the app connects, it verifies that there is a free key, and marks that with its own spid. By having the valid key values be derived from something else (app or server serial number?), you prevent the users from sharing keys, but you make it possible to license the app by seat, possibly even by date range (although then they can start to tinker with the server dates).
There are more ways to skin a cat than there are cats. You just need to find the one that works for your application.