Encryption Varbinary Length

Using the new encryption included in SQL Server 2005, what is a good way to determine what length I should use for the column?

For example, I am encrypting a column, its maxlength is about 30 characters, but when encrypted, the encrypted value extends from between 50 and no more than 68 characters-

So if I had a column with a max of 500 or so characters, how could I know what varbinary length I should set it to if I were to encrypt it, without actually finding the highest value I could possibly fit into the field?

Is it good practice to just make it a varbinary(max) field?

-rob

My recommendation would be to create a dummy value of the maximum possible size (for example, on a varchar(500), create a 500 characters strings) and encrypt it with the same key and parameters you would normally use. That way you know you have reserved enough space to accept the maximum plaintext value your application accepts.

Example:

CREATE SYMMETRIC KEY key_demo WITH ALGORITHM = AES_256

ENCRYPTION BY PASSWORD = 'My 53Cr3+ p@.zzw0rD'

go

OPEN SYMMETRIC KEY key_demo

DECRYPTION BY PASSWORD = 'My 53Cr3+ p@.zzw0rD'

go

DECLARE @.MaxPlaintext nvarchar(500)

DECLARE @.Ciphertext varbinary(8000)

SET @.MaxPlaintext = replicate (N'a', 500)

-- Notice that I am using Unicode and the length is in bytes = 1000

PRINT datalength( @.MaxPlaintext )

-- Using the basic encryption parameters

SET @.Ciphertext = EncryptByKey( key_guid('key_demo'), @.MaxPlaintext )

-- Length using default parameters

PRINT datalength( @.Ciphertext )

-- Using the optional parameters

SET @.Ciphertext = EncryptByKey( key_guid('key_demo'), @.MaxPlaintext, 1, 'Dummy value' )

-- Length using optional parameters

PRINT datalength( @.Ciphertext )

go

