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 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 Keys (Reporting Services Configuration)

Ok I am playing around with SQL Server 2005 got it all setup. Then I decided to change the admin password and run dcpromo. Now I got all the issues resolved except this one. It had a key from what the server use to be called. I deleted it to make a new key for the state of the server now. Well big mistake I am finding out. Below are the only real directions I have found to recreate the key and it is not a lot of help. Does anyone know how to do this? Not like SQL Server 2000 to many choices and I got click happy on over load. Thanks in advance...

SQL Server Setup Help

Encryption Keys (Reporting Services Configuration)
Delete
Deletes the symmetric key and all encrypted content, including connection strings and stored credentials. You should only delete the symmetric key if you cannot restore it.

Once you delete the symmetric key, you must re-enter the missing connection strings and stored credentials in the reports and shared data sources that no longer have these values. You must also update all subscriptions that use delivery extensions that store encrypted data. This includes the file share delivery extension and any third-party delivery extension that use encrypted value.

There is no automated way to update this information. Each report, subscription, and shared data source that uses stored credentials and connection strings must be updated one at a time.

Initialization (Reporting Services Configuration)
Remove
Click Remove to remove the encryption keys of the selected report server instance from the report server database. You can remove keys to remove a report server from a scale-out deployment. With this option, only the encryption keys for the specified report server instance are removed. Encrypted data in the report server database is not affected.

As a precaution, be sure to create a backup copy of the symmetric key before you remove it. Once you remove the encryption keys of the last report server in the list, you introduce new requirements for any subsequent report server initialization for that database. The new requirement is that after you initialize a report server you must restore a backup copy of the symmetric key. Restoring the symmetric key is necessary if you want to access the encrypted data that is currently in the report server database.

If you no longer need the encrypted data or if you do not have a backup copy of the key, you must delete the encrypted data. For more information, see Encryption Keys (Reporting Services Configuration).

Event Viewer Log

Event Type: Error
Event Source: Report Server Windows Service (MSSQLSERVER)
Event Category: Management
Event ID: 107
Date: 2/9/2006
Time: 11:13:32 PM
User: N/A
Computer: VM-IPDG3
Description:
Report Server Windows Service (MSSQLSERVER) cannot connect to the report server database.

If you don't have a backup of your key and you've deleted it, then the only thing you could do is to create a new key. From the "Encryption keys" panel in the config tool, click "change". By now you've lost all secure information, so you should reenter them. You need to redo the "Database setup" panel, and the credentials for data sources.|||

James Wu

Thanks for the reply I will try this when I get home. On the "Encyption Keys" panel in the configure reporting sections if memory serves me. The Change was disabled and the only two buttons out of the four I could click were Restore and Delete. The Change and Backup buttons were disabled. Can you supply a step by step to get where you are talking about.

I know if you go into

Microsoft SQL Server 2005|||

Yes your are right. "Change" and "Backup" will be disabled if your report server is not initialized. Here is a more detailed article on how to delete and recreate encryption keys :) http://msdn2.microsoft.com/en-us/library/ms156010.aspx

|||

James Wu

Thanks for the link I will research it now. I am using vmware so I reverted back a snapshot and started over. Made a few dumb errors that I should have been smarter not to do. Thanks Again

Encryption in SQL Server2000/VB

Hi,

I have an application in SQL Server 2000 and VB as the front end. We want to encrypt the password for connecting to the database so that even the programmers will not be able to see it. Only administrator should know the password. A common db account will be used for connecting to the database. This password needs to be encrypted.
Encryption either in VB or SQL server 2000 is fine.

Is there a way? Thanks in advance.

RajI'm asuming u meant the password written in your codes.

err I'm guessing...compile your connection parameter coding into a object file and call it in your vb?

I know a way to encrypt ASP,vbscript thought, using Windows Script Encoder. You can find it in unser MSDN search.

It not fool proof, but then again, I think it only encrypts script files.|||use VB to encypt...and have a dll to do this encryption and decryption...and embed this logic of encryption and put the password as well into this dll. Actually what i have done is, i have a file name config.txt which this dll accesses and retrives the password..and this config.txt is encrypted. And when user/developer put his password in the login dialog or any connection string...it will be first sent to that dll and it encrypts that password and compares against the encrypted password in the file.|||I have an easier solution. I developed an encryption function, and put them into an DLL. There is an "admin" executable, which shows the result of encryption, and I used the result as the real DB password.

This DLL isn't availabe to the developer directly, but is used in the middle tier, which receives the original password from the developer, and connects to the DB with the encrypted one.

Encryption by Password

Hi,
1) What kind of encryption algorithm is used when we use EncryptByPassPhrase
function?
2) What's the difference between using "EncryptByPassPhrase" and "Symmetric
Key" (when used with password) except that you can use other algorythms in
Symmetric Keys?
Thanks in advance,
Leila"Leila" <Leilas@.hotpop.com> wrote in
news:#b$l6BPIGHA.1188@.TK2MSFTNGP14.phx.gbl:

> Hi,
> 1) What kind of encryption algorithm is used when we use
> EncryptByPassPhrase function?
>
I believe it is Triple DES
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||Leila wrote:
> Hi,
> 1) What kind of encryption algorithm is used when we use EncryptByPassPhra
se
> function?
> 2) What's the difference between using "EncryptByPassPhrase" and "Symmetri
c
> Key" (when used with password) except that you can use other algorythms in
> Symmetric Keys?
> Thanks in advance,
> Leila
1) I can't find this documented. I guess it is Triple DES. That's the
default for key encryption by passphrase and Triple DES is commonly
assumed to be the most secure of the 64-bit algorithms I believe.
2) The important difference is that EncryptByKey makes password
management easier because the key acts as a level of indirection - the
plaintext is encrypted with the key not the password. That means you
can have more than one password and you can add and remove passwords
without having to re-encrypt all your data. For those reasons
EncryptByKey is a much more powerful tool than EncryptByPassPhrase.
It's likely to be more secure too because you can expire old passwords
more promptly and frequently.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks indeed David,
What are 'Password' and 'KEY_SOURCE' in CREATE SYMMETRIC KEY? I can't really
understand that what they do.
I read this in BOL:
"When a symmetric key is encrypted with a password instead of the public key
of the database master key, the TRIPLE_DES encryption algorithm is used"
Does this mean that other algorithms (listed in syntax of CREATE SYMMETRIC
KEY) are available only when we use a certificate, asymmetric key or other
symmetric keys in creation of our symmetric key?
Leila
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1138117788.519975.189610@.z14g2000cwz.googlegroups.com...
> Leila wrote:
> 1) I can't find this documented. I guess it is Triple DES. That's the
> default for key encryption by passphrase and Triple DES is commonly
> assumed to be the most secure of the 64-bit algorithms I believe.
> 2) The important difference is that EncryptByKey makes password
> management easier because the key acts as a level of indirection - the
> plaintext is encrypted with the key not the password. That means you
> can have more than one password and you can add and remove passwords
> without having to re-encrypt all your data. For those reasons
> EncryptByKey is a much more powerful tool than EncryptByPassPhrase.
> It's likely to be more secure too because you can expire old passwords
> more promptly and frequently.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Leila wrote:
> Thanks indeed David,
> What are 'Password' and 'KEY_SOURCE' in CREATE SYMMETRIC KEY? I can't real
ly
> understand that what they do.
> I read this in BOL:
> "When a symmetric key is encrypted with a password instead of the public k
ey
> of the database master key, the TRIPLE_DES encryption algorithm is used"
> Does this mean that other algorithms (listed in syntax of CREATE SYMMETRIC
> KEY) are available only when we use a certificate, asymmetric key or other
> symmetric keys in creation of our symmetric key?
> Leila
>
>
Triple DES is used only to encrypt the *key*. The algorithm used by
EncryptByKey to encrypt your *data* will be whatever is specified in
the CREATE SYMMETRIC KEY statement - be that RC4, DESX, AES, etc.
Where does the key come from? Either it is generated randomly for you
OR if you specify some value for KEY_SOURCE it will be generated
directly from that value (using a hashing function). The point of
KEY_SOURCE is that it means you can reproduce the same key again and
again - on a different server for example. Similarly, if the
IDENTITY_VALUE is specified it is used to reproduce the same key GUID
for that key.
The key itself will be Triple DES encrypted by each password you
specify. So you finish up with one encrypted copy of the key for each
password. Any of those passwords can therefore be used to open
(decrypt) the key and allow encryption and decryption to take place.
Laurentiu Cristofor's blog is a good source on SQL Server's encryption.
It explained a lot for me anyway:
http://blogs.msdn.com/lcris/archive/category/10357.aspx
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks David,
I tried symmetric key in several ways. I encrypted some string with that key
and I wasn't able to decrypt it on the new system unless I provided the same
KEY_SOURCE and IDENTITY_VALUE. Is it right or I am missing something?
If I ignore KEY_SOURCE and IDENTITY_VALUE when creating symmetric key
(supplying only a password) and encrypting some strings, how can I decrypt
them on the new system?
Leila
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1138135007.689698.260300@.g44g2000cwa.googlegroups.com...
> Leila wrote:
> Triple DES is used only to encrypt the *key*. The algorithm used by
> EncryptByKey to encrypt your *data* will be whatever is specified in
> the CREATE SYMMETRIC KEY statement - be that RC4, DESX, AES, etc.
> Where does the key come from? Either it is generated randomly for you
> OR if you specify some value for KEY_SOURCE it will be generated
> directly from that value (using a hashing function). The point of
> KEY_SOURCE is that it means you can reproduce the same key again and
> again - on a different server for example. Similarly, if the
> IDENTITY_VALUE is specified it is used to reproduce the same key GUID
> for that key.
> The key itself will be Triple DES encrypted by each password you
> specify. So you finish up with one encrypted copy of the key for each
> password. Any of those passwords can therefore be used to open
> (decrypt) the key and allow encryption and decryption to take place.
> Laurentiu Cristofor's blog is a good source on SQL Server's encryption.
> It explained a lot for me anyway:
> http://blogs.msdn.com/lcris/archive/category/10357.aspx
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Leila wrote:
> Thanks David,
> I tried symmetric key in several ways. I encrypted some string with that k
ey
> and I wasn't able to decrypt it on the new system unless I provided the sa
me
> KEY_SOURCE and IDENTITY_VALUE. Is it right or I am missing something?
That's correct.

> If I ignore KEY_SOURCE and IDENTITY_VALUE when creating symmetric key
> (supplying only a password) and encrypting some strings, how can I decrypt
> them on the new system?
> Leila
To do that you have to have some mechanism for exchanging keys with the
other server. That's exactly where KEY_SOURCE is useful. If you
regularly need to exchange symmetric keys or passwords with another
system then you would typically want to create an asymmetric key and
exchange public keys first. The target system's public key or
certificate can then be used in conjunction with EncryptByAsmKey or
EncryptByCert to exchange keys or passwords between the two servers.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Encryption by Password

Hi,
1) What kind of encryption algorithm is used when we use EncryptByPassPhrase
function?
2) What's the difference between using "EncryptByPassPhrase" and "Symmetric
Key" (when used with password) except that you can use other algorythms in
Symmetric Keys?
Thanks in advance,
Leila"Leila" <Leilas@.hotpop.com> wrote in
news:#b$l6BPIGHA.1188@.TK2MSFTNGP14.phx.gbl:

