table columns decryptbyphrase - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

table columns decryptbyphrase

Author

Topic

bumba
Starting Member

5 Posts

Posted - 2013-11-20 : 03:51:42

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)

17689 Posts

Posted - 2013-11-20 : 04:07:13

have you try DECRYPTBYPASSPHRASE ?

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

KH
[spoiler]Time is always against us[/spoiler]

bumba
Starting Member

5 Posts

Posted - 2013-11-20 : 06:04:00

KH,

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

thanks and regards,

Bumba!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-11-20 : 09:20:41

try


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

KH
[spoiler]Time is always against us[/spoiler]

bumba
Starting Member

5 Posts

Posted - 2013-11-20 : 13:04:57

thanks KH, this worked!!. much appreciated

regards,

Bumba

bumba
Starting Member

5 Posts

Posted - 2013-11-20 : 16:30:40

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.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-11-20 : 19:47:18

you mean update the table with a different email without encrypt it ?


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

KH
[spoiler]Time is always against us[/spoiler]

bumba
Starting Member

5 Posts

Posted - 2013-11-21 : 01:29:59

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.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-11-21 : 03:57:43

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

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources