Encryption Standards

I have a general question on data encryption.
We need to store encrypted creditcard info in a SQL Server 2000 database.
The encryption method needs to meet the AES standard.
Does anyone know if a value encypted under the AES standard will retain its
data length?
In other words, if I have a 15 character credit card number like...
123456789012345
...will it still be 15 characters in length when it is encrypted like...
shj)k2&bs&_yqE#
...or does the AES standard require something other than a character by
character encryption so I end up with a value that is more than 15 characters
like..
/Zd7slDfqN2u1JC8rfzdgxxJDMMzfG
I need to know if I have to expand my column width and possibly change code
to accomodate the encryption.
If anyone has any experience with this, I would appreciate their insight.
Thanks
Dave,
Might want to ask the third-party vendor directly. Might try here:
http://www.activecrypt.com/products.html
HTH
Jerry
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:883F0E2A-8294-47E9-AE62-A6EE59791618@.microsoft.com...
>I have a general question on data encryption.
> We need to store encrypted creditcard info in a SQL Server 2000 database.
> The encryption method needs to meet the AES standard.
> Does anyone know if a value encypted under the AES standard will retain
> its
> data length?
> In other words, if I have a 15 character credit card number like...
> 123456789012345
> ...will it still be 15 characters in length when it is encrypted like...
> shj)k2&bs&_yqE#
> ..or does the AES standard require something other than a character by
> character encryption so I end up with a value that is more than 15
> characters
> like..
> /Zd7slDfqN2u1JC8rfzdgxxJDMMzfG
> I need to know if I have to expand my column width and possibly change
> code
> to accomodate the encryption.
> If anyone has any experience with this, I would appreciate their insight.
> Thanks
|||You'll need to change your VARCHAR column to BINARY or VARBINARY unless
you are going to implement some character set encoding as well as
encryption.
The AES block size is 128 bits so you'll need at least one extra byte.
Depending on the cipher mode you will also need an additional 128 bit
initialization vector.
Jerry has it right though. Ask the vendor or whoever will implement the
encryption.
David Portas
SQL Server MVP
|||Thanks guys.
Yes I am experimenting with "Ivy Encryption" and whatever value it encrypts
is expanded by a factor of 2.75.
I was just wondering if I should expect this from all AES encryption
schemes.
I don''t think it would meet the standard if it each individual character
were encrypted to a single character. If anyone could confirm I would be
grateful.

Encryption Standards

I have a general question on data encryption.
We need to store encrypted creditcard info in a SQL Server 2000 database.
The encryption method needs to meet the AES standard.
Does anyone know if a value encypted under the AES standard will retain its
data length?
In other words, if I have a 15 character credit card number like...
123456789012345
...will it still be 15 characters in length when it is encrypted like...
shj)k2&bs&_yqE#
..or does the AES standard require something other than a character by
character encryption so I end up with a value that is more than 15 character
s
like..
/Zd7slDfqN2u1JC8rfzdgxxJDMMzfG
I need to know if I have to expand my column width and possibly change code
to accomodate the encryption.
If anyone has any experience with this, I would appreciate their insight.
ThanksDave,
Might want to ask the third-party vendor directly. Might try here:
http://www.activecrypt.com/products.html
HTH
Jerry
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:883F0E2A-8294-47E9-AE62-A6EE59791618@.microsoft.com...
>I have a general question on data encryption.
> We need to store encrypted creditcard info in a SQL Server 2000 database.
> The encryption method needs to meet the AES standard.
> Does anyone know if a value encypted under the AES standard will retain
> its
> data length?
> In other words, if I have a 15 character credit card number like...
> 123456789012345
> ...will it still be 15 characters in length when it is encrypted like...
> shj)k2&bs&_yqE#
> ..or does the AES standard require something other than a character by
> character encryption so I end up with a value that is more than 15
> characters
> like..
> /Zd7slDfqN2u1JC8rfzdgxxJDMMzfG
> I need to know if I have to expand my column width and possibly change
> code
> to accomodate the encryption.
> If anyone has any experience with this, I would appreciate their insight.
> Thanks|||You'll need to change your VARCHAR column to BINARY or VARBINARY unless
you are going to implement some character set encoding as well as
encryption.
The AES block size is 128 bits so you'll need at least one extra byte.
Depending on the cipher mode you will also need an additional 128 bit
initialization vector.
Jerry has it right though. Ask the vendor or whoever will implement the
encryption.
David Portas
SQL Server MVP
--|||Thanks guys.
Yes I am experimenting with "Ivy Encryption" and whatever value it encrypts
is expanded by a factor of 2.75.
I was just wondering if I should expect this from all AES encryption
schemes.
I don''t think it would meet the standard if it each individual character
were encrypted to a single character. If anyone could confirm I would be
grateful.