> Hi,
> 1) What kind of encryption algorithm is used when we use
> EncryptByPassPhrase function?
>
I believe it is Triple DES
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||Leila wrote:
> Hi,
> 1) What kind of encryption algorithm is used when we use EncryptByPassPhra
se
> function?
> 2) What's the difference between using "EncryptByPassPhrase" and "Symmetri
c
> Key" (when used with password) except that you can use other algorythms in
> Symmetric Keys?
> Thanks in advance,
> Leila
1) I can't find this documented. I guess it is Triple DES. That's the
default for key encryption by passphrase and Triple DES is commonly
assumed to be the most secure of the 64-bit algorithms I believe.
2) The important difference is that EncryptByKey makes password
management easier because the key acts as a level of indirection - the
plaintext is encrypted with the key not the password. That means you
can have more than one password and you can add and remove passwords
without having to re-encrypt all your data. For those reasons
EncryptByKey is a much more powerful tool than EncryptByPassPhrase.
It's likely to be more secure too because you can expire old passwords
more promptly and frequently.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks indeed David,
What are 'Password' and 'KEY_SOURCE' in CREATE SYMMETRIC KEY? I can't really
understand that what they do.
I read this in BOL:
"When a symmetric key is encrypted with a password instead of the public key
of the database master key, the TRIPLE_DES encryption algorithm is used"
Does this mean that other algorithms (listed in syntax of CREATE SYMMETRIC
KEY) are available only when we use a certificate, asymmetric key or other
symmetric keys in creation of our symmetric key?
Leila
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1138117788.519975.189610@.z14g2000cwz.googlegroups.com...
> Leila wrote:
> 1) I can't find this documented. I guess it is Triple DES. That's the
> default for key encryption by passphrase and Triple DES is commonly
> assumed to be the most secure of the 64-bit algorithms I believe.
> 2) The important difference is that EncryptByKey makes password
> management easier because the key acts as a level of indirection - the
> plaintext is encrypted with the key not the password. That means you
> can have more than one password and you can add and remove passwords
> without having to re-encrypt all your data. For those reasons
> EncryptByKey is a much more powerful tool than EncryptByPassPhrase.
> It's likely to be more secure too because you can expire old passwords
> more promptly and frequently.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Leila wrote:
> Thanks indeed David,
> What are 'Password' and 'KEY_SOURCE' in CREATE SYMMETRIC KEY? I can't real
ly
> understand that what they do.
> I read this in BOL:
> "When a symmetric key is encrypted with a password instead of the public k
ey
> of the database master key, the TRIPLE_DES encryption algorithm is used"
> Does this mean that other algorithms (listed in syntax of CREATE SYMMETRIC
> KEY) are available only when we use a certificate, asymmetric key or other
> symmetric keys in creation of our symmetric key?
> Leila
>
>
Triple DES is used only to encrypt the *key*. The algorithm used by
EncryptByKey to encrypt your *data* will be whatever is specified in
the CREATE SYMMETRIC KEY statement - be that RC4, DESX, AES, etc.
Where does the key come from? Either it is generated randomly for you
OR if you specify some value for KEY_SOURCE it will be generated
directly from that value (using a hashing function). The point of
KEY_SOURCE is that it means you can reproduce the same key again and
again - on a different server for example. Similarly, if the
IDENTITY_VALUE is specified it is used to reproduce the same key GUID
for that key.
The key itself will be Triple DES encrypted by each password you
specify. So you finish up with one encrypted copy of the key for each
password. Any of those passwords can therefore be used to open
(decrypt) the key and allow encryption and decryption to take place.
Laurentiu Cristofor's blog is a good source on SQL Server's encryption.
It explained a lot for me anyway:
http://blogs.msdn.com/lcris/archive/category/10357.aspx
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks David,
I tried symmetric key in several ways. I encrypted some string with that key
and I wasn't able to decrypt it on the new system unless I provided the same
KEY_SOURCE and IDENTITY_VALUE. Is it right or I am missing something?
If I ignore KEY_SOURCE and IDENTITY_VALUE when creating symmetric key
(supplying only a password) and encrypting some strings, how can I decrypt
them on the new system?
Leila
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1138135007.689698.260300@.g44g2000cwa.googlegroups.com...
> Leila wrote:
> Triple DES is used only to encrypt the *key*. The algorithm used by
> EncryptByKey to encrypt your *data* will be whatever is specified in
> the CREATE SYMMETRIC KEY statement - be that RC4, DESX, AES, etc.
> Where does the key come from? Either it is generated randomly for you
> OR if you specify some value for KEY_SOURCE it will be generated
> directly from that value (using a hashing function). The point of
> KEY_SOURCE is that it means you can reproduce the same key again and
> again - on a different server for example. Similarly, if the
> IDENTITY_VALUE is specified it is used to reproduce the same key GUID
> for that key.
> The key itself will be Triple DES encrypted by each password you
> specify. So you finish up with one encrypted copy of the key for each
> password. Any of those passwords can therefore be used to open
> (decrypt) the key and allow encryption and decryption to take place.
> Laurentiu Cristofor's blog is a good source on SQL Server's encryption.
> It explained a lot for me anyway:
> http://blogs.msdn.com/lcris/archive/category/10357.aspx
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Leila wrote:
> Thanks David,
> I tried symmetric key in several ways. I encrypted some string with that k
ey
> and I wasn't able to decrypt it on the new system unless I provided the sa
me
> KEY_SOURCE and IDENTITY_VALUE. Is it right or I am missing something?
That's correct.

