encryption--the sequel

Hello:
Earlier this week, I had posed a question on encryption and certificates. A
very nice person gave me two blogs to read. I appreciated that and it was
very helpful. Now, I just want to make sure that I have the order down pat.
Could someone please review the order of data that I have below and let me
know if I have this right? Especially, please let me know if I am correct i
n
the order on number 5 and number 6. Thanks!!! Here's my list for your
review:
--create a master key
--create a certificate
--create a symmetric key
--create a stored procedure that encrypts the data with the certificate
--grant the ALTER ANY SYMMETRIC KEY permissions the the user
--create two stored procedures to use the certificate to open the symmetric
key
childofthe1980sHi
Laurentiu's blog gives you everything you need to know about this.
If you want to give a user access to the keys then you can do the following,
this is taken by stripping out the actions for Doc1 in
http://blogs.msdn.com/lcris/archive.../16/504692.aspx :
Create database master key (if not already done so)
Create certificate to encrypt symmetric key with authorisation to userX
Create symmetric key encrypted by certificate
Grant view definition permissions to view key by userX
Create procedure to open symmetric key and encrypt/decrypt using symmetric k
ey
Grant permissions to execute procedure to userX
If you want to restrict the user to only decrypt or encrypt then you can
sign the procedure with a second certificate see
http://blogs.msdn.com/lcris/archive.../13/512829.aspx :
Create database master key (if not already done so)
Create first certificate to encrypt symmetric key
Create symmetric key encrypted by first certificate
Create procedure to open symmetric key and encrypt/decrypt using symmetric k
ey
Create second certificate to sign code
Create user mapped to the second certificate
Grant view definition permission to first symmetric key to second certificat
e
Grant control on first certificate to second certificate
Sign (add signature) the procedure with second certificate
Grant permissions to execute procedure to userX
John
"childofthe1980s" wrote:

> Hello:
> Earlier this week, I had posed a question on encryption and certificates.
A
> very nice person gave me two blogs to read. I appreciated that and it was
> very helpful. Now, I just want to make sure that I have the order down pa
t.
> Could someone please review the order of data that I have below and let me
> know if I have this right? Especially, please let me know if I am correct
in
> the order on number 5 and number 6. Thanks!!! Here's my list for your
> review:
> --create a master key
> --create a certificate
> --create a symmetric key
> --create a stored procedure that encrypts the data with the certificate
> --grant the ALTER ANY SYMMETRIC KEY permissions the the user
> --create two stored procedures to use the certificate to open the symmetr
ic
> key
> childofthe1980ssql

encryption--the sequel

Hello:
Earlier this week, I had posed a question on encryption and certificates. A
very nice person gave me two blogs to read. I appreciated that and it was
very helpful. Now, I just want to make sure that I have the order down pat.
Could someone please review the order of data that I have below and let me
know if I have this right? Especially, please let me know if I am correct in
the order on number 5 and number 6. Thanks!!! Here's my list for your
review:
--create a master key
--create a certificate
--create a symmetric key
--create a stored procedure that encrypts the data with the certificate
--grant the ALTER ANY SYMMETRIC KEY permissions the the user
--create two stored procedures to use the certificate to open the symmetric
key
childofthe1980sHi
Laurentiu's blog gives you everything you need to know about this.
If you want to give a user access to the keys then you can do the following,
this is taken by stripping out the actions for Doc1 in
http://blogs.msdn.com/lcris/archive/2005/12/16/504692.aspx :
Create database master key (if not already done so)
Create certificate to encrypt symmetric key with authorisation to userX
Create symmetric key encrypted by certificate
Grant view definition permissions to view key by userX
Create procedure to open symmetric key and encrypt/decrypt using symmetric key
Grant permissions to execute procedure to userX
If you want to restrict the user to only decrypt or encrypt then you can
sign the procedure with a second certificate see
http://blogs.msdn.com/lcris/archive/2006/01/13/512829.aspx :
Create database master key (if not already done so)
Create first certificate to encrypt symmetric key
Create symmetric key encrypted by first certificate
Create procedure to open symmetric key and encrypt/decrypt using symmetric key
Create second certificate to sign code
Create user mapped to the second certificate
Grant view definition permission to first symmetric key to second certificate
Grant control on first certificate to second certificate
Sign (add signature) the procedure with second certificate
Grant permissions to execute procedure to userX
John
"childofthe1980s" wrote:
> Hello:
> Earlier this week, I had posed a question on encryption and certificates. A
> very nice person gave me two blogs to read. I appreciated that and it was
> very helpful. Now, I just want to make sure that I have the order down pat.
> Could someone please review the order of data that I have below and let me
> know if I have this right? Especially, please let me know if I am correct in
> the order on number 5 and number 6. Thanks!!! Here's my list for your
> review:
> --create a master key
> --create a certificate
> --create a symmetric key
> --create a stored procedure that encrypts the data with the certificate
> --grant the ALTER ANY SYMMETRIC KEY permissions the the user
> --create two stored procedures to use the certificate to open the symmetric
> key
> childofthe1980s

encryption--the sequel

