Data encyption using symmetric keys outside SQL Server

Hello. I have a problem that spans VB.net, SQL Server and SSIS but is rooted in the need to encrypt column data in SQL Server.

I would like to encrypt data that I am bringing into SQL Server in the Data transformation script component of an SSIS package. I have achieved this but I can't decrypt the data because the keys don't match. I would like to use symmetric key encryption but I don't see how to get the symmetric key that I created in SQL Server available to the VB.net script component in SSIS.

Please advise me if my approach is correct and what steps I need to take.

Importing or exporting key material for SYMMETRIC KEYs is not supported in SQL Server 2005. SYMMETRIC KEY material is always encrypted in the database and we don’t have any access point where we display such material in an unprotected form for security reasons, because of this SYMMETRIC KEYS as well as ciphertext created by EncryptByKey are only meant to be consumed by SQL Server.

-Raul Garcia

SDE/T

SQL Server Engine

|||Thank you for the response. I suspected as much for the very reasons you mentioned.
I did some work on asymetric keys but wasn't successful. Can you tell me the correct strategy to expose the public key so I can use it to encrypt within the SSIS package.|||

Here is a link that should be useful. In this link the author was also using ASYMMETRIC KEYS in SQL Server and VB .Net:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=384472&SiteID=1

I hope this information will be useful,but let us know if there is anything else we can do to help.

-Raul Garcia

SDE/T

SQL Server Engine

data encryption in SQL Server 2005 - protect from SQL Admnis

I need to store some sensitive data in SQL 2005.
Stored procedures will encrypt & decrypt the data. The client app is written
in .NEt using a specific user (belonging to a specific - custom role).
However, inspite of the above, the local Admin can always view the code in
the decription stored procedure & decrypt & hence view the data.
How can i prevent the administrator (everyone) except for the application
from being able to view the data.
Is it possible to remove access to a stored procedure even from an
administrator & give access to a special user (the password of which is know
only by the application)'
Then again the owner of the above role will have access to the stored
procedures!!This is a good backgrounder on the topic:
http://blogs.msdn.com/lcris/archive/2006/11/30/who-needs-encryption.aspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Don" <Don@.discussions.microsoft.com> wrote in message
news:A25E337B-AA5C-456B-95AD-E4D2F36D4B0A@.microsoft.com...
>I need to store some sensitive data in SQL 2005.
> Stored procedures will encrypt & decrypt the data. The client app is written
> in .NEt using a specific user (belonging to a specific - custom role).
> However, inspite of the above, the local Admin can always view the code in
> the decription stored procedure & decrypt & hence view the data.
> How can i prevent the administrator (everyone) except for the application
> from being able to view the data.
> Is it possible to remove access to a stored procedure even from an
> administrator & give access to a special user (the password of which is know
> only by the application)'
> Then again the owner of the above role will have access to the stored
> procedures!!

data encryption in SQL Server 2005 - protect from SQL Admnis

I need to store some sensitive data in SQL 2005.
Stored procedures will encrypt & decrypt the data. The client app is written
in .NEt using a specific user (belonging to a specific - custom role).
However, inspite of the above, the local Admin can always view the code in
the decription stored procedure & decrypt & hence view the data.
How can i prevent the administrator (everyone) except for the application
from being able to view the data.
Is it possible to remove access to a stored procedure even from an
administrator & give access to a special user (the password of which is know
only by the application)'
Then again the owner of the above role will have access to the stored
procedures!!This is a good backgrounder on the topic:
http://blogs.msdn.com/lcris/archive...encryption.aspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Don" <Don@.discussions.microsoft.com> wrote in message
news:A25E337B-AA5C-456B-95AD-E4D2F36D4B0A@.microsoft.com...
>I need to store some sensitive data in SQL 2005.
> Stored procedures will encrypt & decrypt the data. The client app is writt
en
> in .NEt using a specific user (belonging to a specific - custom role).
> However, inspite of the above, the local Admin can always view the code in
> the decription stored procedure & decrypt & hence view the data.
> How can i prevent the administrator (everyone) except for the application
> from being able to view the data.
> Is it possible to remove access to a stored procedure even from an
> administrator & give access to a special user (the password of which is kn
ow
> only by the application)'
> Then again the owner of the above role will have access to the stored
> procedures!!

