hooking into sql server file I/O -- file level encryption

There are a number of third party packages that do "whole file"
encryption of SQL databases. For example:
Encryptionizer (http://www.netlib.com/)
XP_CRYPT (http://www.xpcrypt.com/)
We are considering using one of these products, but I am wondering if
it is possible to "roll our own". We have some experience doing this
with sqlite; because sqlite is open source, it is relatively easy to
hook into the "pager" and perform custom encryption/decryption.
What I'd like to know is whether something similar is possible with
SQL Server (or more specifically MSDE). Or rather, clearly it is
_possible_, since the above products do it. But how? How do these
products insert themselves between the database engine and the
physical file I/O?
I speculate that they are re-implementing some file IO interfaces used
by SQL, but I don't know where to start looking for what those
interfaces might be or where they live in the registry. Or maybe I am
off base and there are some hooks provided by SQL I don't know about.
I have scoured MSDN and Google but it appears to be a difficult topic
to query.
Does anyone have any idea how this works?
TIA,
Rol(rolandp66@.yahoo.com) writes:
> There are a number of third party packages that do "whole file"
> encryption of SQL databases. For example:
> Encryptionizer (http://www.netlib.com/)
> XP_CRYPT (http://www.xpcrypt.com/)
> We are considering using one of these products, but I am wondering if
> it is possible to "roll our own". We have some experience doing this
> with sqlite; because sqlite is open source, it is relatively easy to
> hook into the "pager" and perform custom encryption/decryption.
> What I'd like to know is whether something similar is possible with
> SQL Server (or more specifically MSDE). Or rather, clearly it is
> _possible_, since the above products do it. But how? How do these
> products insert themselves between the database engine and the
> physical file I/O?
> I speculate that they are re-implementing some file IO interfaces used
> by SQL, but I don't know where to start looking for what those
> interfaces might be or where they live in the registry. Or maybe I am
> off base and there are some hooks provided by SQL I don't know about.
> I have scoured MSDN and Google but it appears to be a difficult topic
> to query.
The way to do it would indeed be to intercept the file I/O system calls.
Since SQL Server uses the interfaces in NTFS, the tool would have to
be configured to only encrypt selected files, since else you would be
encrypting - and deenscrypting - the entire disk.
Whether this actually is doable I don't know. You would have to ask in
a Windows newsgroup to get some tips on that. But I would assume that
whatever you do, it would be entirely unsupported.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I may be missing something basic here but wouldn't it be a lot easier to
just use NTFS encryption to encrypt the files? One checkbox in the file
attributes seems a lot easier than writing your own disk driver.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<rolandp66@.yahoo.com> wrote in message
news:1175719135.450235.272090@.b75g2000hsg.googlegroups.com...
> There are a number of third party packages that do "whole file"
> encryption of SQL databases. For example:
> Encryptionizer (http://www.netlib.com/)
> XP_CRYPT (http://www.xpcrypt.com/)
> We are considering using one of these products, but I am wondering if
> it is possible to "roll our own". We have some experience doing this
> with sqlite; because sqlite is open source, it is relatively easy to
> hook into the "pager" and perform custom encryption/decryption.
> What I'd like to know is whether something similar is possible with
> SQL Server (or more specifically MSDE). Or rather, clearly it is
> _possible_, since the above products do it. But how? How do these
> products insert themselves between the database engine and the
> physical file I/O?
> I speculate that they are re-implementing some file IO interfaces used
> by SQL, but I don't know where to start looking for what those
> interfaces might be or where they live in the registry. Or maybe I am
> off base and there are some hooks provided by SQL I don't know about.
> I have scoured MSDN and Google but it appears to be a difficult topic
> to query.
> Does anyone have any idea how this works?
> TIA,
> Rol
>|||Roger Wolter[MSFT] (rwolter@.online.microsoft.com) writes:
> I may be missing something basic here but wouldn't it be a lot easier to
> just use NTFS encryption to encrypt the files? One checkbox in the file
> attributes seems a lot easier than writing your own disk driver.
Just for fun, I stopped my SQL 2000 instance and then encrypted the
Northwind database from Explorer. When I started SQL Server, I found that
I was not able to access Northwind. This was what the SQL Server had to
tell me:
2007-04-07 11:10:32.17 spid9 Device activation error. The physical file
name 'E:\Program Files\Microsoft SQL Server\MSSQL\data\northwnd.mdf' may be
incorrect.
2007-04-07 11:10:32.31 spid9 udopen: Operating system error 5(error not
found) during the creation/opening of physical device E:\Program Files\Micro
soft SQL Server\MSSQL\data\northwnd.ldf.
2007-04-07 11:10:32.31 spid9 FCB::Open failed: Could not open device E:\
Program Files\Microsoft SQL Server\MSSQL\data\northwnd.ldf for virtual devic
e number (VDN) 2.
Now, I know zero about NTFS encryption, so there may be a simple solution,
but I figured that I should make this note, in case someone would get the
idea to try this for real.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The key is associated with the user who does the encrypting so if you're not
encrypting while logged in as the domain user that sqlservr.exe executes as,
SQL won't be able to read the file.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns990B72C517060Yazorman@.127.0.0.1...
> Roger Wolter[MSFT] (rwolter@.online.microsoft.com) writes:
> Just for fun, I stopped my SQL 2000 instance and then encrypted the
> Northwind database from Explorer. When I started SQL Server, I found that
> I was not able to access Northwind. This was what the SQL Server had to
> tell me:
> 2007-04-07 11:10:32.17 spid9 Device activation error. The physical
> file name 'E:\Program Files\Microsoft SQL Server\MSSQL\data\northwnd.mdf'
> may be incorrect.
> 2007-04-07 11:10:32.31 spid9 udopen: Operating system error 5(error
> not found) during the creation/opening of physical device E:\Program
> Files\Microsoft SQL Server\MSSQL\data\northwnd.ldf.
> 2007-04-07 11:10:32.31 spid9 FCB::Open failed: Could not open device
> E:\Program Files\Microsoft SQL Server\MSSQL\data\northwnd.ldf for virtual
> device number (VDN) 2.
>
> Now, I know zero about NTFS encryption, so there may be a simple solution,
> but I figured that I should make this note, in case someone would get the
> idea to try this for real.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Roger Wolter[MSFT] (rwolter@.online.microsoft.com) writes:
> The key is associated with the user who does the encrypting so if you're
> not encrypting while logged in as the domain user that sqlservr.exe
> executes as, SQL won't be able to read the file.
I suspected it was something like that, but I didn't feel like testing it.
Thanks for straighten it out, Roger.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql