Encryption of Data

Dear All
We have a requirement to encrypt data within SQL Server,
eg, Bank Account / credit card numbers.
I don't believe there is anything in-built within SQL
Server 2000, but is there something in-built in 2005 ?
Please note I do know about security and SSL but in this
case we are trying to encrypt the data in the server
itself.Hi
SQL Server 2005 will support this at column level using Certificates.
Regards
Mike
"Jane" wrote:

> Dear All
> We have a requirement to encrypt data within SQL Server,
> eg, Bank Account / credit card numbers.
> I don't believe there is anything in-built within SQL
> Server 2000, but is there something in-built in 2005 ?
> Please note I do know about security and SSL but in this
> case we are trying to encrypt the data in the server
> itself.
>
>|||There are a number of offerings, one of which is ours, NetLib Encryptionizer
(www.netlib.com). I am sure the others will speak up for themselves.
Encryptionizer is the only one that has the capability of protecting columns
and/or entire databases. It is already in use in banks, credit card
processors, government agencies, and many other places.
Regards,
Neil Weicher
NetLib
_________________________________
"Jane" <anonymous@.discussions.microsoft.com> wrote in message
Dear All
We have a requirement to encrypt data within SQL Server,
eg, Bank Account / credit card numbers.
I don't believe there is anything in-built within SQL
Server 2000, but is there something in-built in 2005 ?
Please note I do know about security and SSL but in this
case we are trying to encrypt the data in the server
itself.|||anybody tried this yet?
if you do a select from query analyzer, will you get back encrypted data
or can you install the certificate in query analyzer so you get back
decrypted data?
Mike Epprecht (SQL MVP) wrote:[vbcol=seagreen]
> Hi
> SQL Server 2005 will support this at column level using Certificates.
> Regards
> Mike
> "Jane" wrote:
>|||To be honest I was talking a load of rubbish, I don not
know how Certificates work in sufficent detail to know if
it will work or not.
Our problem however is so that DBA's like myself cannot
read the data.
Thanks
Jane

>--Original Message--
>anybody tried this yet?
>if you do a select from query analyzer, will you get back
encrypted data
>or can you install the certificate in query analyzer so
you get back
>decrypted data?
>
>Mike Epprecht (SQL MVP) wrote:
Certificates.[vbcol=seagreen]
Server,[vbcol=seagreen]
this[vbcol=seagreen]
>.
>|||Take a look around on the internet. There are various encryption algorithms
that you can use.
There is an RC4 one out there that is pretty straight-forward. Install the
function and then use it for both encryption and decryption.
Note: While this link shows the info on RC4 algorithm and an
implementation, I do not know if this is IP and whether or not you need to
purchase some type of license for it. Do your due diligence.
http://www.databasejournal.com/img/RC4_enhanced.sql
Rick Sawtell
MCT, MCSD, MCDBA

Encryption of Data

Dear All
We have a requirement to encrypt data within SQL Server,
eg, Bank Account / credit card numbers.
I don't believe there is anything in-built within SQL
Server 2000, but is there something in-built in 2005 ?
Please note I do know about security and SSL but in this
case we are trying to encrypt the data in the server
itself.Hi
SQL Server 2005 will support this at column level using Certificates.
Regards
Mike
"Jane" wrote:
> Dear All
> We have a requirement to encrypt data within SQL Server,
> eg, Bank Account / credit card numbers.
> I don't believe there is anything in-built within SQL
> Server 2000, but is there something in-built in 2005 ?
> Please note I do know about security and SSL but in this
> case we are trying to encrypt the data in the server
> itself.
>
>|||There are a number of offerings, one of which is ours, NetLib Encryptionizer
(www.netlib.com). I am sure the others will speak up for themselves.
Encryptionizer is the only one that has the capability of protecting columns
and/or entire databases. It is already in use in banks, credit card
processors, government agencies, and many other places.
Regards,
Neil Weicher
NetLib
_________________________________
"Jane" <anonymous@.discussions.microsoft.com> wrote in message
Dear All
We have a requirement to encrypt data within SQL Server,
eg, Bank Account / credit card numbers.
I don't believe there is anything in-built within SQL
Server 2000, but is there something in-built in 2005 ?
Please note I do know about security and SSL but in this
case we are trying to encrypt the data in the server
itself.|||anybody tried this yet?
if you do a select from query analyzer, will you get back encrypted data
or can you install the certificate in query analyzer so you get back
decrypted data?
Mike Epprecht (SQL MVP) wrote:
> Hi
> SQL Server 2005 will support this at column level using Certificates.
> Regards
> Mike
> "Jane" wrote:
> > Dear All
> >
> > We have a requirement to encrypt data within SQL Server,
> > eg, Bank Account / credit card numbers.
> >
> > I don't believe there is anything in-built within SQL
> > Server 2000, but is there something in-built in 2005 ?
> >
> > Please note I do know about security and SSL but in this
> > case we are trying to encrypt the data in the server
> > itself.
> >
> >
> >|||To be honest I was talking a load of rubbish, I don not
know how Certificates work in sufficent detail to know if
it will work or not.
Our problem however is so that DBA's like myself cannot
read the data.
Thanks
Jane
>--Original Message--
>anybody tried this yet?
>if you do a select from query analyzer, will you get back
encrypted data
>or can you install the certificate in query analyzer so
you get back
>decrypted data?
>
>Mike Epprecht (SQL MVP) wrote:
>> Hi
>> SQL Server 2005 will support this at column level using
Certificates.
>> Regards
>> Mike
>> "Jane" wrote:
>> > Dear All
>> >
>> > We have a requirement to encrypt data within SQL
Server,
>> > eg, Bank Account / credit card numbers.
>> >
>> > I don't believe there is anything in-built within SQL
>> > Server 2000, but is there something in-built in 2005 ?
>> >
>> > Please note I do know about security and SSL but in
this
>> > case we are trying to encrypt the data in the server
>> > itself.
>> >
>> >
>> >
>.
>|||Take a look around on the internet. There are various encryption algorithms
that you can use.
There is an RC4 one out there that is pretty straight-forward. Install the
function and then use it for both encryption and decryption.
Note: While this link shows the info on RC4 algorithm and an
implementation, I do not know if this is IP and whether or not you need to
purchase some type of license for it. Do your due diligence.
http://www.databasejournal.com/img/RC4_enhanced.sql
Rick Sawtell
MCT, MCSD, MCDBAsql