I wrote an article (SQL Server 2005 Encryption – Encryption and data length limitations, http://blogs.msdn.com/yukondoit/archive/2005/11/24/496521.aspx) that explains a little bit more about the encryption length limitations in SQL Server 2005. In that article I have a formula that may also help, but I would recommend using the method shown above instead of the formula in the article.

I hope I was able to help you. Please let us know if you have any further questions or feedback.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||This example will work for my needs - I should be able to get away with that as it is not often that a user will use the full length of a column. Thanks for validating.|||Raul, thanks for the solution on encrypting large amount of data. We have tried your approach on some BLOBs (e.g. files stored as binaries). The performance is very dismal though it works. Downloading a 3MB file that's encrypted took more than 45 seconds (the same file without encryption can be downloaded from the same webpage in 5 seconds). Any suggestions? I understand that SQL Server is not an efficient way to store files to begin with but we have to use it due to data center restrictions.|||

Hey Bob,

A lot of factors will impact the performance. Given the dramatic difference in times, I'm wondering if this is actually caused by the query you are using the retrieve the data? Are you only decrypting the blob or are other columns in the table encrypted as well?

I will run some tests on this, but if it is possible can you share the queries you are using with us? Or at least sample queries which approximate how you are retrieving the data?

Thanks,

Sung

|||

Sung:

There is only one field in the DB that's encrypted, the one containing the BLOB. The query is pretty much a copy of what Raul posted on his blog:

--
<edited to remove the source code sample>

|||

Unfortunately the work around I give is not very efficient with data that is larger than a few segments.

The main problem with this workaround (in terms of efficiency) is that the overhead SQL Server needs to pay besides the direct encryption/decryption (lookup for key in key ring, initialize key in cryptographic provider, verify inner header for consistency, etc.) will hit you multiple times for each blob. In the sample you mentioned of a 3MB file, you will be affected by this overhead more than 380 times.

Given the length and nature of the data you are manipulating, I would suggest using a CLR UDF instead and encrypt using CLR cryptographic APIs. The performance should be much better and also much more efficient in terms of space needed (not to mention a much cleaner code than my workaround).

I hope this information will help. Thanks a lot for your feedback.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Raul, thanks for the reply. would a CLR UDF approach still use the SQL 2005's key/certificate infrstructure or it's completely .NET solution with separate keys? I have done file encryptions in .NET using X509 cert and symmetric keys, which are pretty easy to implement, but I'd like to leverage the keys created in SQL 2005 so the key management is consistent with other encrypted data in storage.

In addition, if you could show me some examples of using CLR UDF for SQL encryption that'd be great.

Thanks again

|||

Actually I was thinking of doing the whole key management and encryption directly in CLR due to the huge overhead you would have to pay for using the SQL Server infrastructure for large objects.

Another solution I can think of using SQL Server key management infrastructure would be to split the original plaintext in multiple rows and leverage on multiple client connections to encrypt/decrypt in parallel. For example: Create a table that will store the ciphertext as fragments in multiple rows (i.e. file_id, file_fragment_id used as primary key); your client can split the plaintext in segments (you could use a fixed length for the segments to make the file SEEK easier, let’s say 7K per segment) and using multiple connections send in parallel encrypt and insert requests to SQL Server (the fragment_id will be relative to the offset of the plaintext). As the fragments will be encrypted independently, they can also be decrypted independently and assembled again in the client application after decryption.

Hope this idea will work for you, or at least give you some other ideas.

-Raul Garcia

SDE/T

SQL Server Engine

Encryption speed?

Has anyone started using encryption yet? Is it noticably slower than not using it? For the record Im not refering too column encryption, but "network" (not sure what else to call it) encryption when it's encrypted between SQL Server and the client.We did some encryption testing a while back because a client wanted at-rest encryption. Hardware solutions between the storage and sql server seem to have the least impact on data delivery, while software solutions eat up mega cpu cycles.

There was one product that used a card to do the hardware encryption, but also had software installed as a backup in case of h/w failure so it could at least limp along until the h/w could be replaced.

Either way, there will be some performance loss. How much depends on your system(s). The vendors were more than willing to give us test time.sql

Encryption Question - Urgent!

Hi,

I encrypt a column in a table. I am able to decrypt/encrypt the same successfully. However, when I copy the encrypted data to a new database and try to decrypt using the same certificate, it doesn't work. I have created the same "Master Key" and certificates on the new DB .... So, is it possible to decrypt the encrypted data that is transferred from one DB to another? If not, are there any alternatives ?

I have tried opening the master key on the new DB using the following:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'pwd';

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;

CLOSE MASTER KEY;

Thanks.!

How do you encrypt/decrypt and what error messages are you seeing, if any? Please describe the steps that you followed in more detail.

Thanks
Laurentiu

|||

Here are the details:

In DB1

1. I created a master key (CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'pwd')

2. Cerated a certificate (CREATE CERTIFICATE CERTIFICATE_NAME WITH SUBJECT = 'xxx')

3. Created Symmetric key (CREATE SYMMETRIC KEY KEY_NAME WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE CERTIFICATE_NAME).

Then in DB1, I encrypted a column in a table using ENCRYPTBYKEY. I am also able to decrypt using DECRYPTBYKEY.

Then I inserted the encrypted column into another db with same table structure.

INSERT INTO DB2.dbo.table1(enc_column)

SELECT x.enc_column

FROM DB1.dbo.table1 x

now in DB2, I am not able to decrypt the "enc_column". when I use the same syntax I get a "null".

|||

Thanks for the details. The reason why you cannot decrypt in the new table is because you have no decryption key in the new database.

If you plan to move encrypted data from one database to another, you should create the symmetric key used to encrypt the data in both databases. The way you can do that is by using the same IDENTITY_VALUE and KEY_SOURCE parameters for the CREATE SYMMETRIC KEY statement. Of course, you should use the same key algorithm, as well.

Thanks
Laurentiu

|||

Also, see this recent post for a demo of how to do this:

http://blogs.msdn.com/lcris/archive/2006/07/06/658364.aspx

Thanks
Laurentiu

encryption of column in sqlserver 2000

Hi
I have some sensitive info like Credit card number in some of my
database tables. Is there a way to encrypt these credit card information in
database. I am running SQL server 2000 standard edition
Thanks
MangeshMangesh Deshpande wrote:
> Hi
> I have some sensitive info like Credit card number in some of
> my database tables. Is there a way to encrypt these credit card
> information in database. I am running SQL server 2000 standard edition
> Thanks
> Mangesh
No native support for encryption in the database. You can
encrypt/decrypt in the application if you need to. There are a number of
freely available encryption algorithms you can use. Or you could try
securing the database to keep prying eyes away. What are you mostly
concerned about?
--
David Gugick
Imceda Software
www.imceda.com|||Thanks
I am concerned about the credit card information in the database.
What is the best and most widely encryption tools used with SQL server 2000.
Mangesh
"David Gugick" wrote:
> Mangesh Deshpande wrote:
> > Hi
> >
> > I have some sensitive info like Credit card number in some of
> > my database tables. Is there a way to encrypt these credit card
> > information in database. I am running SQL server 2000 standard edition
> >
> > Thanks
> > Mangesh
> No native support for encryption in the database. You can
> encrypt/decrypt in the application if you need to. There are a number of
> freely available encryption algorithms you can use. Or you could try
> securing the database to keep prying eyes away. What are you mostly
> concerned about?
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Mangesh Deshpande wrote:
> Thanks
> I am concerned about the credit card information in the database.
> What is the best and most widely encryption tools used with SQL
> server 2000.
>
Yes, but why are you concerned. Who is going to be able to access this
data? How are they going to access it? Do you have security in place to
prevent unauthorized access to the underlying tables? How does one
currently access the CC information?
You could remove all access to the table and provide a view with a
computed column that just returns the last 4 digits of the CC number for
display: For example:
Create Table order_info3453453 (
cust_name varchar(25) not null,
cc_nbr varchar(16) not null)
DENY ALL ON order_info3453453 TO public
insert into order_info3453453 values ('David', '234234234234234')
insert into order_info3453453 values ('Jeff', '3784662345444441')
Create View order_info_view4324234
as
Select cust_name, cc_nbr = RIGHT(cc_nbr, 4)
From order_info3453453
grant select on order_info_view4324234 to public
select * from order_info_view4324234
cust_name cc_nbr
-- --
David 4234
David 4234
Jeff 4441
I saw your other post regarding backups, so maybe that's what you're
after.
--
David Gugick
Imceda Software
www.imceda.com|||Thanks David
I am primarily a oracle DBA and we used oracle supplied encryption which
allows the CC info to be accessed from certain IPs. The users are all
protected but management is worried in case the userinfo is disclosed and
unauthorized user try to access the DB he should be blocked from seeing CC
and password info in database by IP and first and second by encryption.
Management kind of like that idea, but now they want me to do the same thing
in sqlserver and was looking for various possible scenarious.
I found xp_encrypt and tested it but I am not sure if there any good tools
avaiable.
"David Gugick" wrote:
> Mangesh Deshpande wrote:
> > Thanks
> > I am concerned about the credit card information in the database.
> > What is the best and most widely encryption tools used with SQL
> > server 2000.
> >
> Yes, but why are you concerned. Who is going to be able to access this
> data? How are they going to access it? Do you have security in place to
> prevent unauthorized access to the underlying tables? How does one
> currently access the CC information?
> You could remove all access to the table and provide a view with a
> computed column that just returns the last 4 digits of the CC number for
> display: For example:
> Create Table order_info3453453 (
> cust_name varchar(25) not null,
> cc_nbr varchar(16) not null)
> DENY ALL ON order_info3453453 TO public
> insert into order_info3453453 values ('David', '234234234234234')
> insert into order_info3453453 values ('Jeff', '3784662345444441')
> Create View order_info_view4324234
> as
> Select cust_name, cc_nbr = RIGHT(cc_nbr, 4)
> From order_info3453453
> grant select on order_info_view4324234 to public
> select * from order_info_view4324234
> cust_name cc_nbr
> -- --
> David 4234
> David 4234
> Jeff 4441
>
> I saw your other post regarding backups, so maybe that's what you're
> after.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Mangesh Deshpande wrote:
> Thanks David
> I am primarily a oracle DBA and we used oracle supplied encryption
> which allows the CC info to be accessed from certain IPs. The users
> are all protected but management is worried in case the userinfo is
> disclosed and unauthorized user try to access the DB he should be
> blocked from seeing CC and password info in database by IP and first
> and second by encryption.
> Management kind of like that idea, but now they want me to do the
> same thing in sqlserver and was looking for various possible
> scenarious.
> I found xp_encrypt and tested it but I am not sure if there any good
> tools avaiable.
Maybe this will help:
http://www.windowsitpro.com/Article/ArticleID/44550/44550.html?Ad=1
Go a google search for ["SQL Server" Encryption] and see what comes up.
There are third-party solutions available for data encryption. You could
also manage this from the app or middle-tier if necessary.
Regarding your comments about unauthorized access:
You can revoke access to the underlying tables using built-in security.
You can grant access to the data to the necessary groups/individuals
using specific grants on stored procedures. If no one has SELECT rights
on the table and cannot execute a procedure that selects the data, then
no one can see it. If you use the computed column technique as I
mentioned in my last post, you can show end users the last 4 digits of
the cc number so those in the company that require access to information
for contact with customers can have enough to go on (obviously they
could also have access to the type of card).
So how would an unauthorized user access the database? Well, first they
would need a valid login and would also need to have sufficient rights
to execute the necessary procedures to return the customer information.
You can probably limit access to the server by IP / MAC Address using
your firewall.
For backups, you do need encryption. And as I mentioned I would check
out LiteSpeed (free trial on web site).
David Gugick
Imceda Software
www.imceda.com|||The encryption/decryption of the data is better handled in the presentation
layer tier of the system. There, you can use the native Crypto32 API calls.
If you are using any of the .NET compilers, these are exposed through
System.Security calls.
Sincerely,
Anthony Thomas
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:eRbgYT8HFHA.3332@.TK2MSFTNGP15.phx.gbl...
Mangesh Deshpande wrote:
> Thanks
> I am concerned about the credit card information in the database.
> What is the best and most widely encryption tools used with SQL
> server 2000.
>
Yes, but why are you concerned. Who is going to be able to access this
data? How are they going to access it? Do you have security in place to
prevent unauthorized access to the underlying tables? How does one
currently access the CC information?
You could remove all access to the table and provide a view with a
computed column that just returns the last 4 digits of the CC number for
display: For example:
Create Table order_info3453453 (
cust_name varchar(25) not null,
cc_nbr varchar(16) not null)
DENY ALL ON order_info3453453 TO public
insert into order_info3453453 values ('David', '234234234234234')
insert into order_info3453453 values ('Jeff', '3784662345444441')
Create View order_info_view4324234
as
Select cust_name, cc_nbr = RIGHT(cc_nbr, 4)
From order_info3453453
grant select on order_info_view4324234 to public
select * from order_info_view4324234
cust_name cc_nbr
-- --
David 4234
David 4234
Jeff 4441
I saw your other post regarding backups, so maybe that's what you're
after.
--
David Gugick
Imceda Software
www.imceda.com|||I personally like this idea, but cannot imagine how to do that. Any clues ?
Regards,
Daniel
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:ek0t#UwKFHA.1620@.TK2MSFTNGP14.phx.gbl...
> The encryption/decryption of the data is better handled in the
presentation
> layer tier of the system. There, you can use the native Crypto32 API
calls.
> If you are using any of the .NET compilers, these are exposed through
> System.Security calls.
> Sincerely,
>
> Anthony Thomas
>
> --
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:eRbgYT8HFHA.3332@.TK2MSFTNGP15.phx.gbl...
> Mangesh Deshpande wrote:
> > Thanks
> > I am concerned about the credit card information in the database.
> > What is the best and most widely encryption tools used with SQL
> > server 2000.
> >
> Yes, but why are you concerned. Who is going to be able to access this
> data? How are they going to access it? Do you have security in place to
> prevent unauthorized access to the underlying tables? How does one
> currently access the CC information?
> You could remove all access to the table and provide a view with a
> computed column that just returns the last 4 digits of the CC number for
> display: For example:
> Create Table order_info3453453 (
> cust_name varchar(25) not null,
> cc_nbr varchar(16) not null)
> DENY ALL ON order_info3453453 TO public
> insert into order_info3453453 values ('David', '234234234234234')
> insert into order_info3453453 values ('Jeff', '3784662345444441')
> Create View order_info_view4324234
> as
> Select cust_name, cc_nbr = RIGHT(cc_nbr, 4)
> From order_info3453453
> grant select on order_info_view4324234 to public
> select * from order_info_view4324234
> cust_name cc_nbr
> -- --
> David 4234
> David 4234
> Jeff 4441
>
> I saw your other post regarding backups, so maybe that's what you're
> after.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||I just told you: the .NET framework exposes the Crypto API through the
System.Security objects.
Sincerely,
Anthony Thomas
"Daniel Joskovski" <omnis@.NOSPAMunetREMOVECAPS.com.mk> wrote in message
news:OaaZaK0LFHA.1144@.TK2MSFTNGP09.phx.gbl...
I personally like this idea, but cannot imagine how to do that. Any clues ?
Regards,
Daniel
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:ek0t#UwKFHA.1620@.TK2MSFTNGP14.phx.gbl...
> The encryption/decryption of the data is better handled in the
presentation
> layer tier of the system. There, you can use the native Crypto32 API
calls.
> If you are using any of the .NET compilers, these are exposed through
> System.Security calls.
> Sincerely,
>
> Anthony Thomas
>
> --
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:eRbgYT8HFHA.3332@.TK2MSFTNGP15.phx.gbl...
> Mangesh Deshpande wrote:
> > Thanks
> > I am concerned about the credit card information in the database.
> > What is the best and most widely encryption tools used with SQL
> > server 2000.
> >
> Yes, but why are you concerned. Who is going to be able to access this
> data? How are they going to access it? Do you have security in place to
> prevent unauthorized access to the underlying tables? How does one
> currently access the CC information?
> You could remove all access to the table and provide a view with a
> computed column that just returns the last 4 digits of the CC number for
> display: For example:
> Create Table order_info3453453 (
> cust_name varchar(25) not null,
> cc_nbr varchar(16) not null)
> DENY ALL ON order_info3453453 TO public
> insert into order_info3453453 values ('David', '234234234234234')
> insert into order_info3453453 values ('Jeff', '3784662345444441')
> Create View order_info_view4324234
> as
> Select cust_name, cc_nbr = RIGHT(cc_nbr, 4)
> From order_info3453453
> grant select on order_info_view4324234 to public
> select * from order_info_view4324234
> cust_name cc_nbr
> -- --
> David 4234
> David 4234
> Jeff 4441
>
> I saw your other post regarding backups, so maybe that's what you're
> after.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Hi Antony,
O.K. I will try to restate the question.
For example I am using datagrid bound to table in some dataset.
Data table is populated when form is loaded, and data source is updated when
I close the form.
So I can use some logic to decrypt data from table when the form is going to
load and I can encrypt data again before the form is closed. Do you suggest
writing special dataadapter class, or might be some different approach ?
Regards,
Daniel
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:uAyhm73LFHA.2420@.TK2MSFTNGP12.phx.gbl...
> I just told you: the .NET framework exposes the Crypto API through the
> System.Security objects.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Daniel Joskovski" <omnis@.NOSPAMunetREMOVECAPS.com.mk> wrote in message
> news:OaaZaK0LFHA.1144@.TK2MSFTNGP09.phx.gbl...
> I personally like this idea, but cannot imagine how to do that. Any clues
?
> Regards,
> Daniel
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:ek0t#UwKFHA.1620@.TK2MSFTNGP14.phx.gbl...
> > The encryption/decryption of the data is better handled in the
> presentation
> > layer tier of the system. There, you can use the native Crypto32 API
> calls.
> > If you are using any of the .NET compilers, these are exposed through
> > System.Security calls.
> >
> > Sincerely,
> >
> >
> > Anthony Thomas
> >
> >
> > --
> >
> > "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> > news:eRbgYT8HFHA.3332@.TK2MSFTNGP15.phx.gbl...
> > Mangesh Deshpande wrote:
> > > Thanks
> > > I am concerned about the credit card information in the database.
> > > What is the best and most widely encryption tools used with SQL
> > > server 2000.
> > >
> >
> > Yes, but why are you concerned. Who is going to be able to access this
> > data? How are they going to access it? Do you have security in place to
> > prevent unauthorized access to the underlying tables? How does one
> > currently access the CC information?
> >
> > You could remove all access to the table and provide a view with a
> > computed column that just returns the last 4 digits of the CC number for
> > display: For example:
> >
> > Create Table order_info3453453 (
> > cust_name varchar(25) not null,
> > cc_nbr varchar(16) not null)
> >
> > DENY ALL ON order_info3453453 TO public
> >
> > insert into order_info3453453 values ('David', '234234234234234')
> > insert into order_info3453453 values ('Jeff', '3784662345444441')
> >
> > Create View order_info_view4324234
> > as
> > Select cust_name, cc_nbr = RIGHT(cc_nbr, 4)
> > From order_info3453453
> >
> > grant select on order_info_view4324234 to public
> >
> > select * from order_info_view4324234
> >
> > cust_name cc_nbr
> > -- --
> > David 4234
> > David 4234
> > Jeff 4441
> >
> >
> > I saw your other post regarding backups, so maybe that's what you're
> > after.
> > --
> > David Gugick
> > Imceda Software
> > www.imceda.com
> >
> >
>|||If you do it that way, your system will be SLOOOOW.
I would bind your grid to the desciptive part of the result set (unencrypted
parts). Then, when you users find a particular result they want additional
information on or want to modify, I would launch an independent dialogue
that decrypts the entire contents, but for that one row only.
Allowing users to edit a bound grid is a bad design choice for concurrent
systems anyway, and this way you will only have to run through the
decrypt/encrypt algorythms only when a use specifically request that single
piece of information. You do not want to have to incur this additional
logic while the users are merely searching for the information.
Sincerely,
Anthony Thomas
"Daniel Joskovski" <omnis@.NOSPAMunetREMOVECAPS.com.mk> wrote in message
news:e9hiS$AMFHA.3184@.TK2MSFTNGP09.phx.gbl...
Hi Antony,
O.K. I will try to restate the question.
For example I am using datagrid bound to table in some dataset.
Data table is populated when form is loaded, and data source is updated when
I close the form.
So I can use some logic to decrypt data from table when the form is going to
load and I can encrypt data again before the form is closed. Do you suggest
writing special dataadapter class, or might be some different approach ?
Regards,
Daniel
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:uAyhm73LFHA.2420@.TK2MSFTNGP12.phx.gbl...
> I just told you: the .NET framework exposes the Crypto API through the
> System.Security objects.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Daniel Joskovski" <omnis@.NOSPAMunetREMOVECAPS.com.mk> wrote in message
> news:OaaZaK0LFHA.1144@.TK2MSFTNGP09.phx.gbl...
> I personally like this idea, but cannot imagine how to do that. Any clues
?
> Regards,
> Daniel
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:ek0t#UwKFHA.1620@.TK2MSFTNGP14.phx.gbl...
> > The encryption/decryption of the data is better handled in the
> presentation
> > layer tier of the system. There, you can use the native Crypto32 API
> calls.
> > If you are using any of the .NET compilers, these are exposed through
> > System.Security calls.
> >
> > Sincerely,
> >
> >
> > Anthony Thomas
> >
> >
> > --
> >
> > "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> > news:eRbgYT8HFHA.3332@.TK2MSFTNGP15.phx.gbl...
> > Mangesh Deshpande wrote:
> > > Thanks
> > > I am concerned about the credit card information in the database.
> > > What is the best and most widely encryption tools used with SQL
> > > server 2000.
> > >
> >
> > Yes, but why are you concerned. Who is going to be able to access this
> > data? How are they going to access it? Do you have security in place to
> > prevent unauthorized access to the underlying tables? How does one
> > currently access the CC information?
> >
> > You could remove all access to the table and provide a view with a
> > computed column that just returns the last 4 digits of the CC number for
> > display: For example:
> >
> > Create Table order_info3453453 (
> > cust_name varchar(25) not null,
> > cc_nbr varchar(16) not null)
> >
> > DENY ALL ON order_info3453453 TO public
> >
> > insert into order_info3453453 values ('David', '234234234234234')
> > insert into order_info3453453 values ('Jeff', '3784662345444441')
> >
> > Create View order_info_view4324234
> > as
> > Select cust_name, cc_nbr = RIGHT(cc_nbr, 4)
> > From order_info3453453
> >
> > grant select on order_info_view4324234 to public
> >
> > select * from order_info_view4324234
> >
> > cust_name cc_nbr
> > -- --
> > David 4234
> > David 4234
> > Jeff 4441
> >
> >
> > I saw your other post regarding backups, so maybe that's what you're
> > after.
> > --
> > David Gugick
> > Imceda Software
> > www.imceda.com
> >
> >
>

encryption of column in sqlserver 2000

Hi
I have some sensitive info like Credit card number in some of my
database tables. Is there a way to encrypt these credit card information in
database. I am running SQL server 2000 standard edition
Thanks
MangeshMangesh Deshpande wrote:
> Hi
> I have some sensitive info like Credit card number in some of
> my database tables. Is there a way to encrypt these credit card
> information in database. I am running SQL server 2000 standard edition
> Thanks
> Mangesh
No native support for encryption in the database. You can
encrypt/decrypt in the application if you need to. There are a number of
freely available encryption algorithms you can use. Or you could try
securing the database to keep prying eyes away. What are you mostly
concerned about?
David Gugick
Imceda Software
www.imceda.com|||Thanks
I am concerned about the credit card information in the database.
What is the best and most widely encryption tools used with SQL server 2000.
Mangesh
"David Gugick" wrote:

> Mangesh Deshpande wrote:
> No native support for encryption in the database. You can
> encrypt/decrypt in the application if you need to. There are a number of
> freely available encryption algorithms you can use. Or you could try
> securing the database to keep prying eyes away. What are you mostly
> concerned about?
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Mangesh Deshpande wrote:
> Thanks
> I am concerned about the credit card information in the database.
> What is the best and most widely encryption tools used with SQL
> server 2000.
>
Yes, but why are you concerned. Who is going to be able to access this
data? How are they going to access it? Do you have security in place to
prevent unauthorized access to the underlying tables? How does one
currently access the CC information?
You could remove all access to the table and provide a view with a
computed column that just returns the last 4 digits of the CC number for
display: For example:
Create Table order_info3453453 (
cust_name varchar(25) not null,
cc_nbr varchar(16) not null)
DENY ALL ON order_info3453453 TO public
insert into order_info3453453 values ('David', '234234234234234')
insert into order_info3453453 values ('Jeff', '3784662345444441')
Create View order_info_view4324234
as
Select cust_name, cc_nbr = RIGHT(cc_nbr, 4)
From order_info3453453
grant select on order_info_view4324234 to public
select * from order_info_view4324234
cust_name cc_nbr
-- --
David 4234
David 4234
Jeff 4441
I saw your other post regarding backups, so maybe that's what you're
after.
--
David Gugick
Imceda Software
www.imceda.com|||Thanks David
I am primarily a oracle DBA and we used oracle supplied encryption which
allows the CC info to be accessed from certain IPs. The users are all
protected but management is worried in case the userinfo is disclosed and
unauthorized user try to access the DB he should be blocked from seeing CC
and password info in database by IP and first and second by encryption.
Management kind of like that idea, but now they want me to do the same thing
in sqlserver and was looking for various possible scenarious.
I found xp_encrypt and tested it but I am not sure if there any good tools
avaiable.
"David Gugick" wrote:

> Mangesh Deshpande wrote:
> Yes, but why are you concerned. Who is going to be able to access this
> data? How are they going to access it? Do you have security in place to
> prevent unauthorized access to the underlying tables? How does one
> currently access the CC information?
> You could remove all access to the table and provide a view with a
> computed column that just returns the last 4 digits of the CC number for
> display: For example:
> Create Table order_info3453453 (
> cust_name varchar(25) not null,
> cc_nbr varchar(16) not null)
> DENY ALL ON order_info3453453 TO public
> insert into order_info3453453 values ('David', '234234234234234')
> insert into order_info3453453 values ('Jeff', '3784662345444441')
> Create View order_info_view4324234
> as
> Select cust_name, cc_nbr = RIGHT(cc_nbr, 4)
> From order_info3453453
> grant select on order_info_view4324234 to public
> select * from order_info_view4324234
> cust_name cc_nbr
> -- --
> David 4234
> David 4234
> Jeff 4441
>
> I saw your other post regarding backups, so maybe that's what you're
> after.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Mangesh Deshpande wrote:
> Thanks David
> I am primarily a oracle DBA and we used oracle supplied encryption
> which allows the CC info to be accessed from certain IPs. The users
> are all protected but management is worried in case the userinfo is
> disclosed and unauthorized user try to access the DB he should be
> blocked from seeing CC and password info in database by IP and first
> and second by encryption.
> Management kind of like that idea, but now they want me to do the
> same thing in sqlserver and was looking for various possible
> scenarious.
> I found xp_encrypt and tested it but I am not sure if there any good
> tools avaiable.
Maybe this will help:
http://www.windowsitpro.com/Article...44550.html?Ad=1
Go a google search for ["SQL Server" Encryption] and see what comes up.
There are third-party solutions available for data encryption. You could
also manage this from the app or middle-tier if necessary.
Regarding your comments about unauthorized access:
You can revoke access to the underlying tables using built-in security.
You can grant access to the data to the necessary groups/individuals
using specific grants on stored procedures. If no one has SELECT rights
on the table and cannot execute a procedure that selects the data, then
no one can see it. If you use the computed column technique as I
mentioned in my last post, you can show end users the last 4 digits of
the cc number so those in the company that require access to information
for contact with customers can have enough to go on (obviously they
could also have access to the type of card).
So how would an unauthorized user access the database? Well, first they
would need a valid login and would also need to have sufficient rights
to execute the necessary procedures to return the customer information.
You can probably limit access to the server by IP / MAC Address using
your firewall.
For backups, you do need encryption. And as I mentioned I would check
out LiteSpeed (free trial on web site).
David Gugick
Imceda Software
www.imceda.com|||The encryption/decryption of the data is better handled in the presentation
layer tier of the system. There, you can use the native Crypto32 API calls.
If you are using any of the .NET compilers, these are exposed through
System.Security calls.
Sincerely,
Anthony Thomas
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:eRbgYT8HFHA.3332@.TK2MSFTNGP15.phx.gbl...
Mangesh Deshpande wrote:
> Thanks
> I am concerned about the credit card information in the database.
> What is the best and most widely encryption tools used with SQL
> server 2000.
>
Yes, but why are you concerned. Who is going to be able to access this
data? How are they going to access it? Do you have security in place to
prevent unauthorized access to the underlying tables? How does one
currently access the CC information?
You could remove all access to the table and provide a view with a
computed column that just returns the last 4 digits of the CC number for
display: For example:
Create Table order_info3453453 (
cust_name varchar(25) not null,
cc_nbr varchar(16) not null)
DENY ALL ON order_info3453453 TO public
insert into order_info3453453 values ('David', '234234234234234')
insert into order_info3453453 values ('Jeff', '3784662345444441')
Create View order_info_view4324234
as
Select cust_name, cc_nbr = RIGHT(cc_nbr, 4)
From order_info3453453
grant select on order_info_view4324234 to public
select * from order_info_view4324234
cust_name cc_nbr
-- --
David 4234
David 4234
Jeff 4441
I saw your other post regarding backups, so maybe that's what you're
after.
--
David Gugick
Imceda Software
www.imceda.com|||I personally like this idea, but cannot imagine how to do that. Any clues ?
Regards,
Daniel
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:ek0t#UwKFHA.1620@.TK2MSFTNGP14.phx.gbl...
> The encryption/decryption of the data is better handled in the
presentation
> layer tier of the system. There, you can use the native Crypto32 API
calls.
> If you are using any of the .NET compilers, these are exposed through
> System.Security calls.
> Sincerely,
>
> Anthony Thomas
>
> --
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:eRbgYT8HFHA.3332@.TK2MSFTNGP15.phx.gbl...
> Mangesh Deshpande wrote:
> Yes, but why are you concerned. Who is going to be able to access this
> data? How are they going to access it? Do you have security in place to
> prevent unauthorized access to the underlying tables? How does one
> currently access the CC information?
> You could remove all access to the table and provide a view with a
> computed column that just returns the last 4 digits of the CC number for
> display: For example:
> Create Table order_info3453453 (
> cust_name varchar(25) not null,
> cc_nbr varchar(16) not null)
> DENY ALL ON order_info3453453 TO public
> insert into order_info3453453 values ('David', '234234234234234')
> insert into order_info3453453 values ('Jeff', '3784662345444441')
> Create View order_info_view4324234
> as
> Select cust_name, cc_nbr = RIGHT(cc_nbr, 4)
> From order_info3453453
> grant select on order_info_view4324234 to public
> select * from order_info_view4324234
> cust_name cc_nbr
> -- --
> David 4234
> David 4234
> Jeff 4441
>
> I saw your other post regarding backups, so maybe that's what you're
> after.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||I just told you: the .NET framework exposes the Crypto API through the
System.Security objects.
Sincerely,
Anthony Thomas
"Daniel Joskovski" <omnis@.NOSPAMunetREMOVECAPS.com.mk> wrote in message
news:OaaZaK0LFHA.1144@.TK2MSFTNGP09.phx.gbl...
I personally like this idea, but cannot imagine how to do that. Any clues ?
Regards,
Daniel
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:ek0t#UwKFHA.1620@.TK2MSFTNGP14.phx.gbl...
> The encryption/decryption of the data is better handled in the
presentation
> layer tier of the system. There, you can use the native Crypto32 API
calls.
> If you are using any of the .NET compilers, these are exposed through
> System.Security calls.
> Sincerely,
>
> Anthony Thomas
>
> --
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:eRbgYT8HFHA.3332@.TK2MSFTNGP15.phx.gbl...
> Mangesh Deshpande wrote:
> Yes, but why are you concerned. Who is going to be able to access this
> data? How are they going to access it? Do you have security in place to
> prevent unauthorized access to the underlying tables? How does one
> currently access the CC information?
> You could remove all access to the table and provide a view with a
> computed column that just returns the last 4 digits of the CC number for
> display: For example:
> Create Table order_info3453453 (
> cust_name varchar(25) not null,
> cc_nbr varchar(16) not null)
> DENY ALL ON order_info3453453 TO public
> insert into order_info3453453 values ('David', '234234234234234')
> insert into order_info3453453 values ('Jeff', '3784662345444441')
> Create View order_info_view4324234
> as
> Select cust_name, cc_nbr = RIGHT(cc_nbr, 4)
> From order_info3453453
> grant select on order_info_view4324234 to public
> select * from order_info_view4324234
> cust_name cc_nbr
> -- --
> David 4234
> David 4234
> Jeff 4441
>
> I saw your other post regarding backups, so maybe that's what you're
> after.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Hi Antony,
O.K. I will try to restate the question.
For example I am using datagrid bound to table in some dataset.
Data table is populated when form is loaded, and data source is updated when
I close the form.
So I can use some logic to decrypt data from table when the form is going to
load and I can encrypt data again before the form is closed. Do you suggest
writing special dataadapter class, or might be some different approach ?
Regards,
Daniel
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:uAyhm73LFHA.2420@.TK2MSFTNGP12.phx.gbl...
> I just told you: the .NET framework exposes the Crypto API through the
> System.Security objects.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Daniel Joskovski" <omnis@.NOSPAMunetREMOVECAPS.com.mk> wrote in message
> news:OaaZaK0LFHA.1144@.TK2MSFTNGP09.phx.gbl...
> I personally like this idea, but cannot imagine how to do that. Any clues
?
> Regards,
> Daniel
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:ek0t#UwKFHA.1620@.TK2MSFTNGP14.phx.gbl...
> presentation
> calls.
>

encryption of column in sqlserver 2000

Hi
I have some sensitive info like Credit card number in some of my
database tables. Is there a way to encrypt these credit card information in
database. I am running SQL server 2000 standard edition
Thanks
Mangesh
Mangesh Deshpande wrote:
> Hi
> I have some sensitive info like Credit card number in some of
> my database tables. Is there a way to encrypt these credit card
> information in database. I am running SQL server 2000 standard edition
> Thanks
> Mangesh
No native support for encryption in the database. You can
encrypt/decrypt in the application if you need to. There are a number of
freely available encryption algorithms you can use. Or you could try
securing the database to keep prying eyes away. What are you mostly
concerned about?
David Gugick
Imceda Software
www.imceda.com
|||Thanks
I am concerned about the credit card information in the database.
What is the best and most widely encryption tools used with SQL server 2000.
Mangesh
"David Gugick" wrote:

> Mangesh Deshpande wrote:
> No native support for encryption in the database. You can
> encrypt/decrypt in the application if you need to. There are a number of
> freely available encryption algorithms you can use. Or you could try
> securing the database to keep prying eyes away. What are you mostly
> concerned about?
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Mangesh Deshpande wrote:
> Thanks
> I am concerned about the credit card information in the database.
> What is the best and most widely encryption tools used with SQL
> server 2000.
>
Yes, but why are you concerned. Who is going to be able to access this
data? How are they going to access it? Do you have security in place to
prevent unauthorized access to the underlying tables? How does one
currently access the CC information?
You could remove all access to the table and provide a view with a
computed column that just returns the last 4 digits of the CC number for
display: For example:
Create Table order_info3453453 (
cust_name varchar(25) not null,
cc_nbr varchar(16) not null)
DENY ALL ON order_info3453453 TO public
insert into order_info3453453 values ('David', '234234234234234')
insert into order_info3453453 values ('Jeff', '3784662345444441')
Create View order_info_view4324234
as
Select cust_name, cc_nbr = RIGHT(cc_nbr, 4)
From order_info3453453
grant select on order_info_view4324234 to public
select * from order_info_view4324234
cust_name cc_nbr
-- --
David 4234
David 4234
Jeff 4441
I saw your other post regarding backups, so maybe that's what you're
after.
David Gugick
Imceda Software
www.imceda.com
|||Thanks David
I am primarily a oracle DBA and we used oracle supplied encryption which
allows the CC info to be accessed from certain IPs. The users are all
protected but management is worried in case the userinfo is disclosed and
unauthorized user try to access the DB he should be blocked from seeing CC
and password info in database by IP and first and second by encryption.
Management kind of like that idea, but now they want me to do the same thing
in sqlserver and was looking for various possible scenarious.
I found xp_encrypt and tested it but I am not sure if there any good tools
avaiable.
"David Gugick" wrote:

> Mangesh Deshpande wrote:
> Yes, but why are you concerned. Who is going to be able to access this
> data? How are they going to access it? Do you have security in place to
> prevent unauthorized access to the underlying tables? How does one
> currently access the CC information?
> You could remove all access to the table and provide a view with a
> computed column that just returns the last 4 digits of the CC number for
> display: For example:
> Create Table order_info3453453 (
> cust_name varchar(25) not null,
> cc_nbr varchar(16) not null)
> DENY ALL ON order_info3453453 TO public
> insert into order_info3453453 values ('David', '234234234234234')
> insert into order_info3453453 values ('Jeff', '3784662345444441')
> Create View order_info_view4324234
> as
> Select cust_name, cc_nbr = RIGHT(cc_nbr, 4)
> From order_info3453453
> grant select on order_info_view4324234 to public
> select * from order_info_view4324234
> cust_name cc_nbr
> -- --
> David 4234
> David 4234
> Jeff 4441
>
> I saw your other post regarding backups, so maybe that's what you're
> after.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Mangesh Deshpande wrote:
> Thanks David
> I am primarily a oracle DBA and we used oracle supplied encryption
> which allows the CC info to be accessed from certain IPs. The users
> are all protected but management is worried in case the userinfo is
> disclosed and unauthorized user try to access the DB he should be
> blocked from seeing CC and password info in database by IP and first
> and second by encryption.
> Management kind of like that idea, but now they want me to do the
> same thing in sqlserver and was looking for various possible
> scenarious.
> I found xp_encrypt and tested it but I am not sure if there any good
> tools avaiable.
Maybe this will help:
http://www.windowsitpro.com/Article/...4550.html?Ad=1
Go a google search for ["SQL Server" Encryption] and see what comes up.
There are third-party solutions available for data encryption. You could
also manage this from the app or middle-tier if necessary.
Regarding your comments about unauthorized access:
You can revoke access to the underlying tables using built-in security.
You can grant access to the data to the necessary groups/individuals
using specific grants on stored procedures. If no one has SELECT rights
on the table and cannot execute a procedure that selects the data, then
no one can see it. If you use the computed column technique as I
mentioned in my last post, you can show end users the last 4 digits of
the cc number so those in the company that require access to information
for contact with customers can have enough to go on (obviously they
could also have access to the type of card).
So how would an unauthorized user access the database? Well, first they
would need a valid login and would also need to have sufficient rights
to execute the necessary procedures to return the customer information.
You can probably limit access to the server by IP / MAC Address using
your firewall.
For backups, you do need encryption. And as I mentioned I would check
out LiteSpeed (free trial on web site).
David Gugick
Imceda Software
www.imceda.com
|||The encryption/decryption of the data is better handled in the presentation
layer tier of the system. There, you can use the native Crypto32 API calls.
If you are using any of the .NET compilers, these are exposed through
System.Security calls.
Sincerely,
Anthony Thomas

"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:eRbgYT8HFHA.3332@.TK2MSFTNGP15.phx.gbl...
Mangesh Deshpande wrote:
> Thanks
> I am concerned about the credit card information in the database.
> What is the best and most widely encryption tools used with SQL
> server 2000.
>
Yes, but why are you concerned. Who is going to be able to access this
data? How are they going to access it? Do you have security in place to
prevent unauthorized access to the underlying tables? How does one
currently access the CC information?
You could remove all access to the table and provide a view with a
computed column that just returns the last 4 digits of the CC number for
display: For example:
Create Table order_info3453453 (
cust_name varchar(25) not null,
cc_nbr varchar(16) not null)
DENY ALL ON order_info3453453 TO public
insert into order_info3453453 values ('David', '234234234234234')
insert into order_info3453453 values ('Jeff', '3784662345444441')
Create View order_info_view4324234
as
Select cust_name, cc_nbr = RIGHT(cc_nbr, 4)
From order_info3453453
grant select on order_info_view4324234 to public
select * from order_info_view4324234
cust_name cc_nbr
-- --
David 4234
David 4234
Jeff 4441
I saw your other post regarding backups, so maybe that's what you're
after.
David Gugick
Imceda Software
www.imceda.com
|||I personally like this idea, but cannot imagine how to do that. Any clues ?
Regards,
Daniel
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:ek0t#UwKFHA.1620@.TK2MSFTNGP14.phx.gbl...
> The encryption/decryption of the data is better handled in the
presentation
> layer tier of the system. There, you can use the native Crypto32 API
calls.
> If you are using any of the .NET compilers, these are exposed through
> System.Security calls.
> Sincerely,
>
> Anthony Thomas
>
> --
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:eRbgYT8HFHA.3332@.TK2MSFTNGP15.phx.gbl...
> Mangesh Deshpande wrote:
> Yes, but why are you concerned. Who is going to be able to access this
> data? How are they going to access it? Do you have security in place to
> prevent unauthorized access to the underlying tables? How does one
> currently access the CC information?
> You could remove all access to the table and provide a view with a
> computed column that just returns the last 4 digits of the CC number for
> display: For example:
> Create Table order_info3453453 (
> cust_name varchar(25) not null,
> cc_nbr varchar(16) not null)
> DENY ALL ON order_info3453453 TO public
> insert into order_info3453453 values ('David', '234234234234234')
> insert into order_info3453453 values ('Jeff', '3784662345444441')
> Create View order_info_view4324234
> as
> Select cust_name, cc_nbr = RIGHT(cc_nbr, 4)
> From order_info3453453
> grant select on order_info_view4324234 to public
> select * from order_info_view4324234
> cust_name cc_nbr
> -- --
> David 4234
> David 4234
> Jeff 4441
>
> I saw your other post regarding backups, so maybe that's what you're
> after.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||I just told you: the .NET framework exposes the Crypto API through the
System.Security objects.
Sincerely,
Anthony Thomas

"Daniel Joskovski" <omnis@.NOSPAMunetREMOVECAPS.com.mk> wrote in message
news:OaaZaK0LFHA.1144@.TK2MSFTNGP09.phx.gbl...
I personally like this idea, but cannot imagine how to do that. Any clues ?
Regards,
Daniel
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:ek0t#UwKFHA.1620@.TK2MSFTNGP14.phx.gbl...
> The encryption/decryption of the data is better handled in the
presentation
> layer tier of the system. There, you can use the native Crypto32 API
calls.
> If you are using any of the .NET compilers, these are exposed through
> System.Security calls.
> Sincerely,
>
> Anthony Thomas
>
> --
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:eRbgYT8HFHA.3332@.TK2MSFTNGP15.phx.gbl...
> Mangesh Deshpande wrote:
> Yes, but why are you concerned. Who is going to be able to access this
> data? How are they going to access it? Do you have security in place to
> prevent unauthorized access to the underlying tables? How does one
> currently access the CC information?
> You could remove all access to the table and provide a view with a
> computed column that just returns the last 4 digits of the CC number for
> display: For example:
> Create Table order_info3453453 (
> cust_name varchar(25) not null,
> cc_nbr varchar(16) not null)
> DENY ALL ON order_info3453453 TO public
> insert into order_info3453453 values ('David', '234234234234234')
> insert into order_info3453453 values ('Jeff', '3784662345444441')
> Create View order_info_view4324234
> as
> Select cust_name, cc_nbr = RIGHT(cc_nbr, 4)
> From order_info3453453
> grant select on order_info_view4324234 to public
> select * from order_info_view4324234
> cust_name cc_nbr
> -- --
> David 4234
> David 4234
> Jeff 4441
>
> I saw your other post regarding backups, so maybe that's what you're
> after.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Hi Antony,
O.K. I will try to restate the question.
For example I am using datagrid bound to table in some dataset.
Data table is populated when form is loaded, and data source is updated when
I close the form.
So I can use some logic to decrypt data from table when the form is going to
load and I can encrypt data again before the form is closed. Do you suggest
writing special dataadapter class, or might be some different approach ?
Regards,
Daniel
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:uAyhm73LFHA.2420@.TK2MSFTNGP12.phx.gbl...
> I just told you: the .NET framework exposes the Crypto API through the
> System.Security objects.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Daniel Joskovski" <omnis@.NOSPAMunetREMOVECAPS.com.mk> wrote in message
> news:OaaZaK0LFHA.1144@.TK2MSFTNGP09.phx.gbl...
> I personally like this idea, but cannot imagine how to do that. Any clues
?
> Regards,
> Daniel
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:ek0t#UwKFHA.1620@.TK2MSFTNGP14.phx.gbl...
> presentation
> calls.
>
sql

Encryption of Column

Hi,

Is there any possible way, that while I transferring a table from another table, I can specifically encrypt a column? Is there any step on how can I do this?

Need Help Badly

Thanks

There is no stock encrypt task or transform in this release. However, that needn't stop you.

Kirk Haselden's SSIS book has a CryptoTask which can be adapted from a custom task to a custom transform, or more easily, a script transform.

The options for column encryption right now, are to write code for in-pipeline transforms, or to have an auto-encrypting destination.

In-pipeline encryption would require writing a custom/script tranform to modify the data in the pipeline, or a custom/script destination which call's SQL server's EncryptBykey() function, for example, to modify the data at endpoint.

There may already be third party encryption pipeline transforms available, I'm just not aware of any yet. When I get a chance, I'll post a script transform which uses the RijndaelManaged object similar to Kirk's CryptoTask.|||

Hi

is CryptoTask a third party ? Where Can I Find it? Is there any possible way? I mean more simplier way?

Thanks

|||

arsonist wrote:

Hi

is CryptoTask a third party ? Where Can I Find it? Is there any possible way? I mean more simplier way?

Thanks

You can stage your data in an intermediate table and then use SQL Server's encryption utilities by using an Execute SQL task to call those utilities against your staging table.

Encryption in SSIS Package

Hello,

I want to import data from a excel source to SQL Server 2005 using SSIS. Among all the calls columns to be imported, there is 1 column which needs to be encrypted using asymmetric encryption and stored in destination table. Can anybody guide me how to program SSIS package using encryption function.

JatinShah wrote:

Hello,

I want to import data from a excel source to SQL Server 2005 using SSIS. Among all the calls columns to be imported, there is 1 column which needs to be encrypted using asymmetric encryption and stored in destination table. Can anybody guide me how to program SSIS package using encryption function.

I think Donald Farmer's book contains some information on how to do this. You'll be able to find it at all the usual places.

-Jamie

|||

You would need to use the Script Component, so you can use VB.Net to do the work.

Have you found out how to write the encryption functions in VB.Net, if not try this-

Walkthrough: Encrypting and Decrypting Strings in Visual Basic
(http://msdn2.microsoft.com/en-us/library/ms172831.aspx)

Then just wrap that into a Script Component.

|||

Hello Jamie,

Could you please get me the name of the book.

Thank You

Jatin Shah

|||

JatinShah wrote:

Hello Jamie,

Could you please get me the name of the book.

Thank You

Jatin Shah

http://amazon.com/s/ref=nb_ss_gw/102-7891523-4086513?url=search-alias%3Daps&field-keywords=donald+farmer

Encryption in SSIS Package

Hello,

I want to import data from a excel source to SQL Server 2005 using SSIS. Among all the calls columns to be imported, there is 1 column which needs to be encrypted using asymmetric encryption and stored in destination table. Can anybody guide me how to program SSIS package using encryption function.

JatinShah wrote:

Hello,

I want to import data from a excel source to SQL Server 2005 using SSIS. Among all the calls columns to be imported, there is 1 column which needs to be encrypted using asymmetric encryption and stored in destination table. Can anybody guide me how to program SSIS package using encryption function.

I think Donald Farmer's book contains some information on how to do this. You'll be able to find it at all the usual places.

-Jamie

|||

You would need to use the Script Component, so you can use VB.Net to do the work.

Have you found out how to write the encryption functions in VB.Net, if not try this-

Walkthrough: Encrypting and Decrypting Strings in Visual Basic
(http://msdn2.microsoft.com/en-us/library/ms172831.aspx)

Then just wrap that into a Script Component.

|||

Hello Jamie,

Could you please get me the name of the book.

Thank You

Jatin Shah

|||

JatinShah wrote:

Hello Jamie,

Could you please get me the name of the book.

Thank You

Jatin Shah

http://amazon.com/s/ref=nb_ss_gw/102-7891523-4086513?url=search-alias%3Daps&field-keywords=donald+farmer

encryption in sql server 2005

Hi All,

Does any body know how to use encryption in sql server 2005.Is it
possible to encrypt a particular column in a table.

thanksshark (xavier.sharon@.gmail.com) writes:
> Does any body know how to use encryption in sql server 2005.Is it
> possible to encrypt a particular column in a table.

Yes, you can encrypt column data. A good place to start is this section
in Books Online:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/96c276d5-1bba-4e95-b678-10f059f1fbcf.htm

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I've done a bit of work with Asymetic keys based on an article.
I dont believe this requires a login:

http://www.sqlservercentral.com/col...cencryption.asp
HTH
rob

shark wrote:
> Hi All,
> Does any body know how to use encryption in sql server 2005.Is it
> possible to encrypt a particular column in a table.
> thanks|||I'm just curios, what is the point of encrypting the column info if a
stored proc can be run which decrypts the contents? What is the main
goal of the encryption, just for casual browsers or to do the
encryption only in the DB side and the decryption in another layer, or
is that possible?

rcamarda wrote:
> I've done a bit of work with Asymetic keys based on an article.
> I dont believe this requires a login:
> http://www.sqlservercentral.com/col...cencryption.asp
> HTH
> rob
> shark wrote:
> > Hi All,
> > Does any body know how to use encryption in sql server 2005.Is it
> > possible to encrypt a particular column in a table.
> > thanks

encryption

Hi
Is there any SQL Server encryption to hide the contents of fields
That is I do have a password column and I want to hide/encrpt it from users
to see it.
Is there any way to do it --like turn encrption ON for a column ?
Is there any way SQL Server help us for this ?
Thanks in advanceIs there any SQL Server encryption to hide the contents of
fields
That is I do have a password column and I want to
hide/encrpt it from users
to see it.
Yes. See permissions-sql server in BOL.
>--Original Message--
>Hi
>Is there any SQL Server encryption to hide the contents
of fields
>That is I do have a password column and I want to
hide/encrpt it from users
>to see it.
>Is there any way to do it --like turn encrption ON for a
column ?
>Is there any way SQL Server help us for this ?
>Thanks in advance
>
>.
>|||right, normally encrypt it NOT let the user see it. BUT
they still need to access the column to get verify their
log on.
i am using DES to encrypt it in my user profile password
column.
>--Original Message--
>There is no column level encryption directly in SQL
Server 2000. You can
>use security features in SQL Server to prevent users from
accessing the
>fields at all, however if you give them permission to
access the field then
>they can see the data. If you need to encrypt the data
then you can do it
>in your application or there several 3rd party encryption
packages that will
>handle this for you. For example:
>http://www.netlib.com/sql-server-encryption.htm
>http://www.protegrity.com/pdf/SD_222_SQL_Datasheet_FINAL_v
4.pdf
>
>--
>Hal Berenson, SQL Server MVP
>True Mountain Group LLC
>
>"Abraham" <binu_ca@.yahoo.com> wrote in message
>news:exFZI3ZQDHA.1024@.TK2MSFTNGP12.phx.gbl...
>> Hi
>> Is there any SQL Server encryption to hide the contents
of fields
>> That is I do have a password column and I want to
hide/encrpt it from
>users
>> to see it.
>> Is there any way to do it --like turn encrption ON for
a column ?
>> Is there any way SQL Server help us for this ?
>> Thanks in advance
>>
>
>.
>|||Unless there is a complelling need to be able to read the original password,
you're probably better of storing a password hash. You can still use the
hash to _verify_ the password, you just can't _read_ the original password.
This is why very few systems these days store the password using reversible
encryption...there's just no need.
Check out my previous post:
http://groups.google.com/groups?&hl=en&lr=&ie=UTF-8&selm=0uacnTNyHJ5plZuiRTvUqQ%40speakeasy.net&rnum=2
--
Dan Farino
Sr. Systems Engineer
Stamps.com, Inc.
news.danATstamps.com
"leecs" <leecs@.silverlgobe.com> wrote in message
news:04a101c34290$dd7349f0$a301280a@.phx.gbl...
> right, normally encrypt it NOT let the user see it. BUT
> they still need to access the column to get verify their
> log on.
> i am using DES to encrypt it in my user profile password
> column.
> >--Original Message--
> >There is no column level encryption directly in SQL
> Server 2000. You can
> >use security features in SQL Server to prevent users from
> accessing the
> >fields at all, however if you give them permission to
> access the field then
> >they can see the data. If you need to encrypt the data
> then you can do it
> >in your application or there several 3rd party encryption
> packages that will
> >handle this for you. For example:
> >
> >http://www.netlib.com/sql-server-encryption.htm
> >http://www.protegrity.com/pdf/SD_222_SQL_Datasheet_FINAL_v
> 4.pdf
> >
> >
> >--
> >Hal Berenson, SQL Server MVP
> >True Mountain Group LLC
> >
> >
> >"Abraham" <binu_ca@.yahoo.com> wrote in message
> >news:exFZI3ZQDHA.1024@.TK2MSFTNGP12.phx.gbl...
> >> Hi
> >> Is there any SQL Server encryption to hide the contents
> of fields
> >> That is I do have a password column and I want to
> hide/encrpt it from
> >users
> >> to see it.
> >> Is there any way to do it --like turn encrption ON for
> a column ?
> >>
> >> Is there any way SQL Server help us for this ?
> >>
> >> Thanks in advance
> >>
> >>
> >
> >
> >.
> >sql

Encrypting the actual MDF file - not the data column

Hi there,
We have a website application that we needed to replicae in various laptops
for our team that does not have internet access to our production site
constantly. We wrote the application that keeps in sync the website, the
database and the relevant data between the laptop and our production server.
In order to do that, we had to replicate the production database and
production "compiled" website on the local laptop, and have the applicaion
downloading and uploading the relevant data in order to keep the whole thing
in sync. Success !!!
Now comes the problem:
At this point we have a laptop that holds a production compiled dotfuscated
website (we are ok with that) and the production database with a minimum
subset of data. This means that the Database schema and the data is open to
anybody that has access to that laptop. So what happen if the laptop gets
stolen ?
The only way we can allow that is if we find a way to encrypt somehow the
MDF file.
Constraint:
We cannot change the production website and SP code to ENCRYPT - DECRYPT
certain data columns.
So my question is ..can I secure the DB file in case the laptop get stolen?
Thanks,
FPConsider using Encrypting File System (EFS).
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Hey it's Filippo" <pandiani69@.hotmail.com> wrote in message
news:%23QK1780ZHHA.5020@.TK2MSFTNGP05.phx.gbl...
Hi there,
We have a website application that we needed to replicae in various laptops
for our team that does not have internet access to our production site
constantly. We wrote the application that keeps in sync the website, the
database and the relevant data between the laptop and our production server.
In order to do that, we had to replicate the production database and
production "compiled" website on the local laptop, and have the applicaion
downloading and uploading the relevant data in order to keep the whole thing
in sync. Success !!!
Now comes the problem:
At this point we have a laptop that holds a production compiled dotfuscated
website (we are ok with that) and the production database with a minimum
subset of data. This means that the Database schema and the data is open to
anybody that has access to that laptop. So what happen if the laptop gets
stolen ?
The only way we can allow that is if we find a way to encrypt somehow the
MDF file.
Constraint:
We cannot change the production website and SP code to ENCRYPT - DECRYPT
certain data columns.
So my question is ..can I secure the DB file in case the laptop get stolen?
Thanks,
FP|||Tom,
With EFS, would my local application be able to connect and run queries
normally?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eYxH5O1ZHHA.4000@.TK2MSFTNGP02.phx.gbl...
> Consider using Encrypting File System (EFS).
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> "Hey it's Filippo" <pandiani69@.hotmail.com> wrote in message
> news:%23QK1780ZHHA.5020@.TK2MSFTNGP05.phx.gbl...
> Hi there,
> We have a website application that we needed to replicae in various
> laptops
> for our team that does not have internet access to our production site
> constantly. We wrote the application that keeps in sync the website, the
> database and the relevant data between the laptop and our production
> server.
> In order to do that, we had to replicate the production database and
> production "compiled" website on the local laptop, and have the applicaion
> downloading and uploading the relevant data in order to keep the whole
> thing
> in sync. Success !!!
> Now comes the problem:
> At this point we have a laptop that holds a production compiled
> dotfuscated
> website (we are ok with that) and the production database with a minimum
> subset of data. This means that the Database schema and the data is open
> to
> anybody that has access to that laptop. So what happen if the laptop gets
> stolen ?
> The only way we can allow that is if we find a way to encrypt somehow the
> MDF file.
> Constraint:
> We cannot change the production website and SP code to ENCRYPT - DECRYPT
> certain data columns.
> So my question is ..can I secure the DB file in case the laptop get
> stolen?
> Thanks,
> FP
>
>|||Also,
Since I do need to keep in sync the local DB and sometimes I need to "drop"
it and then recreate an empty one, would I be able to programmatically
encrypt the file using EFS?
If so, do you have a code sample (possibly in C#)?
Thanks again,
Filippo
"Hey it's Filippo" <pandiani69@.hotmail.com> wrote in message
news:uar7Yq1ZHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Tom,
> With EFS, would my local application be able to connect and run queries
> normally?
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:eYxH5O1ZHHA.4000@.TK2MSFTNGP02.phx.gbl...
>|||You'd have SQL running under a domain account and that account would then be
writing files (data and log) on the disk. If you copy files to be attached
or restores, be sure to use that same domain account.
How you connect to SQL Server doesn't matter. The login that you use to
talk to SQL Server isn't the account that writes to those files. The SQL
Server service account is.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Hey it's Filippo" <pandiani69@.hotmail.com> wrote in message
news:uar7Yq1ZHHA.4872@.TK2MSFTNGP03.phx.gbl...
Tom,
With EFS, would my local application be able to connect and run queries
normally?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eYxH5O1ZHHA.4000@.TK2MSFTNGP02.phx.gbl...
> Consider using Encrypting File System (EFS).
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> "Hey it's Filippo" <pandiani69@.hotmail.com> wrote in message
> news:%23QK1780ZHHA.5020@.TK2MSFTNGP05.phx.gbl...
> Hi there,
> We have a website application that we needed to replicae in various
> laptops
> for our team that does not have internet access to our production site
> constantly. We wrote the application that keeps in sync the website, the
> database and the relevant data between the laptop and our production
> server.
> In order to do that, we had to replicate the production database and
> production "compiled" website on the local laptop, and have the applicaion
> downloading and uploading the relevant data in order to keep the whole
> thing
> in sync. Success !!!
> Now comes the problem:
> At this point we have a laptop that holds a production compiled
> dotfuscated
> website (we are ok with that) and the production database with a minimum
> subset of data. This means that the Database schema and the data is open
> to
> anybody that has access to that laptop. So what happen if the laptop gets
> stolen ?
> The only way we can allow that is if we find a way to encrypt somehow the
> MDF file.
> Constraint:
> We cannot change the production website and SP code to ENCRYPT - DECRYPT
> certain data columns.
> So my question is ..can I secure the DB file in case the laptop get
> stolen?
> Thanks,
> FP
>
>|||You wouldn't encrypt it programmatically. It's done automatically.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Hey it's Filippo" <pandiani69@.hotmail.com> wrote in message
news:O5Il8p%23ZHHA.1300@.TK2MSFTNGP02.phx.gbl...
Also,
Since I do need to keep in sync the local DB and sometimes I need to "drop"
it and then recreate an empty one, would I be able to programmatically
encrypt the file using EFS?
If so, do you have a code sample (possibly in C#)?
Thanks again,
Filippo
"Hey it's Filippo" <pandiani69@.hotmail.com> wrote in message
news:uar7Yq1ZHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Tom,
> With EFS, would my local application be able to connect and run queries
> normally?
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:eYxH5O1ZHHA.4000@.TK2MSFTNGP02.phx.gbl...
>|||Also have a look at:
http://technet.microsoft.com/en-us/.../aa906017.aspx, for a new
technology that would be helpful in this scenario.
Thanks
Laurentiu Cristofor [MSFT]
Software Development Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23DmRu$MaHHA.4552@.TK2MSFTNGP05.phx.gbl...
> You'd have SQL running under a domain account and that account would then
> be
> writing files (data and log) on the disk. If you copy files to be
> attached
> or restores, be sure to use that same domain account.
> How you connect to SQL Server doesn't matter. The login that you use to
> talk to SQL Server isn't the account that writes to those files. The SQL
> Server service account is.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> .
> "Hey it's Filippo" <pandiani69@.hotmail.com> wrote in message
> news:uar7Yq1ZHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Tom,
> With EFS, would my local application be able to connect and run queries
> normally?
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:eYxH5O1ZHHA.4000@.TK2MSFTNGP02.phx.gbl...
>

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

Encrypting a Single Row/Column

Hi,
Is there any way to encrypt and decrypt a Single Row/Column in SQL
server 2000 and SQL server 2005.
I do not find any info w.r.t it in the documentation.
Thx in advans,
Karthik BalaguruOn Jul 25, 12:04 pm, karthikbalaguru <karthikbalagur...@.gmail.com>
wrote:
> Hi,
> Is there any way to encrypt and decrypt a Single Row/Column in SQL
> server 2000 and SQL server 2005.
> I do not find any info w.r.t it in the documentation.
> Thx in advans,
> Karthik Balaguru
Hi,
I found the below link having some info w.r.t Encrypt. But it is for
SQL Server 6x & SQL Server 7x.
What about Encryption in SQL Server 2000 & SQL Server 2005. ? Any
ideas / info ?
http://groups.google.co.in/group/microsoft.public.sqlserver.programming/browse_thread/thread/fd4a204efd4a69a8/?hl=en#
Thx in advans,
Karthik Balaguru
-->>
You can use the encrypt function even if you don't know what's the
encryption algorithm.
It will encrypt your data using the same algorithm that is used to
encrypt
stored procedures, triggers and views.
create table users(
usernamed varchar(25),
pass varbinary(16)) -- 8 characters password
insert into users values('user1', encrypt('123'))
insert into users values('user2', encrypt('456'))
insert into users values('user3', encrypt('789'))
select * from users
usernamed pass
-- --
user1 0x3E16CDED63C6
user2 0x3B3AB5941B3C
user3 0x383C6F64E6D0
(3 row(s) affected)
select * from users where pass = encrypt('123')
usernamed pass
-- --
user1 0x3E16CDED63C6
(1 row(s) affected)
Remember that encrypt is an undocumented and unsupported function. As
such
data encrypted with the function might become corrupted in a migration
to a
later version of SQL Server.
The encrypt function changed from SQL Server 6.x to
SQL Server 7.0, and individuals that used the encrypt function to
encrypt
data in SQL Server 6.x had such problems.
-->>
I find that the above has some info w.r.t Encrypt. But it is for SQL
Server 6x & SQL Server 7x.
What about Encryption in SQL Server 2000 & SQL Server 2005. ? Any
ideas / info ?
Thx in advans,
Karthik Balaguru|||Hi,
Please check:
How to: Encrypt a Column of Data from SQL 2005 BOL
Also:
See the following links for information on how to search encrypted
data:
http://blogs.msdn.com/lcris/archive/2005/12/22/506931.aspx
http://blogs.msdn.com/raulga/archive/2006/03/11/549754.aspx
HTH,
Dinesh|||On Jul 25, 12:59 pm, dinu_b...@.hotmail.com wrote:
> Hi,
> Please check:
> How to: Encrypt a Column of Data from SQL 2005 BOL
> Also:
> See the following links for information on how to search encrypted
> data:
> http://blogs.msdn.com/lcris/archive/2005/12/22/506931.aspxhttp://blogs.msdn.com/raulga/archive/2006/03/11/549754.aspx
> HTH,
> Dinesh
Thx Dinesh, Looks interesting.
Karthik Balaguru

Encrypting a Single Row/Column

Hi,
Is there any way to encrypt and decrypt a Single Row/Column in SQL
server 2000 and SQL server 2005.
I do not find any info w.r.t it in the documentation.
Thx in advans,
Karthik BalaguruOn Jul 25, 12:04 pm, karthikbalaguru <karthikbalagur...@.gmail.com>
wrote:
> Hi,
> Is there any way to encrypt and decrypt a Single Row/Column in SQL
> server 2000 and SQL server 2005.
> I do not find any info w.r.t it in the documentation.
> Thx in advans,
> Karthik Balaguru
Hi,
I found the below link having some info w.r.t Encrypt. But it is for
SQL Server 6x & SQL Server 7x.
What about Encryption in SQL Server 2000 & SQL Server 2005. ? Any
ideas / info ?
http://groups.google.co.in/group/mi...d4a69a8/?hl=en#
Thx in advans,
Karthik Balaguru
-->>>>>
You can use the encrypt function even if you don't know what's the
encryption algorithm.
It will encrypt your data using the same algorithm that is used to
encrypt
stored procedures, triggers and views.
create table users(
usernamed varchar(25),
pass varbinary(16)) -- 8 characters password
insert into users values('user1', encrypt('123'))
insert into users values('user2', encrypt('456'))
insert into users values('user3', encrypt('789'))
select * from users
usernamed pass
-- --
user1 0x3E16CDED63C6
user2 0x3B3AB5941B3C
user3 0x383C6F64E6D0
(3 row(s) affected)
select * from users where pass = encrypt('123')
usernamed pass
-- --
user1 0x3E16CDED63C6
(1 row(s) affected)
Remember that encrypt is an undocumented and unsupported function. As
such
data encrypted with the function might become corrupted in a migration
to a
later version of SQL Server.
The encrypt function changed from SQL Server 6.x to
SQL Server 7.0, and individuals that used the encrypt function to
encrypt
data in SQL Server 6.x had such problems.
-->>>
I find that the above has some info w.r.t Encrypt. But it is for SQL
Server 6x & SQL Server 7x.
What about Encryption in SQL Server 2000 & SQL Server 2005. ? Any
ideas / info ?
Thx in advans,
Karthik Balaguru|||Hi,
Please check:
How to: Encrypt a Column of Data from SQL 2005 BOL
Also:
See the following links for information on how to search encrypted
data:
http://blogs.msdn.com/lcris/archive.../22/506931.aspx
http://blogs.msdn.com/raulga/archiv.../11/549754.aspx
HTH,
Dinesh|||On Jul 25, 12:59 pm, dinu_b...@.hotmail.com wrote:
> Hi,
> Please check:
> How to: Encrypt a Column of Data from SQL 2005 BOL
> Also:
> See the following links for information on how to search encrypted
> data:
> http://blogs.msdn.com/lcris/archive.../11/549754.aspx
> HTH,
> Dinesh
Thx Dinesh, Looks interesting.
Karthik Balaguru

Encrypting a Single Row/Column

Hi,
Is there any way to encrypt and decrypt a Single Row/Column in SQL
server 2000 and SQL server 2005.
I do not find any info w.r.t it in the documentation.
Thx in advans,
Karthik Balaguru
On Jul 25, 12:04 pm, karthikbalaguru <karthikbalagur...@.gmail.com>
wrote:
> Hi,
> Is there any way to encrypt and decrypt a Single Row/Column in SQL
> server 2000 and SQL server 2005.
> I do not find any info w.r.t it in the documentation.
> Thx in advans,
> Karthik Balaguru
Hi,
I found the below link having some info w.r.t Encrypt. But it is for
SQL Server 6x & SQL Server 7x.
What about Encryption in SQL Server 2000 & SQL Server 2005. ? Any
ideas / info ?
http://groups.google.co.in/group/microsoft.public.sqlserver.programming/browse_thread/thread/fd4a204efd4a69a8/?hl=en#
Thx in advans,
Karthik Balaguru
-->>>>>
You can use the encrypt function even if you don't know what's the
encryption algorithm.
It will encrypt your data using the same algorithm that is used to
encrypt
stored procedures, triggers and views.
create table users(
usernamed varchar(25),
pass varbinary(16)) -- 8 characters password
insert into users values('user1', encrypt('123'))
insert into users values('user2', encrypt('456'))
insert into users values('user3', encrypt('789'))
select * from users
usernamed pass
-- --
user1 0x3E16CDED63C6
user2 0x3B3AB5941B3C
user3 0x383C6F64E6D0
(3 row(s) affected)
select * from users where pass = encrypt('123')
usernamed pass
-- --
user1 0x3E16CDED63C6
(1 row(s) affected)
Remember that encrypt is an undocumented and unsupported function. As
such
data encrypted with the function might become corrupted in a migration
to a
later version of SQL Server.
The encrypt function changed from SQL Server 6.x to
SQL Server 7.0, and individuals that used the encrypt function to
encrypt
data in SQL Server 6.x had such problems.
-->>>
I find that the above has some info w.r.t Encrypt. But it is for SQL
Server 6x & SQL Server 7x.
What about Encryption in SQL Server 2000 & SQL Server 2005. ? Any
ideas / info ?
Thx in advans,
Karthik Balaguru
|||Hi,
Please check:
How to: Encrypt a Column of Data from SQL 2005 BOL
Also:
See the following links for information on how to search encrypted
data:
http://blogs.msdn.com/lcris/archive/2005/12/22/506931.aspx
http://blogs.msdn.com/raulga/archive/2006/03/11/549754.aspx
HTH,
Dinesh
|||On Jul 25, 12:59 pm, dinu_b...@.hotmail.com wrote:
> Hi,
> Please check:
> How to: Encrypt a Column of Data from SQL 2005 BOL
> Also:
> See the following links for information on how to search encrypted
> data:
> http://blogs.msdn.com/lcris/archive/2005/12/22/506931.aspxhttp://blogs.msdn.com/raulga/archive/2006/03/11/549754.aspx
> HTH,
> Dinesh
Thx Dinesh, Looks interesting.
Karthik Balaguru

encrypting a column in SQL 7

Does anybody know a reliable program that I can use to encrypt confidential
data in SQL 7? I just want to encrypt a column in a table and as it's going
to be implemented on an existing database, I 'm looking for something that
won't change the queries or the programming. Any ideas'
Thanks in advance.Take a look at:
http://www.activecrypt.com/faq.html
--
-oj
http://www.rac4sql.net
"Fulya Erol" <ferol@.no.nospam.mezun.com> wrote in message
news:%23b2HCgu3DHA.1096@.TK2MSFTNGP11.phx.gbl...
> Does anybody know a reliable program that I can use to encrypt
confidential
> data in SQL 7? I just want to encrypt a column in a table and as it's
going
> to be implemented on an existing database, I 'm looking for something that
> won't change the queries or the programming. Any ideas'
> Thanks in advance.
>

encrypting a column in SQL 7

Does anybody know a reliable program that I can use to encrypt confidential
data in SQL 7? I just want to encrypt a column in a table and as it's going
to be implemented on an existing database, I 'm looking for something that
won't change the queries or the programming. Any ideas'
Thanks in advance.Take a look at:
http://www.activecrypt.com/faq.html
-oj
http://www.rac4sql.net
"Fulya Erol" <ferol@.no.nospam.mezun.com> wrote in message
news:%23b2HCgu3DHA.1096@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Does anybody know a reliable program that I can use to encrypt

---

confidential

quote:

---

> data in SQL 7? I just want to encrypt a column in a table and as it's

---

going

quote:

---

> to be implemented on an existing database, I 'm looking for something that
> won't change the queries or the programming. Any ideas'
> Thanks in advance.
>

---

Encrypting a column

Is it possible to have a column in a table stored encrypted but the end
user view the data decrypted?
I'm thinking of a way of having the data remain secure if a copy of the
database or its contents is taken off site.
Any hints appreciated.
RD.
NO. See "Using Encryption Methods" in BOL.
hth
Quentin
"RD" <nospam@.nospam.net> wrote in message
news:%23n8zKy1dFHA.580@.TK2MSFTNGP15.phx.gbl...
> Is it possible to have a column in a table stored encrypted but the end
> user view the data decrypted?
> I'm thinking of a way of having the data remain secure if a copy of the
> database or its contents is taken off site.
> Any hints appreciated.
> RD.
>
|||you'd have to build logic to do this
it aint built in to SQL
Greg Jackson
PDX, Oregon