Encryption Standards

I have a general question on data encryption.
We need to store encrypted creditcard info in a SQL Server 2000 database.
The encryption method needs to meet the AES standard.
Does anyone know if a value encypted under the AES standard will retain its
data length?
In other words, if I have a 15 character credit card number like...
123456789012345
...will it still be 15 characters in length when it is encrypted like...
shj)k2&bs&_yqE#
..or does the AES standard require something other than a character by
character encryption so I end up with a value that is more than 15 characters
like..
/Zd7slDfqN2u1JC8rfzdgxxJDMMzfG
I need to know if I have to expand my column width and possibly change code
to accomodate the encryption.
If anyone has any experience with this, I would appreciate their insight.
ThanksDave,
Might want to ask the third-party vendor directly. Might try here:
http://www.activecrypt.com/products.html
HTH
Jerry
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:883F0E2A-8294-47E9-AE62-A6EE59791618@.microsoft.com...
>I have a general question on data encryption.
> We need to store encrypted creditcard info in a SQL Server 2000 database.
> The encryption method needs to meet the AES standard.
> Does anyone know if a value encypted under the AES standard will retain
> its
> data length?
> In other words, if I have a 15 character credit card number like...
> 123456789012345
> ...will it still be 15 characters in length when it is encrypted like...
> shj)k2&bs&_yqE#
> ..or does the AES standard require something other than a character by
> character encryption so I end up with a value that is more than 15
> characters
> like..
> /Zd7slDfqN2u1JC8rfzdgxxJDMMzfG
> I need to know if I have to expand my column width and possibly change
> code
> to accomodate the encryption.
> If anyone has any experience with this, I would appreciate their insight.
> Thanks|||You'll need to change your VARCHAR column to BINARY or VARBINARY unless
you are going to implement some character set encoding as well as
encryption.
The AES block size is 128 bits so you'll need at least one extra byte.
Depending on the cipher mode you will also need an additional 128 bit
initialization vector.
Jerry has it right though. Ask the vendor or whoever will implement the
encryption.
--
David Portas
SQL Server MVP
--|||Thanks guys.
Yes I am experimenting with "Ivy Encryption" and whatever value it encrypts
is expanded by a factor of 2.75.
I was just wondering if I should expect this from all AES encryption
schemes.
I don''t think it would meet the standard if it each individual character
were encrypted to a single character. If anyone could confirm I would be
grateful.

Encryption question

Hello Everybody,
I have a Encryption - Decription Question.
In my project we are getting an XML File from a vendor which has a credit
card number in clear text. We use XML bulk load process to load table from
xml file.
How can i encrypt credit card number while storing into table.
Also i will have to decrypt CC number while i create comma separated file
for another vendor ?
Pls let me know.
thxA credit card number is typically not the type of attribute used for
indexing, sorting, grouping, or composing a primary key, so I see no reason
why it would not be a good candidate for encryption. It would be reasonable
and fairly simple to store it encrypted and decrypt it at the application
level only when needed for display on a customer information form or to
complete a business transaction.
However, a social security number is a different issue.
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:81898E3B-37E1-4B90-9F83-2E522A29D064@.microsoft.com...
> Hello Everybody,
> I have a Encryption - Decription Question.
> In my project we are getting an XML File from a vendor which has a credit
> card number in clear text. We use XML bulk load process to load table from
> xml file.
> How can i encrypt credit card number while storing into table.
> Also i will have to decrypt CC number while i create comma separated file
> for another vendor ?
> Pls let me know.
> thx|||I have a few line sof VB.Net code that Encrypts or Decrypts a string passed
to it; If interested then let me know and I will post the code for you.
"mvp" wrote:

> Hello Everybody,
> I have a Encryption - Decription Question.
> In my project we are getting an XML File from a vendor which has a credit
> card number in clear text. We use XML bulk load process to load table from
> xml file.
> How can i encrypt credit card number while storing into table.
> Also i will have to decrypt CC number while i create comma separated file
> for another vendor ?
> Pls let me know.
> thx|||Thanks for the reply
But can i know how can i do this encryption-decryption..
thanks
"JT" wrote:

> A credit card number is typically not the type of attribute used for
> indexing, sorting, grouping, or composing a primary key, so I see no reaso
n
> why it would not be a good candidate for encryption. It would be reasonabl
e
> and fairly simple to store it encrypted and decrypt it at the application
> level only when needed for display on a customer information form or to
> complete a business transaction.
> However, a social security number is a different issue.
> "mvp" <mvp@.discussions.microsoft.com> wrote in message
> news:81898E3B-37E1-4B90-9F83-2E522A29D064@.microsoft.com...
>
>|||I want to read from XML file (which has CC number in clear text, i use sql
bulk load to load xml into table) and load into sql server table in encrypt
form and then decrypt again when i create comma seperated file from table
again to provide feed to another vendor ?
Let me know, do u have similar thing ?
"Shariq" wrote:
> I have a few line sof VB.Net code that Encrypts or Decrypts a string passe
d
> to it; If interested then let me know and I will post the code for you.
> "mvp" wrote:
>|||If your entire input and output processing is done with SQL server then I
haven't done similar thing but you could possibly use ActiveX script to
Encrypt/Decrypt.
In a similiar type of situation when I had to encrypt/decrypt for input and
output; I use VB.Net application to re-process XML file as input, encrypted
the Credit Card numbers and output to an XML file. Then I executed a DTS
package to upload the XML formatted output file to a SQL Server.
When sending data to a client with decrypted CC numbers; I also used the VB
application to rad data from the SQL Server and during the data read,
decrypted the CC numbers and produced XML file.
"mvp" wrote:
> I want to read from XML file (which has CC number in clear text, i use sq
l
> bulk load to load xml into table) and load into sql server table in encry
pt
> form and then decrypt again when i create comma seperated file from table
> again to provide feed to another vendor ?
> Let me know, do u have similar thing ?
> "Shariq" wrote:
>|||Hello Shariq,
I'm Interesting about the encryption/decryption code that you wrote in VB.NE
T.
Would you like to post it me?
Thank you!
"Shariq" wrote:
> If your entire input and output processing is done with SQL server then I
> haven't done similar thing but you could possibly use ActiveX script to
> Encrypt/Decrypt.
> In a similiar type of situation when I had to encrypt/decrypt for input an
d
> output; I use VB.Net application to re-process XML file as input, encrypte
d
> the Credit Card numbers and output to an XML file. Then I executed a DTS
> package to upload the XML formatted output file to a SQL Server.
> When sending data to a client with decrypted CC numbers; I also used the V
B
> application to rad data from the SQL Server and during the data read,
> decrypted the CC numbers and produced XML file.
> "mvp" wrote:
>|||If you are taking about decrypting data coming in from the source data or
encrypting data as it extract to another format, then this would be
implemented at the application level not the database level. It depends on
what application programming tool your are using.
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:8470213F-47A3-417A-B920-BB7759AC0738@.microsoft.com...
> Thanks for the reply
> But can i know how can i do this encryption-decryption..
> thanks
> "JT" wrote:
>|||x-rays,
You might be able to better technique but the technique I use works great
for me.
This function will either encrypt or decrypt depanding on what is passed to
it.
Public Function MyCryption(ByVal strInput As String) As String
' Encrypts/decrypts the passed string using
' a simple ASCII value-swapping algorithm
Dim strTempChar As String, i As Integer
For i = 1 To Len(strInput)
If Asc(Mid$(strInput, i, 1)) < 128 Then
strTempChar = CType(Asc(Mid$(strInput, i, 1)) + 128, String)
ElseIf Asc(Mid$(strInput, i, 1)) > 128 Then
strTempChar = CType(Asc(Mid$(strInput, i, 1)) - 128, String)
End If
Mid$(strInput, i, 1) = Chr(CType(strTempChar, Integer))
Next i
Return strInput
End Function
"x-rays" wrote:
> Hello Shariq,
> I'm Interesting about the encryption/decryption code that you wrote in VB.
NET.
> Would you like to post it me?
> Thank you!
> "Shariq" wrote:
>|||Keep in mind that whatever encryption you end up using, the encrypted data
is only as secure as the code you use for the encryption. If your
encryption code and key are stored in a source control system, everyone with
access to that system will be able to decrypt the data, provided they have
access to the table where the data is stored.
If your programmers do not have access to the production database, and your
DBAs do not have access to your application code, then you should be ok.
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:81898E3B-37E1-4B90-9F83-2E522A29D064@.microsoft.com...
> Hello Everybody,
> I have a Encryption - Decription Question.
> In my project we are getting an XML File from a vendor which has a credit
> card number in clear text. We use XML bulk load process to load table from
> xml file.
> How can i encrypt credit card number while storing into table.
> Also i will have to decrypt CC number while i create comma separated file
> for another vendor ?
> Pls let me know.
> thx

