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 data

Does MS Server 2000 have any way of encrypting data (not just passwords) in
a table, or can it only be done by a function/stored procedure? If only by a
function - does anyone have any advice/suggestions on a good resource about
this?
thanks muchSSL allows you to encrypt data sent in packets so intercepted data is not de
cipherable.|||So the data is encrypted enroute but not in the database?|||There are 3rd party products that will do this but MS SQL will not do it
natively.
Christian Smith
"Jan" <anonymous@.discussions.microsoft.com> wrote in message
news:1320869D-7219-4799-B4CC-4A5648DD663B@.microsoft.com...
> So the data is encrypted enroute but not in the database?|||Hi,
How passwords can be encrypted in MS SQL? Thanks
-- Jan wrote: --
Does MS Server 2000 have any way of encrypting data (not just passwords) in
a table, or can it only be done by a function/stored procedure? If only by a
function - does anyone have any advice/suggestions on a good resource about
this?
thanks much|||Jen,
Password can be encrypted using the undocumented pwdencrypt() and pwdcompare
()
functions. pwdencrypt() is used to create a one-way hash of the data and
pwdcompare() is used to test for comparison.
I believe that Microsoft recommends against using these functions because th
e
algorithms could change from version to version.
If you are looking for a third-party solution check out:
Whamware.Crypt from www.whamware.com
ActiveCrypt from www.activecrypt.com
Encryptionizer for SQL Server from www.netlib.com
Tom
"Jen" wrote:
> Hi,
> How passwords can be encrypted in MS SQL? Thanks
> -- Jan wrote: --
> Does MS Server 2000 have any way of encrypting data (not just passwords)[/col
or]
in a table, or can it only be done by a function/stored procedure? If only b
y a
function - does anyone have any advice/suggestions on a good resource about
this?
> thanks much

encrypting data

Does MS Server 2000 have any way of encrypting data (not just passwords) in a table, or can it only be done by a function/stored procedure? If only by a function - does anyone have any advice/suggestions on a good resource about this?
thanks muchSSL allows you to encrypt data sent in packets so intercepted data is not decipherable.|||So the data is encrypted enroute but not in the database?|||There are 3rd party products that will do this but MS SQL will not do it
natively.
Christian Smith
"Jan" <anonymous@.discussions.microsoft.com> wrote in message
news:1320869D-7219-4799-B4CC-4A5648DD663B@.microsoft.com...
> So the data is encrypted enroute but not in the database?|||Hi
How passwords can be encrypted in MS SQL? Thank
-- Jan wrote: --
Does MS Server 2000 have any way of encrypting data (not just passwords) in a table, or can it only be done by a function/stored procedure? If only by a function - does anyone have any advice/suggestions on a good resource about this
thanks much|||Jen,
Password can be encrypted using the undocumented pwdencrypt() and pwdcompare()
functions. pwdencrypt() is used to create a one-way hash of the data and
pwdcompare() is used to test for comparison.
I believe that Microsoft recommends against using these functions because the
algorithms could change from version to version.
If you are looking for a third-party solution check out:
Whamware.Crypt from www.whamware.com
ActiveCrypt from www.activecrypt.com
Encryptionizer for SQL Server from www.netlib.com
Tom
"Jen" wrote:
> Hi,
> How passwords can be encrypted in MS SQL? Thanks
> -- Jan wrote: --
> Does MS Server 2000 have any way of encrypting data (not just passwords)
in a table, or can it only be done by a function/stored procedure? If only by a
function - does anyone have any advice/suggestions on a good resource about
this?
> thanks muchsql

encrypting a column

We have a SOX requirment that disallows the storing of passwords as plain
text... they must be encrypted in the database. Does SQL Server handle this
natively?Not in 2000. Use Windows authentication or use the MS crypto API / .NET
crypto classes.
SQL Server 2005 will support native encryption and password management
policies for SQL Server logins.
David Portas
SQL Server MVP
--

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

Encrypted data in tables

How could I store encrypted data in tables in SQL Server 2000 Database.
Say I have made a table to store passwords and I don't want every user to be able to view this table.
Thanks
Jasmitado you really need to store the passwords, or could you just store a one-way hashed value of the password instead, this way the passwords are realivily useless.

if not surely you can set it so that only a certain user can select (and others) from this table?

Encrypt Passwords

I would like to put all my sa and other passwords into a
text-like file and save it out on the network w/o fear
that it can be cracked. Can someone suggest some (and
reasonable) third party tools for something basic like
this?
You may want to look at using something like Whisper 32.
It's free and available at:
http://www.ivory.org/index.html
-Sue
On Tue, 20 Jul 2004 13:32:07 -0700, "Blnt"
<anonymous@.discussions.microsoft.com> wrote:

>I would like to put all my sa and other passwords into a
>text-like file and save it out on the network w/o fear
>that it can be cracked. Can someone suggest some (and
>reasonable) third party tools for something basic like
>this?

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

Encrypt Passwords

I would like to put all my sa and other passwords into a
text-like file and save it out on the network w/o fear
that it can be cracked. Can someone suggest some (and
reasonable) third party tools for something basic like
this?You may want to look at using something like Whisper 32.
It's free and available at:
http://www.ivory.org/index.html
-Sue
On Tue, 20 Jul 2004 13:32:07 -0700, "Blnt"
<anonymous@.discussions.microsoft.com> wrote:
>I would like to put all my sa and other passwords into a
>text-like file and save it out on the network w/o fear
>that it can be cracked. Can someone suggest some (and
>reasonable) third party tools for something basic like
>this?

Encrypt Passwords

I would like to put all my sa and other passwords into a
text-like file and save it out on the network w/o fear
that it can be cracked. Can someone suggest some (and
reasonable) third party tools for something basic like
this?You may want to look at using something like Whisper 32.
It's free and available at:
http://www.ivory.org/index.html
-Sue
On Tue, 20 Jul 2004 13:32:07 -0700, "Blnt"
<anonymous@.discussions.microsoft.com> wrote:

>I would like to put all my sa and other passwords into a
>text-like file and save it out on the network w/o fear
>that it can be cracked. Can someone suggest some (and
>reasonable) third party tools for something basic like
>this?

Encrypt a column

How do I encrypt a column in an SQL table - say I want to store passwords in
that column?
Thanks.Assuming you are on SQL2005
How To: Encrypt a Column of Data
http://msdn2.microsoft.com/en-us/library/ms179331(en-US,SQL.90).aspx
Cryptographic Functions (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms173744.aspx
Encryption Hierarchy
http://msdn2.microsoft.com/en-us/library/ms189586(en-US,SQL.90).aspx
A couple of blogs as well
http://blogs.msdn.com/lcris/default.aspx
http://blogs.msdn.com/yukondoit/default.aspx
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"M$" <M$@.xyz.com> wrote in message
news:%23kne7taAGHA.1028@.TK2MSFTNGP11.phx.gbl...
> How do I encrypt a column in an SQL table - say I want to store passwords
> in that column?
> Thanks.
>|||M$ wrote:
> How do I encrypt a column in an SQL table - say I want to store passwords in
> that column?
> Thanks.
M$ wrote:
> How do I encrypt a column in an SQL table - say I want to store passwords in
> that column?
> Thanks.
If you are using SQL Server 2005 you can use the EncryptByKey or
EncryptByPassPhrase functions to encrypt data in a column.
Password recovery mechanisms are an inherent security flaw however and
I recommend you avoid them. Don't store the password (encrypted or
otherwise) in the database. Instead, make a secure hash of the password
and store that. For example:
/* Store a password */
DECLARE @.pw NVARCHAR(256), @.salt NVARCHAR(36)
SET @.pw = 'foobar'
SET @.salt = CAST(NEWID() AS NVARCHAR(36))
UPDATE user_passwords
SET pw_salt = @.salt,
pw_hash = HashBytes('MD5', @.salt + @.pw)
WHERE userid = 123 ;
/* Retrieve and compare a password */
DECLARE @.pw NVARCHAR(256), @.userid INT
SET @.pw = 'foobar'
SET @.userid = 123
SELECT CASE pw_hash WHEN HashBytes('MD5', pw_salt + @.pw)
THEN 'Valid'
ELSE 'Invalid' END
FROM user_passwords
WHERE userid = @.userid ;
Conventional wisdom has it that the "salt" value limits the
effectiveness of possible password dictionary attacks. The reality in
today's environment though is that it's far more important to have
mechanisms to prevent users picking easy passwords and to monitor
intrusion attempts. Better yet, use asymetric methods of authentication
that don't require a central password store.
--
David Portas
SQL Server MVP
--

Encrypt a column

How do I encrypt a column in an SQL table - say I want to store passwords in
that column?
Thanks.Assuming you are on SQL2005
How To: Encrypt a Column of Data
http://msdn2.microsoft.com/en-us/library/ms179331(en-US,SQL.90).aspx
Cryptographic Functions (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms173744.aspx
Encryption Hierarchy
http://msdn2.microsoft.com/en-us/library/ms189586(en-US,SQL.90).aspx
A couple of blogs as well
http://blogs.msdn.com/lcris/default.aspx
http://blogs.msdn.com/yukondoit/default.aspx
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"M$" <M$@.xyz.com> wrote in message
news:%23kne7taAGHA.1028@.TK2MSFTNGP11.phx.gbl...
> How do I encrypt a column in an SQL table - say I want to store passwords
> in that column?
> Thanks.
>|||M$ wrote:

> How do I encrypt a column in an SQL table - say I want to store passwords
in
> that column?
> Thanks.
M$ wrote:

> How do I encrypt a column in an SQL table - say I want to store passwords
in
> that column?
> Thanks.
If you are using SQL Server 2005 you can use the EncryptByKey or
EncryptByPassPhrase functions to encrypt data in a column.
Password recovery mechanisms are an inherent security flaw however and
I recommend you avoid them. Don't store the password (encrypted or
otherwise) in the database. Instead, make a secure hash of the password
and store that. For example:
/* Store a password */
DECLARE @.pw NVARCHAR(256), @.salt NVARCHAR(36)
SET @.pw = 'foobar'
SET @.salt = CAST(NEWID() AS NVARCHAR(36))
UPDATE user_passwords
SET pw_salt = @.salt,
pw_hash = HashBytes('MD5', @.salt + @.pw)
WHERE userid = 123 ;
/* Retrieve and compare a password */
DECLARE @.pw NVARCHAR(256), @.userid INT
SET @.pw = 'foobar'
SET @.userid = 123
SELECT CASE pw_hash WHEN HashBytes('MD5', pw_salt + @.pw)
THEN 'Valid'
ELSE 'Invalid' END
FROM user_passwords
WHERE userid = @.userid ;
Conventional wisdom has it that the "salt" value limits the
effectiveness of possible password dictionary attacks. The reality in
today's environment though is that it's far more important to have
mechanisms to prevent users picking easy passwords and to monitor
intrusion attempts. Better yet, use asymetric methods of authentication
that don't require a central password store.
David Portas
SQL Server MVP
--