Hello:
Earlier this week, I had posed a question on encryption and certificates. A
very nice person gave me two blogs to read. I appreciated that and it was
very helpful. Now, I just want to make sure that I have the order down pat.
Could someone please review the order of data that I have below and let me
know if I have this right? Especially, please let me know if I am correct in
the order on number 5 and number 6. Thanks!!! Here's my list for your
review:
--create a master key
--create a certificate
--create a symmetric key
--create a stored procedure that encrypts the data with the certificate
--grant the ALTER ANY SYMMETRIC KEY permissions the the user
--create two stored procedures to use the certificate to open the symmetric
key
childofthe1980s
Hi
Laurentiu's blog gives you everything you need to know about this.
If you want to give a user access to the keys then you can do the following,
this is taken by stripping out the actions for Doc1 in
http://blogs.msdn.com/lcris/archive/2005/12/16/504692.aspx :
Create database master key (if not already done so)
Create certificate to encrypt symmetric key with authorisation to userX
Create symmetric key encrypted by certificate
Grant view definition permissions to view key by userX
Create procedure to open symmetric key and encrypt/decrypt using symmetric key
Grant permissions to execute procedure to userX
If you want to restrict the user to only decrypt or encrypt then you can
sign the procedure with a second certificate see
http://blogs.msdn.com/lcris/archive/2006/01/13/512829.aspx :
Create database master key (if not already done so)
Create first certificate to encrypt symmetric key
Create symmetric key encrypted by first certificate
Create procedure to open symmetric key and encrypt/decrypt using symmetric key
Create second certificate to sign code
Create user mapped to the second certificate
Grant view definition permission to first symmetric key to second certificate
Grant control on first certificate to second certificate
Sign (add signature) the procedure with second certificate
Grant permissions to execute procedure to userX
John
"childofthe1980s" wrote:

> Hello:
> Earlier this week, I had posed a question on encryption and certificates. A
> very nice person gave me two blogs to read. I appreciated that and it was
> very helpful. Now, I just want to make sure that I have the order down pat.
> Could someone please review the order of data that I have below and let me
> know if I have this right? Especially, please let me know if I am correct in
> the order on number 5 and number 6. Thanks!!! Here's my list for your
> review:
> --create a master key
> --create a certificate
> --create a symmetric key
> --create a stored procedure that encrypts the data with the certificate
> --grant the ALTER ANY SYMMETRIC KEY permissions the the user
> --create two stored procedures to use the certificate to open the symmetric
> key
> childofthe1980s

Encryption; SQL Server 2005 & Windows 2003 Server

Any further input would be appreciated ...
Pro EFS:
Indexs, Primary Keys, Foreign Keys, DEFAULTS, CHECK CONSTRAINTS are preserve
d.
Databases modifications need not consider Encryption.
Patterns & Practices
http://msdn.microsoft.com/library/d...
h05.asp
http://msdn.microsoft.com/library/d...r />
MCh18.asp
http://msdn.microsoft.com/library/d.../>
SecDBSe.asp
Other Technical Articles
http://www.microsoft.com/technet/pr...0.mspx?mfr=true
http://www.microsoft.com/technet/pr...fr=
true
http://www.microsoft.com/technet/ar...n/sp3sec02.mspx
http://www.microsoft.com/technet/pr...n/sp3sec04.mspx
http://www.microsoft.com/technet/pr...y/sqlorcle.mspx
http://www.microsoft.com/technet/se...phyetc/efs.mspx
http://www.sqlservercentral.com/col...menting_efs.asp
http://www.microsoft.com/technet/pr...5/multisec.mspx
http://www.akadia.com/services/sqls...l#_Toc513865376
http://www.sans.org/top20/2002/mssql_checklist.pdf
Case Study
http://www.microsoft.com/canada/cas...worksafebc.mspx
Anti-EFS:
1. If the file is not created in an Encrypted Directory the temporary fil
e
created by EFS during encryption remains in clear-text and is
vulnerable.
a) cipher.exe /W must be used to Wipe the temporary file.
2. EFS will not function in a Clustered Environment.
3. If the Server crashes when an Encrypted File is open the pagefile.sys
will
contain vulnerable clear-text of the Encrypted File on restart.
4. The Windows Administrator(s) can "Set Password ..." of the Key Owner
and the Key Owner will not be able to access the data.
5. If the Key Owner does not specify a Data Recovery Agent (DRA) AND does
not backup the PKI the data might become inaccessible under
circumstances
such as "4." above.
6. Encrypted Files cannot be backed up to non-NTFS devices except with
Windows
Backup utilities.
7. Extra steps must be taken over-and-above conventional SQL Server Backu
p,
Recovery and Disaster Recovery procedures.
8. The Windows Administrator can access the (otherwise) encrypted data if
SQL Server "BUILTIN\Administrators" is not removed.
9. The Database *.mdf & *.ldf files cannot be moved between domains and
retain
the Encrypted Attribute.
10. Stealing a local account password is easy using common hacker tools in
standalone mode.
11. Encrypted files stored on file servers are decrypted on the server
and then
transported in clear text across the network to the user's workstation.
Because EFS needs access to the user's private key, which is held in
the
profile, the server must be "trusted for delegation" and have access to
the user's local profile.
a) Requires IPSec to secure the file transfer between file server
and
user machine.
12. "The EnCase EFS Module provides Encrypting File System (EFS) folder a
nd
file decryption capabilities, for locally authenticated users."
(http://www.digitalintelligence.com/...oftware/encase/)"ITContractor" <ITContractor@.discussions.microsoft.com> wrote in message
news:16785415-7A45-4031-A599-86896233DC15@.microsoft.com...
> Any further input would be appreciated ...
> Pro EFS:
>
. . .
> 8. The Windows Administrator can access the (otherwise) encrypted data
> if
> SQL Server "BUILTIN\Administrators" is not removed.
Although this is true, removing the BUILTIN\Administrators account is no
protection against a Windows admin. A windows admin on the box can shut
down SQL Server, replace the Master database, restart and attach your
encrypted database. Or just restart the instance in single-user mode.
David