> If I ignore KEY_SOURCE and IDENTITY_VALUE when creating symmetric key
> (supplying only a password) and encrypting some strings, how can I decrypt
> them on the new system?
> Leila
To do that you have to have some mechanism for exchanging keys with the
other server. That's exactly where KEY_SOURCE is useful. If you
regularly need to exchange symmetric keys or passwords with another
system then you would typically want to create an asymmetric key and
exchange public keys first. The target system's public key or
certificate can then be used in conjunction with EncryptByAsmKey or
EncryptByCert to exchange keys or passwords between the two servers.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

encryption

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

Encrypting the 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 passwords in an access DB

Hi,
I usually work with MySQL where it is able tl encrypt a password in a database (for users /clients etc) is there a way to do somthing similar in access.
Thanking you in advance
OliOnce again, google has the answer... it really is the best place to look.

e.g.
http://www.winnetmag.com/Articles/Index.cfm?ArticleID=102
http://www.transactsql.com/statement/PWDCOMPARE.html
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20698196.html
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20606901.html
http://dbforums.com/arch/7/2002/9/326891

Encrypting passwords

A friend of my self asked me how he can save a password not as clear text. He wanted to encrypt the password and save the encrypted string in the database.

How can he do this? Maybe somebody can help me here.

Regards Markus

What does your friend need the password for? If it is used for authentication (to verify that another password submitted is matching the password stored in the database), then you can hash the password. If you need to store the password to use it somewhere else in clear form, then you need to encrypt it.

For SQL Server 2000 there are no builtin functions for hashing or encryption You may hear about pwdencrypt - an undocumented function - do not use it. For SQL Server 2000, you will have to write your own extended procedures for performing encryption or hashing.

In SQL Server 2005, you can use HashBytes to hash the password and EncryptByKey to encrypt it.

Thanks
Laurentiu

|||

but is this truly secure? you're still sending the password over a connection in clear text, unless you're in SSL, yes? isn't it best to simply hash the password on the clientside to begin with?

Laurentiu Cristofor wrote:

In SQL Server 2005, you can use HashBytes to hash the password and EncryptByKey to encrypt it.

Thanks
Laurentiu

|||

It is secure if the connection is secured using SSL - it should be secured that way if you're concerned about security.