Encrypting Credit Card details

Hi there,
I need some advice. We have been told that if I want to store credit
card details in an SQL database that it must be stored as enrypted data.
Can anyone recommend how I do this please.
Cheers,
Steve
If you are using SQL Server 2005, you can encrypt it with a key.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Dooza" <steve@.dont.spam.me.dooza.tv> wrote in message
news:%23VNv2OPHGHA.648@.TK2MSFTNGP14.phx.gbl...
Hi there,
I need some advice. We have been told that if I want to store credit
card details in an SQL database that it must be stored as enrypted data.
Can anyone recommend how I do this please.
Cheers,
Steve
|||Tom Moreau wrote:
> If you are using SQL Server 2005, you can encrypt it with a key.
>
Hi Tom,
I am using SQL2000. Should I be looking at a product like ASPEncrypt?
Steve
|||we use encryptionizer -- works fast & very well.
On Thu, 19 Jan 2006 13:08:50 +0000, Dooza <steve@.dont.spam.me.dooza.tv> wrote:

>Tom Moreau wrote:
>Hi Tom,
>I am using SQL2000. Should I be looking at a product like ASPEncrypt?
>Steve
-- AntiSpam/harvest --
Remove X's to send email to me.

Encrypting Credit Card details

Hi there,
I need some advice. We have been told that if I want to store credit
card details in an SQL database that it must be stored as enrypted data.
Can anyone recommend how I do this please.
Cheers,
SteveIf you are using SQL Server 2005, you can encrypt it with a key.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Dooza" <steve@.dont.spam.me.dooza.tv> wrote in message
news:%23VNv2OPHGHA.648@.TK2MSFTNGP14.phx.gbl...
Hi there,
I need some advice. We have been told that if I want to store credit
card details in an SQL database that it must be stored as enrypted data.
Can anyone recommend how I do this please.
Cheers,
Steve|||Tom Moreau wrote:
> If you are using SQL Server 2005, you can encrypt it with a key.
>
Hi Tom,
I am using SQL2000. Should I be looking at a product like ASPEncrypt?
Steve|||we use encryptionizer -- works fast & very well.
On Thu, 19 Jan 2006 13:08:50 +0000, Dooza <steve@.dont.spam.me.dooza.tv> wrot
e:

>Tom Moreau wrote:
>Hi Tom,
>I am using SQL2000. Should I be looking at a product like ASPEncrypt?
>Steve
-- AntiSpam/harvest --
Remove X's to send email to me.

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
>