Encryption of Data

Dear All
We have a requirement to encrypt data within SQL Server,
eg, Bank Account / credit card numbers.
I don't believe there is anything in-built within SQL
Server 2000, but is there something in-built in 2005 ?
Please note I do know about security and SSL but in this
case we are trying to encrypt the data in the server
itself.
Hi
SQL Server 2005 will support this at column level using Certificates.
Regards
Mike
"Jane" wrote:

> Dear All
> We have a requirement to encrypt data within SQL Server,
> eg, Bank Account / credit card numbers.
> I don't believe there is anything in-built within SQL
> Server 2000, but is there something in-built in 2005 ?
> Please note I do know about security and SSL but in this
> case we are trying to encrypt the data in the server
> itself.
>
>
|||There are a number of offerings, one of which is ours, NetLib Encryptionizer
(www.netlib.com). I am sure the others will speak up for themselves.
Encryptionizer is the only one that has the capability of protecting columns
and/or entire databases. It is already in use in banks, credit card
processors, government agencies, and many other places.
Regards,
Neil Weicher
NetLib
_________________________________
"Jane" <anonymous@.discussions.microsoft.com> wrote in message
Dear All
We have a requirement to encrypt data within SQL Server,
eg, Bank Account / credit card numbers.
I don't believe there is anything in-built within SQL
Server 2000, but is there something in-built in 2005 ?
Please note I do know about security and SSL but in this
case we are trying to encrypt the data in the server
itself.
|||anybody tried this yet?
if you do a select from query analyzer, will you get back encrypted data
or can you install the certificate in query analyzer so you get back
decrypted data?
Mike Epprecht (SQL MVP) wrote:[vbcol=seagreen]
> Hi
> SQL Server 2005 will support this at column level using Certificates.
> Regards
> Mike
> "Jane" wrote:
|||To be honest I was talking a load of rubbish, I don not
know how Certificates work in sufficent detail to know if
it will work or not.
Our problem however is so that DBA's like myself cannot
read the data.
Thanks
Jane

>--Original Message--
>anybody tried this yet?
>if you do a select from query analyzer, will you get back
encrypted data
>or can you install the certificate in query analyzer so
you get back[vbcol=seagreen]
>decrypted data?
>
>Mike Epprecht (SQL MVP) wrote:
Certificates.[vbcol=seagreen]
Server,[vbcol=seagreen]
this
>.
>
|||Take a look around on the internet. There are various encryption algorithms
that you can use.
There is an RC4 one out there that is pretty straight-forward. Install the
function and then use it for both encryption and decryption.
Note: While this link shows the info on RC4 algorithm and an
implementation, I do not know if this is IP and whether or not you need to
purchase some type of license for it. Do your due diligence.
http://www.databasejournal.com/img/RC4_enhanced.sql
Rick Sawtell
MCT, MCSD, MCDBA

encrypte strings and numbers

i need a function for encrypte data in t-sql.any idea?
Regards,
OfirSQL does not have an inbuilt encryption function, except the one used for
passwords... If you search for Encrypt SQL Data on the web you will get many
results... Some indiviuduals have come up with simple encryption Sps, etc...
Here is a URL for a 3rd party product..
http://www.activecrypt.com/
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ofir" <ofir@.millenium.org.il> wrote in message
news:uTAz%231QZFHA.3628@.TK2MSFTNGP09.phx.gbl...
>i need a function for encrypte data in t-sql.any idea?
> Regards,
> Ofir
>

encrypt sensitive data

How do you encrypt sensitive data that may be stored in a database such as
passwords, creditcard numbers,etc.. ?
3rd party tools ? Internal ..If so how.. ? Does decrypting add latency ..
Any info that you can provide would be valuable. Using SQL 2000Hi
Do it at application level using the Windows API's of if you use .NET, use
the Crypto classes.
SQL server 2005 will support encryption at row level.
Regards
Mike
"Hassan" wrote:

> How do you encrypt sensitive data that may be stored in a database such as
> passwords, creditcard numbers,etc.. ?
> 3rd party tools ? Internal ..If so how.. ? Does decrypting add latency ..
> Any info that you can provide would be valuable. Using SQL 2000
>
>|||Hassan,
If you use passwords for user authentication purposes only (i.e. you do not
need to know their plaintext values, but want to make sure that the user
logging on submits the right password) use hashing instead of encryption.
This way you don't have to deal with key protection.
For values that need to be decrypted (e.g. SSN or credit card number), use
symmetric key encryption, such as Rijndael algorithm, with strong keys.
Alternatively, you can use public-private keys, but the performance can be
bad.
Whether you use hashing or encryption, implement it in C#, C/C++, VB or
whatever your application is written in, not in SQL Server (T-SQL).
Although, I have to say that there are tools which allow encryption directly
in the database (such as nCipher: http://www.ncipher.com/dbe/, netlib:
http://www.netlib.com/, etc), I do not have much experience with them, so I
cannot comment.
SQL Server 2000 (and earlier) has very limited cryptographic features, so
they are of little use. As Mike mentioned, the next version of SQL Server
offers much better cryptographic features, but the main problem I see is
that if you decrypt a value on the database side and pass it to the
application which resides on a different machine (which is a typical case in
most enterprise environments), a simple network sniffer will reveal all your
secrets. That is unless you use SSL between the application server and the
database server, which you can do, but it would require more administrative
hassles and cause performance degradation since all payloads will have to be
encrypted and decrypted.
A couple of notes. If you use encryption in application (not T-SQL), you may
need to store the same value twice: one hashed (without salt), the other
encrypted. Let me illustrate. Say you store SSNs encrypted. Normally, you
would need to use an initialization vector (IV) or a pseudo-IV with
encryption, so depending on the IV value the result of the encryption can be
different. In this case, how do you perform a search for a record with a par
ticular SSN? Unless you use the same IV for all records (which kinda defeats
the purpose), you cannot just encrypt the submitted value and run a select
query (SELECT * FROM ... WHERE SSN = <encrypted value> ), because you do not
know which IV to use to generate the encrypted result. In this case, when
storing SSN, you may need to keep two values: a hash of the SSN and an
encrypted value, so in your SELECT statement you can use the hash. (Note:
You can generate hash using SQL Server's PWDENCRYPT function, but then again
you will be passing the value in plain text over the network.)
Finally, when you use encryption, you have to protect the key (or a
passphrase - and other characteristics - from which the key is derived).
Basically, your application "owns" the key, so when the application is
installed on a server, you - or a server admin - must "install" the key in
some way and store it in a secure manner so that the application can
retrieve it and use for encryption and decryption (the idea here is to allow
different key to be used in different environments - development, test,
production - and have a person responsible for the key access: i.e.
developers should not know which key is used in production).
Unfortunately, there are no totally secure ways of doing this and better or
worse options depend on the type of your application. You can use third
party tools (e.g. CipherSafe: http://www.obviex.com/ciphersafe) or implement
your own protection mechanism using DPAPI (which is the best option,
although with some limitations, partially in the context of ASP.NET apps),
or something else. If you want to learn more about this topic, check this
article: "Protect It: Safeguard Database Connection Strings and Other
Sensitive Settings in Your Code" at
[url]http://msdn.microsoft.com/msdnmag/issues/03/11/ProtectYourData/default.aspx.;[/url
]
it covers several related aspects.
And, yes, whichever method you choose, expect encryption and decryption to
cause performance degradation, but depending on the method you choose it can
be anything from negligible to non-acceptable.
Good luck,
Alek
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OJsFSrEMFHA.3076@.TK2MSFTNGP14.phx.gbl...
> How do you encrypt sensitive data that may be stored in a database such as
> passwords, creditcard numbers,etc.. ?
> 3rd party tools ? Internal ..If so how.. ? Does decrypting add latency ..
> Any info that you can provide would be valuable. Using SQL 2000
>