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 and storing credit cards

We need to store credit cards for a limited amount of time because we charge for orders as they ship and occasionally the authorization expires before the item can ship.

I know I can use AES_256 to encrypt our credit cards but I am not sure if all I need to do is follow the directions in the "How-to" for encrypting data.

I created the master key, backed it up off the server the certificate and the symmetric key and I can encrypt and decrypt data.

Is this all I need to do or am I missing something?

Sounds like you have it under control. The 'How to' is nicely put together.

The next step is to test, test, test.

|||I have been testing and keep thinking in the back of my head "This is much too easy. I must be doing something wrong."

I got the encryption working on the second try.|||Microsoft got it right. It really shouldn't be difficult.

Encryption - Protect from attacks by programmers?

I can encrypt columns in sql 2005 but where do I store the key to decrypt the columns?

I can store the key in the database (or server on which the database resides) but I think that offers little security. I could store the key on another server that the sql server accesses only upon startup (though I don't know exactly how to do that). Or I could store the key on a removable drive that is read (and only needed) when the sql server starts up.

What are your ideas on this matter?

TIA,

barkingdog

Have a look at the encryption hierarchy in SQL Server: http://msdn2.microsoft.com/en-US/library/ms189586.aspx. Also check the other resources mentioned here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=286374&SiteID=1.

If you store the key in the database, it is stored encrypted. I am not sure why would you think that this scheme offers "little security", can you elaborate on that statement? Before discussing a protection scheme, it would be helpful to state what you are trying to protect against - against what attacks do you want to protect the key?

Thanks
Laurentiu

|||

Laurentiu,

From what you said about storing the key in the database I obviously have a misconception here. But how then, is one supposed to access the encrypted key to de-crypt the data for later display in the UI? Is there a "proxy" stand-in for the real key once it is encrypted?

My concern is to prevent outsiders from, if they somehow gained access to the database (say a stolen or lost backup tape), from being able to decode sensitive fields such as Social Security number. At the same time, when a SSN is entered via the UI the application needs the key to drive the encryption of sensitive fields.

Barkingdog

|||

Basically, there are two ways to encrypt keys (hence two ways to decrypt them): one is to eventually use a password, so the password needs to be specified when the key needs to be used; the second protection is based on DPAPI, so no password needs to be specified. DPAPI basically uses the credentials of the machine and of the service account to protect the key, so to break it, one would have to know those credentials.

For details on DPAPI, see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/seccrypto/security/cryptprotectdata.asp. How DPAPI ties in to the key protection scheme is shown in the key hierarchy diagram from the first link in my previous message - you have a chain of encryptions rooted at the DPAPI encryption of the service master key.

For additional information and examples, see the blogs I referred to you earlier. The example from http://blogs.msdn.com/lcris/archive/2005/12/16/504692.aspx has the symmetric keys protected so that no password is required for their use (access control for keys is done through permissions). The post from http://blogs.msdn.com/lcris/archive/2005/10/14/481434.aspx goes over additional considerations related to the use of encryption keys. For a discussion of the protection conferred by encryption, see http://blogs.msdn.com/lcris/archive/2005/12/20/506187.aspx.

For the following explanation, I will assume that you have examined the key hierarchy diagram and that you understand the encryption chain. I will also use SMK and DbMK as shortcuts for the service master key and, respectively, database master key.

In the case of a stolen backup tape that contained a database with encrypted data, the thief cannot decrypt the data without knowledge of the password that protects the DbMK. So the data is secure because removing a database from the server will cut the database from the encryption chain that allows its data to be decrypted.

If you're worried about a stolen laptop scenario, this threat is a little different from the previous one because the thief might be able to figure out how to gain full control of the laptop so that he can connect to the server as usual and decrypt the data. For this scenario you would want to protect the encryption keys by password, and then have the password specified when you're accessing the data (think of it as an additional login operation that grants access to the encrypted data). This would protect against a lost laptop scenario because even with full access to the laptop, knowledge of the password is still required.

Thanks
Laurentiu

|||

Hi,

I have a different type of problem. Lets say I have created a Symetric key (without using a password) and i authorize that key to a user called ASP_NET_My_Appln.

This user is used by my UI(ASP.NET) for querying the DB.

All the programmers who are coding the project WILL know the key name that we are using for encrypting / decrypting the data.

Therefore any programmer who can log into the production server or for that matter the local environment (where we place production server dumps to get the latest data) can decrypt the data by passing a simple SQL like this:

select EncryptXXX(Key,Column) from table

To protect the same I had to resort to use a password phrase. But however there is a problem there too. All the SQLs that we use are stored in SPs. Therefore a sample SP would be:

Sp_GetData @.pwd varchar(10)
AS
OPEN SYMMETRIC...... Password=@.pwd
select EncryptXXX(Key,Column) from table
Close Symmetric...

Anybody who is running the profiler can now read the password as the profiler does not block the same.

What is the best way to overcome this?

Therefore categorising the problems:
1. As far as I see the decyprtion seems to be a very simple select statement. Therefore anyone who has access to the server and knows the correct Key name and table name etc can do the same (Which a programmer WILL know).
2. Profiler is capable of blocking the actual select statements that use encryption commands but NOT the SP that takes the password. How can I overcome that? Should I change my design? Once again the password cannot be hardcoded into an SP as any developer can open it and look into it.

Kindly correct me if I have misqouted anything.

|||

1. To decrypt, you need to have previously opened the key. The access checks on the key and the knowledge of the passwords used to access the key come into place at this time, and it is these checks that restrict the access and use of an encryption key.

2. You're right about not wanting to hardcode the password in a SP. You should treat the key password as a login password and issue a direct OPEN SYMMETRIC KEY statement whenever you want to use the key - the password passed to OPEN will not be traced.

However, I am not sure I understand your scenario very well. Why are your programmers manipulating sensitive data while developing the application? What kind of access to the database and to the sensitive data do they need?

Thanks
Laurentiu

|||>>However, I am not sure I understand your scenario very well. Why are your programmers manipulating sensitive data while developing the application? What kind of access to the database and to the sensitive data do they need?

Its like an internal application that deals with the data for the entire organisation (really sensitive data of employees).

So the programmers themselves MIGHT be hackers...

Since I have to give access to my UI user, anybody who gets hold on the connection string can open a connection to the DB and remove the data by a very simple select stmt. So to protect this I wanted to have passwords for the key. But now i am stuck as to how to protect the password from hackers...|||

As long as the password is not hardcoded in the application, for the programmers to see, they should not be able to get it from just examining the code. What are your concerns if the user is specifying the password to the application?

Thanks
Laurentiu

|||Hi,

When you mean "As long as the password is not hardcoded in the application, for the programmers to see, they should not be able to get it from just examining the code. What are your concerns if the user is specifying the password to the application?"

Are you talking about the UI? If yes then the problem arises when I have to pass it to the DB (to an SP in the DB).

Where and how exactly do you want me to store the password that protects the key?|||

I am suggesting to have the user specify the password. I am not suggesting for the password to be stored somewhere where it can be programmatically retrieved, given that you are trying to prevent the developers of your application from accessing it. Also, I am not suggesting for the password to be passed around as an argument to stored procedures (which would make it visible in a trace) - it should just be passed to the OPEN SYMMETRIC KEY statement.

Thanks
Laurentiu

|||>>it should just be passed to the OPEN SYMMETRIC KEY statement

Exactly, but my open symmetric statement is inside an SP. There can be more than 100 SPs that have to access this password. In this case there are 2 options for me:

1. Hardcode the password in each SP.
2. Pass it as a parameter to the SP.

I choose the second one therefore the problem.|||

Why do you open the key inside the SP? Why can't you open it as part of the logon process for your application and keep it open for as long as you work with the encrypted data. Once you open a key, it is only available within the current session, so you don't have to worry about other users getting to it. Also, you don't need to open it and close it for each access to encrypted data. You can open it once, use it for many encryptions and decryptions (which can happen in stored procedures that you call - they will have access to the opened key), and then close it when you are done (or you can just disconnect your session and it will be destroyed).

Thanks
Laurentiu

|||Hi,

Before I implement your idea i would like to know more about the defenition of a current session.

I am using EntLib, therefore each call to an SP opens / closes a connection from the pool. How is the session defined in this case?

One more thing, to hide the data from the profiler i will have to pass the OPEN stmt as a direct SQL rather than using an SP right?|||

Hi,

You can use CLR function to open symetric key , The script will be in assembly

and the password is hidden at all.

Thanks,

Tarek Ghazali

SQL Server MVP

web site : www.sqlmvp.com

|||

Hi,

I am totally new to this. Could you possibly giude me to some tutorials on the same?

Encrypting the configuration file values stored in SQL server

Hi All,

I have the following requirement. I need to store the password for the connection manager in the configuration file. The sink for the configuration file is SQL Server. Though the password field appears as "******" the actual value is being taken as ""******" itself. If i update the SQL server table with the correct value, then the package starts working. But, the password is shown as clear text.

If i write logic to encrypt the password column in the configuration table, is there a way to tell the SSIS execute engine to decrypt the password before using the same for making the connection.

Is there a place holder, where i can write the decrypt code so that the decrypted password can be sent to the execution engine?

Thanks In Advance,

Madhu

I think the short answer to this is no, and no code hooks either.

I think though that there is also an argument, that says it would not be more secure than what you have now. If you encrypt the data, you need to then secure the key. So what will you do to secure the key? Why not use strong security to secure the password data instead of worrying about how to secure the key? I accept that the encryption adds an extra step, but I'm not convinced it will actually be any safer.

|||I'm not sure if it's a good idea, but couldn't he create a script task to decrypt the password and reset the connection manager's connectionstring property before the connection manager is used in the package?|||

Yes and no. Some connections are used before your script task could run, such as connections used for logging.

How would you secure the key used to decrypt the password? You need to secure the encryption/decryption key, so why not just secure the password to start with?

|||DarrenSQLIS is right the recommended way to do this is to store the password in the connection. SSIS will automatically encrypt these so that they are not stored in cleartext.|||Thanks for the thoughts Darren. As suggested by you, way to go is to store the password in SQL server and make sure that the access to the configuration table is only for administrators.|||

Denise, I think you are talking about the package level encryption, protection levels and such like. Nice though it is, it is not very useful, as I think you should "externalise" any kind of security information.

Using package encryption becomes unfeasible when you have to migrate packages between environments. Configurations solve that migration issue, but don't give you the encryption that is often seen as a requirement for some organisations. I'd argue that is should not be a big deal, secure the password so you don't have to worry about the key, but often it is an internal "standard" that must be complied with.

Still we have the choice of package encryption, which is better than not!

Encrypting Data in SQL 2000

What is the recommended way to store data in SQL Server 2000 encrypted so
not even DBA can see the values? I know SQL 2005 has encryption at the row
level, but what are my options in SQL 2000?
Thank you,
SteveVery limited.
If using a .NET applicaiton, there is a rich encryption library that could
be used at the application level to encrypt data before it is stored in the
server, and decrypt it after if is retreived.
And there are several third party 'Add-ins' to allow encryption via extended
procedures.
A very crude 'hiding (and generally ineffective) scheme would be to use one
of the checksum() functions and do a one-way process. You couldn't 'decrypt'
it but you could use a comparison to verify if an input value matches.
As far as I know, the application level method is the only way to keep a
determined DBA (or Admin) from being able to access the encrypted data AND
the means to decrypt it. (But then, the question is "Is that a wise
decision?, Are you feeling lucky? Go ahead, make my ..."
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Steven" <Lazans@.mskcc.org> wrote in message
news:eDdtTMrpGHA.4812@.TK2MSFTNGP04.phx.gbl...
> What is the recommended way to store data in SQL Server 2000 encrypted so
> not even DBA can see the values? I know SQL 2005 has encryption at the
> row level, but what are my options in SQL 2000?
> Thank you,
> Steve
>|||What about Microsoft's Crypto API?
Steve
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:e8qenSrpGHA.3324@.TK2MSFTNGP05.phx.gbl...
> Very limited.
> If using a .NET applicaiton, there is a rich encryption library that could
> be used at the application level to encrypt data before it is stored in
> the server, and decrypt it after if is retreived.
> And there are several third party 'Add-ins' to allow encryption via
> extended procedures.
> A very crude 'hiding (and generally ineffective) scheme would be to use
> one of the checksum() functions and do a one-way process. You couldn't
> 'decrypt' it but you could use a comparison to verify if an input value
> matches.
> As far as I know, the application level method is the only way to keep a
> determined DBA (or Admin) from being able to access the encrypted data AND
> the means to decrypt it. (But then, the question is "Is that a wise
> decision?, Are you feeling lucky? Go ahead, make my ..."
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "Steven" <Lazans@.mskcc.org> wrote in message
> news:eDdtTMrpGHA.4812@.TK2MSFTNGP04.phx.gbl...
>|||You would need to use some type of third party encryption.
Check the links under field level encryption in the
following FAQ:
http://www.sqlsecurity.com/FAQs/SQL...55/Default.aspx
-Sue
On Thu, 13 Jul 2006 15:32:26 -0400, "Steven"
<Lazans@.mskcc.org> wrote:

>What is the recommended way to store data in SQL Server 2000 encrypted so
>not even DBA can see the values? I know SQL 2005 has encryption at the row
>level, but what are my options in SQL 2000?
>Thank you,
>Steve
>|||Yes, that could be used. (I'm not sure if it's status is still considered
secure...)
You may wish to check the resourses at:
http://www.sqlsecurity.com/FAQs/SQL...55/Default.aspx
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Steven" <Lazans@.mskcc.org> wrote in message
news:ucwNjZrpGHA.3324@.TK2MSFTNGP05.phx.gbl...
> What about Microsoft's Crypto API?
> Steve
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:e8qenSrpGHA.3324@.TK2MSFTNGP05.phx.gbl...
>|||Just to clarify: CryptoAPI is a set of encryption functions available in
Windows that expose standard cryptographic algorithms. .Net encryption
routines offer a higher level interface to CryptoAPI. The encryption in SQL
Server 2005 is also based on CryptoAPI. Any Windows application can use
these routines. For a list of the algorithms available through CryptoAPI,
you can take a look at:
http://msdn.microsoft.com/library/d...
y/alg_id.asp.
Thanks
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:O2IubprpGHA.4268@.TK2MSFTNGP04.phx.gbl...
> Yes, that could be used. (I'm not sure if it's status is still considered
> secure...)
> You may wish to check the resourses at:
> http://www.sqlsecurity.com/FAQs/SQL...55/Default.aspx
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "Steven" <Lazans@.mskcc.org> wrote in message
> news:ucwNjZrpGHA.3324@.TK2MSFTNGP05.phx.gbl...
>|||Thanks Laurentin, for the clarification about CRYPTOAPI library.
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Laurentiu Cristofor [MSFT]" <laur@.nospam.com> wrote in message
news:elpC%23vspGHA.4188@.TK2MSFTNGP04.phx.gbl...
> Just to clarify: CryptoAPI is a set of encryption functions available in
> Windows that expose standard cryptographic algorithms. .Net encryption
> routines offer a higher level interface to CryptoAPI. The encryption in
> SQL Server 2005 is also based on CryptoAPI. Any Windows application can
> use these routines. For a list of the algorithms available through
> CryptoAPI, you can take a look at:
> http://msdn.microsoft.com/library/d...ity/alg_id.asp.
> Thanks
> --
> Laurentiu Cristofor [MSFT]
> Software Design Engineer
> SQL Server Engine
> http://blogs.msdn.com/lcris/
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:O2IubprpGHA.4268@.TK2MSFTNGP04.phx.gbl...
>|||Here are some free XP's that perform column-level encryption using the
CryptoAPI:
http://www.sqlservercentral.com/col...oolkitpart1.asp
"Steven" <Lazans@.mskcc.org> wrote in message
news:ucwNjZrpGHA.3324@.TK2MSFTNGP05.phx.gbl...
> What about Microsoft's Crypto API?
> Steve
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:e8qenSrpGHA.3324@.TK2MSFTNGP05.phx.gbl...
>

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> wrote:
>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
-- 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.

Encrypting column values

I want to store a function in SQL Server database that when called during insertion, encrypts a value and then stores encrypted text in the column.

Is there any built in Encrypting and Hashing mechanism in SQL 2005.

There is very 'rich' encryption capabilities in SQL 2005.

You may wish to start by referring to Books Online, Topic: Cryptographic Functions

Encrypted Store Procedure

Yesterday, I would like to test the Encrypted Store
Procedure with the opinion "ENCRYPTION". After appling the
opinion, I found I can't view the source code of the Store
Procedure.
How can I review the source code of the Stroe Procedure Or
edit it? It is very emergent.Look here:
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=505&lngW
Id=5
HTH, Jens Süßmeyer.
"Alistair" <alistair.wang@.corp.elong.com> schrieb im Newsbeitrag
news:03d001c3781d$c53cd9b0$a301280a@.phx.gbl...
> Yesterday, I would like to test the Encrypted Store
> Procedure with the opinion "ENCRYPTION". After appling the
> opinion, I found I can't view the source code of the Store
> Procedure.
> How can I review the source code of the Stroe Procedure Or
> edit it? It is very emergent.|||To add to the response by Jens, it's a good practice to keep your DDL
scripts in a source control system. This ensures you always have a copy
and versioning capabilities as well.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Alistair" <alistair.wang@.corp.elong.com> wrote in message
news:03d001c3781d$c53cd9b0$a301280a@.phx.gbl...
> Yesterday, I would like to test the Encrypted Store
> Procedure with the opinion "ENCRYPTION". After appling the
> opinion, I found I can't view the source code of the Store
> Procedure.
> How can I review the source code of the Stroe Procedure Or
> edit it? It is very emergent.

Encrypted data in tables

How could I store encrypted data in tables in SQL Server 2000 Database.
Say I have made a table to store passwords and I don't want every user to be able to view this table.
Thanks
Jasmitado you really need to store the passwords, or could you just store a one-way hashed value of the password instead, this way the passwords are realivily useless.

if not surely you can set it so that only a certain user can select (and others) from this table?

Encrypt payroll fields

I need to store payroll information (pay rates) in my data warehouse.
I've search on field level encryption and most the stuff i find is for
passwords, I.E. not reversable. I need to protect the payrate field
from dba's and sa's, but I need to allow the authorized users to see
the contents.
Any ideas? Does 2005 handle this?
I don't have a web app, per se, that access's this information. however
I am using Cognos' Report Net.
thanks in advance
RobThe most common solution is to use some kind of encryption library in
your client application or middle-tier, and encrypt/decrpyt the data as
people enter it or retrieve it. In SQL2005, you could use the .NET
crypto libraries within a .NET stored proc, but you can use them
already in a .NET client application regardless of the database.

So it probably depends on what your development environment and
language are. These links might give you some ideas:

http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22
http://www.aspfaq.com/show.asp?id=2536

Simon|||SQL2005 actually has encryption built-in using either shared key or
certificates (which it can generate). Alternatively, for SQL2000 look
at xp_crypt from http://www.activecrypt.com/.

Encrypt data in a Stored Procedure

I am trying to insert data in a table using a stored procedure, but somehow I cannot store the values passed by the stored procedure in the table.

Table has two fields FIRST_NAME, LAST_NAME with varbinary data type(I need to encrypt the data)

My stored procedure is as follows. Please let me know what i am doing wrong!

***************************************************************

ALTER PROCEDURE [dbo].[SP_InsertInfo]
-- Add the parameters for the stored procedure here

@.FIRST_NAME varBINARY(100)
,@.LAST_NAME varBINARY(100)

AS
OPEN SYMMETRIC KEY key DECRYPTION BY CERTIFICATE cert

BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here

Insert into [dbo].[INFO] (FIRST_NAME, LAST_NAME)
Values ( encryptbykey( key_guid('key'),'@.FIRST_NAME'),
encryptbykey( key_guid('key'),'@.LAST_NAME')
)
close SYMMETRIC KEY key

END

**********************************************
EXEC sp_InsertInfo 'larry', 'Smith'

when I run the SP, the data stored in the first_name, last_name fields are @.FIRST_NAME', @.LAST_NAME' instead of larry, smith respectively.

Thanks

Insert into [dbo].[INFO] (FIRST_NAME, LAST_NAME)
Values ( encryptbykey( key_guid('key'),'@.FIRST_NAME'),
encryptbykey( key_guid('key'),'@.LAST_NAME')
)

You have single quotes around the variable names. This leads to SQL Server to see them as characters and not variables.

Replace the above with this instead

Insert into [dbo].[INFO] (FIRST_NAME, LAST_NAME)
Values ( encryptbykey( key_guid('key'),@.FIRST_NAME),
encryptbykey( key_guid('key'),@.LAST_NAME)
)

No magic, I only remove ' around @.FIRST_NAME and @.LAST_NAME

|||Thanks Andreas for your reply|||

ks06,

did it help you? Please mark the reply as answer if that is the case.

Encrypt a column

How do I encrypt a column in an SQL table - say I want to store passwords in
that column?
Thanks.Assuming you are on SQL2005
How To: Encrypt a Column of Data
http://msdn2.microsoft.com/en-us/library/ms179331(en-US,SQL.90).aspx
Cryptographic Functions (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms173744.aspx
Encryption Hierarchy
http://msdn2.microsoft.com/en-us/library/ms189586(en-US,SQL.90).aspx
A couple of blogs as well
http://blogs.msdn.com/lcris/default.aspx
http://blogs.msdn.com/yukondoit/default.aspx
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"M$" <M$@.xyz.com> wrote in message
news:%23kne7taAGHA.1028@.TK2MSFTNGP11.phx.gbl...
> How do I encrypt a column in an SQL table - say I want to store passwords
> in that column?
> Thanks.
>|||M$ wrote:
> How do I encrypt a column in an SQL table - say I want to store passwords in
> that column?
> Thanks.
M$ wrote:
> How do I encrypt a column in an SQL table - say I want to store passwords in
> that column?
> Thanks.
If you are using SQL Server 2005 you can use the EncryptByKey or
EncryptByPassPhrase functions to encrypt data in a column.
Password recovery mechanisms are an inherent security flaw however and
I recommend you avoid them. Don't store the password (encrypted or
otherwise) in the database. Instead, make a secure hash of the password
and store that. For example:
/* Store a password */
DECLARE @.pw NVARCHAR(256), @.salt NVARCHAR(36)
SET @.pw = 'foobar'
SET @.salt = CAST(NEWID() AS NVARCHAR(36))
UPDATE user_passwords
SET pw_salt = @.salt,
pw_hash = HashBytes('MD5', @.salt + @.pw)
WHERE userid = 123 ;
/* Retrieve and compare a password */
DECLARE @.pw NVARCHAR(256), @.userid INT
SET @.pw = 'foobar'
SET @.userid = 123
SELECT CASE pw_hash WHEN HashBytes('MD5', pw_salt + @.pw)
THEN 'Valid'
ELSE 'Invalid' END
FROM user_passwords
WHERE userid = @.userid ;
Conventional wisdom has it that the "salt" value limits the
effectiveness of possible password dictionary attacks. The reality in
today's environment though is that it's far more important to have
mechanisms to prevent users picking easy passwords and to monitor
intrusion attempts. Better yet, use asymetric methods of authentication
that don't require a central password store.
--
David Portas
SQL Server MVP
--

Encrypt a column

How do I encrypt a column in an SQL table - say I want to store passwords in
that column?
Thanks.Assuming you are on SQL2005
How To: Encrypt a Column of Data
http://msdn2.microsoft.com/en-us/library/ms179331(en-US,SQL.90).aspx
Cryptographic Functions (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms173744.aspx
Encryption Hierarchy
http://msdn2.microsoft.com/en-us/library/ms189586(en-US,SQL.90).aspx
A couple of blogs as well
http://blogs.msdn.com/lcris/default.aspx
http://blogs.msdn.com/yukondoit/default.aspx
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"M$" <M$@.xyz.com> wrote in message
news:%23kne7taAGHA.1028@.TK2MSFTNGP11.phx.gbl...
> How do I encrypt a column in an SQL table - say I want to store passwords
> in that column?
> Thanks.
>|||M$ wrote:

> How do I encrypt a column in an SQL table - say I want to store passwords
in
> that column?
> Thanks.
M$ wrote:

> How do I encrypt a column in an SQL table - say I want to store passwords
in
> that column?
> Thanks.
If you are using SQL Server 2005 you can use the EncryptByKey or
EncryptByPassPhrase functions to encrypt data in a column.
Password recovery mechanisms are an inherent security flaw however and
I recommend you avoid them. Don't store the password (encrypted or
otherwise) in the database. Instead, make a secure hash of the password
and store that. For example:
/* Store a password */
DECLARE @.pw NVARCHAR(256), @.salt NVARCHAR(36)
SET @.pw = 'foobar'
SET @.salt = CAST(NEWID() AS NVARCHAR(36))
UPDATE user_passwords
SET pw_salt = @.salt,
pw_hash = HashBytes('MD5', @.salt + @.pw)
WHERE userid = 123 ;
/* Retrieve and compare a password */
DECLARE @.pw NVARCHAR(256), @.userid INT
SET @.pw = 'foobar'
SET @.userid = 123
SELECT CASE pw_hash WHEN HashBytes('MD5', pw_salt + @.pw)
THEN 'Valid'
ELSE 'Invalid' END
FROM user_passwords
WHERE userid = @.userid ;
Conventional wisdom has it that the "salt" value limits the
effectiveness of possible password dictionary attacks. The reality in
today's environment though is that it's far more important to have
mechanisms to prevent users picking easy passwords and to monitor
intrusion attempts. Better yet, use asymetric methods of authentication
that don't require a central password store.
David Portas
SQL Server MVP
--

Encrypt a column

How do I encrypt a column in an SQL table - say I want to store passwords in
that column?
Thanks.
Assuming you are on SQL2005
How To: Encrypt a Column of Data
http://msdn2.microsoft.com/en-us/library/ms179331(en-US,SQL.90).aspx
Cryptographic Functions (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms173744.aspx
Encryption Hierarchy
http://msdn2.microsoft.com/en-us/library/ms189586(en-US,SQL.90).aspx
A couple of blogs as well
http://blogs.msdn.com/lcris/default.aspx
http://blogs.msdn.com/yukondoit/default.aspx
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"M$" <M$@.xyz.com> wrote in message
news:%23kne7taAGHA.1028@.TK2MSFTNGP11.phx.gbl...
> How do I encrypt a column in an SQL table - say I want to store passwords
> in that column?
> Thanks.
>
|||M$ wrote:

> How do I encrypt a column in an SQL table - say I want to store passwords in
> that column?
> Thanks.
M$ wrote:

> How do I encrypt a column in an SQL table - say I want to store passwords in
> that column?
> Thanks.
If you are using SQL Server 2005 you can use the EncryptByKey or
EncryptByPassPhrase functions to encrypt data in a column.
Password recovery mechanisms are an inherent security flaw however and
I recommend you avoid them. Don't store the password (encrypted or
otherwise) in the database. Instead, make a secure hash of the password
and store that. For example:
/* Store a password */
DECLARE @.pw NVARCHAR(256), @.salt NVARCHAR(36)
SET @.pw = 'foobar'
SET @.salt = CAST(NEWID() AS NVARCHAR(36))
UPDATE user_passwords
SET pw_salt = @.salt,
pw_hash = HashBytes('MD5', @.salt + @.pw)
WHERE userid = 123 ;
/* Retrieve and compare a password */
DECLARE @.pw NVARCHAR(256), @.userid INT
SET @.pw = 'foobar'
SET @.userid = 123
SELECT CASE pw_hash WHEN HashBytes('MD5', pw_salt + @.pw)
THEN 'Valid'
ELSE 'Invalid' END
FROM user_passwords
WHERE userid = @.userid ;
Conventional wisdom has it that the "salt" value limits the
effectiveness of possible password dictionary attacks. The reality in
today's environment though is that it's far more important to have
mechanisms to prevent users picking easy passwords and to monitor
intrusion attempts. Better yet, use asymetric methods of authentication
that don't require a central password store.
David Portas
SQL Server MVP