September 10, 2007 at 8:08 am
My company deploys laptops for use by staff in public. These run a non-SQL Server database that holds personal health-related data, so we use a Third-Party application to provide an encrypted 'drive': the user has to key in a password to mount this before running the database.
We are adding SQL Server 2005 Express to these laptops, initially to run a large read-only database that need not be encrypted, potentially to add a database with personal data that will need securing. I know SQL Server 2005 has encryption options, but my first approach is to try use the encrypted partition that works now.
I have already found that
So I can secure the user database, and we already deal with other "temp" etc files left behind when a laptop is shut down. My concern is whether there is readable user data left in tempdb on the unencrypted C: drive, that might be accessed if the laptop were stolen. SQL Server "recreates" tempdb when it starts up; but do I need some way to clean up tempdb when SQL Server closes? Has anyone experience or advice about using an encrypted drive with SQL Server?
September 10, 2007 at 9:53 pm
I believe that anything in tempdb will be on the disk only when SQL Server experiences a dirty shutdown (beter know as crash). If you look at the C: drive after you shut things down normally you should find the mdf and ldf files on disk. My guess is that the mds will have no usable data but the ldf might have something salvagable. So the short answer is 'yes' you probably have some sort of risk, how much is the question. A little testing and usage of a healthy text editor will confirm this.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
September 12, 2007 at 3:21 am
Has your company looked at boot-level encryption tools such as Pointsec? The organisation I work for uses Pointsec on all company laptops. You need to supply a password at boot time to get anywhere, and there are good admin facilities to deal with forgotten passwords, etc.
Anything that is installed on top of Pointsec (e.g. Windows...) neither knows or cares that the drive is encrypted. Things run a bit slower than if the drive was plaintext, but if the laptop ever goes AWOL and the drive is removed all that anyone could see is a collection of 1s and 0s.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
September 12, 2007 at 4:01 am
Thanks to both:
Rudy, I will look into (i) flushing tempdb somehow when closing SQL Server (maybe just a restart followed by shutdown, to leave it fresh and empty - the words Quick and Dirty come to mind but, hey, if it works...) and (ii) the alternative of using SQL Server to encrypt tempdb.
Ed, thanks for the suggestion: apparently we looked at Pointsec, but our specific need to replicate removable laptop drives from a master ruled out methods that override the NTFS structure. But I will flag up Pointsec as a possible general-purpose solution for personal laptops.
September 12, 2007 at 8:38 am
You could just delete the tempdb mdf and ldf files ... but that opens up another can of worms ... you have to clean out the deleted files folder too ... then someone could use some type of file recovery software ... then ...
Well it just goes to show you that where there is a will there is a way ...
or ...
"locks only keep honest people out"
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply