Thanks Stephen

That does not answer my question though. But it is illustrative of how other companies want to hide sensitive data from snooping eyes (government, nosy employees, competitors, etc) In my client's case, it is about not sharing confidential sales data, building plans and management salaries with the local staff Some of their employees can steal this information and sell it to outsiders

And yes, the databases are on a desk PC

My problem is to determine why the data persists in memory and how to prevent it

Rafael

http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail Libre de virus. www.avg.com http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail <#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

2017-11-10 16:33 GMT-03:00 Rafael Copquin rafael.copquin@gmail.com:

One of my clients uses a VFP9 app with MSSQL SERVER databases

Two databases

 database A  stored in the hard disk  (C:)

 databaseB (with confidential data) stored in an external hard disk

(E:)

The Sql Server Management Studio correctly shows, in each database properties windows, in the files option, that the A database resides in disk C and the B database resides in disk E

However, if I physically remove the hard disk (E:), the management studio still shows all tables of database B and select statements show data from the applicable tables.

I thought that the data resides in memory in the cache. So I disconnected the SSMS from the engine, even turned off the SSMS. Then, when the SSMS was up again, it would still show the database B (even if its lodging HD was disconnected and put away in a drawer) and queries would still show data from any of the tables.

What is going on?

How can I prevent this "ghost" behaviour?

It is very important that, when the external hard disk is removed, no confidential data can be read at all.

Rafael Copquin

---

Este correo electrónico ha sido comprobado en busca de virus por AVG. http://www.avg.com

[excessive quoting removed by server]

However, if I physically remove the hard disk (E:), the management studio still shows all tables of database B and select statements show data from the applicable tables.

I thought that the data resides in memory in the cache. So I disconnected the SSMS from the engine, even turned off the SSMS. Then, when the SSMS was up again, it would still show the database B (even if its lodging HD was disconnected and put away in a drawer) and queries would still show data from any of the tables.

SSMS has nothing to do with how SQL Server caches data. It's merely a client that keeps a connection open. All caching is done in the SQL Server service. If you want to clear that cache, you need to restart the service. However, let's be clear here: Putting a database file on another drive isn't increasing security for a variety of reasons.

First of all, as you've noticed, SQL server itself operates ONLY from memory. There's another component involved (called the SQL reader) that is responsible for getting any missing pages of a SQL server database into memory if SQL server needs those. It's not like in VFP where memory is used to increase performance.

Second, SQL server persists data into other databases, mainly the tempdb. Any data from database B can end up written to tempdb at any time. tempdb is located on the C: drive by default, unless you changed this during SQL server installation.

Third, if you fear that employees can access drive C: on the SQL server (to which only admins should have access anyway), then they can also access drive E:, either physically or by using the physical access to C: to get into the computer and access E: remotely.

Now, to answer your question... In order to completely remove data from the SQL server you have to follow these steps:

1) in SSMS *detach* database B. 2) Shut down the SQL Server instance 3) Remove drive E: safely 4) Restart the SQL server instance

This will, of course, kick all users out of the application and cancel any running transaction. In addition you should at least have one encrypted database attached to SQL server as this will cause SQL server encrypt tempdb, as well. This will, depending on your hardware, slow down operations, though. Without encrypting tempdb, you still risk data from B being available on drive C:

Seriously, though: I'd put database B on a second SQL server and instead of taking the hard disk with me, grab the whole server. This would require a second connection in your VFP application, but it's more secure than trying to purge any trace from the first server. The whole environment sounds like any inexpensive small computer would actually be sufficient for this like an Intel NUC with an SSD.