Encrypt a column

How do I encrypt a column in an SQL table - say I want to store passwords in
that column?
Thanks.
Assuming you are on SQL2005
How To: Encrypt a Column of Data
http://msdn2.microsoft.com/en-us/library/ms179331(en-US,SQL.90).aspx
Cryptographic Functions (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms173744.aspx
Encryption Hierarchy
http://msdn2.microsoft.com/en-us/library/ms189586(en-US,SQL.90).aspx
A couple of blogs as well
http://blogs.msdn.com/lcris/default.aspx
http://blogs.msdn.com/yukondoit/default.aspx
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"M$" <M$@.xyz.com> wrote in message
news:%23kne7taAGHA.1028@.TK2MSFTNGP11.phx.gbl...
> How do I encrypt a column in an SQL table - say I want to store passwords
> in that column?
> Thanks.
>
|||M$ wrote:

> How do I encrypt a column in an SQL table - say I want to store passwords in
> that column?
> Thanks.
M$ wrote:

> How do I encrypt a column in an SQL table - say I want to store passwords in
> that column?
> Thanks.
If you are using SQL Server 2005 you can use the EncryptByKey or
EncryptByPassPhrase functions to encrypt data in a column.
Password recovery mechanisms are an inherent security flaw however and
I recommend you avoid them. Don't store the password (encrypted or
otherwise) in the database. Instead, make a secure hash of the password
and store that. For example:
/* Store a password */
DECLARE @.pw NVARCHAR(256), @.salt NVARCHAR(36)
SET @.pw = 'foobar'
SET @.salt = CAST(NEWID() AS NVARCHAR(36))
UPDATE user_passwords
SET pw_salt = @.salt,
pw_hash = HashBytes('MD5', @.salt + @.pw)
WHERE userid = 123 ;
/* Retrieve and compare a password */
DECLARE @.pw NVARCHAR(256), @.userid INT
SET @.pw = 'foobar'
SET @.userid = 123
SELECT CASE pw_hash WHEN HashBytes('MD5', pw_salt + @.pw)
THEN 'Valid'
ELSE 'Invalid' END
FROM user_passwords
WHERE userid = @.userid ;
Conventional wisdom has it that the "salt" value limits the
effectiveness of possible password dictionary attacks. The reality in
today's environment though is that it's far more important to have
mechanisms to prevent users picking easy passwords and to monitor
intrusion attempts. Better yet, use asymetric methods of authentication
that don't require a central password store.
David Portas
SQL Server MVP

Encripting a field in a table

I have inherited a database program that has a table for usernames and passwords. Unfortunetly, I must give certain users read only access to the SQL tables. Therefore, the Usernames and the passwords can be viewed. Question isw there a way of encripting this field so that it can't be read?

Thanks in advance!!!not sure i understand the question? You need to give users access to read the data in the table, but you want the data encrypted so it can't be read?|||The SQL DB is the backend and the Front end is a VBA program. I have to give certian users direct access to the tables (backend). I want to still be able to use the user/password table when users access the front end but I want to prevent users from viewing the passwords while they are accessing the tables directly from the SQL DB.|||Hi,

you can use pwdencrypt sql function., but there are certain thing u have to keep in mind..

The field should be nvarchar.
u cannt unencrypt the string after encrypting it.

select pwdencrypt('abc')

this is how u can compare 2 strings -
select pwdcompare('abc',pwdencrypt('abc'))
1 for true
0 for false.

hope this will help u.
Cheers
Gola munjal

Originally posted by Chumpie999typla
I have inherited a database program that has a table for usernames and passwords. Unfortunetly, I must give certain users read only access to the SQL tables. Therefore, the Usernames and the passwords can be viewed. Question isw there a way of encripting this field so that it can't be read?

Thanks in advance!!!