Data encryption and keys

Hi,
I would like to encrypt data in my database. I want encrypted column value to be viewable only for certain group of users. Users that has access to my database doesn't meant they can access to my encrypted data.

Currently, I am using the following "approach" as my key management.

create master key encryption by password= 'MasterKeyPass'

CREATE ASYMMETRIC KEY MyAsymmKey AUTHORIZATION MyUser
WITH ALGORITHM = RSA_1024
ENCRYPTION BY PASSWORD ='MyAsymmPass'

CREATE SYMMETRIC KEY MySymmKey WITH ALGORITHM = DES
ENCRYPTION BY ASYMMETRIC KEY MyAsymmKey

My data will be encrypted using Symmetric key MySymmKey.

User who want to access my data must have MasterKey and MyAsymmKey password.
Is it OK? Any better way?

Thank you

As long as the user you are trying to protect against is not a dbo or sysadmin, you can also use permissions (i.e. "GRANT CONTROL ON ASYMMETRIC KEY :: MyAsymmKey TO user1") to restrict access rather than through passwords. The advantage is the user then doesn't have to depend on memorizing a password and you don't have to pass any password values in which is safer from a security standpoint.

Sung

|||Fyi, Books online links up a section about BACKUP and RESTORING encryption keys http://msdn2.microsoft.com/en-US/library/ms157275.aspx link.

Data encryption

Hi,
Does anyone use the database encryption tools before? Any suggestion? I
want to encrypt my data inside the table in the sql server.
Thank you very much!
Best regards,
Florence
Hi
If you are using .Net then you could use the inbuilt classes, if not then
you can write your own with the cryptoapi, otherwise check out
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=30
John
"Florence Lee" <florencelee@.visualsolutions.com.my> wrote in message
news:%23nSKBSA5EHA.3124@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Does anyone use the database encryption tools before? Any suggestion? I
> want to encrypt my data inside the table in the sql server.
> --
> Thank you very much!
> Best regards,
> Florence
>
|||Florence,
To encrypt data in tables, for SQL Server 2000 you'll need a third party
tool. There are a number out there, but research carefully.
SQL Server 2005 will provide column-level encryption with key management.
Ron
Ron Talmage
SQL Server MVP
"Florence Lee" <florencelee@.visualsolutions.com.my> wrote in message
news:%23nSKBSA5EHA.3124@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Does anyone use the database encryption tools before? Any suggestion?
I
> want to encrypt my data inside the table in the sql server.
> --
> Thank you very much!
> Best regards,
> Florence
>

Data encryption

Hi,
Does anyone use the database encryption tools before? Any suggestion? I
want to encrypt my data inside the table in the sql server.
--
Thank you very much!
Best regards,
FlorenceHi
If you are using .Net then you could use the inbuilt classes, if not then
you can write your own with the cryptoapi, otherwise check out
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=30
John
"Florence Lee" <florencelee@.visualsolutions.com.my> wrote in message
news:%23nSKBSA5EHA.3124@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Does anyone use the database encryption tools before? Any suggestion? I
> want to encrypt my data inside the table in the sql server.
> --
> Thank you very much!
> Best regards,
> Florence
>|||Florence,
To encrypt data in tables, for SQL Server 2000 you'll need a third party
tool. There are a number out there, but research carefully.
SQL Server 2005 will provide column-level encryption with key management.
Ron
--
Ron Talmage
SQL Server MVP
"Florence Lee" <florencelee@.visualsolutions.com.my> wrote in message
news:%23nSKBSA5EHA.3124@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Does anyone use the database encryption tools before? Any suggestion?
I
> want to encrypt my data inside the table in the sql server.
> --
> Thank you very much!
> Best regards,
> Florence
>

Data Encryption

