SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 table columns decryptbyphrase
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bumba
Starting Member

5 Posts

Posted - 11/20/2013 :  03:51:42  Show Profile  Reply with Quote

hello everyone,

this is my first time in this forum.

i have a table in sql server 2008R2 whose columns have been encrypted.

kindly assist me on how i can decrypt the data.

the following is the stored procedure of the table:


USE [BM]
GO
/****** Object: StoredProcedure [dbo].[sp_subsUpdate] Script Date: 11/20/2013 11:44:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[sp_subsUpdate]
(
@CUSTID nvarchar(14),
@EMAIL nvarchar(50)=NULL,
@MPIN nvarchar(5)=NULL,
@TELEPHONE nvarchar(10)=NULL,
@TELEPHONE2 nvarchar(10)=NULL,
@STAFF bit=NULL,
@LARGEDEBITALERT money= NULL,
@LARGECREDITALERT money= NULL,
@MAXDEBITLIMITPERDAY money=NULL,
@ACTIVE bit= NULL,
@BLOCKED bit= NULL,
@PREFLANGUAGE nvarchar(50) = NULL,
@MPINDELIVERY nvarchar(50)=NULL,
@MPINCHANGEDATE datetime =NULL,
@SMS bit= NULL,
@USSD bit= NULL,
@JAVA bit= NULL,
@ANDROID bit= NULL,
@BBERRY bit=NULL,
@IPHONE bit=NULL,
@WAP bit=NULL
)
AS
UPDATE subscribers
SET
EMAIL=COALESCE(ENCRYPTBYPASSPHRASE('b53d261a-5436-48e0-bb59-d1b77cfaa826',@EMAIL),EMAIL),
MPIN=COALESCE(ENCRYPTBYPASSPHRASE('b53d261a-5436-48e0-bb59-d1b77cfaa826',@MPIN),MPIN),
TELEPHONE=COALESCE(ENCRYPTBYPASSPHRASE('b53d261a-5436-48e0-bb59-d1b77cfaa826',@TELEPHONE),TELEPHONE),
TELEPHONE2=COALESCE(ENCRYPTBYPASSPHRASE('b53d261a-5436-48e0-bb59-d1b77cfaa826',@TELEPHONE2),TELEPHONE2),
STAFF=COALESCE(@STAFF,STAFF),
LARGEDEBITALERT=COALESCE(@LARGEDEBITALERT,LARGEDEBITALERT),
LARGECREDITALERT=COALESCE(@LARGECREDITALERT,LARGECREDITALERT),
MAXDEBITLIMITPERDAY=COALESCE(@MAXDEBITLIMITPERDAY,MAXDEBITLIMITPERDAY),
ACTIVE=COALESCE(@ACTIVE,ACTIVE),
BLOCKED=COALESCE(@BLOCKED,BLOCKED),
PREFLANGUAGE=COALESCE(@PREFLANGUAGE,PREFLANGUAGE),
MPINDELIVERY=COALESCE(@MPINDELIVERY,MPINDELIVERY),
MPINCHANGEDATE=COALESCE(@MPINCHANGEDATE,MPINCHANGEDATE),
SMS=COALESCE(@SMS,SMS),
USSD=COALESCE(@USSD,USSD),
JAVA=COALESCE(@JAVA,JAVA),
ANDROID=COALESCE(@ANDROID,ANDROID),
BBERRY=COALESCE(@BBERRY,BBERRY),
IPHONE=COALESCE(@IPHONE,IPHONE),
WAP=COALESCE(@WAP,WAP)

WHERE (CUSTID=@CUSTID )


thanks and regards,

Bumba.

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 11/20/2013 :  04:07:13  Show Profile  Reply with Quote
have you try DECRYPTBYPASSPHRASE ?

http://technet.microsoft.com/en-us/library/ms188910.aspx


KH
Time is always against us

Go to Top of Page

bumba
Starting Member

5 Posts

Posted - 11/20/2013 :  06:04:00  Show Profile  Reply with Quote
KH,

thanks for your reply, i have tried that but with no success.

thanks and regards,

Bumba!
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 11/20/2013 :  09:20:41  Show Profile  Reply with Quote
try

select convert(nvarchar(50), DECRYPTBYPASSPHRASE ( 'b53d261a-5436-48e0-bb59-d1b77cfaa826' , EMAIL ) )
from   subscribers



KH
Time is always against us

Go to Top of Page

bumba
Starting Member

5 Posts

Posted - 11/20/2013 :  13:04:57  Show Profile  Reply with Quote

thanks KH, this worked!!. much appreciated


regards,

Bumba
Go to Top of Page

bumba
Starting Member

5 Posts

Posted - 11/20/2013 :  16:30:40  Show Profile  Reply with Quote

KH,

the result of the query you provided was : 'kipet@yahoo.com', but then from the same query , how do i update the table and set the value of the resulted raw to a different email.. say bumba@gmail.com ?

thanks and regards,
Bumba.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 11/20/2013 :  19:47:18  Show Profile  Reply with Quote
you mean update the table with a different email without encrypt it ?

update subscribers
set    EMAIL = 'bumba@gmail.com'
where  . . .



KH
Time is always against us

Go to Top of Page

bumba
Starting Member

5 Posts

Posted - 11/21/2013 :  01:29:59  Show Profile  Reply with Quote

KH,

i mean update with decrypt; modify the update statement of the above stored procedure so that when i open the table 'subscribers' the records should not be encrypted.

regards,

Bumba.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 11/21/2013 :  03:57:43  Show Profile  Reply with Quote

update s
set    EMAIL = convert(nvarchar(50), DECRYPTBYPASSPHRASE ( 'b53d261a-5436-48e0-bb59-d1b77cfaa826' , EMAIL ) )
from   subscribers s



KH
Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000