Hashing on the client side does not address the insecure connection problem, because your authentication will then only depend on the hash (that's all the server will see from the client), and then the hash will effectively serve the same role as the password, so if a hash is intercepted on an insecure connection, a third party can pass it back to the server and connect this way.

Also, note that in my previous post I did not recommend implementing custom authentication schemes using those functions. Instead, you should leverage the mechanisms already provided by SQL Server.

Thanks
Laurentiu

sql

Encrypting Field in Database

I have existing application that we need to encrypt the field that stores
the user password. This is a SQL Server 2000 database that has a table
that stores userid and passwords.
Please let me know the procedures that I need to implement to encrypt the
password field.
Thanks,Refer to the Encryption section in the following FAQ:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22
-Sue
On Thu, 27 Oct 2005 09:32:13 -0700, Joe K. <Joe
K.@.discussions.microsoft.com> wrote:

>I have existing application that we need to encrypt the field that stores
>the user password. This is a SQL Server 2000 database that has a table
>that stores userid and passwords.
>Please let me know the procedures that I need to implement to encrypt the
>password field.
>Thanks,|||Hi,
refer and visit my site www.activecrypt.com .
Regards
--
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
"Sue Hoegemeier" wrote:

> Refer to the Encryption section in the following FAQ:
> http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22
> -Sue
> On Thu, 27 Oct 2005 09:32:13 -0700, Joe K. <Joe
> K.@.discussions.microsoft.com> wrote:
>
>

Encrypting Data using SQL Server 2005

If you encrypt some data using a symmetric key with a password. It appears
that the database master key is not used at all to encrypt the data. Is thi
s
true? Also it appears you can backup the database and move it to another
server, and retrain the password for the symmetric key from the old server.
Meaning that after you restore the database to a new server you can use the
symmetric key password from the old server to open the symmetric key in the
database on the new server and decrypt the data.
My basic question if you create a symmetric key with a password, and encrypt
data with that symmetric key, then is there any reason you would need to
create a master key for the database?
--
If you are looking for SQL Server examples check out my Website at
http://www.sqlserverexamples.comHello Greg,
GL> If you encrypt some data using a symmetric key with a password. It
GL> appears that the database master key is not used at all to encrypt
GL> the data. Is this true?
Strictly speaking, yes. But recall that the symmetric's key decryption devic
e
is stored encrypted by the Service Master (SMK) in its absence. So while
the SMK isn't part the encryption vector per se, you aren't using going to
be able to decrypt encrypted data without the correct SMK if you don't use
a Database Master Key (DBMK).
GL> Also it appears you can backup the
GL> database and move it to another server, and retrain the password for
GL> the symmetric key from the old server. Meaning that after you
GL> restore the database to a new server you can use the symmetric key
GL> password from the old server to open the symmetric key in the
GL> database on the new server and decrypt the data.
Yes, you wouldn't want to unrecoverable data, but you will still need to
regenerate off that instance's SMK.
GL> My basic question if you create a symmetric key with a password, and
GL> encrypt data with that symmetric key, then is there any reason you
GL> would need to create a master key for the database?
Consider the following example. Although both DBs have the same keys, they
really don't because the keys have different GUIDs. And if you look at the
encrypted data carefully enough, its pretty obvious that the key guid is
part of the encrypted data.
use master
go
create database enc1
create database enc2
go
use enc2
create table dbo.secrets(data varbinary(255))
go
use enc1
create symmetric key signingKey with algorithm = triple_des encryption by
password = 'theKey'
open symmetric key signingkey decryption by password = 'theKey'
create symmetric key enc_Key with algorithm = triple_des encryption by symme
tric
key signingKey
close symmetric key signingKey
go
open symmetric key signingkey decryption by password = 'theKey'
open symmetric key enc_key decryption by symmetric key signingKey
close symmetric key signingKey
select name,key_guid,algorithm_desc from sys.symmetric_keys
insert into enc2.dbo.secrets values (encryptByKey(key_guid('enc_key'),'beSur
eToDrinkYourOvaltine'))
select key_guid('enc_key'),data,cast(decryptByK
ey(data) as varchar(255))
from enc2.dbo.secrets
close symmetric key enc_key
go
use enc2
create symmetric key signingKey with algorithm = triple_des encryption by
password = 'theKey'
open symmetric key signingkey decryption by password = 'theKey'
create symmetric key enc_Key with algorithm = triple_des encryption by symme
tric
key signingKey
close symmetric key signingKey
go
open symmetric key signingkey decryption by password = 'theKey'
open symmetric key enc_key decryption by symmetric key signingKey
close symmetric key signingKey
select name,key_guid,algorithm_desc from sys.symmetric_keys
select key_guid('enc_key'),data,cast(decryptByK
ey(data) as varchar(255))
from enc2.dbo.secrets
close symmetric key enc_key
go|||So if I understand you correctly the encrypted data can not be decrypted
without the appropriate Service Master Key, even if you have the correct
symmetric key password. Meaning you can't move a dataase backup of the
encrypted data from one server to another and decrypt it using the only the
symmetric key. Is this true?
I'm guessing I don't have this right because I can copy a database backup
from one server to another and still decrypt the encrypted data. Here is a
script I tested it with:
-- on server 1 do this:
use master
go
if exists (select * from master.sys.databases where name = 'enc1')
drop database enc1
create database enc1
go
use enc1
create table dbo.secrets(data varbinary(255))
go
create symmetric key signingKey with algorithm = triple_des encryption by
password = 'theKey'
open symmetric key signingkey decryption by password = 'theKey'
create symmetric key enc_Key with algorithm = triple_des encryption by
symmetric
key signingKey
close symmetric key signingKey
go
open symmetric key signingkey decryption by password = 'theKey'
open symmetric key enc_key decryption by symmetric key signingKey
close symmetric key signingKey
select name,key_guid,algorithm_desc from sys.symmetric_keys
insert into dbo.secrets values
(encryptByKey(key_guid('enc_key'),'beSur
eToDrinkYourOvaltine'))
select key_guid('enc_key'),data,cast(decryptByK
ey(data) as varchar(255))
from dbo.secrets
close symmetric key enc_key
backup database enc1 to disk = 'C:\temp\enc1.bak'
-- copy C:\temp\enc1.bak from server 1 to server 2
-- server 2 do this:
use master
go
if exists (select * from master.sys.databases where name = 'enc1')
drop database enc1
go
restore database enc1 from disk='c:\temp\enc1.bak'
go
use enc1
go
open symmetric key signingkey decryption by password = 'theKey'
open symmetric key enc_key decryption by symmetric key signingKey
close symmetric key signingKey
select name,key_guid,algorithm_desc from sys.symmetric_keys
select key_guid('enc_key'),data,cast(decryptByK
ey(data) as varchar(255))
from dbo.secrets
close symmetric key enc_key
Now so I'm wondering why I can move a database that has encrypted data from
one server to another by just doing a database backup and restore and then
issuing the open symmetric key using the password from the target server,
like so.
If you are looking for SQL Server examples check out my Website at
http://ww.sqlserverexamples.com
"Kent Tegels" wrote:

> Hello Greg,
> GL> If you encrypt some data using a symmetric key with a password. It
> GL> appears that the database master key is not used at all to encrypt
> GL> the data. Is this true?
> Strictly speaking, yes. But recall that the symmetric's key decryption dev
ice
> is stored encrypted by the Service Master (SMK) in its absence. So while
> the SMK isn't part the encryption vector per se, you aren't using going to
> be able to decrypt encrypted data without the correct SMK if you don't use
> a Database Master Key (DBMK).
> GL> Also it appears you can backup the
> GL> database and move it to another server, and retrain the password for
> GL> the symmetric key from the old server. Meaning that after you
> GL> restore the database to a new server you can use the symmetric key
> GL> password from the old server to open the symmetric key in the
> GL> database on the new server and decrypt the data.
> Yes, you wouldn't want to unrecoverable data, but you will still need to
> regenerate off that instance's SMK.
> GL> My basic question if you create a symmetric key with a password, and
> GL> encrypt data with that symmetric key, then is there any reason you
> GL> would need to create a master key for the database?
> Consider the following example. Although both DBs have the same keys, they
> really don't because the keys have different GUIDs. And if you look at the
> encrypted data carefully enough, its pretty obvious that the key guid is
> part of the encrypted data.
> use master
> go
> create database enc1
> create database enc2
> go
> use enc2
> create table dbo.secrets(data varbinary(255))
> go
> use enc1
> create symmetric key signingKey with algorithm = triple_des encryption by
> password = 'theKey'
> open symmetric key signingkey decryption by password = 'theKey'
> create symmetric key enc_Key with algorithm = triple_des encryption by sym
metric
> key signingKey
> close symmetric key signingKey
> go
> open symmetric key signingkey decryption by password = 'theKey'
> open symmetric key enc_key decryption by symmetric key signingKey
> close symmetric key signingKey
> select name,key_guid,algorithm_desc from sys.symmetric_keys
> insert into enc2.dbo.secrets values (encryptByKey(key_guid('enc_key'),'beS
ureToDrinkYourOvaltine'))
> select key_guid('enc_key'),data,cast(decryptByK
ey(data) as varchar(255))
> from enc2.dbo.secrets
> close symmetric key enc_key
> go
> use enc2
> create symmetric key signingKey with algorithm = triple_des encryption by
> password = 'theKey'
> open symmetric key signingkey decryption by password = 'theKey'
> create symmetric key enc_Key with algorithm = triple_des encryption by sym
metric
> key signingKey
> close symmetric key signingKey
> go
> open symmetric key signingkey decryption by password = 'theKey'
> open symmetric key enc_key decryption by symmetric key signingKey
> close symmetric key signingKey
> select name,key_guid,algorithm_desc from sys.symmetric_keys
> select key_guid('enc_key'),data,cast(decryptByK
ey(data) as varchar(255))
> from enc2.dbo.secrets
> close symmetric key enc_key
> go
>
>|||Hello Greg,
GL> So if I understand you correctly the encrypted data can not be decrypted
without the appropriate Service Master Key, even if you have the correct sy
mmetric key password. Meaning you ca
n't move a dataase backup of the encrypted data from one server to another a
nd decrypt it using the only the symmetric key. Is this true?
It was certainly the understanding I had from reading BOL and the testing I
did. I couldn't get your backup example to work and wondered if there wasn't
maybe so vodoo getting done during
the restore process so a did a dettach/attach insead (attachment #1.)
GL> Now so I'm wondering why I can move a database that has encrypted data f
rom one server to another by just doing a database backup and restore and th
en issuing the open symmetric key=2
0using the password from the target server, like so.
There's a note in BOL that gave me a different understanding of this:
"When a symmetric key is encrypted with a password instead of the public key
of the database master key, the TRIPLE_DES encryption algorithm is used. Be
cause of this, keys that are created20with a strong encryption algorithm, su
ch as AES, are themselves secured by a weaker algorithm."
This was added in December 2006. So when you sign a symmetric key with a pas
sword, it looks like it just internalizes the key under 3DES and makes it tr
ansportable. That sucks because n
ow its way easier to brute force attack that key. UGH!
Even more annoyingly, the same behavior seems to apply to symmetic keys at a
re encrypted by asymmetric keys where that key is encrypted by a password. S
ee attachment #2.|||Can't seem to see those attachments. But I think from your reply you
confirmed what I was saying.
Now what I wonder is why are you encrypting a symmetric key with another
symmetric key. What exactly does this accomplish?|||"Greg Larsen" <gregalarsen@.removeit.msn.com> wrote in message
news:1DB4AD7C-69C7-4BD8-B48F-8166CD35DABE@.microsoft.com...
> Can't seem to see those attachments. But I think from your reply you
> confirmed what I was saying.
> Now what I wonder is why are you encrypting a symmetric key with another
> symmetric key. What exactly does this accomplish?
It provides layered protection for your keys. You can theoretically replace
any key in the mid- to upper-levels of your key hierarchy and only need to
decrypt and re-encrypt the keys it protects, until you reach the
bottom-level keys. The result is that you can theoretically change
intermediate and top-level keys on very often with very little effect on
your server or processes, and you can change bottom-level keys much less
often.
Of course if you change the bottom-level keys you have to decrypt and
re-encrypt all your protected data, which can be a resource-intensive
operation.sql

Encrypting and Decrypting Data

CREATE TABLE TabEncr (
id int identity (1,1),
NonEncrField varchar(30),
EncrField varchar(30)
)

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'OurSecretPassword'
CREATE CERTIFICATE my_cert with subject = 'Some Certificate'
CREATE SYMMETRIC KEY my_key with algorithm = triple_des encryption by certificate my_cert

OPEN SYMMETRIC KEY my_key DECRYPTION BY CERTIFICATE my_cert
INSERT INTO TabEncr (NonEncrField,EncrField)
VALUES ('Some Plain Value',encryptbykey(key_guid('my_key'),'Some Plain Value'))
CLOSE SYMMETRIC KEY my_key

OPEN SYMMETRIC KEY my_key DECRYPTION BY CERTIFICATE my_cert
SELECT NonEncrField,CONVERT(VARCHAR(30),DecryptByKey(EncrField))
FROM dbo.TabEncr
CLOSE SYMMETRIC KEY my_key

What is the problem with this code. It works fine , inserting the value encrypted but when i try to decrypt ,it returns a null value. What is missing. I also tried with symmetric key encryption with asymmetric key. Result is same, returns NULL value. I am using SQL 2005

Happy Coding...

The EncrField is of a wrong type; it should be varbinary, because the result of encryption is a varbinary value. If you replace the EncrField line with the following, then your script will work as expected:

EncrField varbinary(60)

Thanks
Laurentiu

|||

Hi Laurentiu Cristofor
Thanks for help. It works f?ne. But while trying your solution i also tried my original code and it worked fine. How can it be, i made some simple changes on code to see am i wrong but believe its working. Now there is big question, 1 week before it didn't work. But now its fine. Interesting and confusing.

(Modified; i tried again but it didn't worked. I think i miss somethink but what.)

|||

Maybe you are not recreating the table? The encryption code was correct - the table creation code was incorrect.

Thanks
Laurentiu

|||

how about batch update of data?

Edit:

Follow up on above@.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1365306&SiteID=1&mode=1 with the script

|||

What do you mean by batch update? Or do you mean batch insert?

Thanks
Laurentiu

|||what if i have a data (column) that i want to batch encrypt ?|||

You could do something similar to what you would do if you wanted to update all values of a non-encrypted column.

For example, you can issue an update statement like:

update t set c = encryptbykey(key_guid('skey'), c)

This assumes that c is varbinary and can accommodate the output of the encryption.

Thanks
Laurentiu

|||

Hi,

I got a similar issue with encrypt and decrypt.

In my case,

...

create table ( column Password varbinay(128) )

...

create symmetric key with certificate

...

OPEN SYMMETRIC KEY Sym_Key_01

DECRYPTION BY CERTIFICATE Cert;

UPDATE mytable

SET Password = EncryptByKey(Key_GUID('Password_01'),'ok')

select CONVERT(nvarchar, DecryptByKey(Password)) AS "Decrypted Password" from mytable

here, I didn't get the value 'ok' but a another wierd word (like a chinese word).

does someone know the reason?

Thanks,

Jone

Encrypting and Decrypting Data

CREATE TABLE TabEncr (
id int identity (1,1),
NonEncrField varchar(30),
EncrField varchar(30)
)

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'OurSecretPassword'
CREATE CERTIFICATE my_cert with subject = 'Some Certificate'
CREATE SYMMETRIC KEY my_key with algorithm = triple_des encryption by certificate my_cert

OPEN SYMMETRIC KEY my_key DECRYPTION BY CERTIFICATE my_cert
INSERT INTO TabEncr (NonEncrField,EncrField)
VALUES ('Some Plain Value',encryptbykey(key_guid('my_key'),'Some Plain Value'))
CLOSE SYMMETRIC KEY my_key

OPEN SYMMETRIC KEY my_key DECRYPTION BY CERTIFICATE my_cert
SELECT NonEncrField,CONVERT(VARCHAR(30),DecryptByKey(EncrField))
FROM dbo.TabEncr
CLOSE SYMMETRIC KEY my_key

What is the problem with this code. It works fine , inserting the value encrypted but when i try to decrypt ,it returns a null value. What is missing. I also tried with symmetric key encryption with asymmetric key. Result is same, returns NULL value. I am using SQL 2005

Happy Coding...

The EncrField is of a wrong type; it should be varbinary, because the result of encryption is a varbinary value. If you replace the EncrField line with the following, then your script will work as expected:

EncrField varbinary(60)

Thanks
Laurentiu

|||

Hi Laurentiu Cristofor
Thanks for help. It works f?ne. But while trying your solution i also tried my original code and it worked fine. How can it be, i made some simple changes on code to see am i wrong but believe its working. Now there is big question, 1 week before it didn't work. But now its fine. Interesting and confusing.

(Modified; i tried again but it didn't worked. I think i miss somethink but what.)

|||

Maybe you are not recreating the table? The encryption code was correct - the table creation code was incorrect.

Thanks
Laurentiu

|||

how about batch update of data?

Edit:

Follow up on above@.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1365306&SiteID=1&mode=1 with the script

|||

What do you mean by batch update? Or do you mean batch insert?

Thanks
Laurentiu

|||what if i have a data (column) that i want to batch encrypt ?|||

You could do something similar to what you would do if you wanted to update all values of a non-encrypted column.

For example, you can issue an update statement like:

update t set c = encryptbykey(key_guid('skey'), c)

This assumes that c is varbinary and can accommodate the output of the encryption.

Thanks
Laurentiu

|||

Hi,

I got a similar issue with encrypt and decrypt.

In my case,

...

create table ( column Password varbinay(128) )

...

create symmetric key with certificate

...

OPEN SYMMETRIC KEY Sym_Key_01

DECRYPTION BY CERTIFICATE Cert;

UPDATE mytable

SET Password = EncryptByKey(Key_GUID('Password_01'),'ok')

select CONVERT(nvarchar, DecryptByKey(Password)) AS "Decrypted Password" from mytable

here, I didn't get the value 'ok' but a another wierd word (like a chinese word).

does someone know the reason?

Thanks,

Jone

Encrypting and Decrypting Data

CREATE TABLE TabEncr (
id int identity (1,1),
NonEncrField varchar(30),
EncrField varchar(30)
)

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'OurSecretPassword'
CREATE CERTIFICATE my_cert with subject = 'Some Certificate'
CREATE SYMMETRIC KEY my_key with algorithm = triple_des encryption by certificate my_cert

OPEN SYMMETRIC KEY my_key DECRYPTION BY CERTIFICATE my_cert
INSERT INTO TabEncr (NonEncrField,EncrField)
VALUES ('Some Plain Value',encryptbykey(key_guid('my_key'),'Some Plain Value'))
CLOSE SYMMETRIC KEY my_key

OPEN SYMMETRIC KEY my_key DECRYPTION BY CERTIFICATE my_cert
SELECT NonEncrField,CONVERT(VARCHAR(30),DecryptByKey(EncrField))
FROM dbo.TabEncr
CLOSE SYMMETRIC KEY my_key

What is the problem with this code. It works fine , inserting the value encrypted but when i try to decrypt ,it returns a null value. What is missing. I also tried with symmetric key encryption with asymmetric key. Result is same, returns NULL value. I am using SQL 2005

Happy Coding...

The EncrField is of a wrong type; it should be varbinary, because the result of encryption is a varbinary value. If you replace the EncrField line with the following, then your script will work as expected:

EncrField varbinary(60)

Thanks
Laurentiu

|||

Hi Laurentiu Cristofor
Thanks for help. It works f?ne. But while trying your solution i also tried my original code and it worked fine. How can it be, i made some simple changes on code to see am i wrong but believe its working. Now there is big question, 1 week before it didn't work. But now its fine. Interesting and confusing.

(Modified; i tried again but it didn't worked. I think i miss somethink but what.)

|||

Maybe you are not recreating the table? The encryption code was correct - the table creation code was incorrect.

Thanks
Laurentiu

|||

how about batch update of data?

Edit:

Follow up on above@.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1365306&SiteID=1&mode=1 with the script

|||

What do you mean by batch update? Or do you mean batch insert?

Thanks
Laurentiu

|||what if i have a data (column) that i want to batch encrypt ?|||

You could do something similar to what you would do if you wanted to update all values of a non-encrypted column.

For example, you can issue an update statement like:

update t set c = encryptbykey(key_guid('skey'), c)

This assumes that c is varbinary and can accommodate the output of the encryption.

Thanks
Laurentiu

|||

Hi,

I got a similar issue with encrypt and decrypt.

In my case,

...

create table ( column Password varbinay(128) )

...

create symmetric key with certificate

...

OPEN SYMMETRIC KEY Sym_Key_01

DECRYPTION BY CERTIFICATE Cert;

UPDATE mytable

SET Password = EncryptByKey(Key_GUID('Password_01'),'ok')

select CONVERT(nvarchar, DecryptByKey(Password)) AS "Decrypted Password" from mytable

here, I didn't get the value 'ok' but a another wierd word (like a chinese word).

does someone know the reason?

Thanks,

Jone

Encrypted password

when we built the login control by built in login control of vs2005 the passwords are saved in the database which is automatically created.how can i get the unencrypted form of these passwords from database.

This post talks about it in great details.

http://mishler.net/2006/04/18/AspNet+Membership+Password+Administration.aspx

hope it helps

|||actually i am a begginer and this articles didnt helped me tell me something else

encrypt the data of collumn

Hi!
Is there any way to encrypt the data stored in collumn of a table ? actually
we are storing the password of all employees in that table.
Regards,
Swatiswati
Serach on internet for this subject.There were discussions at the forum a
week (or something like that) ago.
Some postes by David Portas and Steve Kass.
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:OFksBWXzEHA.1932@.TK2MSFTNGP09.phx.gbl...
> Hi!
> Is there any way to encrypt the data stored in collumn of a table ?
actually
> we are storing the password of all employees in that table.
> Regards,
> Swati
>
>|||thanks , I got the required link
Regards,
Swati
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OhGjbdXzEHA.2016@.TK2MSFTNGP15.phx.gbl...
> swati
> Serach on internet for this subject.There were discussions at the forum a
> week (or something like that) ago.
> Some postes by David Portas and Steve Kass.
>
>
>
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:OFksBWXzEHA.1932@.TK2MSFTNGP09.phx.gbl...
> > Hi!
> >
> > Is there any way to encrypt the data stored in collumn of a table ?
> actually
> > we are storing the password of all employees in that table.
> >
> > Regards,
> > Swati
> >
> >
> >
> >
>

encrypt the data of collumn

Hi!
Is there any way to encrypt the data stored in collumn of a table ? actually
we are storing the password of all employees in that table.
Regards,
Swatiswati
Serach on internet for this subject.There were discussions at the forum a
week (or something like that) ago.
Some postes by David Portas and Steve Kass.
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:OFksBWXzEHA.1932@.TK2MSFTNGP09.phx.gbl...
> Hi!
> Is there any way to encrypt the data stored in collumn of a table ?
actually
> we are storing the password of all employees in that table.
> Regards,
> Swati
>
>|||thanks , I got the required link
Regards,
Swati
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OhGjbdXzEHA.2016@.TK2MSFTNGP15.phx.gbl...
> swati
> Serach on internet for this subject.There were discussions at the forum a
> week (or something like that) ago.
> Some postes by David Portas and Steve Kass.
>
>
>
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:OFksBWXzEHA.1932@.TK2MSFTNGP09.phx.gbl...
> actually
>

encrypt the data of collumn

Hi!
Is there any way to encrypt the data stored in collumn of a table ? actually
we are storing the password of all employees in that table.
Regards,
Swati
swati
Serach on internet for this subject.There were discussions at the forum a
week (or something like that) ago.
Some postes by David Portas and Steve Kass.
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:OFksBWXzEHA.1932@.TK2MSFTNGP09.phx.gbl...
> Hi!
> Is there any way to encrypt the data stored in collumn of a table ?
actually
> we are storing the password of all employees in that table.
> Regards,
> Swati
>
>
|||thanks , I got the required link
Regards,
Swati
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OhGjbdXzEHA.2016@.TK2MSFTNGP15.phx.gbl...
> swati
> Serach on internet for this subject.There were discussions at the forum a
> week (or something like that) ago.
> Some postes by David Portas and Steve Kass.
>
>
>
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:OFksBWXzEHA.1932@.TK2MSFTNGP09.phx.gbl...
> actually
>

Encrypt passwords

Can anyone tell me the best way to encrypt password in a
SQL Server 2000 database? We are using IIS 5.0 and MS
Interdev. Hopefully, we will be moving to .Net in the
next couple of months, but for now I still need to find a
way to encrypt passwords in the current application.See: Storing Database Connection Strings Securely
http://msdn.microsoft.com/library/d...-us/dnnetsec/ht
ml/SecNetch12.asp
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||take a look at www.xpcrypt.com|||"Michelle" <michelle.vanden@.eglin.af.mil> wrote in message
news:0b4b01c3c57a$ae8db3b0$a601280a@.phx.gbl...

quote:

---

> Can anyone tell me the best way to encrypt password in a
> SQL Server 2000 database? We are using IIS 5.0 and MS
> Interdev. Hopefully, we will be moving to .Net in the
> next couple of months, but for now I still need to find a
> way to encrypt passwords in the current application.

---

Assuming you would like to store the password in an encrypted format
(as opposed to transmitting the data securely over the wire)
I found the following article to be helpful:
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=9809
I have used the method described successfully in a coldfusion application
using SQL Server 2000, and
the article is written for both 7.0 and 2000.
Benefit is, it's completely native to SQL, no 3rd party software to muck
about with.
xpcrypt does appear to be more robust from a security perspective (stronger
encryption algorithms, etc.)
but if all you want to do is not have plain text in the database,
pwdencrypt() should work fine.
Regards,
Jason