I have a simple .NET page that asks the user to create a new account. One of the fields on that page is 'Password'. I store the password in a SQL 2000 Database. However, it appears in the database as clear-text.
Is there a way to encrypt this so it doesn't appear as clear-text in the DB?
Hi everyone, I am currently reading ASP.NET unleashed and practising few examples. The following code converts a user's text into a symmetric encryption: 'nd: define keys Const DESKey As String = "ABCDEFGH" Const DESIV As String = "HGFEDCBA" 'nd: convert string to byte array Function convert2ByteArray(ByVal strInput As String) As Byte() Dim intCounter As Integer Dim arrChar As Char() arrChar = strInput.ToCharArray Dim arrByte(arrChar.Length - 1) As Byte For intCounter = 0 To arrByte.Length - 1 arrByte(intCounter) = Convert.ToByte(arrChar(intCounter)) Next Return arrByte End Function
Private Sub btnGo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGo.Click Dim arrDESKey As Byte() Dim arrDESIV As Byte() Dim arrInput As Byte() Dim objFileStream As FileStream Dim objDES As DESCryptoServiceProvider Dim objEncryptor As ICryptoTransform Dim objCryptoStream As CryptoStream 'convert string to bytes arrDESKey = convert2ByteArray(DESKey) arrDESIV = convert2ByteArray(DESIV) arrInput = convert2ByteArray(txtInput.Text) objDES = New DESCryptoServiceProvider 'pass keys objEncryptor = objDES.CreateEncryptor(arrDESKey, arrDESIV) 'create to file to save password objFileStream = New FileStream(MapPath("secret.txt"), FileMode.Create, FileAccess.Write) 'pass in file and keys objCryptoStream = New CryptoStream(objFileStream, objEncryptor, CryptoStreamMode.Write) 'pass in text objCryptoStream.Write(arrInput, 0, arrInput.Length) objCryptoStream.Close() lblDone.Text = "Done!" End Sub
It works fine. But, how to i save this encrypted password into a database field instead of a writing it to a file? Also, could some please tell me how to paste code into this forum? I tried <code></code> tags but it did not work. Many thanks, Kevin
For security reason, the sensitive information must be encrypted, and they should be configurable dynamically (by an ASP.NET application).
so I tried several ways to achieve this,
1. use SSIS Package Configurations to generate an XML config file It's a convenient way to generate config file. but the passwords are not encrypted (or I don't know how).
2. use Variables and Property Expressions I can set variables by reading from an external custom xml config file which was content encrypted (read and decrypt the custom config file in a script task). but in a FTP Connection Manager entity, the Password property can not be set via Property Expressions.
Is any way to store password encrypted to an external file?
I need to start encrypting several fields in a database and have been doing some testing with a test database first. I've run into problems when attempting to restore the database on either the same server (but different database) or to a separate server.
First, here's how i created the symmetric key and encrypted data in the original database:
create master key encryption by password = 'testAppleA3';
create certificate test with subject = 'test certificate', EXPIRY_DATE = '1/1/2010';
create symmetric key sk_Test with algorithm = triple_des encryption by certificate test;
open symmetric key sk_Test decryption by certificate test;
insert into employees values (101,'Jane Doe',encryptbykey(key_guid('sk_Test'),'$200000')); insert into employees values(102,'Bob Jones',encryptbykey(key_guid('sk_Test'),'$500000'));
select * from employees --delete from employees select id,name,cast(decryptbykey(salary) as varchar(10)) as salary from employees
close all symmetric keys
Next I backup up this test database and restore it to a new database on a different server (same issue if restore to different database but on same server).
Then if i attempt to open the key in the new database and decrypt:
open symmetric key sk_Test decryption by certificate test;
I get the error: An error occurred during decryption.
Ok, well not unexpected, so reading the forums, i try doing the below first in the new database:
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
Then I try opening the key again and get the error again:
An error occurred during decryption.
So then it occurs to me, maybe i need to drop and recreate it so i do
drop symmetric key sk_test
then
create symmetric key sk_Test with algorithm = triple_des encryption by certificate test;
and then try to open it.
Same error!
So then i decide, let's drop everything, the master key, the certificate and then symmetric key:
drop symmetric key sk_test drop certificate test drop master key
Then recreate the master key:
create master key encryption by password = 'testAppleA3';
Restore the certificate from a backup i had made to a file:
CREATE CERTIFICATE test FROM FILE = 'c:storedcertsencryptiontestcert'
Recreate the symmetric key again:
create symmetric key sk_Test with algorithm = triple_des encryption by certificate test;
And now open the key only to get the error:
Cannot decrypt or encrypt using the specified certificate, either because it has no private key or because the password provided for the private key is incorrect.
So what am I doing wrong here? In this scenario I would appear to have lost all access to decrypt the data in the database despite restoring from a backup which restored the symmetric key and certificate and i obviously know the password for the master key.
I also tried running the command
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
I have to create some type of an sp that will force users to change their passwords every 30 days. It sounds like I should be able to create a simple table with the login and the password expiration date. Then, I should create some type of function to check the expiration, get the new password and run sp_password. Has anyone done this before? Where would this table be created? In Master? Would that also be where I should create this sp? Should this be an xp?
I'm fairly light on creating sp's. Can anyone suggest a starting point for me or give any suggestions? I would appreciate any help. Thanks! Toni E.
Does some one know of a way to use windows passwords in sql w/o having to enter them in? Like a program or sql statement? As of now, we are having to enter them in ourselves , and would like to make our jobs easier....Thanks!!
I have SQL Server 2000 and in one of the databases we store the Application passwords as a Clear text. we would like to encrypt these passwords so that we will pass the auditing.
Can some one please suggest a good way to encrypt these passwords.
I have a pgp file requiring a password that is emailed to me. Is it possible to set up a DTS package that will open the file (using the password), and insert the data into a table, file, etc. TB
There are set of logins in sql server some are NT authentication and some are sql server authentication. How can i retrieve the passwords for both these logins.
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.
I have installed Sql Server 2000 Reporting Services. I desinged report in VS 2003.When press F5 key.Every time it is asking for user id password then only it is giving List of Report Names.
Is there any way to avoid giving user id and pwd every time. Regards.
Wondering on how to script over the passwords from one 2000 box to another. We are cutting a box over from dev to production and need to copy userids and passwords from another box.
Hi, WE are moving one of our applications to oracle from sql server.For this migration is there any way I can move passwords from sql server syslogins table to oracle or extract the passwords. thanks in advance Mohan
We store passwords of users of our website. They need to be autenticated and based on that it gives them access to what they are entitled. But its not like NT or server authentication.
This has been setup so that we have a user table and it stores the password. However, it stores it in plain text. Is there any way I can encrypt this field so it is unreadable? Is there a property or a datatype that I can't find? Is there a way to simulate the encryption?
Does anyone have a script or tip on how to move passwords associated with logins. I am consolidating two 6.5s into one and want to maintain the passwords associated with the logins. Any hints?
I am creating a new sql server 2000 machine and was wondering how to get the users and passwords from the sql6.5 box over onto the new machine. I know for sql 6.5 there is a stored proc sp_TransferPasswords that I have used but it will not script over cleanly to the sql 2000 box.