Locking a database from a non paying client

[Pat]

Has anyone got any ideas on this?
I need to lock out a client from using a database until they make the final payment.

[HansV]

Can you tell us something about the setup?

• Is the database split into a frontend and backend?
• If so:
  • Does each user have an individual copy of the frontend?
  • Is the backend an Access database or a SQL Server database?
  • Do you manage the backend database, or does the client do this?
• How is access to the database currently managed?
  • It's not secured.
  • It's secured by a database-level password in Access.
  • It's secured by user-level security in Access.
  • (Only if there is a SQL Server backend) It's secured by controlling access to SQL Server.
• Any other relevant information?

[Pat]

DB is split into FE/BE.
Each user has a copy.
BE is access.
Q. what do you mean by "Do you manage the backend database, or does the client do this?"?
It's not secured, the only security is that selected users cannot see money fields.

[HansV]

By "Do you manage the backend database, or does the client do this?", I meant: is the backend located on a server that you have access to, so that you can modify, lock or move it, or do you depend on the client for modifying it?

[JohnH]

So the database is already in use, and is, I assume, all physically located on the client's computers.So you only get access to the database with the client's consent?
If the client is refusing to pay then I can't imagine he is going to let you go into the database and change something.

[Pat]

The BE is on a server.
The question was, is there anyway to lock database so a user cannot use it, i have another app where clients will pay a monthly fee to use it.
Thanks for looking at this Hans.

[HansV]

As John indicates: if the database is located on a computer owned and controlled by the client, you'd have to get permission from the client to modify it. If they don't want to pay that seems unlikely.

[Pat]

and thanks to John too.
Any ideas on the client who pays a monthly fee to use it?

[HansV]

I guess you'd have to put a code on a webserver somewhere, and the database would have to connect to the Internet to check whether it is still allowed to operate, but the technical aspects of that are far beyond me. Sorry.

[Pat]

Thanks anyway Hans

[JohnH]

Any ideas on the client who pays a monthly fee to use it?

Presumably you can still make changes to this db.
It seems to me that you need an expiry date in the database, a process for updating the date and code to check the Expiry Date agains the current date.
I don't see any great problem with that..but it is a bit of a nuisance to have to do it every month.

Perhaps you could email them a code everytime they make a payment. They enter that code into a form, and the expiry is updated. or perhaps the code is just stored, and the expiry date is calculated from the code when needed.

[Pat]

Thanks Hans, I have been thinking along those lines, where would you put the date, in a hidden table, in say a field that is a password so people wouldn't be able to see the value?
What code of code did you have in mind?

[JohnH]

Pat it was John that wrote the last reply.
I think the Expiry Date needs to be in a table rather than hidden in code. Much easier to update.
Yes I think I would hide the table.
Dates are just numbers so it would be easy to do some arithmetic on the date to hide the real date.
For example if you multiplied by 13 then added two random letters on the end.
Today would be 532649FJ. You could store that as a piece of text.

[Pat]

Sorry John, I must be going senile.
Nice idea John.
I have thought that i would also store that field as a password to further hide it.

[Mark L]

Has anyone got any ideas on this?
I need to lock out a client from using a database until they make the final payment.

Kind of late to be thinking about this, isn't it?

I think best approach is to have some sort of authentication via internet. A simple passthrough query to a SQL Server could return a date or code. Perhaps have it set to require the authentication every month, so it doesn't have to happen each time a user opens the database.

[Pat]

You are right, it is late to be thinking about this, i guess trust only goes so far.
Would you further explain what you said there.

[Mark L]

I don't have an example to cite, I was merely suggesting a method.

Ideally, this needs to be fairly automatic, which is why I was suggesting a way for client to check permission without bothering you.

All you need is a "Client" table in a SQL Server database. The client record would have an expiration date and an authentication code; this code would be something that uses a formula to convert the date to a number or string of characters (which your database can confirm). You only need to have a passthrough query (establish a user with read-only permission) to get this info and store it locally. The user doesn't need to connect to server every time, it can just check the locally stored values; if date() <= expiration date AND if you can validate the authentication code, then the user can use database.

The advantage of this is that you can set the expiration date for any date in the future, depending on the situation! If it is being tested prior to final payment, set it to 2 weeks. Or set it to end of this month, end of this year, etc.

[Pat]

All you need is a "Client" table in a SQL Server database. The client record would have an expiration date and an authentication code; this code would be something that uses a formula to convert the date to a number or string of characters (which your database can confirm). You only need to have a passthrough query (establish a user with read-only permission) to get this info and store it locally. The user doesn't need to connect to server every time, it can just check the locally stored values; if date() <= expiration date AND if you can validate the authentication code, then the user can use database.

Bearing in mind that this is an Access Fe/BE where does SQL Server come into this?

[HansV]

I think Mark means a SQL Server database on a server controlled by you.
The frontend database would periodically connect to the SQL Server database and look for a valid authentication code; if this fails, the frontend shuts down.

[Pat]

Where is the SQL Server database, on the developers server or on the clients server?
If this code to check for a valid code is in Access VBA then they could disable it, so i presume that MDE would be the answer there.
I think the gate is well and truly closed on this particular customer.