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

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

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

Encrytion - EncryptByKey(Key_guid('EncyptKey'),(

I am trying to encrypt more 30 chars of plaintext with TRIPLE_AES, but SQL
gives me errors or autmatically truncates my ciphertext/plaintext to 30
plaintext characters.
This particular SQL Statement .
UPDATE tblDB
SET ConnectionString = EncryptByKey(Key_guid('EncyptKey'),('Example
connection string 33333333333333333333'))
WHERE Label = @.varLabel
The 30 character result I get is:
select * from tblDB
Result - '"Example connection string 33"
Does anyone have some suggestions? Thanks,
PremHi
How big is the connection string column?
John
"Prem" <u33747@.uwe> wrote in message news:7e4fff8cd40ad@.uwe...
>I am trying to encrypt more 30 chars of plaintext with TRIPLE_AES, but SQL
> gives me errors or autmatically truncates my ciphertext/plaintext to 30
> plaintext characters.
> This particular SQL Statement .
>
> UPDATE tblDB
> SET ConnectionString = EncryptByKey(Key_guid('EncyptKey'),('Example
> connection string 33333333333333333333'))
> WHERE Label = @.varLabel
> The 30 character result I get is:
> select * from tblDB
> Result - '"Example connection string 33"
> Does anyone have some suggestions? Thanks,
>
> Prem|||The connection string column is VarBinary 500
John Bell wrote:
>Hi
>How big is the connection string column?
>John
>>I am trying to encrypt more 30 chars of plaintext with AES_256, but SQL
>> gives me errors or autmatically truncates my ciphertext/plaintext to 30
>[quoted text clipped - 15 lines]
>> Prem
--
Message posted via http://www.sqlmonster.com|||Hi
This works fine!
CREATE SYMMETRIC KEY EncyptKey
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY PASSWORD = 'The quick brown fox jumps over the laxy dog'
GO
OPEN SYMMETRIC KEY EncyptKey
DECRYPTION BY PASSWORD = 'The quick brown fox jumps over the laxy dog'
GO
DECLARE @.encrypted varbinary(500)
SET @.encrypted = EncryptByKey(Key_guid('EncyptKey'),('Example connection
string 33333333333333333333'))
SELECT @.encrypted AS EncryptedValue, datalength(@.encrypted) As length,
CONVERT(varchar(100), DecryptByKey(@.encrypted))
AS 'Decrypted Value'
GO
CLOSE SYMMETRIC KEY EncyptKey
GO
DROP SYMMETRIC KEY EncyptKey
GO
How are you decrypting it?
John
"Prem via SQLMonster.com" <u33747@.uwe> wrote in message
news:7e5b1c9d8d930@.uwe...
> The connection string column is VarBinary 500
> John Bell wrote:
>>Hi
>>How big is the connection string column?
>>John
>>I am trying to encrypt more 30 chars of plaintext with AES_256, but SQL
>> gives me errors or autmatically truncates my ciphertext/plaintext to 30
>>[quoted text clipped - 15 lines]
>> Prem
> --
> Message posted via http://www.sqlmonster.com
>|||HI John
I appreciate your help.
I had to change my code like this
select convert(varchar(400), decryptbykey(test)) from dbtest
varchar(400) - did the trick. earlier i was just using varchar.
Thanks again !
John Bell wrote:
>Hi
>This works fine!
>CREATE SYMMETRIC KEY EncyptKey
>WITH ALGORITHM = TRIPLE_DES
>ENCRYPTION BY PASSWORD = 'The quick brown fox jumps over the laxy dog'
>GO
>OPEN SYMMETRIC KEY EncyptKey
>DECRYPTION BY PASSWORD = 'The quick brown fox jumps over the laxy dog'
>GO
>DECLARE @.encrypted varbinary(500)
>SET @.encrypted = EncryptByKey(Key_guid('EncyptKey'),('Example connection
>string 33333333333333333333'))
>SELECT @.encrypted AS EncryptedValue, datalength(@.encrypted) As length,
>CONVERT(varchar(100), DecryptByKey(@.encrypted))
>AS 'Decrypted Value'
>GO
>CLOSE SYMMETRIC KEY EncyptKey
>GO
>DROP SYMMETRIC KEY EncyptKey
>GO
>How are you decrypting it?
>John
>> The connection string column is VarBinary 500
>>Hi
>[quoted text clipped - 8 lines]
>> Prem
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200801/1|||Hi
I realised after I posted that the issue was probably in your verification
process and that somewhere you had not given a length to the variable you
were using and therefore it defaulted to 30 characters.
I guess from now on you will know that it is best practice to explicitly
give a length to any variable length data type when declaring or casting.
John
"Prem via SQLMonster.com" wrote:
> HI John
> I appreciate your help.
> I had to change my code like this
> select convert(varchar(400), decryptbykey(test)) from dbtest
> varchar(400) - did the trick. earlier i was just using varchar.
> Thanks again !
> John Bell wrote:
> >Hi
> >
> >This works fine!
> >CREATE SYMMETRIC KEY EncyptKey
> >
> >WITH ALGORITHM = TRIPLE_DES
> >
> >ENCRYPTION BY PASSWORD = 'The quick brown fox jumps over the laxy dog'
> >
> >GO
> >
> >OPEN SYMMETRIC KEY EncyptKey
> >
> >DECRYPTION BY PASSWORD = 'The quick brown fox jumps over the laxy dog'
> >
> >GO
> >
> >DECLARE @.encrypted varbinary(500)
> >
> >SET @.encrypted = EncryptByKey(Key_guid('EncyptKey'),('Example connection
> >string 33333333333333333333'))
> >
> >SELECT @.encrypted AS EncryptedValue, datalength(@.encrypted) As length,
> >
> >CONVERT(varchar(100), DecryptByKey(@.encrypted))
> >
> >AS 'Decrypted Value'
> >
> >GO
> >
> >CLOSE SYMMETRIC KEY EncyptKey
> >
> >GO
> >
> >DROP SYMMETRIC KEY EncyptKey
> >
> >GO
> >
> >How are you decrypting it?
> >
> >John
> >
> >> The connection string column is VarBinary 500
> >>Hi
> >[quoted text clipped - 8 lines]
> >>
> >> Prem
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200801/1
>sql

Encrytion - EncryptByKey(Key_guid('EncyptKey'),(

I am trying to encrypt more 30 chars of plaintext with TRIPLE_AES, but SQL
gives me errors or autmatically truncates my ciphertext/plaintext to 30
plaintext characters.
This particular SQL Statement .
UPDATE tblDB
SET ConnectionString = EncryptByKey(Key_guid('EncyptKey'),('Example
connection string 33333333333333333333'))
WHERE Label = @.varLabel
The 30 character result I get is:
select * from tblDB
Result - '"Example connection string 33"
Does anyone have some suggestions? Thanks,
Prem
Hi
How big is the connection string column?
John
"Prem" <u33747@.uwe> wrote in message news:7e4fff8cd40ad@.uwe...
>I am trying to encrypt more 30 chars of plaintext with TRIPLE_AES, but SQL
> gives me errors or autmatically truncates my ciphertext/plaintext to 30
> plaintext characters.
> This particular SQL Statement .
>
> UPDATE tblDB
> SET ConnectionString = EncryptByKey(Key_guid('EncyptKey'),('Example
> connection string 33333333333333333333'))
> WHERE Label = @.varLabel
> The 30 character result I get is:
> select * from tblDB
> Result - '"Example connection string 33"
> Does anyone have some suggestions? Thanks,
>
> Prem
|||The connection string column is VarBinary 500
John Bell wrote:[vbcol=seagreen]
>Hi
>How big is the connection string column?
>John
>[quoted text clipped - 15 lines]
Message posted via http://www.droptable.com
|||Hi
This works fine!
CREATE SYMMETRIC KEY EncyptKey
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY PASSWORD = 'The quick brown fox jumps over the laxy dog'
GO
OPEN SYMMETRIC KEY EncyptKey
DECRYPTION BY PASSWORD = 'The quick brown fox jumps over the laxy dog'
GO
DECLARE @.encrypted varbinary(500)
SET @.encrypted = EncryptByKey(Key_guid('EncyptKey'),('Example connection
string 33333333333333333333'))
SELECT @.encrypted AS EncryptedValue, datalength(@.encrypted) As length,
CONVERT(varchar(100), DecryptByKey(@.encrypted))
AS 'Decrypted Value'
GO
CLOSE SYMMETRIC KEY EncyptKey
GO
DROP SYMMETRIC KEY EncyptKey
GO
How are you decrypting it?
John
"Prem via droptable.com" <u33747@.uwe> wrote in message
news:7e5b1c9d8d930@.uwe...
> The connection string column is VarBinary 500
> John Bell wrote:
> --
> Message posted via http://www.droptable.com
>
|||HI John
I appreciate your help.
I had to change my code like this
select convert(varchar(400), decryptbykey(test)) from dbtest
varchar(400) - did the trick. earlier i was just using varchar.
Thanks again !
John Bell wrote:[vbcol=seagreen]
>Hi
>This works fine!
>CREATE SYMMETRIC KEY EncyptKey
>WITH ALGORITHM = TRIPLE_DES
>ENCRYPTION BY PASSWORD = 'The quick brown fox jumps over the laxy dog'
>GO
>OPEN SYMMETRIC KEY EncyptKey
>DECRYPTION BY PASSWORD = 'The quick brown fox jumps over the laxy dog'
>GO
>DECLARE @.encrypted varbinary(500)
>SET @.encrypted = EncryptByKey(Key_guid('EncyptKey'),('Example connection
>string 33333333333333333333'))
>SELECT @.encrypted AS EncryptedValue, datalength(@.encrypted) As length,
>CONVERT(varchar(100), DecryptByKey(@.encrypted))
>AS 'Decrypted Value'
>GO
>CLOSE SYMMETRIC KEY EncyptKey
>GO
>DROP SYMMETRIC KEY EncyptKey
>GO
>How are you decrypting it?
>John
>[quoted text clipped - 8 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200801/1
|||Hi
I realised after I posted that the issue was probably in your verification
process and that somewhere you had not given a length to the variable you
were using and therefore it defaulted to 30 characters.
I guess from now on you will know that it is best practice to explicitly
give a length to any variable length data type when declaring or casting.
John
"Prem via droptable.com" wrote:

> HI John
> I appreciate your help.
> I had to change my code like this
> select convert(varchar(400), decryptbykey(test)) from dbtest
> varchar(400) - did the trick. earlier i was just using varchar.
> Thanks again !
> John Bell wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200801/1
>

Encryption; SQL Server 2005 & Windows 2003 Server

Issue(s):
1. We *must* encrypt all data items in a Database.
2. SQL Server 2005 Encryption places a tremendous burden on the
system as a whole when many columns are encrypted, especially
columns involved in indexes. Response time(s) are unbearable.
Proposed Solution:
A. Create/Encrypt the *.mdf & *.ldf files as Windows 2003 Server
Encrypted files thereby using the CrytoAPI at the file-level rather
than through SQL Server wrappers etc.
B. As the Key Owner/Windows User of the Encrypted files of "A."
above, attach to the files in SQL Server 2005/2000.
C. The availability of individual Column Privileges is handled by
conventional SQL Server privileges.
Observations (based on actual Testing):
I. The Database application runs fine and response times are
very good.
II. Database changes can be made as though no encryption were
done. This includes the creation of ad hoc tables etc.
III. Current an furture Applications need not be modified to OPEN
SYMMETRIC KEY(s) or access hashed values to speed Queries.
QUESTIONS:
* Has Microsoft tested this approach to encrypting the entire
Database ?
* Is there any collateral experience or other Developers who have
such a configuration in Production ?
* Are there any foreseeable issues with this approach?
Thank you (in advance)I am using SQL2000 and SQL2005 and have all database files on an encrypted
partion. Encryption is done with TrueCrypt and i never had any problem
with this solution.
But that is only for securing the data if somebody will steal the computer.
It does not help if somebody hacks the database server. For this you need
the built in encryption from SQL-Server, where you need the password in the
query, even if you logged in as admin and have all rights.
With built in encrytion it is possible, that two persons with different
passphrases see different parts of the data, even if they use the same
query on the same column.
So encryption of the filesystem and encryption of data in the database are
different things.
bye, Helmut|||Thank you Helmut.
Our primary Data Security concerns are, indeed:
* intentional theft of the System(s) or, specifically, the Hard
Drives.
* unintentional loss/natural breach of physical security
e.g. earthquake, hurricane, terrorism ...
We are unconcerned about :
* the Database Administrator (in the serveradmin role) being
able to view unencrypted data.
* having many levels of granularity of Security within a given
Database where different Users/Roles see differing sets of
Confidential Data depending on their particular need-to-know.
We prefer to use current Vendors for the solution since the Client
has a rather complex and time-consuming procurement process.
Regards ...
"Helmut Woess" wrote:

> I am using SQL2000 and SQL2005 and have all database files on an encrypted
> partion. Encryption is done with TrueCrypt and i never had any problem
> with this solution.
> But that is only for securing the data if somebody will steal the computer
.
> It does not help if somebody hacks the database server. For this you need
> the built in encryption from SQL-Server, where you need the password in th
e
> query, even if you logged in as admin and have all rights.
> With built in encrytion it is possible, that two persons with different
> passphrases see different parts of the data, even if they use the same
> query on the same column.
> So encryption of the filesystem and encryption of data in the database are
> different things.
> bye, Helmut
>|||"ITContractor" <ITContractor@.discussions.microsoft.com> wrote in message
news:EA318A7B-AD03-4E9C-891E-495EE1BA0B49@.microsoft.com...
> Issue(s):
> 1. We *must* encrypt all data items in a Database.
> 2. SQL Server 2005 Encryption places a tremendous burden on the
> system as a whole when many columns are encrypted, especially
> columns involved in indexes. Response time(s) are unbearable.
> Proposed Solution:
> A. Create/Encrypt the *.mdf & *.ldf files as Windows 2003 Server
> Encrypted files thereby using the CrytoAPI at the file-level rather
> than through SQL Server wrappers etc.
> B. As the Key Owner/Windows User of the Encrypted files of "A."
> above, attach to the files in SQL Server 2005/2000.
> C. The availability of individual Column Privileges is handled by
> conventional SQL Server privileges.
> Observations (based on actual Testing):
> I. The Database application runs fine and response times are
> very good.
> II. Database changes can be made as though no encryption were
> done. This includes the creation of ad hoc tables etc.
> III. Current an furture Applications need not be modified to OPEN
> SYMMETRIC KEY(s) or access hashed values to speed Queries.
> QUESTIONS:
> * Has Microsoft tested this approach to encrypting the entire
> Database ?
Yes. Putting SQL Databases on an Encrypted File System is supported.

> * Is there any collateral experience or other Developers who have
> such a configuration in Production ?
> * Are there any foreseeable issues with this approach?
>
Expect physical IO performance to be really bad. Plan around that by
providing plenty of RAM and load-testing your application.
David|||Thank you David,
I find your response incredibly interesting as I assume your corporation
has some first-hand experience as well as feedback from Microsoft Corp.
with the approach I am describing. My bosses are a big City Department
and have many Applications' Databases they would like to protect
against accidental/intentional exposure e.g. System/HDD theft or
loss or physical exposure due to natural or other unsavory causes.
At the risk of over-stepping my bounds, any further information you are
at liberty to share as:
1. Types of applications (high xactoin telecomm, DSS, low xactoin
record-keeping) 2. DB Size (our DB's will be 3Gb to 600Gb depending on the
App.)
3. Production environment (SAN, Cluster, Stand-alone)
In any event, many thanks for taking the time to respond.
========================================
===========
> "ITContractor" <ITContractor@.discussions.microsoft.com> wrote in message
> news:EA318A7B-AD03-4E9C-891E-495EE1BA0B49@.microsoft.com...
> Yes. Putting SQL Databases on an Encrypted File System is supported.
>
> Expect physical IO performance to be really bad. Plan around that by
> providing plenty of RAM and load-testing your application.
> David

Encryption with SSAS 2005

Is there a way to encrypt data in SSAS in a similar fashion to the built in encryption provided for a relational database in SQL Server 2005 (SMK to encrypt the DMK, which can then be used to create sym/asym keys to encrypt specific columns)?

If so, how do I create a DSV in SSAS that is based on a table(s) which has encrypted data? Also, how do I encrypt specific fact tables in my cube in SSAS?

Analysis Services does not support encryption of it's data.

As for creating cubes based on the encrypted data within SQL Server, that should not be different from any other application reading encypted data.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

sql

Encryption SQL 2005

I have a desire to encrypt an entire database rather than utilizing TSQL to encrypt individual columns. Outside the SQL Server authentication and access should function as normal.

Reason: avoid customization and change to a vendor applicaiton, and satisfying the group security ghouls by being able to state definatively that the data within the database is encrypted.

The database is small as it contains only financial statement data, so performance should not be an issue.

You may gain some insight from this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1784536&SiteID=1

Encryption question, sql 2000

Situation: We are required to encrypt procs in part of our production
environment. We have the
unencrypted procs in test environment. As a sanity check (when a problem
arises), is there a way
to verify that an encrypted proc (ctext) in production matches a specific
unencrypted version?
If the procs were first encrypted with Alter Proc ... With Encryption, then
the same script can be
run again, and before/after syscomments.ctext values compared. But this is
a destructive test
that potentially changes prod environment.
Is there some benign alternative?
Thanks,
Craig Hessel
WPS, Madison, WICraig,
Two possibilities:
1) Restore db to development machine. Unencrypt stored procs there and check them
( unencryption algo is straightforward but destructive )
2) Check the length of the procedures in syscomments - most edits will change this
Regards
AJ
"Craig Hessel" <craig_hessel@.hotmail.com> wrote in message news:10181dttf4ta204@.corp.supernews.com...
> Situation: We are required to encrypt procs in part of our production
> environment. We have the
> unencrypted procs in test environment. As a sanity check (when a problem
> arises), is there a way
> to verify that an encrypted proc (ctext) in production matches a specific
> unencrypted version?
> If the procs were first encrypted with Alter Proc ... With Encryption, then
> the same script can be
> run again, and before/after syscomments.ctext values compared. But this is
> a destructive test
> that potentially changes prod environment.
> Is there some benign alternative?
> Thanks,
> Craig Hessel
> WPS, Madison, WI
>sql

Encryption question, sql 2000

Situation: We are required to encrypt procs in part of our production
environment. We have the
unencrypted procs in test environment. As a sanity check (when a problem
arises), is there a way
to verify that an encrypted proc (ctext) in production matches a specific
unencrypted version?
If the procs were first encrypted with Alter Proc ... With Encryption, then
the same script can be
run again, and before/after syscomments.ctext values compared. But this is
a destructive test
that potentially changes prod environment.
Is there some benign alternative?
Thanks,
Craig Hessel
WPS, Madison, WICraig,
Two possibilities:
1) Restore db to development machine. Unencrypt stored procs there and chec
k them
( unencryption algo is straightforward but destructive )
2) Check the length of the procedures in syscomments - most edits will chang
e this
Regards
AJ
"Craig Hessel" <craig_hessel@.hotmail.com> wrote in message news:10181dttf4ta204@.corp.supernews.com
..

quote:

---

> Situation: We are required to encrypt procs in part of our production
> environment. We have the
> unencrypted procs in test environment. As a sanity check (when a problem
> arises), is there a way
> to verify that an encrypted proc (ctext) in production matches a specific
> unencrypted version?
> If the procs were first encrypted with Alter Proc ... With Encryption, the
n
> the same script can be
> run again, and before/after syscomments.ctext values compared. But this i
s
> a destructive test
> that potentially changes prod environment.
> Is there some benign alternative?
> Thanks,
> Craig Hessel
> WPS, Madison, WI
>

---

Encryption Question - Urgent!

Hi,

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

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

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'pwd';

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;

CLOSE MASTER KEY;

Thanks.!

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

Thanks
Laurentiu

|||

Here are the details:

In DB1

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

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

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

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

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

INSERT INTO DB2.dbo.table1(enc_column)

SELECT x.enc_column

FROM DB1.dbo.table1 x

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

|||

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

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

Thanks
Laurentiu

|||

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

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

Thanks
Laurentiu

Encryption problem

I use keyword WITh ENCRYPTION to encrypt my procedures that other users can'
see them.
For example:
CREATE PROCEDURE test
WITH ENCRYPTION
AS
select 1
GO
If I click on this procedure in enterprise manager, I get message:
Error 20585:[SQL-DMO]/*****Encrypted object is not transferable,and script
can not be generated.*****/
Everything is fine so far.
But if I go to master database I can find the sintax of this procedure.
For example, if I use RedGate SQL bundle to compare 2 databases, I can see
the sintax of all procedures on some database even if they are encrypted.
Is there any way to prevent other users to see my code?
If I sell my program with my database to other company, every one who has
access to their database, can see my code and still it.
Any suggestion?
Thank you,
SimonThe WITH ENCRYPTION option only provides a trivial level of security. You ma
y
find other third-party tools that claim to do better but ultimately you
cannot stop an administrator using SQL Profiler to examine your code.
IMO you should not try to hide code in this way. Admins may have a
legitimate need to see what code is running. The best protection for your
intellectual property is a licence agreement, not phoney and obstructive
encryption. Just my opinion.
David Portas
SQL Server MVP
--|||Simon even with encryption there's plenty of tools on the web that people ca
n
use to decrypt it. All that I can say is implement physical security - ie.
Memory stick
and get your DB to sort out security issues. If you have an extra hour a day
for admin you can always use sourcesafe.
As for the code sitting in the database, your never safe. There's no IP
(Intellectual Property ) when you are working for a company, they pay you
thus they own everything you create during work hours.
You can write manual encryptions by putting your code in extended procs, and
atleast like this you remove the google junkies from your code. Just google
"sql decryption tools" .
With dlls it's a bit more difficult to see the code, although someone can
still just catch the code through profiler...
No that I think of it, if you don't have the DBA on your side your "￡$*￡$
"simon" wrote:

> I use keyword WITh ENCRYPTION to encrypt my procedures that other users ca
n'
> see them.
> For example:
> CREATE PROCEDURE test
> WITH ENCRYPTION
> AS
> select 1
> GO
>
> If I click on this procedure in enterprise manager, I get message:
> Error 20585:[SQL-DMO]/*****Encrypted object is not transferable,and script
> can not be generated.*****/
> Everything is fine so far.
> But if I go to master database I can find the sintax of this procedure.
> For example, if I use RedGate SQL bundle to compare 2 databases, I can see
> the sintax of all procedures on some database even if they are encrypted.
> Is there any way to prevent other users to see my code?
> If I sell my program with my database to other company, every one who has
> access to their database, can see my code and still it.
> Any suggestion?
> Thank you,
> Simon
>
>|||The text of encrypted procedures does not show up in SQL Profiler. That's
one of the reasons I dislike encryption, because it make debugging and
performance tuning a real hassle. But I agree with the rest of your post.
Jacco Schalkwijk
SQL Server MVP
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:C451F175-1594-4DBC-AE84-24779EC8ABFF@.microsoft.com...
> The WITH ENCRYPTION option only provides a trivial level of security. You
> may
> find other third-party tools that claim to do better but ultimately you
> cannot stop an administrator using SQL Profiler to examine your code.
> IMO you should not try to hide code in this way. Admins may have a
> legitimate need to see what code is running. The best protection for your
> intellectual property is a licence agreement, not phoney and obstructive
> encryption. Just my opinion.
> --
> David Portas
> SQL Server MVP
> --
>|||Thanks for the correction Jacco. Actually I've not even tried Profiler with
encryption -I just unwisely made an assumption. Sorry Simon. Another reason
to avoid encryption though.
David Portas
SQL Server MVP
--|||just an addition, it'd show up as *encrypted* only after you have sp3
installed. so, you'd see the entire text if you're running earlier bits.
-oj
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23blcesxSFHA.3636@.TK2MSFTNGP14.phx.gbl...
> The text of encrypted procedures does not show up in SQL Profiler. That's
> one of the reasons I dislike encryption, because it make debugging and
> performance tuning a real hassle. But I agree with the rest of your post.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:C451F175-1594-4DBC-AE84-24779EC8ABFF@.microsoft.com...
>|||>> No that I think of it, if you don't have the DBA on your side your "$*$
What do you mean by this?
Thanks
"Mal .mullerjannie@.hotmail.com>" <<removethis> wrote in message
news:99588A6B-5C8A-4A2A-B64C-65A5139EDD1A@.microsoft.com...
> Simon even with encryption there's plenty of tools on the web that people
> can
> use to decrypt it. All that I can say is implement physical security - ie.
> Memory stick
> and get your DB to sort out security issues. If you have an extra hour a
> day
> for admin you can always use sourcesafe.
> As for the code sitting in the database, your never safe. There's no IP
> (Intellectual Property ) when you are working for a company, they pay you
> thus they own everything you create during work hours.
> You can write manual encryptions by putting your code in extended procs,
> and
> atleast like this you remove the google junkies from your code. Just
> google
> "sql decryption tools" .
> With dlls it's a bit more difficult to see the code, although someone can
> still just catch the code through profiler...
> No that I think of it, if you don't have the DBA on your side your "$*$
> "simon" wrote:
>sql

Encryption Problem

I am Using SQl 2000. I use Vb as front end. I am using a simple encryption logic to encrypt password and save it in the Table. Both the encrypt and decrypt functions i have written in VB. The password is saved in the encrypt form in the table. The problem is when i decrypt the password it decrypts wrongly in some cases. In most of the cases if the password is alpha type , the decryption is right but if i use numeric passwords, while decrypting it gives different values. The ascii values are same. Is it because of SQL collation type in the Table Latin1_General_BIN

The code used for encrypting and decrypting is as given below

------
' FUNCTION : EncryptWord
' Purpose : Encrypts the Password depending on the first
' character of the Login Name
' Input : The Login Name , The Password
' Output : The encrypted password
'------------
Public Function EncryptWord(ByVal argLoginName As String, ByVal argPassword As String) As String
Dim strEncWord As String
Dim cntr As Byte
Dim strLoginName As String
Dim strPassword As String
strLoginName = Trim$(argLoginName)
strPassword = Trim$(argPassword)
If Len(strPassword) = 0 Then Exit Function
For cntr = 1 To Len(strPassword)
strEncWord = strEncWord & Chr(Abs(Asc(Mid(strPassword, cntr, 1)) + Asc(Left(strLoginName, 1)) + cntr))
Next cntr

EncryptWord = Trim$(strEncWord)
End Function

'----------------------
' FUNCTION : DecryptWord
' Purpose : Decrypts the Password depending on the first
' character of the Login Name
' Input : The Login Name , The Password
' Output : The Decrypted password
'----------------------

Public Function DecryptWord(ByVal argLoginName As String, ByVal argPassword As String) As String
On Error Resume Next
Dim strEncWord As String
Dim cntr As Byte
Dim strLoginName As String
Dim strPassword As String
strLoginName = Trim$(argLoginName)
strPassword = Trim$(argPassword)
If Len(strPassword) = 0 Then Exit Function
For cntr = 1 To Len(strPassword)
Debug.Print Abs(Asc(Mid(strPassword, cntr, 1)))
strEncWord = strEncWord & Chr(Abs(Asc(Mid(strPassword, cntr, 1)) - Asc(Left(strLoginName, 1)) - cntr))

Next cntr
DecryptWord = Trim$(strEncWord)
End FunctionGenerally, for passwords, it is unnecessary to ever decrypt the password.

The usual technique is to store the encrytped password in the database, and then when someone logs in the password they supply is encrypted using the same algorithm and compared to the stored value.

These are known as one-way encryption schemes, and because they do not ever need to be unencrypted they can be very secure. I have a one-way encryption method configured as a Function if you are interested.|||I would appreciate the one way encryption function.|||Here is the function. Store your encrypted passwords as 10 character strings. When someone logs in, use the same function to encrypt the password they supply, and the result should match the value associated with their login.|||The performance of this Forum is really starting to suck.

Here is the function code. For some reason, I can't get the file uploaded.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Encrypt_Password]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[Encrypt_Password]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE FUNCTION [dbo].[Encrypt_Password]
(@.RawPassword varchar(20))
Returns varchar(20)
as
BEGIN
--Function dbo.EncryptPassword
--Bruce Lindman, 11/19/2002
--
--This function returns a 20 character encryption string derived from a supplied password.
--It uses a non-linear deterministic number generation algorithm known as the Linear Congruential Method
--to generate pseudo-random numbers from the Ascii values of the password characters, and these
--random numbers are then converted back into Ascii characters to form the the encrypted string.
--Because the algorithm is non-linear and uses the password itself as the initial key value, it should be
--practically impossible to reverse engineer the process.
--These variables used for testing
--declare @.RawPassword varchar(20)
--set @.RawPassword = 'Pa$$w0rD'
--set @.RawPassword = '!!!!!!!!!!' --A low ascii value password
--set @.RawPassword = '' --A high ascii value password
declare @.counter int --we'll use this to step through the password character by character
declare @.seed decimal(10, 9) --The derived seed value for the random number generator
declare @.EncryptedPassword varchar(20)
set @.EncryptedPassword = ''
declare @.Modulo int --The divisor in the random number generator
set @.Modulo = 100000000
declare @.Multiplier int --The multiplier in the random number generator
declare @.AsciiValue numeric
--Extend the password to 20 characters by repeating it, separated by the character x
--The x character ensures that password ABC does not return the same value when doubled,
--as ABCABC, but passwords that are doubled with a padded x character will return the same
--encrypted value. ABC returns the same value as ABCxABC or ABCxABCxABC.
while datalength(@.RawPassword) < 20
begin
set @.RawPassword = @.RawPassword + 'x' + @.RawPassword
end
--I think it is unavoidable that for any function F() there exists a pair of values A, B such
--that F(A) = F(B).
--Derive the seed value for the random number function from the password itself
set @.counter = 0
set @.seed = 1
while @.counter < datalength(@.RawPassword)
begin
set @.counter = @.counter + 1
--Use the ascii value of each character to revise the seed value
set @.AsciiValue = ascii(substring(@.RawPassword, @.Counter, 1))
set @.seed = @.seed * (@.AsciiValue/1000)
--We don't want any leading zeros in our decimal value, or the seed may get too small
while @.seed < 0.1 set @.seed = @.seed * 10
end
--We'll derive the multiplier from the seed value, following the principle that a good multiplier
--should be 1 digit less than the Modulo, and should follow the pattern ...x21 where x is an even number
set @.Multiplier = round(@.seed * @.Modulo/100, 0) * 200 + 21
--Now encrypt the password
set @.counter = 0
while @.counter < datalength(@.RawPassword)
begin
set @.counter = @.counter + 1
set @.AsciiValue = ascii(substring(@.RawPassword, @.Counter, 1))
--This next statement is the guts of the random number generator
--It creates a new seed value between 0 and 1
set @.seed = cast(cast(1 + (@.seed + @.AsciiValue/1000) * @.Multiplier * @.Modulo as bigint) % @.Modulo as numeric)/@.Modulo
--Now use the first three digits of the seed value to lookup an ascii character between 1 and 255 and append it to the encrypted password
set @.EncryptedPassword = @.EncryptedPassword + char(1 + cast(round(@.seed * 1000, 0) as int) % 254)
end
Return @.EncryptedPassword
end|||OK, lets try it as a text file. (Why a database forum won't accept files with an sql extension, I have no idea.)|||Thanks for the Function

Encryption Performance

Hi

I am trying to encrypt data using a symmetric key which is encrypted by certificate. I do not want grant control on these objects to the users who wants to decrypt this data. Instead I have created a udf with execute context as "dbo" and used DecryptByKeyAutoCert built-in function.

Now this works fine but large data operations this is extremely slow. It takes around 10 minutes to select decrypted data whic in comparision takes 11 seconds when DecryptByKey function is used.

But I am not sure when DecryptByKey is used, whether the symmetric key is decrypted by the private key of the certificate or not. Can somebody give some explanation of this ?

Also, I can not have a UDF with these following steps

1. Open symmetric key

2. Convert secretdata using DecryptByKey

3. Close Symmetric Key.

4. return decrypted value.

Can some one give some insights on this ?

Can you show the way you call the DecryptByKeyAutoCert and DecryptByKey builtins? Also, how much data are you decrypting - what are the number of rows you select and the size of encrypted data per row?

You cannot create a function to decrypt, but you can create a procedure to decrypt. For example, see the procedure from http://blogs.msdn.com/lcris/archive/2006/01/13/512829.aspx.

Thanks
Laurentiu

|||

Number of rows that I am decrypting is 10000. The record size is 516 bytes

DecryptByKey code:

OPEN SYMMETRIC KEY [Cert_Account_Data_Key] DECRYPTION by certificate [cert_Account_Data]

-- Account table has 10000 records

select account_id,

convert( nvarchar(100), decryptbykey(account_number)) as 'Decrypted Account Number',

convert( nvarchar(100), decryptbykey(account_ssn)) as 'Decrypted Account SSN'

from account_t

CLOSE SYMMETRIC KEY [Cert_Account_Data_Key]

DecryptByCert code:

-- 1. create udf

CREATE FUNCTION [dbo].[udf_Decrypt_Account_Data] (@.Secret_Data VARBINARY(256)) returns nvarchar(100)

WITH EXECUTE AS 'DBO'

AS

begin

-- This return decrypted value for the input data using Account Data

return convert( nvarchar(100), decryptbykeyautocert( cert_id( 'cert_Account_Data' ), null, @.Secret_Data))

end

-- selects decrypted data using Account decryption function

select ACCOUNT_ID,

dbo.udf_Decrypt_Account_Data (ACCOUNT_NUMBER) as 'Decrypted Account Number',

dbo.udf_Decrypt_Account_Data (ACCOUNT_SSN) as 'Decrypted Account SSN'

from ACCOUNT_T

thanks

satya

|||

Using decryptbykeyautocert like this will give abysmal performance. The reason for this is that decryptbykeyautocert is efficient if you use it in a query - it will decrypt the key once and it will keep it open for the duration of the query. By putting the builtin call within a function and calling the function from a query, you are basically forcing the builtin to reopen the key each time the function is called - twice per row in your case, and this represents significant overhead.

You don't need to give CONTROL on the encryption key to a user, for him to be able to use it. It is sufficient to grant him VIEW DEFINITION and add another encryption to the key so that the user can access the key through the new encryption. You can add, for example, another certificate encryption using one of the user's certificates. Then the user will be able to just call decryptbykeyautocert directly, instead of this function, and the query will execute much faster.

Thanks
Laurentiu

Encryption Password

Hai everybody
i have an question,
if we use mysql, we can encrypt our password using md5() function,
other wise, i want to encrypt my password in sql server 2000, can anyone tell me, what function i must use, and how to use it?

Thanks for all...

Quote:

Originally Posted by xpcer

Hai everybody
i have an question,
if we use mysql, we can encrypt our password using md5() function,
other wise, i want to encrypt my password in sql server 2000, can anyone tell me, what function i must use, and how to use it?

Thanks for all...

I use an Encripta stored procedure in the database that u want encrypted passwords to be in, it runs off 2 Extended stored procedures in master database, and those 2 stored procedures run off a .dll file in Program Files\Microsoft SQL Server\MSSQL\Binn

If you want the files/querys to make this happen, send me a private message :)

Encryption on data fields

Dear all,
I wonder if there is any encrypting function that I can use to encrypt the
data on the fields I want. Can anyone advise?
Thanks.
IvanNot built-in. Either do it in the client application, or wait two months for
SQL Server 2005.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ivan" <Ivan@.discussions.microsoft.com> wrote in message
news:228E7755-733D-4000-BE6E-8FAF788C79F8@.microsoft.com...
> Dear all,
> I wonder if there is any encrypting function that I can use to encrypt the
> data on the fields I want. Can anyone advise?
> Thanks.
> Ivan|||Hi Tibor
> Not built-in. Either do it in the client application, or wait two months
> for SQL Server 2005.
It should be november 7, should not it?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23$Kszx0wFHA.612@.TK2MSFTNGP10.phx.gbl...
> Not built-in. Either do it in the client application, or wait two months
> for SQL Server 2005.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ivan" <Ivan@.discussions.microsoft.com> wrote in message
> news:228E7755-733D-4000-BE6E-8FAF788C79F8@.microsoft.com...
>|||Yep, that's the plan. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:uEhCrA1wFHA.2656@.TK2MSFTNGP09.phx.gbl
..
> Hi Tibor
> It should be november 7, should not it?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:%23$Kszx0wFHA.612@.TK2MSFTNGP10.phx.gbl...
>

Encryption on 1 field

Is there a way to encrypt just one field in a table? If not I guess I could
use MD5. Can anybody make a suggestion on the best way to do this?
Hi
As I undestood you would like to store the passwords, am I right?
As far as I know you cannot encrypt only one field and regarding to storing
the passwords do search on internet ,there are posts (examples) by Steve
Kass how to encrypt and store them.
"DAVID S" <DAVIDS@.discussions.microsoft.com> wrote in message
news:F26C9AE8-B6A9-4EDD-8129-1A2136C8F8F8@.microsoft.com...
> Is there a way to encrypt just one field in a table? If not I guess I
could
> use MD5. Can anybody make a suggestion on the best way to do this?
|||> Is there a way to encrypt just one field in a table? If not I guess I
could
> use MD5. Can anybody make a suggestion on the best way to do this?
You can do it from clint app, or you can use 3rd party tools - check
http://www.activecrypt.com/products.html, for example.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Thanks for the input, I didn't think you could but it never hurts to ask.
What I want to do is encrypt credit card numbers.
"DAVID S" wrote:

> Is there a way to encrypt just one field in a table? If not I guess I could
> use MD5. Can anybody make a suggestion on the best way to do this?
|||Thank you so much, I will check on this software.
"Dejan Sarka" wrote:

> could
> You can do it from clint app, or you can use 3rd party tools - check
> http://www.activecrypt.com/products.html, for example.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
>
|||Try the sofware xp_crypto 2.5
search for xp_crypto 2.5 on google
it worked in my case
though besides it i also have udf_encrypt udf_decrypt for my database
"DAVID S" <DAVIDS@.discussions.microsoft.com> wrote in message
news:F26C9AE8-B6A9-4EDD-8129-1A2136C8F8F8@.microsoft.com...
> Is there a way to encrypt just one field in a table? If not I guess I
could
> use MD5. Can anybody make a suggestion on the best way to do this?

Encryption on 1 field

Is there a way to encrypt just one field in a table? If not I guess I could
use MD5. Can anybody make a suggestion on the best way to do this?Hi
As I undestood you would like to store the passwords, am I right?
As far as I know you cannot encrypt only one field and regarding to storing
the passwords do search on internet ,there are posts (examples) by Steve
Kass how to encrypt and store them.
"DAVID S" <DAVIDS@.discussions.microsoft.com> wrote in message
news:F26C9AE8-B6A9-4EDD-8129-1A2136C8F8F8@.microsoft.com...
> Is there a way to encrypt just one field in a table? If not I guess I
could
> use MD5. Can anybody make a suggestion on the best way to do this?|||> Is there a way to encrypt just one field in a table? If not I guess I
could
> use MD5. Can anybody make a suggestion on the best way to do this?
You can do it from clint app, or you can use 3rd party tools - check
http://www.activecrypt.com/products.html, for example.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Thanks for the input, I didn't think you could but it never hurts to ask.
What I want to do is encrypt credit card numbers.
"DAVID S" wrote:

> Is there a way to encrypt just one field in a table? If not I guess I coul
d
> use MD5. Can anybody make a suggestion on the best way to do this?|||Thank you so much, I will check on this software.
"Dejan Sarka" wrote:

> could
> You can do it from clint app, or you can use 3rd party tools - check
> http://www.activecrypt.com/products.html, for example.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
>|||Try the sofware xp_crypto 2.5
search for xp_crypto 2.5 on google
it worked in my case
though besides it i also have udf_encrypt udf_decrypt for my database
"DAVID S" <DAVIDS@.discussions.microsoft.com> wrote in message
news:F26C9AE8-B6A9-4EDD-8129-1A2136C8F8F8@.microsoft.com...
> Is there a way to encrypt just one field in a table? If not I guess I
could
> use MD5. Can anybody make a suggestion on the best way to do this?

Encryption on 1 field

Is there a way to encrypt just one field in a table? If not I guess I could
use MD5. Can anybody make a suggestion on the best way to do this?Hi
As I undestood you would like to store the passwords, am I right?
As far as I know you cannot encrypt only one field and regarding to storing
the passwords do search on internet ,there are posts (examples) by Steve
Kass how to encrypt and store them.
"DAVID S" <DAVIDS@.discussions.microsoft.com> wrote in message
news:F26C9AE8-B6A9-4EDD-8129-1A2136C8F8F8@.microsoft.com...
> Is there a way to encrypt just one field in a table? If not I guess I
could
> use MD5. Can anybody make a suggestion on the best way to do this?|||> Is there a way to encrypt just one field in a table? If not I guess I
could
> use MD5. Can anybody make a suggestion on the best way to do this?
You can do it from clint app, or you can use 3rd party tools - check
http://www.activecrypt.com/products.html, for example.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Thanks for the input, I didn't think you could but it never hurts to ask.
What I want to do is encrypt credit card numbers.
"DAVID S" wrote:
> Is there a way to encrypt just one field in a table? If not I guess I could
> use MD5. Can anybody make a suggestion on the best way to do this?|||Thank you so much, I will check on this software.
"Dejan Sarka" wrote:
> > Is there a way to encrypt just one field in a table? If not I guess I
> could
> > use MD5. Can anybody make a suggestion on the best way to do this?
> You can do it from clint app, or you can use 3rd party tools - check
> http://www.activecrypt.com/products.html, for example.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
>|||Try the sofware xp_crypto 2.5
search for xp_crypto 2.5 on google
it worked in my case
though besides it i also have udf_encrypt udf_decrypt for my database
"DAVID S" <DAVIDS@.discussions.microsoft.com> wrote in message
news:F26C9AE8-B6A9-4EDD-8129-1A2136C8F8F8@.microsoft.com...
> Is there a way to encrypt just one field in a table? If not I guess I
could
> use MD5. Can anybody make a suggestion on the best way to do this?sql

Encryption of trasaction logs

Is it possible to encrypt the transaction log during log shipping? How?

While there is no explicit log encryption in SQL Server 2005, any entries that are encrypted will be treated as binary data so it will remain encrypted during log shipping as well.

We do plan on offering enhanced encryption features for SQL Server 2008, we will post any further info as soon as they are publicly available.

Hope this helps, please let us know if you have any further questions.

Sung

|||is there any third party utilites that support encryption that are supported by Microsoft ?|||

Hi,

I'm not currently aware of any third party utilities that are supported by Microsoft. We may have a few partners that we perhaps recommend. I am currently checking up on this and will post any info that I find.

UPDATE: While we don't have any specific recommedations in this space, we do have a large number of third parties who have developed solutions in this area. They would be the ones who would directly support any solutions that they may have. Please contact software vendors to discuss any options that they may have or perhaps may provide.

Thanks,

Sung