HI There,
I was wondering how I encrypt the data I have in my SQL sevrer database? is
there some built in tool that will do this? if there is can someone provide
me with a code snippet to get me started?
SeanSean,
Generally you will be required to write extended stored procedures that
calls on CryptoAPIs for this, as SQL Server encrypts at the packet level
when you setup your network libraries with encryption turn on either using
SSL or multiprotocol. I don't know of samples that I can supply you with
but I believe companies such as Protegrity has some sort of solution, you
might like visiting their web site and search on the internet for some
others. I'm not sure if the next release of SQL Server, Yukon, will have
this built in, but because it can call into the Cryptography namespace in
the CLR, it may make your life a little easier. Another solution is to use
Windows encrypted file system (EFS) and encrypt your entire database that
way although this might not be as granular as you want it to be.
I hope this helps.
Regards
James|||Have a look at:
Whamware.Crypt - www.whamware.com
dbEncrypt - www.appsecinc.com
Encryptionizer for SQL Server - www.netlib.com
Tom
"sean" wrote:
> HI There,
> I was wondering how I encrypt the data I have in my SQL sevrer database? i
s
> there some built in tool that will do this? if there is can someone provid
e
> me with a code snippet to get me started?
> ...|||I can also recommend XP_Crypt from www.activecrypt.com cheap, fast and very
easy to use, they also sell a great activeX component so you can easily encr
ypt in your front end app and decrypt in the database etc..
nntp://news.microsoft.com/microsoft.public.sqlserver.security/ >
Have a look at:
Whamware.Crypt - www.whamware.com
dbEncrypt - www.appsecinc.com
Encryptionizer for SQL Server - www.netlib.com
Tom
"sean" wrote:
> HI There,
> I was wondering how I encrypt the data I have in my SQL sevrer database? i
s
> there some built in tool that will do this? if there is can someone provid
e
> me with a code snippet to get me started?
> ...
[microsoft.public.sqlserver.security > ]

data encryption

i have to encrypt my data in sql2005 database using assymetric key encryption which i have done properly.But i have a doubt that while writing stored procedure i have to provide key information in it , that will be visible to everyone then whats the use of taht encryption. Can anyone tell me how can i write stored procedure without providing key information in it.

Divya

Well, you can have SQL Server manage the keys for you, then you never have to expose them to client applications.

But I assume from your question that you are using passwords (passphrases) to protect the asymmetric keys? If so, is there a reason you are doing that? You could instead protect the key using the database master key and then not have to worry about providing the key in your stored procedure.

Don

|||

A very good tutorial for similar situation is

http://aspnet.4guysfromrolla.com/articles/022807-1.aspx

|||

It all depends on who and what you are trying to protect from.

By the web server passing the key, the loss of just the physical database server itself would be meaningless. You couldn't decrypt the data on it since they does not exist on that machine. It also can simplify a number of other issues as well since the key is stored with the application that is accessing the data. And it does make for a stronger link between application and data. if you were store the key on the database (and not pass it), then you most likely wouldn't gain very much. The stored procedure could look it up, but then you are subject to the problem that if the physical machine is compomised (Stolen), the data can be decrypted. It also causes a problem because then you have to make a second security layer so that the stored procedure is only then able to be executed by connections it trusts. And if you write that layer, what is the point of a stored procedure that encrypts data since you are now dependant on the security of the connection? If you can trust the connection, the just put permissions on your tables and you've got the same level of security. If somehow the stored procedure is compromised, again, the encryption is useless. It's encrypted, but anyone can decrypt it just by asking, so what is the point in that?

Now, that's not to say that the key being stored/sent from the application doesn't have it's own set of problems, but there is a lot more to encryption and security that must be thought through. And your security scheme must reflect what types of attacks you want to protect yourself from. All-in-all, having the application send the key is more secure in most cases. Just make sure your connection is encrypted as well.

|||

hello don,

i tried d solution suggested by u by creating the database master key, but i am not able to take backup of this key in ntfs directory as it involves editing registry information. Can u suggest me any feasible solution for it.

Thanks

Divya

|||Hello don i tried this solution from this linkhttp://www.sqlservercentral.com/columnists/mcoles/sql2005symmetricencryption.asp and trying to encrypt and decrypt using database master key , creating certificate without password but here i am not able to decrypt the column with this method, can u plz help me on this.|||

Why don't you post the code you're using at this point? That will make it easier to see what you're trying to do.

And is a certificate the best type of encryption to use? Why did you select that? (I don't mean to imply there is anything wrong with that choice. I just want to understand what you are trying to do.)

Don

|||

thanx don, earlier i was trying the encryption using password. But when i tried using master key it worked fine.

thanx