Re: SQL Server Encrypt, Failure..

From: Bill Dodd (codergeek_at_gmail.com)
Date: 07/19/04 

Date: 19 Jul 2004 10:45:17 -0700

I found the answer to my question (well, sort of) in case anyone else
runs into this stuff.. I'm still not sure why function quit working
but I found a way to work with the password after they've been
"encrypted" with this function. This function doesn't encrypt at
all, and -against popular belief- it doesn't generate a tradition
hash. All the function really does is cast the varchar value into a
varbinary value..
(Not that I had too high of expectations for a undocumented SQL
Function from circa SQL 6.5 or below =) )
Here's the proof:

Declare @Password1 varchar(50)
Declare @Password2 nvarchar(50)
Declare @PassBinary binary(25)

Set @Password1= 'ABC'
Set @PassBinary=Encrypt(@Password1)
Set @Password2=Cast(@PassBinary as nvarchar(50))
Select @Password1
Select @PassBinary
Select @PAssword2

Bill Dodd

codergeek@gmail.com (Bill Dodd) wrote in message news:<4092be5a.0407141220.3b469ac0@posting.google.com>...
> We just aren't sure if anything has been done to that box.
> I am starting to become increasingly aware of the unsupported nature
> of that function. Unfortunately, we have 4,000 users implementing
> that password encryption method. Sounds like we should look into
> retooling that to something we have more explicit control of.
>
> Thanks for the reply!
> Bill Dodd
>
>
> "Brian Moran" <brian@solidqualitylearning.com> wrote in message news:<#Igh1iaaEHA.3752@TK2MSFTNGP12.phx.gbl>...
> > Have you added a hot fix or service pack recently?
> >
> > Those functions are NOT supported and do change from time to time in SP's.
> >
> > --
> >
> > Brian Moran
> > Principal Mentor
> > Solid Quality Learning
> > SQL Server MVP
> > http://www.solidqualitylearning.com
> >
> >
> > "Bill Dodd" <codergeek@gmail.com> wrote in message
> > news:4092be5a.0407140532.57f4ff3d@posting.google.com...
> > > Has anyone seen the Encrypt() function of T-SQL just arbitrarily stop
> > > working?
> > > We have some stored procedures that use the encrypt function to store
> > > passwords in user table and mid-afternoon - after seemingly nothing
> > > changed - the SP that verified those password no longer correctly
> > > worked.
> > >
> > > It would be nice to be able to reverse engineer a few of the passwords
> > > to see if the Encrypt() function is now screwed up or find out what is
> > > really being put in the field. I seem to read that the encrypt()
> > > function isn't an overly secure hash, so is there anything I can do to
> > > take a look at that? Has anyone heard of this function acting flaky?
> > >
> > > There seems to be limited support for it on the internet and honestly
> > > - I didn't know it existed before I saw this SP.
> > >
> > > Thanks Again!
> > > Bill Dodd

Relevant Pages

  • RE: local admin account password
    ... > encrypt the database and create alerts in the event of unsuccessful ... >> no more recovery console and don't think cached logins will work. ... >> The DB file would be encrypted with EFS so only the limited user SQL ... >> itself doesn't really need to be secure as the authentication is based ...
    (Focus-Microsoft)
  • Re: Profiler Bug viewing trace flat file?
    ... Kalen Delaney, SQL Server MVP ... Run this code to create a trace capture of the RPC:Complete event: ... declare @error INT ... Now open the trace file in Profiler and you will see that the Textdata ...
    (microsoft.public.sqlserver.tools)
  • Re: Sync production db with test
    ... Declare @strTableSchema Nvarchar ... DECLARE @SQL varchar ... CREATE TABLE #SQLtemp ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL Server 2000 / 2005 Encryption
    ... encrypting the connection makes sense to me. ... This is possible on SQL 2005 via built-in T-SQL statements and functions, but requires 3rd-party tools, usually some sort of extended procedures. ... to encrypt your SSL connections will be different. ... Excel does not know how to decrypt data stored in SQL Server 2005 in encrypted form. ...
    (microsoft.public.sqlserver.security)
  • Re: Performance Issues With Query
    ... SQL Server MVP ... > DECLARE @Timestamp datetime ... > DECLARE @CallEventName varchar ... > UPDATE CallByCallYesterdayFinal SET Script = SUBSTRING(@Destination, ...
    (microsoft.public.sqlserver)