Hello,
We have a requirement to encrypt our database so that us
DBA cannot read the infomation on it.
Can anyone point me to resource that will help us do this.
M
You cannot "encrypt a database" -- you can encrypt some of the data in the
database. But the application(s) that read and write the data should
probably handle that. The app can use the MS Crypto API for that ...
http://msdn.microsoft.com/library/de...n_cryptapi.asp
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Moira" <anonymous@.discussions.microsoft.com> wrote in message
news:046101c4fa4c$a3be1b20$a501280a@.phx.gbl...
> Hello,
> We have a requirement to encrypt our database so that us
> DBA cannot read the infomation on it.
> Can anyone point me to resource that will help us do this.
> M
|||Thanks Adam.
>--Original Message--
>You cannot "encrypt a database" -- you can encrypt some
of the data in the
>database. But the application(s) that read and write the
data should
>probably handle that. The app can use the MS Crypto API
for that ...
>http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dncapi/html/msdn_cryptapi.asp
>
>--
>Adam Machanic
>SQL Server MVP
>http://www.sqljunkies.com/weblog/amachanic
>--
>
>"Moira" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:046101c4fa4c$a3be1b20$a501280a@.phx.gbl...
this.
>
>.
>
|||Moira wrote:
> Hello,
> We have a requirement to encrypt our database so that us
> DBA cannot read the infomation on it.
> Can anyone point me to resource that will help us do this.
>
You may try putting your MDF and LDF on a Windows EFS volume. It
essentially encrypts your file so that only the account "owner" of the
volume can read the file. SQL Server should be using that "owner"
account. There is a big performance hit though. But if you want to
secure your files from being "hijacked" by a system admin or restored
elsewhere, then this may be a solution. However, I cannot see how you
can keep a DBA out of the DB without having "DBA" expertise, at least
not without resorting to encrypting the data before it gets stored in
the DB...as described in other places
Showing posts with label requirement. Show all posts
Showing posts with label requirement. Show all posts
Thursday, March 29, 2012
Encypting The Database
Hello,
We have a requirement to encrypt our database so that us
DBA cannot read the infomation on it.
Can anyone point me to resource that will help us do this.
MYou cannot "encrypt a database" -- you can encrypt some of the data in the
database. But the application(s) that read and write the data should
probably handle that. The app can use the MS Crypto API for that ...
http://msdn.microsoft.com/library/d...>
cryptapi.asp
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Moira" <anonymous@.discussions.microsoft.com> wrote in message
news:046101c4fa4c$a3be1b20$a501280a@.phx.gbl...
> Hello,
> We have a requirement to encrypt our database so that us
> DBA cannot read the infomation on it.
> Can anyone point me to resource that will help us do this.
> M|||Thanks Adam.
>--Original Message--
>You cannot "encrypt a database" -- you can encrypt some
of the data in the
>database. But the application(s) that read and write the
data should
>probably handle that. The app can use the MS Crypto API
for that ...
>http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dncapi/html/msdn_cryptapi.asp
>
>--
>Adam Machanic
>SQL Server MVP
>http://www.sqljunkies.com/weblog/amachanic
>--
>
>"Moira" <anonymous@.discussions.microsoft.com> wrote in
message
>news:046101c4fa4c$a3be1b20$a501280a@.phx.gbl...
this.[vbcol=seagreen]
>
>.
>|||Moira wrote:
> Hello,
> We have a requirement to encrypt our database so that us
> DBA cannot read the infomation on it.
> Can anyone point me to resource that will help us do this.
>
You may try putting your MDF and LDF on a Windows EFS volume. It
essentially encrypts your file so that only the account "owner" of the
volume can read the file. SQL Server should be using that "owner"
account. There is a big performance hit though. But if you want to
secure your files from being "hijacked" by a system admin or restored
elsewhere, then this may be a solution. However, I cannot see how you
can keep a DBA out of the DB without having "DBA" expertise, at least
not without resorting to encrypting the data before it gets stored in
the DB...as described in other places
We have a requirement to encrypt our database so that us
DBA cannot read the infomation on it.
Can anyone point me to resource that will help us do this.
MYou cannot "encrypt a database" -- you can encrypt some of the data in the
database. But the application(s) that read and write the data should
probably handle that. The app can use the MS Crypto API for that ...
http://msdn.microsoft.com/library/d...>
cryptapi.asp
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Moira" <anonymous@.discussions.microsoft.com> wrote in message
news:046101c4fa4c$a3be1b20$a501280a@.phx.gbl...
> Hello,
> We have a requirement to encrypt our database so that us
> DBA cannot read the infomation on it.
> Can anyone point me to resource that will help us do this.
> M|||Thanks Adam.
>--Original Message--
>You cannot "encrypt a database" -- you can encrypt some
of the data in the
>database. But the application(s) that read and write the
data should
>probably handle that. The app can use the MS Crypto API
for that ...
>http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dncapi/html/msdn_cryptapi.asp
>
>--
>Adam Machanic
>SQL Server MVP
>http://www.sqljunkies.com/weblog/amachanic
>--
>
>"Moira" <anonymous@.discussions.microsoft.com> wrote in
message
>news:046101c4fa4c$a3be1b20$a501280a@.phx.gbl...
this.[vbcol=seagreen]
>
>.
>|||Moira wrote:
> Hello,
> We have a requirement to encrypt our database so that us
> DBA cannot read the infomation on it.
> Can anyone point me to resource that will help us do this.
>
You may try putting your MDF and LDF on a Windows EFS volume. It
essentially encrypts your file so that only the account "owner" of the
volume can read the file. SQL Server should be using that "owner"
account. There is a big performance hit though. But if you want to
secure your files from being "hijacked" by a system admin or restored
elsewhere, then this may be a solution. However, I cannot see how you
can keep a DBA out of the DB without having "DBA" expertise, at least
not without resorting to encrypting the data before it gets stored in
the DB...as described in other places
Encypting The Database
Hello,
We have a requirement to encrypt our database so that us
DBA cannot read the infomation on it.
Can anyone point me to resource that will help us do this.
MYou cannot "encrypt a database" -- you can encrypt some of the data in the
database. But the application(s) that read and write the data should
probably handle that. The app can use the MS Crypto API for that ...
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dncapi/html/msdn_cryptapi.asp
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Moira" <anonymous@.discussions.microsoft.com> wrote in message
news:046101c4fa4c$a3be1b20$a501280a@.phx.gbl...
> Hello,
> We have a requirement to encrypt our database so that us
> DBA cannot read the infomation on it.
> Can anyone point me to resource that will help us do this.
> M|||Thanks Adam.
>--Original Message--
>You cannot "encrypt a database" -- you can encrypt some
of the data in the
>database. But the application(s) that read and write the
data should
>probably handle that. The app can use the MS Crypto API
for that ...
>http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dncapi/html/msdn_cryptapi.asp
>
>--
>Adam Machanic
>SQL Server MVP
>http://www.sqljunkies.com/weblog/amachanic
>--
>
>"Moira" <anonymous@.discussions.microsoft.com> wrote in
message
>news:046101c4fa4c$a3be1b20$a501280a@.phx.gbl...
>> Hello,
>> We have a requirement to encrypt our database so that us
>> DBA cannot read the infomation on it.
>> Can anyone point me to resource that will help us do
this.
>> M
>
>.
>|||Moira wrote:
> Hello,
> We have a requirement to encrypt our database so that us
> DBA cannot read the infomation on it.
> Can anyone point me to resource that will help us do this.
>
You may try putting your MDF and LDF on a Windows EFS volume. It
essentially encrypts your file so that only the account "owner" of the
volume can read the file. SQL Server should be using that "owner"
account. There is a big performance hit though. But if you want to
secure your files from being "hijacked" by a system admin or restored
elsewhere, then this may be a solution. However, I cannot see how you
can keep a DBA out of the DB without having "DBA" expertise, at least
not without resorting to encrypting the data before it gets stored in
the DB...as described in other places
We have a requirement to encrypt our database so that us
DBA cannot read the infomation on it.
Can anyone point me to resource that will help us do this.
MYou cannot "encrypt a database" -- you can encrypt some of the data in the
database. But the application(s) that read and write the data should
probably handle that. The app can use the MS Crypto API for that ...
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dncapi/html/msdn_cryptapi.asp
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Moira" <anonymous@.discussions.microsoft.com> wrote in message
news:046101c4fa4c$a3be1b20$a501280a@.phx.gbl...
> Hello,
> We have a requirement to encrypt our database so that us
> DBA cannot read the infomation on it.
> Can anyone point me to resource that will help us do this.
> M|||Thanks Adam.
>--Original Message--
>You cannot "encrypt a database" -- you can encrypt some
of the data in the
>database. But the application(s) that read and write the
data should
>probably handle that. The app can use the MS Crypto API
for that ...
>http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dncapi/html/msdn_cryptapi.asp
>
>--
>Adam Machanic
>SQL Server MVP
>http://www.sqljunkies.com/weblog/amachanic
>--
>
>"Moira" <anonymous@.discussions.microsoft.com> wrote in
message
>news:046101c4fa4c$a3be1b20$a501280a@.phx.gbl...
>> Hello,
>> We have a requirement to encrypt our database so that us
>> DBA cannot read the infomation on it.
>> Can anyone point me to resource that will help us do
this.
>> M
>
>.
>|||Moira wrote:
> Hello,
> We have a requirement to encrypt our database so that us
> DBA cannot read the infomation on it.
> Can anyone point me to resource that will help us do this.
>
You may try putting your MDF and LDF on a Windows EFS volume. It
essentially encrypts your file so that only the account "owner" of the
volume can read the file. SQL Server should be using that "owner"
account. There is a big performance hit though. But if you want to
secure your files from being "hijacked" by a system admin or restored
elsewhere, then this may be a solution. However, I cannot see how you
can keep a DBA out of the DB without having "DBA" expertise, at least
not without resorting to encrypting the data before it gets stored in
the DB...as described in other places
Tuesday, March 27, 2012
encryption optimization
Hi,
I have an application that requires the storing of personal data, name,
address, ssn with a requirement that key fields, first name, last name,
address1, city, zip, ssn be encrypted. Currently, there are over 100,000
records, and growing fast. Searches need to be done based on these key
fields (lastname like 'A%' for instance). I am loading the return set into
SqlDataSource and GridView using .net version 2.0.
I'm using a stored procedure to return the data.
When the page first loads (which filters on lastname = 'A%', it loads
slowly, about 20 seconds), if I change the filter (lastname = 'Q%') it times
out.
Is there a best practice to follow in a case like this that would result in
the best performance possible. I realize with all the encryption that it is
very processing intensive, but with a first load at least usable, but the
second load timing out, there may be some things I should do (clear buffers
or something) that I do not know about doing.
Can you help on this?
Thanks.The bottom line is that you can't efficiently search (use indexes) on these
encrypted key columns because you are searching using the decrypted value
and this value is not stored in the database. During the search, not only
must each value be decrypted, a scan of all table rows is required. This is
a very expensive operation.
For an equality search, you can store a hash in clear text, index the hash
value and add the hash search criteria to your search. This will greatly
reduce the number of qualifying rows and usually result in acceptable
performance. See Laurentiu Cristofor's blog
(http://blogs.msdn.com/lcris/archive.../22/506931.aspx) for a more
complete discussion.
Unfortunately, there is no way to perform efficient wildcard/range searches
on encrypted data. If you must have this functionality, you'll need to take
a different approach like encrypting at the file level (EFS) rather than
column level. I don't know if that's an option in your environment.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gerhard" <acsla@.community.nospam> wrote in message
news:CA33D347-57EC-451F-A3BB-A3931D8CFF65@.microsoft.com...
> Hi,
> I have an application that requires the storing of personal data, name,
> address, ssn with a requirement that key fields, first name, last name,
> address1, city, zip, ssn be encrypted. Currently, there are over 100,000
> records, and growing fast. Searches need to be done based on these key
> fields (lastname like 'A%' for instance). I am loading the return set
> into
> SqlDataSource and GridView using .net version 2.0.
> I'm using a stored procedure to return the data.
> When the page first loads (which filters on lastname = 'A%', it loads
> slowly, about 20 seconds), if I change the filter (lastname = 'Q%') it
> times
> out.
> Is there a best practice to follow in a case like this that would result
> in
> the best performance possible. I realize with all the encryption that it
> is
> very processing intensive, but with a first load at least usable, but the
> second load timing out, there may be some things I should do (clear
> buffers
> or something) that I do not know about doing.
> Can you help on this?
> Thanks.|||Hi Acsla,
I am interested in this issue. Would you mind letting me know the result of
the suggestions?
I noticed that you bind the query result to a Gridview, so I guess that the
loading time (20s and time out) refers to the whole loading process when
all data has been displayed in your GridView. Does the Gridview have a
paging function? According to my experience, if the query result is also
huge, the render time of the control may also spend a long time.
Basically I agree with Dan's suggestions on SQL. Additionally, I would like
your checking how long it will spend if you separately run the SQL
statement in Query Analyzer.
If you need further assistance, feel free to let me know. I will be more
than happy to be of assistance.
Have a great day!
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============sql
I have an application that requires the storing of personal data, name,
address, ssn with a requirement that key fields, first name, last name,
address1, city, zip, ssn be encrypted. Currently, there are over 100,000
records, and growing fast. Searches need to be done based on these key
fields (lastname like 'A%' for instance). I am loading the return set into
SqlDataSource and GridView using .net version 2.0.
I'm using a stored procedure to return the data.
When the page first loads (which filters on lastname = 'A%', it loads
slowly, about 20 seconds), if I change the filter (lastname = 'Q%') it times
out.
Is there a best practice to follow in a case like this that would result in
the best performance possible. I realize with all the encryption that it is
very processing intensive, but with a first load at least usable, but the
second load timing out, there may be some things I should do (clear buffers
or something) that I do not know about doing.
Can you help on this?
Thanks.The bottom line is that you can't efficiently search (use indexes) on these
encrypted key columns because you are searching using the decrypted value
and this value is not stored in the database. During the search, not only
must each value be decrypted, a scan of all table rows is required. This is
a very expensive operation.
For an equality search, you can store a hash in clear text, index the hash
value and add the hash search criteria to your search. This will greatly
reduce the number of qualifying rows and usually result in acceptable
performance. See Laurentiu Cristofor's blog
(http://blogs.msdn.com/lcris/archive.../22/506931.aspx) for a more
complete discussion.
Unfortunately, there is no way to perform efficient wildcard/range searches
on encrypted data. If you must have this functionality, you'll need to take
a different approach like encrypting at the file level (EFS) rather than
column level. I don't know if that's an option in your environment.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gerhard" <acsla@.community.nospam> wrote in message
news:CA33D347-57EC-451F-A3BB-A3931D8CFF65@.microsoft.com...
> Hi,
> I have an application that requires the storing of personal data, name,
> address, ssn with a requirement that key fields, first name, last name,
> address1, city, zip, ssn be encrypted. Currently, there are over 100,000
> records, and growing fast. Searches need to be done based on these key
> fields (lastname like 'A%' for instance). I am loading the return set
> into
> SqlDataSource and GridView using .net version 2.0.
> I'm using a stored procedure to return the data.
> When the page first loads (which filters on lastname = 'A%', it loads
> slowly, about 20 seconds), if I change the filter (lastname = 'Q%') it
> times
> out.
> Is there a best practice to follow in a case like this that would result
> in
> the best performance possible. I realize with all the encryption that it
> is
> very processing intensive, but with a first load at least usable, but the
> second load timing out, there may be some things I should do (clear
> buffers
> or something) that I do not know about doing.
> Can you help on this?
> Thanks.|||Hi Acsla,
I am interested in this issue. Would you mind letting me know the result of
the suggestions?
I noticed that you bind the query result to a Gridview, so I guess that the
loading time (20s and time out) refers to the whole loading process when
all data has been displayed in your GridView. Does the Gridview have a
paging function? According to my experience, if the query result is also
huge, the render time of the control may also spend a long time.
Basically I agree with Dan's suggestions on SQL. Additionally, I would like
your checking how long it will spend if you separately run the SQL
statement in Query Analyzer.
If you need further assistance, feel free to let me know. I will be more
than happy to be of assistance.
Have a great day!
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============sql
Encryption of Data
Dear All
We have a requirement to encrypt data within SQL Server,
eg, Bank Account / credit card numbers.
I don't believe there is anything in-built within SQL
Server 2000, but is there something in-built in 2005 ?
Please note I do know about security and SSL but in this
case we are trying to encrypt the data in the server
itself.Hi
SQL Server 2005 will support this at column level using Certificates.
Regards
Mike
"Jane" wrote:
> Dear All
> We have a requirement to encrypt data within SQL Server,
> eg, Bank Account / credit card numbers.
> I don't believe there is anything in-built within SQL
> Server 2000, but is there something in-built in 2005 ?
> Please note I do know about security and SSL but in this
> case we are trying to encrypt the data in the server
> itself.
>
>|||There are a number of offerings, one of which is ours, NetLib Encryptionizer
(www.netlib.com). I am sure the others will speak up for themselves.
Encryptionizer is the only one that has the capability of protecting columns
and/or entire databases. It is already in use in banks, credit card
processors, government agencies, and many other places.
Regards,
Neil Weicher
NetLib
_________________________________
"Jane" <anonymous@.discussions.microsoft.com> wrote in message
Dear All
We have a requirement to encrypt data within SQL Server,
eg, Bank Account / credit card numbers.
I don't believe there is anything in-built within SQL
Server 2000, but is there something in-built in 2005 ?
Please note I do know about security and SSL but in this
case we are trying to encrypt the data in the server
itself.|||anybody tried this yet?
if you do a select from query analyzer, will you get back encrypted data
or can you install the certificate in query analyzer so you get back
decrypted data?
Mike Epprecht (SQL MVP) wrote:[vbcol=seagreen]
> Hi
> SQL Server 2005 will support this at column level using Certificates.
> Regards
> Mike
> "Jane" wrote:
>|||To be honest I was talking a load of rubbish, I don not
know how Certificates work in sufficent detail to know if
it will work or not.
Our problem however is so that DBA's like myself cannot
read the data.
Thanks
Jane
>--Original Message--
>anybody tried this yet?
>if you do a select from query analyzer, will you get back
encrypted data
>or can you install the certificate in query analyzer so
you get back
>decrypted data?
>
>Mike Epprecht (SQL MVP) wrote:
Certificates.[vbcol=seagreen]
Server,[vbcol=seagreen]
this[vbcol=seagreen]
>.
>|||Take a look around on the internet. There are various encryption algorithms
that you can use.
There is an RC4 one out there that is pretty straight-forward. Install the
function and then use it for both encryption and decryption.
Note: While this link shows the info on RC4 algorithm and an
implementation, I do not know if this is IP and whether or not you need to
purchase some type of license for it. Do your due diligence.
http://www.databasejournal.com/img/RC4_enhanced.sql
Rick Sawtell
MCT, MCSD, MCDBA
We have a requirement to encrypt data within SQL Server,
eg, Bank Account / credit card numbers.
I don't believe there is anything in-built within SQL
Server 2000, but is there something in-built in 2005 ?
Please note I do know about security and SSL but in this
case we are trying to encrypt the data in the server
itself.Hi
SQL Server 2005 will support this at column level using Certificates.
Regards
Mike
"Jane" wrote:
> Dear All
> We have a requirement to encrypt data within SQL Server,
> eg, Bank Account / credit card numbers.
> I don't believe there is anything in-built within SQL
> Server 2000, but is there something in-built in 2005 ?
> Please note I do know about security and SSL but in this
> case we are trying to encrypt the data in the server
> itself.
>
>|||There are a number of offerings, one of which is ours, NetLib Encryptionizer
(www.netlib.com). I am sure the others will speak up for themselves.
Encryptionizer is the only one that has the capability of protecting columns
and/or entire databases. It is already in use in banks, credit card
processors, government agencies, and many other places.
Regards,
Neil Weicher
NetLib
_________________________________
"Jane" <anonymous@.discussions.microsoft.com> wrote in message
Dear All
We have a requirement to encrypt data within SQL Server,
eg, Bank Account / credit card numbers.
I don't believe there is anything in-built within SQL
Server 2000, but is there something in-built in 2005 ?
Please note I do know about security and SSL but in this
case we are trying to encrypt the data in the server
itself.|||anybody tried this yet?
if you do a select from query analyzer, will you get back encrypted data
or can you install the certificate in query analyzer so you get back
decrypted data?
Mike Epprecht (SQL MVP) wrote:[vbcol=seagreen]
> Hi
> SQL Server 2005 will support this at column level using Certificates.
> Regards
> Mike
> "Jane" wrote:
>|||To be honest I was talking a load of rubbish, I don not
know how Certificates work in sufficent detail to know if
it will work or not.
Our problem however is so that DBA's like myself cannot
read the data.
Thanks
Jane
>--Original Message--
>anybody tried this yet?
>if you do a select from query analyzer, will you get back
encrypted data
>or can you install the certificate in query analyzer so
you get back
>decrypted data?
>
>Mike Epprecht (SQL MVP) wrote:
Certificates.[vbcol=seagreen]
Server,[vbcol=seagreen]
this[vbcol=seagreen]
>.
>|||Take a look around on the internet. There are various encryption algorithms
that you can use.
There is an RC4 one out there that is pretty straight-forward. Install the
function and then use it for both encryption and decryption.
Note: While this link shows the info on RC4 algorithm and an
implementation, I do not know if this is IP and whether or not you need to
purchase some type of license for it. Do your due diligence.
http://www.databasejournal.com/img/RC4_enhanced.sql
Rick Sawtell
MCT, MCSD, MCDBA
Monday, March 26, 2012
Encryption of Data
Dear All
We have a requirement to encrypt data within SQL Server,
eg, Bank Account / credit card numbers.
I don't believe there is anything in-built within SQL
Server 2000, but is there something in-built in 2005 ?
Please note I do know about security and SSL but in this
case we are trying to encrypt the data in the server
itself.Hi
SQL Server 2005 will support this at column level using Certificates.
Regards
Mike
"Jane" wrote:
> Dear All
> We have a requirement to encrypt data within SQL Server,
> eg, Bank Account / credit card numbers.
> I don't believe there is anything in-built within SQL
> Server 2000, but is there something in-built in 2005 ?
> Please note I do know about security and SSL but in this
> case we are trying to encrypt the data in the server
> itself.
>
>|||There are a number of offerings, one of which is ours, NetLib Encryptionizer
(www.netlib.com). I am sure the others will speak up for themselves.
Encryptionizer is the only one that has the capability of protecting columns
and/or entire databases. It is already in use in banks, credit card
processors, government agencies, and many other places.
Regards,
Neil Weicher
NetLib
_________________________________
"Jane" <anonymous@.discussions.microsoft.com> wrote in message
Dear All
We have a requirement to encrypt data within SQL Server,
eg, Bank Account / credit card numbers.
I don't believe there is anything in-built within SQL
Server 2000, but is there something in-built in 2005 ?
Please note I do know about security and SSL but in this
case we are trying to encrypt the data in the server
itself.|||anybody tried this yet?
if you do a select from query analyzer, will you get back encrypted data
or can you install the certificate in query analyzer so you get back
decrypted data?
Mike Epprecht (SQL MVP) wrote:
> Hi
> SQL Server 2005 will support this at column level using Certificates.
> Regards
> Mike
> "Jane" wrote:
> > Dear All
> >
> > We have a requirement to encrypt data within SQL Server,
> > eg, Bank Account / credit card numbers.
> >
> > I don't believe there is anything in-built within SQL
> > Server 2000, but is there something in-built in 2005 ?
> >
> > Please note I do know about security and SSL but in this
> > case we are trying to encrypt the data in the server
> > itself.
> >
> >
> >|||To be honest I was talking a load of rubbish, I don not
know how Certificates work in sufficent detail to know if
it will work or not.
Our problem however is so that DBA's like myself cannot
read the data.
Thanks
Jane
>--Original Message--
>anybody tried this yet?
>if you do a select from query analyzer, will you get back
encrypted data
>or can you install the certificate in query analyzer so
you get back
>decrypted data?
>
>Mike Epprecht (SQL MVP) wrote:
>> Hi
>> SQL Server 2005 will support this at column level using
Certificates.
>> Regards
>> Mike
>> "Jane" wrote:
>> > Dear All
>> >
>> > We have a requirement to encrypt data within SQL
Server,
>> > eg, Bank Account / credit card numbers.
>> >
>> > I don't believe there is anything in-built within SQL
>> > Server 2000, but is there something in-built in 2005 ?
>> >
>> > Please note I do know about security and SSL but in
this
>> > case we are trying to encrypt the data in the server
>> > itself.
>> >
>> >
>> >
>.
>|||Take a look around on the internet. There are various encryption algorithms
that you can use.
There is an RC4 one out there that is pretty straight-forward. Install the
function and then use it for both encryption and decryption.
Note: While this link shows the info on RC4 algorithm and an
implementation, I do not know if this is IP and whether or not you need to
purchase some type of license for it. Do your due diligence.
http://www.databasejournal.com/img/RC4_enhanced.sql
Rick Sawtell
MCT, MCSD, MCDBAsql
We have a requirement to encrypt data within SQL Server,
eg, Bank Account / credit card numbers.
I don't believe there is anything in-built within SQL
Server 2000, but is there something in-built in 2005 ?
Please note I do know about security and SSL but in this
case we are trying to encrypt the data in the server
itself.Hi
SQL Server 2005 will support this at column level using Certificates.
Regards
Mike
"Jane" wrote:
> Dear All
> We have a requirement to encrypt data within SQL Server,
> eg, Bank Account / credit card numbers.
> I don't believe there is anything in-built within SQL
> Server 2000, but is there something in-built in 2005 ?
> Please note I do know about security and SSL but in this
> case we are trying to encrypt the data in the server
> itself.
>
>|||There are a number of offerings, one of which is ours, NetLib Encryptionizer
(www.netlib.com). I am sure the others will speak up for themselves.
Encryptionizer is the only one that has the capability of protecting columns
and/or entire databases. It is already in use in banks, credit card
processors, government agencies, and many other places.
Regards,
Neil Weicher
NetLib
_________________________________
"Jane" <anonymous@.discussions.microsoft.com> wrote in message
Dear All
We have a requirement to encrypt data within SQL Server,
eg, Bank Account / credit card numbers.
I don't believe there is anything in-built within SQL
Server 2000, but is there something in-built in 2005 ?
Please note I do know about security and SSL but in this
case we are trying to encrypt the data in the server
itself.|||anybody tried this yet?
if you do a select from query analyzer, will you get back encrypted data
or can you install the certificate in query analyzer so you get back
decrypted data?
Mike Epprecht (SQL MVP) wrote:
> Hi
> SQL Server 2005 will support this at column level using Certificates.
> Regards
> Mike
> "Jane" wrote:
> > Dear All
> >
> > We have a requirement to encrypt data within SQL Server,
> > eg, Bank Account / credit card numbers.
> >
> > I don't believe there is anything in-built within SQL
> > Server 2000, but is there something in-built in 2005 ?
> >
> > Please note I do know about security and SSL but in this
> > case we are trying to encrypt the data in the server
> > itself.
> >
> >
> >|||To be honest I was talking a load of rubbish, I don not
know how Certificates work in sufficent detail to know if
it will work or not.
Our problem however is so that DBA's like myself cannot
read the data.
Thanks
Jane
>--Original Message--
>anybody tried this yet?
>if you do a select from query analyzer, will you get back
encrypted data
>or can you install the certificate in query analyzer so
you get back
>decrypted data?
>
>Mike Epprecht (SQL MVP) wrote:
>> Hi
>> SQL Server 2005 will support this at column level using
Certificates.
>> Regards
>> Mike
>> "Jane" wrote:
>> > Dear All
>> >
>> > We have a requirement to encrypt data within SQL
Server,
>> > eg, Bank Account / credit card numbers.
>> >
>> > I don't believe there is anything in-built within SQL
>> > Server 2000, but is there something in-built in 2005 ?
>> >
>> > Please note I do know about security and SSL but in
this
>> > case we are trying to encrypt the data in the server
>> > itself.
>> >
>> >
>> >
>.
>|||Take a look around on the internet. There are various encryption algorithms
that you can use.
There is an RC4 one out there that is pretty straight-forward. Install the
function and then use it for both encryption and decryption.
Note: While this link shows the info on RC4 algorithm and an
implementation, I do not know if this is IP and whether or not you need to
purchase some type of license for it. Do your due diligence.
http://www.databasejournal.com/img/RC4_enhanced.sql
Rick Sawtell
MCT, MCSD, MCDBAsql
Encryption of Data
Dear All
We have a requirement to encrypt data within SQL Server,
eg, Bank Account / credit card numbers.
I don't believe there is anything in-built within SQL
Server 2000, but is there something in-built in 2005 ?
Please note I do know about security and SSL but in this
case we are trying to encrypt the data in the server
itself.
Hi
SQL Server 2005 will support this at column level using Certificates.
Regards
Mike
"Jane" wrote:
> Dear All
> We have a requirement to encrypt data within SQL Server,
> eg, Bank Account / credit card numbers.
> I don't believe there is anything in-built within SQL
> Server 2000, but is there something in-built in 2005 ?
> Please note I do know about security and SSL but in this
> case we are trying to encrypt the data in the server
> itself.
>
>
|||There are a number of offerings, one of which is ours, NetLib Encryptionizer
(www.netlib.com). I am sure the others will speak up for themselves.
Encryptionizer is the only one that has the capability of protecting columns
and/or entire databases. It is already in use in banks, credit card
processors, government agencies, and many other places.
Regards,
Neil Weicher
NetLib
_________________________________
"Jane" <anonymous@.discussions.microsoft.com> wrote in message
Dear All
We have a requirement to encrypt data within SQL Server,
eg, Bank Account / credit card numbers.
I don't believe there is anything in-built within SQL
Server 2000, but is there something in-built in 2005 ?
Please note I do know about security and SSL but in this
case we are trying to encrypt the data in the server
itself.
|||anybody tried this yet?
if you do a select from query analyzer, will you get back encrypted data
or can you install the certificate in query analyzer so you get back
decrypted data?
Mike Epprecht (SQL MVP) wrote:[vbcol=seagreen]
> Hi
> SQL Server 2005 will support this at column level using Certificates.
> Regards
> Mike
> "Jane" wrote:
|||To be honest I was talking a load of rubbish, I don not
know how Certificates work in sufficent detail to know if
it will work or not.
Our problem however is so that DBA's like myself cannot
read the data.
Thanks
Jane
>--Original Message--
>anybody tried this yet?
>if you do a select from query analyzer, will you get back
encrypted data
>or can you install the certificate in query analyzer so
you get back[vbcol=seagreen]
>decrypted data?
>
>Mike Epprecht (SQL MVP) wrote:
Certificates.[vbcol=seagreen]
Server,[vbcol=seagreen]
this
>.
>
|||Take a look around on the internet. There are various encryption algorithms
that you can use.
There is an RC4 one out there that is pretty straight-forward. Install the
function and then use it for both encryption and decryption.
Note: While this link shows the info on RC4 algorithm and an
implementation, I do not know if this is IP and whether or not you need to
purchase some type of license for it. Do your due diligence.
http://www.databasejournal.com/img/RC4_enhanced.sql
Rick Sawtell
MCT, MCSD, MCDBA
We have a requirement to encrypt data within SQL Server,
eg, Bank Account / credit card numbers.
I don't believe there is anything in-built within SQL
Server 2000, but is there something in-built in 2005 ?
Please note I do know about security and SSL but in this
case we are trying to encrypt the data in the server
itself.
Hi
SQL Server 2005 will support this at column level using Certificates.
Regards
Mike
"Jane" wrote:
> Dear All
> We have a requirement to encrypt data within SQL Server,
> eg, Bank Account / credit card numbers.
> I don't believe there is anything in-built within SQL
> Server 2000, but is there something in-built in 2005 ?
> Please note I do know about security and SSL but in this
> case we are trying to encrypt the data in the server
> itself.
>
>
|||There are a number of offerings, one of which is ours, NetLib Encryptionizer
(www.netlib.com). I am sure the others will speak up for themselves.
Encryptionizer is the only one that has the capability of protecting columns
and/or entire databases. It is already in use in banks, credit card
processors, government agencies, and many other places.
Regards,
Neil Weicher
NetLib
_________________________________
"Jane" <anonymous@.discussions.microsoft.com> wrote in message
Dear All
We have a requirement to encrypt data within SQL Server,
eg, Bank Account / credit card numbers.
I don't believe there is anything in-built within SQL
Server 2000, but is there something in-built in 2005 ?
Please note I do know about security and SSL but in this
case we are trying to encrypt the data in the server
itself.
|||anybody tried this yet?
if you do a select from query analyzer, will you get back encrypted data
or can you install the certificate in query analyzer so you get back
decrypted data?
Mike Epprecht (SQL MVP) wrote:[vbcol=seagreen]
> Hi
> SQL Server 2005 will support this at column level using Certificates.
> Regards
> Mike
> "Jane" wrote:
|||To be honest I was talking a load of rubbish, I don not
know how Certificates work in sufficent detail to know if
it will work or not.
Our problem however is so that DBA's like myself cannot
read the data.
Thanks
Jane
>--Original Message--
>anybody tried this yet?
>if you do a select from query analyzer, will you get back
encrypted data
>or can you install the certificate in query analyzer so
you get back[vbcol=seagreen]
>decrypted data?
>
>Mike Epprecht (SQL MVP) wrote:
Certificates.[vbcol=seagreen]
Server,[vbcol=seagreen]
this
>.
>
|||Take a look around on the internet. There are various encryption algorithms
that you can use.
There is an RC4 one out there that is pretty straight-forward. Install the
function and then use it for both encryption and decryption.
Note: While this link shows the info on RC4 algorithm and an
implementation, I do not know if this is IP and whether or not you need to
purchase some type of license for it. Do your due diligence.
http://www.databasejournal.com/img/RC4_enhanced.sql
Rick Sawtell
MCT, MCSD, MCDBA
Wednesday, March 21, 2012
Encrypting the configuration file values stored in SQL server
Hi All,
I have the following requirement. I need to store the password for the connection manager in the configuration file. The sink for the configuration file is SQL Server. Though the password field appears as "******" the actual value is being taken as ""******" itself. If i update the SQL server table with the correct value, then the package starts working. But, the password is shown as clear text.
If i write logic to encrypt the password column in the configuration table, is there a way to tell the SSIS execute engine to decrypt the password before using the same for making the connection.
Is there a place holder, where i can write the decrypt code so that the decrypted password can be sent to the execution engine?
Thanks In Advance,
Madhu
I think the short answer to this is no, and no code hooks either.
I think though that there is also an argument, that says it would not be more secure than what you have now. If you encrypt the data, you need to then secure the key. So what will you do to secure the key? Why not use strong security to secure the password data instead of worrying about how to secure the key? I accept that the encryption adds an extra step, but I'm not convinced it will actually be any safer.
|||I'm not sure if it's a good idea, but couldn't he create a script task to decrypt the password and reset the connection manager's connectionstring property before the connection manager is used in the package?|||Yes and no. Some connections are used before your script task could run, such as connections used for logging.
How would you secure the key used to decrypt the password? You need to secure the encryption/decryption key, so why not just secure the password to start with?
|||DarrenSQLIS is right the recommended way to do this is to store the password in the connection. SSIS will automatically encrypt these so that they are not stored in cleartext.|||Thanks for the thoughts Darren. As suggested by you, way to go is to store the password in SQL server and make sure that the access to the configuration table is only for administrators.|||Denise, I think you are talking about the package level encryption, protection levels and such like. Nice though it is, it is not very useful, as I think you should "externalise" any kind of security information.
Using package encryption becomes unfeasible when you have to migrate packages between environments. Configurations solve that migration issue, but don't give you the encryption that is often seen as a requirement for some organisations. I'd argue that is should not be a big deal, secure the password so you don't have to worry about the key, but often it is an internal "standard" that must be complied with.
Still we have the choice of package encryption, which is better than not!
Encrypting data across replication
We are looking at setting up peer-to-peer transactional replication between two databases. We have a customer requirement to encrypt the SSN in this database. I have configured replication successfully. I have also successfully encrypted the SSN using a symmetric key (with encryption by certificate). What I haven't done yet is set up encryption to work across a replication topology.
What steps would I have to follow in order to be able to encrypt the SSN on one server, replicate it to the subscriber, and then decrypt the SSN on the subscriber? For this scenario, is there a better way to handle encryption other than a symmetric key encrypted by a certificate?
The most important thing is to set up your encryption key so that you can manually recreate it on the subscriber. The setup must be manual, because replication will only work for the content of the tables, not for the keys used to encrypt that content. Have a look at this post for additional information: http://blogs.msdn.com/lcris/archive/2006/07/06/658364.aspx.
Thanks
Laurentiu
Encrypting data across replication
We are looking at setting up peer-to-peer transactional replication between two databases. We have a customer requirement to encrypt the SSN in this database. I have configured replication successfully. I have also successfully encrypted the SSN using a symmetric key (with encryption by certificate). What I haven't done yet is set up encryption to work across a replication topology.
What steps would I have to follow in order to be able to encrypt the SSN on one server, replicate it to the subscriber, and then decrypt the SSN on the subscriber? For this scenario, is there a better way to handle encryption other than a symmetric key encrypted by a certificate?
The most important thing is to set up your encryption key so that you can manually recreate it on the subscriber. The setup must be manual, because replication will only work for the content of the tables, not for the keys used to encrypt that content. Have a look at this post for additional information: http://blogs.msdn.com/lcris/archive/2006/07/06/658364.aspx.
Thanks
Laurentiu
Wednesday, March 7, 2012
Enabling SQL audit Logging for SELECT queries
Hi all,
We have a requirement in our project where we need to audit log any query
(SELECT queries inclusive) that are fired on a specific set of objects (Tabl
e
and views) in our database. We need to capture information like Who fired th
e
query, When and the actual query itself.
The approach we have thought of is:
Run SQL Profiler and log the output of the trace into a SQL table
Create an INSERT trigger on the SQL table.
Trigger should write data into a custom Audit table with limited information
.
However the di
vantages we see here are performance issues due to profiler
being run continuously, maintenance overhead to clear the SQL table where th
e
trace is written etc.
Can anyone suggest any other better alternative for this requirement?
Thanks
GSNot sure if it ius an option for you, but SQL 2005 contains DML
triggers which allow you to audit Select statements.
Markus|||How about using any of the 3:rd party tools put there? Check the log reader
tools, they tend to have
this support (possibly in special versions): http://www.karaszi.com/SQLServer/link
s.asp
These tools does use the transaction log to audit modifications and Profiler
to audit SELECT.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"GS" <GS@.discussions.microsoft.com> wrote in message
news:6A359D6A-CCD1-4EC5-AEA2-C74C7F551971@.microsoft.com...
> Hi all,
> We have a requirement in our project where we need to audit log any query
> (SELECT queries inclusive) that are fired on a specific set of objects (Ta
ble
> and views) in our database. We need to capture information like Who fired
the
> query, When and the actual query itself.
> The approach we have thought of is:
> Run SQL Profiler and log the output of the trace into a SQL table
> Create an INSERT trigger on the SQL table.
> Trigger should write data into a custom Audit table with limited informati
on.
> However the divantages we see here are performance issues due to profil
er
> being run continuously, maintenance overhead to clear the SQL table where
the
> trace is written etc.
> Can anyone suggest any other better alternative for this requirement?
> Thanks
> GS
>
We have a requirement in our project where we need to audit log any query
(SELECT queries inclusive) that are fired on a specific set of objects (Tabl
e
and views) in our database. We need to capture information like Who fired th
e
query, When and the actual query itself.
The approach we have thought of is:
Run SQL Profiler and log the output of the trace into a SQL table
Create an INSERT trigger on the SQL table.
Trigger should write data into a custom Audit table with limited information
.
However the di
vantages we see here are performance issues due to profilerbeing run continuously, maintenance overhead to clear the SQL table where th
e
trace is written etc.
Can anyone suggest any other better alternative for this requirement?
Thanks
GSNot sure if it ius an option for you, but SQL 2005 contains DML
triggers which allow you to audit Select statements.
Markus|||How about using any of the 3:rd party tools put there? Check the log reader
tools, they tend to have
this support (possibly in special versions): http://www.karaszi.com/SQLServer/link
s.asp
These tools does use the transaction log to audit modifications and Profiler
to audit SELECT.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"GS" <GS@.discussions.microsoft.com> wrote in message
news:6A359D6A-CCD1-4EC5-AEA2-C74C7F551971@.microsoft.com...
> Hi all,
> We have a requirement in our project where we need to audit log any query
> (SELECT queries inclusive) that are fired on a specific set of objects (Ta
ble
> and views) in our database. We need to capture information like Who fired
the
> query, When and the actual query itself.
> The approach we have thought of is:
> Run SQL Profiler and log the output of the trace into a SQL table
> Create an INSERT trigger on the SQL table.
> Trigger should write data into a custom Audit table with limited informati
on.
> However the di
vantages we see here are performance issues due to profiler
> being run continuously, maintenance overhead to clear the SQL table where
the
> trace is written etc.
> Can anyone suggest any other better alternative for this requirement?
> Thanks
> GS
>
Subscribe to:
Posts (Atom)