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

We need to be able to encrypt very sensitive columns of data in a
quasi-warehouse type environment and, from what I've read, SS 2005 should
work quite well for this. But I need examples of how to implement this. I
know very little about encryption and so I need some easy-to-read examples of
how to encrypt/decrypt using stored procs, scripts and code in 2005. Where
can I find examples like this? Any sites, books, articles, links would be
much appreciated.CLM wrote:
> We need to be able to encrypt very sensitive columns of data in a
> quasi-warehouse type environment and, from what I've read, SS 2005 should
> work quite well for this. But I need examples of how to implement this. I
> know very little about encryption and so I need some easy-to-read examples of
> how to encrypt/decrypt using stored procs, scripts and code in 2005. Where
> can I find examples like this? Any sites, books, articles, links would be
> much appreciated.
There are some examples in Books Online...|||See Laurentiu's blog:
http://blogs.msdn.com/lcris/archive/category/10357.aspx
--
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:e3lV0PUmGHA.1252@.TK2MSFTNGP02.phx.gbl...
> CLM wrote:
>> We need to be able to encrypt very sensitive columns of data in a
>> quasi-warehouse type environment and, from what I've read, SS 2005 should
>> work quite well for this. But I need examples of how to implement this.
>> I know very little about encryption and so I need some easy-to-read
>> examples of how to encrypt/decrypt using stored procs, scripts and code
>> in 2005. Where can I find examples like this? Any sites, books,
>> articles, links would be much appreciated.
> There are some examples in Books Online...

Encryption

CLM wrote:
> We need to be able to encrypt very sensitive columns of data in a
> quasi-warehouse type environment and, from what I've read, SS 2005 should
> work quite well for this. But I need examples of how to implement this.
I
> know very little about encryption and so I need some easy-to-read examples
of
> how to encrypt/decrypt using stored procs, scripts and code in 2005. Wher
e
> can I find examples like this? Any sites, books, articles, links would be
> much appreciated.
There are some examples in Books Online...See Laurentiu's blog:
http://blogs.msdn.com/lcris/archive/category/10357.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:e3lV0PUmGHA.1252@.TK2MSFTNGP02.phx.gbl...
> CLM wrote:
> There are some examples in Books Online...|||We need to be able to encrypt very sensitive columns of data in a
quasi-warehouse type environment and, from what I've read, SS 2005 should
work quite well for this. But I need examples of how to implement this. I
know very little about encryption and so I need some easy-to-read examples o
f
how to encrypt/decrypt using stored procs, scripts and code in 2005. Where
can I find examples like this? Any sites, books, articles, links would be
much appreciated.|||CLM wrote:
> We need to be able to encrypt very sensitive columns of data in a
> quasi-warehouse type environment and, from what I've read, SS 2005 should
> work quite well for this. But I need examples of how to implement this.
I
> know very little about encryption and so I need some easy-to-read examples
of
> how to encrypt/decrypt using stored procs, scripts and code in 2005. Wher
e
> can I find examples like this? Any sites, books, articles, links would be
> much appreciated.
There are some examples in Books Online...|||See Laurentiu's blog:
http://blogs.msdn.com/lcris/archive/category/10357.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:e3lV0PUmGHA.1252@.TK2MSFTNGP02.phx.gbl...
> CLM wrote:
> There are some examples in Books Online...

encrypting data in SQL table?

