hello
what is the function that calculate the number of days in a given month ?
10xromy
I don't know such functions
Try this
declare @.d datetime
set @.d='20050110'
select datestring, case
when isdate(datestring/100*100+31) = 1 then 31
when isdate(datestring/100*100+30) = 1 then 30
when isdate(datestring/100*100+29) = 1 then 29
when isdate(datestring/100*100+28) = 1 then 28
end
from (
select convert(varchar,@.d,112) as datestring
) D
"romy" <romyedevelop@.hotmail.com> wrote in message
news:u3iwsVbGFHA.3284@.TK2MSFTNGP10.phx.gbl...
> hello
> what is the function that calculate the number of days in a given month ?
> 10x
>
>|||You can write your own user defined function that takes the month number or
month name as input parameter and returns the number of days in that month
using a CASE or an IF statement.
"romy" <romyedevelop@.hotmail.com> wrote in message
news:u3iwsVbGFHA.3284@.TK2MSFTNGP10.phx.gbl...
> hello
> what is the function that calculate the number of days in a given month ?
> 10x
>
>|||Try,
declare @.d datetime
set @.d = '20050215'
select datediff(day, convert(char(6), @.d, 112) + '01', dateadd(month, 1,
convert(char(6), @.d, 112) + '01'))
go
AMB
"romy" wrote:
> hello
> what is the function that calculate the number of days in a given month ?
> 10x
>
>
>|||romy,
"romy" <romyedevelop@.hotmail.com> wrote in message
news:u3iwsVbGFHA.3284@.TK2MSFTNGP10.phx.gbl...
> hello
> what is the function that calculate the number of days in a given month ?
> 10x
I use this:
declare @.d datetime
set @.d = '20050215'
select day(dateadd(ss, -1, dateadd(mm, datediff(m, 0, @.d) + 1, 0)))
Andrea|||DECLARE @.month INT
SET @.month=2
select day(CONVERT(Datetime, Convert(VARCHAR, (CASE WHEN @.month=12 THEN 0
Else @.month END +1)) + '/01/2005' ) -1)
"Andrea Benedetti" <abenedetti@.absistemi.it> wrote in message
news:OqHur9bGFHA.2748@.tk2msftngp13.phx.gbl...
> romy,
> "romy" <romyedevelop@.hotmail.com> wrote in message
> news:u3iwsVbGFHA.3284@.TK2MSFTNGP10.phx.gbl...
?
> I use this:
> declare @.d datetime
> set @.d = '20050215'
> select day(dateadd(ss, -1, dateadd(mm, datediff(m, 0, @.d) + 1, 0)))
> Andrea
>
Showing posts with label number. Show all posts
Showing posts with label number. Show all posts
Thursday, March 29, 2012
Tuesday, March 27, 2012
Encryption question
Hello Everybody,
I have a Encryption - Decription Question.
In my project we are getting an XML File from a vendor which has a credit
card number in clear text. We use XML bulk load process to load table from
xml file.
How can i encrypt credit card number while storing into table.
Also i will have to decrypt CC number while i create comma separated file
for another vendor ?
Pls let me know.
thxA credit card number is typically not the type of attribute used for
indexing, sorting, grouping, or composing a primary key, so I see no reason
why it would not be a good candidate for encryption. It would be reasonable
and fairly simple to store it encrypted and decrypt it at the application
level only when needed for display on a customer information form or to
complete a business transaction.
However, a social security number is a different issue.
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:81898E3B-37E1-4B90-9F83-2E522A29D064@.microsoft.com...
> Hello Everybody,
> I have a Encryption - Decription Question.
> In my project we are getting an XML File from a vendor which has a credit
> card number in clear text. We use XML bulk load process to load table from
> xml file.
> How can i encrypt credit card number while storing into table.
> Also i will have to decrypt CC number while i create comma separated file
> for another vendor ?
> Pls let me know.
> thx|||I have a few line sof VB.Net code that Encrypts or Decrypts a string passed
to it; If interested then let me know and I will post the code for you.
"mvp" wrote:
> Hello Everybody,
> I have a Encryption - Decription Question.
> In my project we are getting an XML File from a vendor which has a credit
> card number in clear text. We use XML bulk load process to load table from
> xml file.
> How can i encrypt credit card number while storing into table.
> Also i will have to decrypt CC number while i create comma separated file
> for another vendor ?
> Pls let me know.
> thx|||Thanks for the reply
But can i know how can i do this encryption-decryption..
thanks
"JT" wrote:
> A credit card number is typically not the type of attribute used for
> indexing, sorting, grouping, or composing a primary key, so I see no reaso
n
> why it would not be a good candidate for encryption. It would be reasonabl
e
> and fairly simple to store it encrypted and decrypt it at the application
> level only when needed for display on a customer information form or to
> complete a business transaction.
> However, a social security number is a different issue.
> "mvp" <mvp@.discussions.microsoft.com> wrote in message
> news:81898E3B-37E1-4B90-9F83-2E522A29D064@.microsoft.com...
>
>|||I want to read from XML file (which has CC number in clear text, i use sql
bulk load to load xml into table) and load into sql server table in encrypt
form and then decrypt again when i create comma seperated file from table
again to provide feed to another vendor ?
Let me know, do u have similar thing ?
"Shariq" wrote:
> I have a few line sof VB.Net code that Encrypts or Decrypts a string passe
d
> to it; If interested then let me know and I will post the code for you.
> "mvp" wrote:
>|||If your entire input and output processing is done with SQL server then I
haven't done similar thing but you could possibly use ActiveX script to
Encrypt/Decrypt.
In a similiar type of situation when I had to encrypt/decrypt for input and
output; I use VB.Net application to re-process XML file as input, encrypted
the Credit Card numbers and output to an XML file. Then I executed a DTS
package to upload the XML formatted output file to a SQL Server.
When sending data to a client with decrypted CC numbers; I also used the VB
application to rad data from the SQL Server and during the data read,
decrypted the CC numbers and produced XML file.
"mvp" wrote:
> I want to read from XML file (which has CC number in clear text, i use sq
l
> bulk load to load xml into table) and load into sql server table in encry
pt
> form and then decrypt again when i create comma seperated file from table
> again to provide feed to another vendor ?
> Let me know, do u have similar thing ?
> "Shariq" wrote:
>|||Hello Shariq,
I'm Interesting about the encryption/decryption code that you wrote in VB.NE
T.
Would you like to post it me?
Thank you!
"Shariq" wrote:
> If your entire input and output processing is done with SQL server then I
> haven't done similar thing but you could possibly use ActiveX script to
> Encrypt/Decrypt.
> In a similiar type of situation when I had to encrypt/decrypt for input an
d
> output; I use VB.Net application to re-process XML file as input, encrypte
d
> the Credit Card numbers and output to an XML file. Then I executed a DTS
> package to upload the XML formatted output file to a SQL Server.
> When sending data to a client with decrypted CC numbers; I also used the V
B
> application to rad data from the SQL Server and during the data read,
> decrypted the CC numbers and produced XML file.
> "mvp" wrote:
>|||If you are taking about decrypting data coming in from the source data or
encrypting data as it extract to another format, then this would be
implemented at the application level not the database level. It depends on
what application programming tool your are using.
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:8470213F-47A3-417A-B920-BB7759AC0738@.microsoft.com...
> Thanks for the reply
> But can i know how can i do this encryption-decryption..
> thanks
> "JT" wrote:
>|||x-rays,
You might be able to better technique but the technique I use works great
for me.
This function will either encrypt or decrypt depanding on what is passed to
it.
Public Function MyCryption(ByVal strInput As String) As String
' Encrypts/decrypts the passed string using
' a simple ASCII value-swapping algorithm
Dim strTempChar As String, i As Integer
For i = 1 To Len(strInput)
If Asc(Mid$(strInput, i, 1)) < 128 Then
strTempChar = CType(Asc(Mid$(strInput, i, 1)) + 128, String)
ElseIf Asc(Mid$(strInput, i, 1)) > 128 Then
strTempChar = CType(Asc(Mid$(strInput, i, 1)) - 128, String)
End If
Mid$(strInput, i, 1) = Chr(CType(strTempChar, Integer))
Next i
Return strInput
End Function
"x-rays" wrote:
> Hello Shariq,
> I'm Interesting about the encryption/decryption code that you wrote in VB.
NET.
> Would you like to post it me?
> Thank you!
> "Shariq" wrote:
>|||Keep in mind that whatever encryption you end up using, the encrypted data
is only as secure as the code you use for the encryption. If your
encryption code and key are stored in a source control system, everyone with
access to that system will be able to decrypt the data, provided they have
access to the table where the data is stored.
If your programmers do not have access to the production database, and your
DBAs do not have access to your application code, then you should be ok.
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:81898E3B-37E1-4B90-9F83-2E522A29D064@.microsoft.com...
> Hello Everybody,
> I have a Encryption - Decription Question.
> In my project we are getting an XML File from a vendor which has a credit
> card number in clear text. We use XML bulk load process to load table from
> xml file.
> How can i encrypt credit card number while storing into table.
> Also i will have to decrypt CC number while i create comma separated file
> for another vendor ?
> Pls let me know.
> thx
I have a Encryption - Decription Question.
In my project we are getting an XML File from a vendor which has a credit
card number in clear text. We use XML bulk load process to load table from
xml file.
How can i encrypt credit card number while storing into table.
Also i will have to decrypt CC number while i create comma separated file
for another vendor ?
Pls let me know.
thxA credit card number is typically not the type of attribute used for
indexing, sorting, grouping, or composing a primary key, so I see no reason
why it would not be a good candidate for encryption. It would be reasonable
and fairly simple to store it encrypted and decrypt it at the application
level only when needed for display on a customer information form or to
complete a business transaction.
However, a social security number is a different issue.
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:81898E3B-37E1-4B90-9F83-2E522A29D064@.microsoft.com...
> Hello Everybody,
> I have a Encryption - Decription Question.
> In my project we are getting an XML File from a vendor which has a credit
> card number in clear text. We use XML bulk load process to load table from
> xml file.
> How can i encrypt credit card number while storing into table.
> Also i will have to decrypt CC number while i create comma separated file
> for another vendor ?
> Pls let me know.
> thx|||I have a few line sof VB.Net code that Encrypts or Decrypts a string passed
to it; If interested then let me know and I will post the code for you.
"mvp" wrote:
> Hello Everybody,
> I have a Encryption - Decription Question.
> In my project we are getting an XML File from a vendor which has a credit
> card number in clear text. We use XML bulk load process to load table from
> xml file.
> How can i encrypt credit card number while storing into table.
> Also i will have to decrypt CC number while i create comma separated file
> for another vendor ?
> Pls let me know.
> thx|||Thanks for the reply
But can i know how can i do this encryption-decryption..
thanks
"JT" wrote:
> A credit card number is typically not the type of attribute used for
> indexing, sorting, grouping, or composing a primary key, so I see no reaso
n
> why it would not be a good candidate for encryption. It would be reasonabl
e
> and fairly simple to store it encrypted and decrypt it at the application
> level only when needed for display on a customer information form or to
> complete a business transaction.
> However, a social security number is a different issue.
> "mvp" <mvp@.discussions.microsoft.com> wrote in message
> news:81898E3B-37E1-4B90-9F83-2E522A29D064@.microsoft.com...
>
>|||I want to read from XML file (which has CC number in clear text, i use sql
bulk load to load xml into table) and load into sql server table in encrypt
form and then decrypt again when i create comma seperated file from table
again to provide feed to another vendor ?
Let me know, do u have similar thing ?
"Shariq" wrote:
> I have a few line sof VB.Net code that Encrypts or Decrypts a string passe
d
> to it; If interested then let me know and I will post the code for you.
> "mvp" wrote:
>|||If your entire input and output processing is done with SQL server then I
haven't done similar thing but you could possibly use ActiveX script to
Encrypt/Decrypt.
In a similiar type of situation when I had to encrypt/decrypt for input and
output; I use VB.Net application to re-process XML file as input, encrypted
the Credit Card numbers and output to an XML file. Then I executed a DTS
package to upload the XML formatted output file to a SQL Server.
When sending data to a client with decrypted CC numbers; I also used the VB
application to rad data from the SQL Server and during the data read,
decrypted the CC numbers and produced XML file.
"mvp" wrote:
> I want to read from XML file (which has CC number in clear text, i use sq
l
> bulk load to load xml into table) and load into sql server table in encry
pt
> form and then decrypt again when i create comma seperated file from table
> again to provide feed to another vendor ?
> Let me know, do u have similar thing ?
> "Shariq" wrote:
>|||Hello Shariq,
I'm Interesting about the encryption/decryption code that you wrote in VB.NE
T.
Would you like to post it me?
Thank you!
"Shariq" wrote:
> If your entire input and output processing is done with SQL server then I
> haven't done similar thing but you could possibly use ActiveX script to
> Encrypt/Decrypt.
> In a similiar type of situation when I had to encrypt/decrypt for input an
d
> output; I use VB.Net application to re-process XML file as input, encrypte
d
> the Credit Card numbers and output to an XML file. Then I executed a DTS
> package to upload the XML formatted output file to a SQL Server.
> When sending data to a client with decrypted CC numbers; I also used the V
B
> application to rad data from the SQL Server and during the data read,
> decrypted the CC numbers and produced XML file.
> "mvp" wrote:
>|||If you are taking about decrypting data coming in from the source data or
encrypting data as it extract to another format, then this would be
implemented at the application level not the database level. It depends on
what application programming tool your are using.
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:8470213F-47A3-417A-B920-BB7759AC0738@.microsoft.com...
> Thanks for the reply
> But can i know how can i do this encryption-decryption..
> thanks
> "JT" wrote:
>|||x-rays,
You might be able to better technique but the technique I use works great
for me.
This function will either encrypt or decrypt depanding on what is passed to
it.
Public Function MyCryption(ByVal strInput As String) As String
' Encrypts/decrypts the passed string using
' a simple ASCII value-swapping algorithm
Dim strTempChar As String, i As Integer
For i = 1 To Len(strInput)
If Asc(Mid$(strInput, i, 1)) < 128 Then
strTempChar = CType(Asc(Mid$(strInput, i, 1)) + 128, String)
ElseIf Asc(Mid$(strInput, i, 1)) > 128 Then
strTempChar = CType(Asc(Mid$(strInput, i, 1)) - 128, String)
End If
Mid$(strInput, i, 1) = Chr(CType(strTempChar, Integer))
Next i
Return strInput
End Function
"x-rays" wrote:
> Hello Shariq,
> I'm Interesting about the encryption/decryption code that you wrote in VB.
NET.
> Would you like to post it me?
> Thank you!
> "Shariq" wrote:
>|||Keep in mind that whatever encryption you end up using, the encrypted data
is only as secure as the code you use for the encryption. If your
encryption code and key are stored in a source control system, everyone with
access to that system will be able to decrypt the data, provided they have
access to the table where the data is stored.
If your programmers do not have access to the production database, and your
DBAs do not have access to your application code, then you should be ok.
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:81898E3B-37E1-4B90-9F83-2E522A29D064@.microsoft.com...
> Hello Everybody,
> I have a Encryption - Decription Question.
> In my project we are getting an XML File from a vendor which has a credit
> card number in clear text. We use XML bulk load process to load table from
> xml file.
> How can i encrypt credit card number while storing into table.
> Also i will have to decrypt CC number while i create comma separated file
> for another vendor ?
> Pls let me know.
> thx
Monday, March 26, 2012
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
> >
> >
>
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.
>
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
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 By Passphrase
I am investigating methods of encrypting data in a large number of databases. It seems to me that encrypting by passphrase would be optimum because it would prevent me from having to create all those database and symmetric keys on the various databases (there wil lbe dozens of them). Can anyone give me any advice as whether or not using encryption by passphrase is a good secure method of encrypting sensitive data in a large-scale production environment? I would most likely create a unique passphrase for each database and pass it to a stored procedure as an imput parameter.
Thanks,
Al
If you encrypt using passphrases, your application will have to manage the passphrases that you use, and it will have to store them securely. Depending on how you pass the passphrase around in your application, you might allow a SQL administrator to figure it out by using SQL Profiler and tracing through your application's TSQL requests.
You can use passphrases if you really want to, but they're more difficult to manage. By using the key encryption mechanisms in SQL Server, you can minimize the work you need to do for managing passwords.
Thanks
Laurentiu
Hey, Laurentiu. Thanks for the reply. Your comments make sense, but I have some concerns with the encryption mechanism. First of all, the process and syntax for encrypting/decrypting data seems to be quite a bit more involved, given the fact that you need to create a database master, certificate and symettric keys for each database (remember, I will have a large number of databases). Even if much of this is managed in the background (as you mentioned), it seems I would have to keep track of a lot of things. I am also troubled by the thought that I might have to move my database to a new server at some point. Am I incorrect in assuming this could have a dire affect on my ability to decrypt data on the new server due to the fact that the system master key is a server installation-based item?
Al
|||Regarding your concern for moving a DB from one server to another, the builtin key hierarchy supports this scenario. The reason why a DB master key (DBMK) requires to be protected at least by one password is to allow the recovery of the DBMK when the DB is being moved from one server to another, or when a backup is being recovered. For this reason it is very important to keep track of the DBMK password and to keep such passwords in a secure place.
If you use the builtin key management infrastructure, most likely your application will still need to manage the DBMK password for each database, but the main difference would be that you will only need to access this password when you create the key and when you move the DB from one server to another, and not every time you try to encrypt/decrypt the data. Because the DBMK passwords won’t be needed for regular operations, you can store them out of band in a safe place.
Another advantage of using the builtin key infrastructure is that when creating/modifying the master key using the DDL directly the password will be automatically removed from the trace logs.
-Raul Garcia
SDE/T
SQL Server Engine
|||Ok, please let me know if I am getting this right...
1) I create a DBMK for each database on my SQL Server instance using:
CREATE MASTER KEY ENCRYPTION BY PASSWORD='password'.
2) I backup the DBMK for each database to some offline storage using:
BACKUP MASTE KEY TO FILE='filepath' ENCRYPTION BY PASSWORD='password'.
3) If I change servers, then I just have to run the following for each database in order to restore the same DBMK as I had on the original server:
RESTORE MASTER KEY FROM FILE='filepath' DECRYPTION BY PASSWORD='password' ENCRYPTION BY PASSWORD=password'.
This seems straightforward. Then I can create a symmetric key for each database which will be used in the encrypting/decrypting of data. Am I right in assuming that after a server switch, as long as I use the same DBMK and symmetric key creation attributes (passphrase, algorithm, IV and password), then the newly created symmetric key should work to encryp/decrypt my data on the new server?
|||
Creating a backup of the master keys (DB master keys and service master key) is a really good practice I always try to encourage. The MK backup main purpose is for disaster recovery, and many people also use it to to replicate the same master key in multiple machines/DBs to simplify offline key maintenance.
For the scenario you describe in particular (detach a DB, and reattach it in a different server) there is an ever simpler solution. After attaching the DB in the new server, the DBMK will still be present in the DB itself, but it cannot be automatically used as it is protected by at a password, but we have lost the old server master key protection. We need to explicitly open the DBMK with the correct password and add the encryption by the new server service master key.
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
Go
After the DBMK has been protected by the new server MK, it can be automatically used again.
-Raul Garcia
SDE/T
SQL Server Engine
|||Thanks, Raul. That all makes a lot of sense. One last question. It seems to me that when you use the encryp/decrypt by key functions, you always need to specify the password within the stored procedure. If an unauthorized person gets access to your db and its tables/procedures (hense the reason for encrypting data in the first place), doesn't the existence of these passwords in the stored procedures essentially eliminate the security you have by encrypting the data?
-Al
|||You don't need to specify a password with the encryptbykey/decryptbykey functions. The keys just have to have been previously opened.
If you use the key protection hierarchy in SQL Server, you don't need to specify passwords at all, except in the scenario that Raul explained previously, for restoring the service master key encryption of a database master key, after moving a database from one server to another.
If you choose to not use the key protection hierarchy and you have symmetric or asymmetric keys protected by passwords, you can restrict the need to specify the password to the time when you open the keys, which should not happen within a stored procedure. Note that while the password management is similar to the passphrase management, you only need the password for opening the key - you don't need it again for encryptions and decryptions, as it would be the case with the encryptbypassphrase use, so the password needs to be specified less often. You could, for example, specify the key password within your application as part of the login process to the database (the application would login and open the key using the password you specified). You should not hardcode passwords in TSQL procedure or in your application, but have the user specify them instead.
Thanks
Laurentiu
<<You don't need to specify a password with the encryptbykey/decryptbykey functions. The keys just have to have been previously opened.>>
I'm not an expert in TSQL, but I was under the impression that you had to have syntax like the following in the stored procedure:
OPEN SYMMETRIC KEY keyname DECRYPTION BY ASYMMETRIC KEY asymmetricName WITH PASSWORD='asymmetric password'
select cast(decryptBykey.....
CLOSE SYMMETRIC KEY keyname
How else (and where) would you open the symmetric key, if not within the stored procedure that uses it?
-Al
|||The syntax you are using is designed for the last scenario Laurentiu described, where the asymmetric key/certificate private key is not protected by the DBMK, but instead it is protected by a user provided password.
<< If you choose to not use the key protection hierarchy and you have symmetric or asymmetric keys protected by passwords, you can restrict the need to specify the password to the time when you open the keys, which should not happen within a stored procedure. …>>
In this case, as the password protecting the private key is never stored in the DB, there is no way for the system to automatically open it therefore the caller must provide the password every time. If your application works as a middle-tier between your users and SQL Server and you have control over the session, you can explicitly open the key at the beginning of the session, before giving control to the app user.
On the other hand, if you don’t want to use passwords or you don’t have control over the session, you can use the full key management infrastructure. I wrote a small demo to demonstrate this feature.
CREATE DATABASE db_Demo
go
USE db_Demo
go
-- Create the master key
-- protected by a password
--
-- NOTE: The only time in this demo where
-- a password is needed is for this DBMK creation
-- The rest of the objects will be protected by
-- other objects in teh key hierarchy model,
-- having the DBMK as the root for the DB objects,
-- and the service master key (SMK) as the root for the instance
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password!'
go
-- Create a certificate
-- protecting the private key with the DBMK
-- No password is needed
CREATE CERTIFICATE cert_demo
WITH SUBJECT = 'encryption demo'
go
-- Let's take a look to the MD
-- Notice that the cert private key
-- is protected by the DBMK
SELECT name, pvt_key_encryption_type_desc
FROM sys.certificates
WHERE name = 'cert_demo'
go
-- Now let's create a symmetric key
-- and protect it with this cert
CREATE SYMMETRIC KEY symkey_demo
-- You can use AES in Windows 2003 machines
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE cert_demo
go
-- Open the symmetric key using the cert
-- the cert private key is protected by the DBMK
-- As the DBMK is protected by the SMK,
-- it can be used automatically.
OPEN SYMMETRIC KEY symkey_demo
DECRYPTION BY CERTIFICATE cert_demo
go
-- Create a simple SP to test it
CREATE PROC sp_demo @.PlainText varchar(100)
AS
DECLARE @.blob varbinary(256)
SET @.blob = EncryptByKey(
key_guid( 'symkey_demo' ),
@.PlainText )
SELECT
@.blob as 'encrypted blob',
convert( varchar(100), DecryptByKey( @.blob )) as 'recovered plaintext'
go
-- Let's try our SP
-- We expect to see the encrypted blob and
-- the original value
EXEC sp_demo 'Hello World!'
go
-- after we close the symmetric key...
CLOSE SYMMETRIC KEY symkey_demo
go
-- ... our SP won't be able to encrypt/decrypt anymore
EXEC sp_demo 'Hello World!'
go
-- Create a store procedure that takes care of
-- opening and closing the symmetric key
CREATE PROC sp_demo2 @.PlainText varchar(100)
AS
DECLARE @.Key_opened int
BEGIN TRY
OPEN SYMMETRIC KEY symkey_demo
DECRYPTION BY CERTIFICATE cert_demo
SET @.Key_opened = 1
END TRY
BEGIN CATCH
PRINT 'Failed to open the symmetric key'
PRINT error_message()
END CATCH
if( @.Key_opened = 1 )
BEGIN
DECLARE @.blob varbinary(256)
SET @.blob = EncryptByKey(
key_guid( 'symkey_demo' ),
@.PlainText )
SELECT
@.blob as 'encrypted blob',
convert( varchar(100), DecryptByKey( @.blob ))
as 'recovered plaintext'
-- NOTE: Make sure to always close the symmetric key
-- In this case teh above calls will not raise
-- an error that may abort the SP and leave
-- the key opened for teh session
-- if any such call may be present,
-- you can use TRY/CATCH to help you close
-- the smmetric key in case of error
CLOSE SYMMETRIC KEY symkey_demo
END
go
-- Let's create a user to test our app
CREATE USER AppUser WITHOUT LOGIN
go
-- Grant our user permission to execute the app
GRANT EXECUTE ON sp_demo2 TO AppUser
go
-- Let's give it a try ...
EXEC ('EXEC sp_demo2 ''Hello World!''')
AS USER = 'AppUser'
go
-- ... it failed for 2 reasons:
-- 1) We need VIEW DEFINITON permission on
-- the symmetric key. We can potentially
-- give this permission to all app users
-- 2) We also need CONTROL permission on
-- the certificate protecting the symmetric key,
-- and most likely we don't want to grant
-- such elevated permission to this user!
-- What can we do?
-- We can use digital sigantures for this case
-- and grant permissions via the siganture
-- We can actually grant both permissions needed
-- to the siganture
CREATE CERTIFICATE cert_signing
WITH SUBJECT = 'signing App'
go
CREATE USER cert_signing
FOR CERTIFICATE cert_signing
go
GRANT VIEW DEFINITION ON
SYMMETRIC KEY::symkey_demo
TO cert_signing
go
GRANT CONTROL ON
CERTIFICATE::cert_demo
TO cert_signing
go
ADD SIGNATURE TO sp_demo2 BY CERTIFICATE cert_signing
go
-- Let's give it another try ...
EXEC ('EXEC sp_demo2 ''Hello World!''')
AS USER = 'AppUser'
go
-- ... it works!!!
-- And our SP doesn't require any passwords
-- Now let's take a quick look
-- at the opened keys
-- it should be empty.
SELECT * FROM sys.openkeys
go
--
-- End of demo
|||Thanks for the help. I see what you mean. The only thing I wasn't sure about was using a certificate instead of an assymetric key (and password) to do the encrypting of the symmetric key. Don't certificates need to be created with an expiration date, and wouldn't using them cause me to have to recreate the certificates periodically?|||
Hey, just as a quick aside. Since I am new to Encryption in SQL Server 2005, I have encountered a few issues when trying to implement what I need to do and have been reading a lot in these forums and elsewhere. One of the issues I have encountered is the idea of searching on an encrypted field. I made a post under the T-SQL area called "filtering on encrypted fields", but no one seams to have any ideas. I did more research and found your articles on hashing the clear text with MAC and storing in a new column. This would work for direct searches, but wouldn't seem to work for "LIKE" searches. If this is the type of search I need to do, is the only alternative to include the decryptbykey function in the WHERE clause? I was wondering if there was a way to use temportary tables, or something to that effect to do this kind of search without incurring the second function call hit.
Additionally, at what table size do I really need to worry about the additional function call. If my table is only on the order of 10000-100000 records, is this that big a concern?
-Al
|||See this link for a more in-depth discussion of searching encrypted data: http://blogs.msdn.com/lcris/archive/2005/12/22/506931.aspx. Your question is discussed in the comments.
Short story is that LIKE searches cannot be performed efficiently on data that is encrypted with a strong algorithm, you will have to do a full table scan. A decryption of 100000 entries could take a couple of seconds - the result might vary depending on server load and machine specifications.
Thanks
Laurentiu
For the question of certificates vs asymmetric keys, see http://blogs.msdn.com/lcris/archive/2006/03/13/550904.aspx. For encryption/decryption purposes, certificates do not expire.
Thanks
Laurentiu
Thanks for all your help, Raul and Laurentiu.
-Al
Thursday, March 22, 2012
Encryption and bulk insert
Hello,
I need to build a table with encrypted data with the source data coming from a .csv. The account number from the .csv will need to be encrypted in the final table and I will also need to have a hashed or MAC index on the account number. I'm guessing that I can do this with a stored procedure using a temp table to load the unedited data and then load the actual table from there. The table will be built from a download every evening. The key and certificate for the encryption are already set. I have been able to load data directly to the table providing values. But, I have been struggling getting a bulk insert to work.
I'd appreciate any ideas or examples.
Thanks!
Could you please explain what problems you are having with bulk insert? Do you have any error messages that can help us diagnose the problem? How are you doing the bulk insert? Are you using bcp or BULK INSERT or OPENROWSET(BULK)? Please post a simple repro of the problem if possible also.|||Thanks for your quick reply! I have been working with encryption examples from Raul Garcia's demo for the encryption basics. But I need to be able to bulk load a lot of data and end up with an encrypted account number, and also need to be able to do look ups by the account number which is the reason for the MAC portion. This is what I'm working with. The bulk insert piece works fine. I got past that point when I was trying to use a trigger to do the encryption steps. I was able to get one record inserted when I did it that way. But then received an error about a duplicate key on the second record. A regular insert statement specifying values works fine also. With the code below I'm getting a syntax error on the select statement. Query analyzer doesn't like the two lines following the select. This is the error message:
Incorrect syntax near ','.
and it occurs for either line after the select. I experimented by commenting them out one at a time just to be sure. Any suggestions you have will be appreciated!
create procedure test_sp as
create table #InsertTmp
( acct_no_macHolder varchar(20), -- will build mac later
acct_no nvarchar(60), -- (unencrytped acctNo)
acct_lname char(24) ,
acct_fname char(24) )
BULK INSERT #InsertTmp
FROM 'd:\YukonTSQLLibrary\BulkTest.csv'
with (
DATAFILETYPE = 'char',
FIELDTERMINATOR='<F',
ROWTERMINATOR = '\n' )
GO
Insert into buildfiche (acct_no_index, acct_no_cipher, lname, fname)
select (
( dbo.MAC (acct_no, object_id('buildfiche') ) ), --LINE ERROR OCCURS ON
(encryptbykey(key_guid('key_Encryption'), acct_no)), --OTHER LINE ERROR OCCURS ON
lname,
fname)
from #InsertTmp
GO
I don't know why you have so many paranthesis in the SELECT list. It makes it really hard to read. And looks like the columns in the SELECT list is also wrong. I simplified the SELECT statement to:
select dbo.MAC (acct_no, object_id('buildfiche')), --LINE ERROR OCCURS ON
encryptbykey(key_guid('key_Encryption'), acct_no), --OTHER LINE ERROR OCCURS ON
acct_lname,
acct_fname
from #InsertTmp
Also, I am not sure how you are using the OBJECT_ID value in the dbo.MAC UDF. You need to be aware of the fact that the object identifier is not guaranteed to be same on multiple database that contain the same table or even in the same database if you happen to drop & recreate the table. So the acct_no_index value that you generate on one server will be different from another and so on.
|||Thanks for your help. Getting much closer . . .
When I get a good working example, I'll post it.
encryption
I have a number of both classic asp and asp.net applications which are
writing data into a sql server 2000 database. I am am looking at a least
effort way of encrypting some of these details, I was thinking the best way
might be to create a trigger on the relevant database fields which encrypts
the information on insert. I am not sure what should go into the trigger to
do the encryption, any ideas?
There are a couple of issues to note
1. It would have to be a strong encryption algorithm like triple DES.
2. Data would from the database needs to be extracted into a vb.net
application which would need to be able to decrypt it, ideally with the
encryption functions built into .Net.
3. Encrypting the data in the asp/asp.net code before inserting is not
particularly practical due to the large number of separate applications.
ScottThere are 3rd party vendors that offer databse encryption, that SQL 2000
cannot natively provide.
Here's one of the vendors.
http://www.protegrity.com/
Some articles that may be of interest.
325757 INF: Using SQL Server 2000 with FIPS 140-1 Ciphers
http://support.microsoft.com/?id=325757
331367 PRB: Cannot Decrypt Data Using Data Encryption Standard (DES) Key
Across
http://support.microsoft.com/?id=331367
Building Secure ASP.NET Applications: Authentication, Authorization, and
Secure Communication
http://msdn.microsoft.com/library/d...-us/dnnetsec/ht
ml/SecNetHT08.asp
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Hmmm .. Interesting. I am not a supporter of writing this in a trigger
personally. This will not be efficient and you will land up writing an
Extended stored procedures to do the encryption for you. In SQL Server 2005
this is out of the box feature to use and capitalize.
As a strategy I would always support things to be done in the middle tier
and then encrypting and decrypting them at that layer rather than sending it
to database and later trying to encrypt before inserting. Just my personal
views ...
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"scottrm" <scottrm@.newsgroup.nospam> wrote in message
news:3CE2425A-6063-48B3-A2DF-76C6F37AA06A@.microsoft.com...
> I have a number of both classic asp and asp.net applications which are
> writing data into a sql server 2000 database. I am am looking at a least
> effort way of encrypting some of these details, I was thinking the best
way
> might be to create a trigger on the relevant database fields which
encrypts
> the information on insert. I am not sure what should go into the trigger
to
> do the encryption, any ideas?
> There are a couple of issues to note
> 1. It would have to be a strong encryption algorithm like triple DES.
> 2. Data would from the database needs to be extracted into a vb.net
> application which would need to be able to decrypt it, ideally with the
> encryption functions built into .Net.
> 3. Encrypting the data in the asp/asp.net code before inserting is not
> particularly practical due to the large number of separate applications.
>
> --
> Scott
writing data into a sql server 2000 database. I am am looking at a least
effort way of encrypting some of these details, I was thinking the best way
might be to create a trigger on the relevant database fields which encrypts
the information on insert. I am not sure what should go into the trigger to
do the encryption, any ideas?
There are a couple of issues to note
1. It would have to be a strong encryption algorithm like triple DES.
2. Data would from the database needs to be extracted into a vb.net
application which would need to be able to decrypt it, ideally with the
encryption functions built into .Net.
3. Encrypting the data in the asp/asp.net code before inserting is not
particularly practical due to the large number of separate applications.
ScottThere are 3rd party vendors that offer databse encryption, that SQL 2000
cannot natively provide.
Here's one of the vendors.
http://www.protegrity.com/
Some articles that may be of interest.
325757 INF: Using SQL Server 2000 with FIPS 140-1 Ciphers
http://support.microsoft.com/?id=325757
331367 PRB: Cannot Decrypt Data Using Data Encryption Standard (DES) Key
Across
http://support.microsoft.com/?id=331367
Building Secure ASP.NET Applications: Authentication, Authorization, and
Secure Communication
http://msdn.microsoft.com/library/d...-us/dnnetsec/ht
ml/SecNetHT08.asp
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Hmmm .. Interesting. I am not a supporter of writing this in a trigger
personally. This will not be efficient and you will land up writing an
Extended stored procedures to do the encryption for you. In SQL Server 2005
this is out of the box feature to use and capitalize.
As a strategy I would always support things to be done in the middle tier
and then encrypting and decrypting them at that layer rather than sending it
to database and later trying to encrypt before inserting. Just my personal
views ...
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"scottrm" <scottrm@.newsgroup.nospam> wrote in message
news:3CE2425A-6063-48B3-A2DF-76C6F37AA06A@.microsoft.com...
> I have a number of both classic asp and asp.net applications which are
> writing data into a sql server 2000 database. I am am looking at a least
> effort way of encrypting some of these details, I was thinking the best
way
> might be to create a trigger on the relevant database fields which
encrypts
> the information on insert. I am not sure what should go into the trigger
to
> do the encryption, any ideas?
> There are a couple of issues to note
> 1. It would have to be a strong encryption algorithm like triple DES.
> 2. Data would from the database needs to be extracted into a vb.net
> application which would need to be able to decrypt it, ideally with the
> encryption functions built into .Net.
> 3. Encrypting the data in the asp/asp.net code before inserting is not
> particularly practical due to the large number of separate applications.
>
> --
> Scott
Wednesday, March 7, 2012
Encapsulate encryption with an UDF and a view
I have a table that has an encrypted account number column. In order to give users access to the data BUT not access to the symmetric key used to encrypt the data, I'm trying to implement the following:
Create an UDF that opens the symmetric key, call the EncryptByKey function, and close the symmetric key
Create a view on the table containing decrypted account numbers
Grant users select permission on the view
However, I got an error when trying to add 'open symmetric key' statement to the UDF:
Msg 443, Level 16, State 14, Procedure fn_DecyptAcctNum, Line 7
Invalid use of side-effecting or time-dependent operator in 'OPEN SYMMETRIC KEY' within a function.
Has anyone else had the same problem OR is there another way that I can limit users' access to the keys?
Thanks!
You cannot use OPEN SYMMETRIC KEY and CLOSE SYMMETRIC KEY in a UDF because they change the state of the openkeys catalog (this is the side-effect from the message).But if you only want to give users access to data, a view that performs the decryption should be sufficient; there is no need to encapsulate the encryption in a UDF, unless you want to do more than limiting access to the data.
Thanks
Laurentiu|||Laurentiu, Thanks for your help on this!
However, I'm still not sure where to put the 'open symmetric key' statement since it is required to make both EncryptByKey and DecryptByKey work. Let me give you a scenario:
A user wants to lookup the account ID for an account number
The application calls a stored procedure sp_AccountNumberLookup:
sp validates the passed in account number
sp selects from the view (with decrypted account number using UDF)
sp returns the account ID if found
Without having the 'open key' statement before 'select from view', the decrypted account number returns NULL. If I put the 'open key' statement in the sp and give the user exec perm to the sp, then the user would have access to keys as well.
Thanks again for your time!
|||You should create your view using one of the "auto" decrypt builtins, as shown in the example from: http://blogs.msdn.com/lcris/archive/2005/06/10/428178.aspx - check view v_employees_auto. You'll need to protect your symmetric key using a certificate for this to work, but it will allow you to avoid opening the key explicitly.
Thanks
Laurentiu|||Your code sample is extremely helpful and I've implemented the DecryptByKeyAutoCert in the view to restrict user access to the symmetric key.
Thanks a lot, Laurentiu!
Encapsulate encryption with an UDF and a view
I have a table that has an encrypted account number column. In order to give users access to the data BUT not access to the symmetric key used to encrypt the data, I'm trying to implement the following:
Create an UDF that opens the symmetric key, call the EncryptByKey function, and close the symmetric key
Create a view on the table containing decrypted account numbers
Grant users select permission on the view
However, I got an error when trying to add 'open symmetric key' statement to the UDF:
Msg 443, Level 16, State 14, Procedure fn_DecyptAcctNum, Line 7
Invalid use of side-effecting or time-dependent operator in 'OPEN SYMMETRIC KEY' within a function.
Has anyone else had the same problem OR is there another way that I can limit users' access to the keys?
Thanks!
You cannot use OPEN SYMMETRIC KEY and CLOSE SYMMETRIC KEY in a UDF because they change the state of the openkeys catalog (this is the side-effect from the message).But if you only want to give users access to data, a view that performs the decryption should be sufficient; there is no need to encapsulate the encryption in a UDF, unless you want to do more than limiting access to the data.
Thanks
Laurentiu|||Laurentiu, Thanks for your help on this!
However, I'm still not sure where to put the 'open symmetric key' statement since it is required to make both EncryptByKey and DecryptByKey work. Let me give you a scenario:
A user wants to lookup the account ID for an account number
The application calls a stored procedure sp_AccountNumberLookup:
sp validates the passed in account number
sp selects from the view (with decrypted account number using UDF)
sp returns the account ID if found
Without having the 'open key' statement before 'select from view', the decrypted account number returns NULL. If I put the 'open key' statement in the sp and give the user exec perm to the sp, then the user would have access to keys as well.
Thanks again for your time!|||You should create your view using one of the "auto" decrypt builtins, as shown in the example from: http://blogs.msdn.com/lcris/archive/2005/06/10/428178.aspx - check view v_employees_auto. You'll need to protect your symmetric key using a certificate for this to work, but it will allow you to avoid opening the key explicitly.
Thanks
Laurentiu|||Your code sample is extremely helpful and I've implemented the DecryptByKeyAutoCert in the view to restrict user access to the symmetric key.
Thanks a lot, Laurentiu!
Subscribe to:
Posts (Atom)