Is there any way to encrypt sensitive information in a table? I'd like to
save sensitive data in an encrypted form and then later retrieve it and
decrypt it.
Is this at all possible?
Thanks in advance,
TomTJT wrote:
> Is there any way to encrypt sensitive information in a table? I'd like to
> save sensitive data in an encrypted form and then later retrieve it and
> decrypt it.
> Is this at all possible?
> Thanks in advance,
> Tom
http://msdn2.microsoft.com/en-us/library/ms174361(sql.90).aspx
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks David.
I omitted an important fact - I am dealing with SQL2000 (not 2005). It
looks like that function is new for SQL 2005. Is there anything I could do
for sql2000?
Thanks.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1142368825.523764.75110@.p10g2000cwp.googlegroups.com...
> TJT wrote:
> > Is there any way to encrypt sensitive information in a table? I'd like
to
> > save sensitive data in an encrypted form and then later retrieve it and
> > decrypt it.
> >
> > Is this at all possible?
> >
> > Thanks in advance,
> > Tom
>
> http://msdn2.microsoft.com/en-us/library/ms174361(sql.90).aspx
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

encrypting data in SQL table?

Is there any way to encrypt sensitive information in a table? I'd like to
save sensitive data in an encrypted form and then later retrieve it and
decrypt it.
Is this at all possible?
Thanks in advance,
TomTJT wrote:
> Is there any way to encrypt sensitive information in a table? I'd like to
> save sensitive data in an encrypted form and then later retrieve it and
> decrypt it.
> Is this at all possible?
> Thanks in advance,
> Tom
http://msdn2.microsoft.com/en-us/library/ms174361(sql.90).aspx
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks David.
I omitted an important fact - I am dealing with SQL2000 (not 2005). It
looks like that function is new for SQL 2005. Is there anything I could do
for sql2000?
Thanks.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1142368825.523764.75110@.p10g2000cwp.googlegroups.com...
> TJT wrote:
to[vbcol=seagreen]
>
> http://msdn2.microsoft.com/en-us/library/ms174361(sql.90).aspx
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

encrypting data in SQL table?

Is there any way to encrypt sensitive information in a table? I'd like to
save sensitive data in an encrypted form and then later retrieve it and
decrypt it.
Is this at all possible?
Thanks in advance,
Tom
TJT wrote:
> Is there any way to encrypt sensitive information in a table? I'd like to
> save sensitive data in an encrypted form and then later retrieve it and
> decrypt it.
> Is this at all possible?
> Thanks in advance,
> Tom
http://msdn2.microsoft.com/en-us/library/ms174361(sql.90).aspx
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Thanks David.
I omitted an important fact - I am dealing with SQL2000 (not 2005). It
looks like that function is new for SQL 2005. Is there anything I could do
for sql2000?
Thanks.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1142368825.523764.75110@.p10g2000cwp.googlegro ups.com...[vbcol=seagreen]
> TJT wrote:
to
>
> http://msdn2.microsoft.com/en-us/library/ms174361(sql.90).aspx
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

Encrypting Data

I have an application that has stores sensitive data in an SQL server and I am currently handling this through my ASP.NET application using the encryption classes in C#.

One of the things we would eventually like to be able to do is use other programs (like Microsoft Access) to run advanced Querys on the tables and retrive the data. With the encyption being done in C#/ASP all that would be returned would be the encrypted data.

I wondering if there is a way to build a layer to encrypt/decrypt data at the database level, my searches haven't yeiled to much info (As well as a trip to Borders) as I haven't seen any books that even touch on this.

I don't know a lot about SQL right now (Mainly only MySQL so Stored Procs and all that stuff are really new to me) so I don't know if I would go about it this way or not? (Using a stored procedure)

Can anyone reccommened where I should start to learn about accomplishing this? Books are usually the best help but I'll take any kind of information that can be thrown my way :-)

Thanks!ummm... what are you encrypting? and does it NEED to be encrypted up the wazoo?|||Yes, It needs to be encrypted. No matter what it is there should be a way to encrypt it correct?|||Sorry, To add to that what are my other options? I know there are user access controls (although never have worked with them) and that type of stuff but one thing that worried me would be using access control, right now as it is I can remote desktop to the SQL machine open enterprise manager and browse the tables. (No password needed) I'm all for doing it the correct way. But part of the thing to is even though I'm writing the program because it's encrypted I never have to see the data.

encrypt sensitive data

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

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