Decrypting And Encrypted Stored Procedure
Sep 6, 2007
Hello,
In SQL 2000,
I have created a Stored Procedure as follows,
Code Snippet
CREATE PROCEDURE MyTest
WITH RECOMPILE, ENCRYPTION
AS
Select * From Customer
Then after this when i run this sp it giving me the perfect results wht i want, BUT when i want to change something in sp then for I am using the below line of code.
Code Snippet
sp_helptext mytest
But its displaying me that this sp is encrypted so you can't see the details and when i am trying to see trhe code of this sp from enterprise manager then also its not displaying me the details and giving me the same error,
So i want to ask that if there is a functionality of enrypting the sp code then is there any functionality for decrypting the Stored Procedure also,
or not,
If yes then wht it is and if NO then wht will be the alternative way for this,
?????
View 2 Replies
ADVERTISEMENT
Sep 19, 2006
Hi all,As all of you are aware you can Encrypt your Triggers/Stored Procedures/Views And Functionsin Sql Server with "WITH ENCRYPTION" clause.recently i came across a Stored procedure on the Net that could reverse and decrypt all Encrypted objects.i personally tested it and it really works.That's fine (of course for some body)Now i want to know is it a Known Bug for Sql Server 2000 and is there a permanent solution for Encrypting mentioned objects.Thanks in advance.Best Regards.
View 2 Replies
View Related
Jan 18, 2012
i have a ms sql base which contains tables encrypted with EncryptByKey, who knows how to make me a script do save the encrypted tables to clear text pm me in ym : hgfrfv or msn : [URL], i have all the keys used on encryption and all those stuff.
View 1 Replies
View Related
May 5, 2015
I am executing a stored procedure in one database (Database1) that pulls data from another database (Database2) that is the back end for a third party application. Some of the fields in that other database are now encrypted. I need to decrypt those fields but since the query is running in a database other than where the data lives (which is also where the symmetric key + cert lives), I am getting the following error: "Cannot find the symmetric key" Below is an example of what I am running in the stored procedure:
OPEN SYMMETRIC KEY [XXXXKey] DECRYPTION
BY CERTIFICATE [XXXX_CERT];
select CONVERT(Varchar(50), DECRYPTBYKEY( <ENCRYPTED FIELD> ))
FROM Database2.dbo.TABLE1
CLOSE SYMMETRIC KEY [XXXXKey];
What do I need to add to Database1 so the stored procedure can decrypt the data it pulls from Database2?
View 5 Replies
View Related
Mar 22, 2001
Hi
I have some encrypted stored procedure. I want to use the output of the encrypted stored procedure insert the output into temp table. is it possible to do. If so please let me know how can I proceed. Thanks in advance
Regards
Ram
View 1 Replies
View Related
Nov 2, 2005
Hi, i has another problem i think you can help me on,
I need to open a encrypted stored procedure, how can i do it????
Thanks.
View 2 Replies
View Related
Oct 19, 2005
How to Decrypt the SQL Encrypted Stored procedure in ASP.NET (i.e.vb code)Pls let me know ASAP.Thanks.
View 1 Replies
View Related
Oct 19, 2004
Hi all...
Im wondering how do you encrypt and decrypt a stored procedure within sql server 2000 ?
i want to be able to encrypt data been inserted / updated using triple des algorithm and then on select / read - decrypt it.
Does anyone know a solution / stored procedure on how to do this? any ideas.
Thanks
Paul..
Im also wondering if there is a away of doing this on the code side without using stored procedures (asp.net c#) i have been able to encrypt and decrpt to a string but unable to do it through the datagrid object (dataset) or a datareader..
Any tips / help would be helpful
Thanks..
View 2 Replies
View Related
Dec 9, 2004
How to decrypt encrypted procedure?Any help is thankful.
View 7 Replies
View Related
Sep 7, 2007
Hey yall
I ran into something interesting today and was wondering how one would do this. I have some 3rd party stored procs and one was kicking out a truncate error so I took it upon myself to investigate the stored proc that was kicking out this error. So when I tried viewing the sp, I received an error:*****Encrypted object is not transferable, and script could not be generated.***** and then it brings up a blank editing screen.
First I believe this was a custom error message as it just doesnt seem like the way SQL Server would have presented it.
So how would someone prohibit viewing of a stored proc like this?
Thanks
View 3 Replies
View Related
Mar 24, 2006
I have several stored procedures, created in a development environment,that I need to move to a 'QA' environment, and then in turn, to variousproduction environments.When I move these stored procedures, I would like to encrypt them,using the 'WITH ENCRYPTION' clause.My question is, how do I copy these stored procedures from developmentto their target SQL server environment in an encrypted state?Up until now, we have been moving them by generating an SQL script andthen executing that script on the target server. I have tried thisusing a script with 'WITH ENCRYPTION' specified within it, but itdoesn't appear to work when I try and execute that script on the targetserver.Any advice would be greatly appreciated.Nick.
View 1 Replies
View Related
Jul 14, 2015
I have a strange situation when I try to execute the same Stored Procedure on servers with different processors. Both servers are running the SQL SERVER 2008 R2 version with all updates.
All updates bios, disk controller, firmware, were applied on the new server.
New Server: (considerable difference in processing time)
Stored Procedure without encryption, runs at about 02:16hs
Stored Procedure with encryption, runs at about 08:00hs
Server Processor:
Intel (R) Xeon (R) CPU @ 2.60GHz E5-2697 v3
Old Server: (There Are No difference in processing time)
Stored Procedure without encryption, runs at about 01:00hs
Stored Procedure with encryption, runs at about 01:00hs
Server Processor:
Intel (R) Xeon (R) CPU @ 2.7GHz E5-2697 V2
In terms of configuration, the server 2, have lower technology, lower bus, lower number of processors.
I believe that combination of Hardware/SO/Sql Server has a potential performance loss when running encrypted SP.
View 3 Replies
View Related
Aug 4, 2006
Hi...
I want to retrieve SQL 2000 Encrypted Column Data From SQL 2005 strored proc. My Stored Procedure was on SQL 2000 and it works fine....Then I restore Database From SQL 2000 to SQL 2005. The Following Statement is on my store proce.
select user_id , Encrypt(user_pass) from OpenRowset('SQLOLEDB','myserver';'sa';'mypass',databasename.dbo.users) as a
The Following Error I get When I execute the above statement.
Msg 195, Level 15, State 10, Line 1
'Encrypt' is not a recognized built-in function name.
Thank you.
Bal.
View 9 Replies
View Related
Nov 1, 2007
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
View 1 Replies
View Related
Mar 3, 2008
Hi all,
I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):
(1) /////--spTopSixAnalytes.sql--///
USE ssmsExpressDB
GO
CREATE Procedure [dbo].[spTopSixAnalytes]
AS
SET ROWCOUNT 6
SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName
FROM LabTests
ORDER BY LabTests.Result DESC
GO
(2) /////--spTopSixAnalytesEXEC.sql--//////////////
USE ssmsExpressDB
GO
EXEC spTopSixAnalytes
GO
I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class
///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance,
Scott Chang
More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
View 11 Replies
View Related
Jun 14, 2006
Hi there,
I am having a table in Sql which has 2 columns which has data in encrypted format. It shows data in junk format (ascii format). But in frontend (tht is in software)it shows data in proper format. Can any one help me in decrypting data.
Thanks,
Regards,
mystical
View 16 Replies
View Related
Sep 8, 2007
I have a SQL Server Login and I forgot the password for it.
Is there any way I can decrypt the password. I don't want to
change the password.
Thanks
Venu
View 8 Replies
View Related
Nov 14, 2014
I am new to work on Sql server,
I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.
Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.
View 1 Replies
View Related
Jan 29, 2015
I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete]
@QQ_YYYY char(7),
@YYYYQQ char(8)
AS
begin
SET NOCOUNT ON;
select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
[Code] ....
View 9 Replies
View Related
Sep 19, 2006
I have a requirement to execute an Oracle procedure from within an SQL Server procedure and vice versa.
How do I do that? Articles, code samples, etc???
View 1 Replies
View Related
Dec 22, 2006
CREATE TABLE TabEncr (
id int identity (1,1),
NonEncrField varchar(30),
EncrField varchar(30)
)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'OurSecretPassword'
CREATE CERTIFICATE my_cert with subject = 'Some Certificate'
CREATE SYMMETRIC KEY my_key with algorithm = triple_des encryption by certificate my_cert
OPEN SYMMETRIC KEY my_key DECRYPTION BY CERTIFICATE my_cert
INSERT INTO TabEncr (NonEncrField,EncrField)
VALUES ('Some Plain Value',encryptbykey(key_guid('my_key'),'Some Plain Value'))
CLOSE SYMMETRIC KEY my_key
OPEN SYMMETRIC KEY my_key DECRYPTION BY CERTIFICATE my_cert
SELECT NonEncrField,CONVERT(VARCHAR(30),DecryptByKey(EncrField))
FROM dbo.TabEncr
CLOSE SYMMETRIC KEY my_key
What is the problem with this code. It works fine , inserting the value encrypted but when i try to decrypt ,it returns a null value. What is missing. I also tried with symmetric key encryption with asymmetric key. Result is same, returns NULL value. I am using SQL 2005
Happy Coding...
View 15 Replies
View Related
Mar 31, 2006
Hi!
I want to encrypt a whole column in my table and I do this with this SQL code:
OPEN symmetric key Sym_Key DECRYPTION BY certificate My_Cert
GO
UPDATE [My_demo].[dbo].[My-DemoList]
SET [Test_crypt] = encryptByKey(Key_GUID('Sym_Key'),[Test])
GO
CLOSE all symmetric keys
GO
And this seems ok but when I want to decrypt it with the view I have created it seems that I get a "rubbish" character between each "real" character.
So my questions is: What am I doing wrong?
Because if I do an insert like this
OPEN symmetric key Sym_Key DECRYPTION BY certificate My_Cert
GO
INSERT INTO [My-DemoList] (Test_crypt) VALUES(encryptByKey(Key_GUID('Sym_Key'),'1234567'))
GO
CLOSE all symmetric keys
And then use my view to look at the decrypted value that I put in its ok.
Many thanks in advance!
View 4 Replies
View Related
Apr 26, 2007
I find it weird when decrypting a column from a baked up database and restoring it to another database. Here's the scenario:
Server1 has Database1.
Database1 has Table1 with two columns encyrpted -- Card Number and SS Number
Encryption and decryption in this Database1 is perfectly fine. Records are encrypted and can be decrypted too.
Now, I tried to backup this Database1 and restore it to another server with SQL 2005 instance called Server2. Of course the columns Card and SS Numbers were encrypted. I tried decrypting the columns using the same command to decrypt in Database1, however, it returns a NULL value
Here's exactly what I did to create the encyprtion and decryption keys on the restored database:
-- Create the master key encryption
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'myMasterPassword'
-- Create a symetric key
CREATE SYMMETRIC KEY myKey WITH ALGORITHM = DES
ENCRYPTION BY Password='myPassword';
Go
-- Create Card Certificate
CREATE CERTIFICATE myCert WITH SUBJECT = 'My Certificate on this Server';
GO
-- Change symmetric key
OPEN SYMMETRIC KEY myKey DECRYPTION BY PASSWORD = 'myPassword';
-- I then verified if the key is opened
SELECT * FROM sys.openkeys
If I create a new database, say Database2 from that Server2, create table, master key, certificate, and symmetric key. Encrpytion and decryption on Database2 will work!
Any suggestions gurus? I tried all searches and help for almost 2 weeks regarding this issue but nobody could resolve this.
Thanks in advance!
faiga16
3 Posts
View 9 Replies
View Related
Oct 2, 2007
I have a OLE DB Source that has a varbinary column of encrypted data. The sorce table is on a hosted SQL Server database where I cannot install a asymetric key. The SSIS package is running on a local SQL Server box that does have the asymetric key installed and working. Can you recommend the best way to transform this column is SSIS using a connection to the local SQL Server box that has the installed public key?
select @encryptedstuff = 0xA19B9F77E5319283311F325D6D29265721A8451148DCD33FA37DF34737C29690BEE35F99972AD7D40F31E8EFE81A30A9B830ABCD1B6BE386462071D67198CE6E52E15FAD84CA62AA35F847948F40B3F8CF4F50D5F2A14D0CCD9FF990F3C1701784F0A8771B93D329144528455937511EF2691BB42A0D4505AC8F9296BF6700801ECE05B102F0CC6DAF204F4EA4C8317AAEDDEC7D83BCD78BA1718C9E55C840AEA280D8BC9CC58D8E05AAE0AE5AC4B7DA0CE7D5DF1DDCAEEA1FB7431ACDF20BBDB2F29ECD744FDEE3D688920E56BEF5508D8224D0DE6AAE8FF944E389D376138885FC4300AFD281C8CC677CDA1762B56D8D1363C7878EAA7A65FC10B8AE168E75
SELECT CONVERT(nvarchar(50),DecryptByAsymKey(AsymKey_ID('rsakey'), REVERSE(@encryptedstuff), N'P4ssw0rd'))
Each row of the OLE DB Source contains a varbinary column with the ecrypted data. I need to to transform that one column and end up with a new record set that contains all of the columns from the OLE DB Source plus a new decrypted column.
Thanks,
Steve
View 4 Replies
View Related
May 2, 2007
I have following problem. I would like to provide to my STP encrypted data and decrypt them inside it. To decrypt the data I'd like to use DecryptByKey. Encryption should be made on the Win32/.NET client.
Unfortunately I cannot find the way to make the data understable between the both worlds. First how to assign the same key on the both side? Do I get the same key from
Code Snippet
create symmetric key MyKey with
algorithm=triple_des,
key_source='abrakadabra'
encryption by password='aaa'
and from
Code Snippet
string Key = "abrakadabra";
byte[] bKey = Encoding.ASCII.GetBytes(Key);
byte[] salt = new byte[8];
RNGCryptoServiceProvider rnd = new RNGCryptoServiceProvider();
rnd.GetBytes(salt);
PasswordDeriveBytes pdb = new PasswordDeriveBytes(bKey, salt);
TripleDESCryptoServiceProvider prov = new TripleDESCryptoServiceProvider();
prov.GenerateIV();
prov.Key = pdb.CryptDeriveKey("TripleDES", "SHA1", 168, prov.IV);
???
I have read somewhere that create symmetric key calls CryptDeriveKey, but I'm not sure.
The next point is the format of the output data. I have noted, that the output from EncryptByKey is 16 bytes longer (after stripping key guid and fixed 0x01000000) than the one from Win32/.NET application. I assume, that it can lie in the inserting of IV as the first block, but should not IV be only 64 bit long in the DES family of algorithms?
Neverthless I have not magaged to perform encrypted communication such way between SQL Server 2005 and client application. Is it possible at all?
View 1 Replies
View Related
Jul 20, 2005
....it's possible without any third party application?I need to recover some encrypted user functions but the sources have beenlost long time ago, someone can help me?--Lav.
View 2 Replies
View Related
Dec 28, 2005
I have a sub that passes values from my form to my stored procedure. The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page. Here's where I'm stuck: Public Sub InsertOrder() Conn.Open() cmd = New SqlCommand("Add_NewOrder", Conn) cmd.CommandType = CommandType.StoredProcedure ' pass customer info to stored proc cmd.Parameters.Add("@FirstName", txtFName.Text) cmd.Parameters.Add("@LastName", txtLName.Text) cmd.Parameters.Add("@AddressLine1", txtStreet.Text) cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue) cmd.Parameters.Add("@Zip", intZip.Text) cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text) cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text) cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text) cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text) cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text) ' pass order info to stored proc cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue) cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue) cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue) 'Session.Add("FirstName", txtFName.Text) cmd.ExecuteNonQuery() cmd = New SqlCommand("Add_EntreeItems", Conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc) <------------------------- Dim li As ListItem Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar) For Each li In chbxl_entrees.Items If li.Selected Then p.Value = li.Value cmd.ExecuteNonQuery() End If Next Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder) and pass that to my second stored procedure (Add_EntreeItems)
View 9 Replies
View Related
Sep 26, 2014
I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure
at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT
I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT
View 3 Replies
View Related
Jan 23, 2006
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
again but this does not resolve the issue.
Thx.
View 6 Replies
View Related
Dec 15, 2005
Hi,
Is there any way of decrypting password value stored in sysxlogins table of SQL database?
Thx in Adv
View 4 Replies
View Related
Mar 17, 2008
In SQL 2005, I've created a test scenario in AdventureWorks where I:
1.) Create a database master key:
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>'
GO
2.) Create a certificate:
CREATE CERTIFICATE HRCert
WITH SUBJECT = 'Comments'
GO
3.) Create a symmetric key:
CREATE SYMMETRIC KEY CommentKey
WITH ALGORITHM = DES
ENCRYPTION BY CERTIFICATE HRCert
GO
4.) Add a test column to the HumanResources.JobCandidate table:
ALTER TABLE HumanResources.JobCandidate
ADD Comments varbinary(8000)
GO
5.) Open the symmetric key for use:
OPEN SYMMETRIC KEY CommentKey
DECRYPTION BY CERTIFICATE HRCert
GO
6.) Insert and Encrypt values ('Yes') to the newly created column:
UPDATE HumanResources.JobCandidate
SET Comments = EncryptByKey(Key_GUID('CommentKey'), 'Yes')
GO
-------
Great. Now all the textbooks say that, to view the column values in a decrypted state, you should:
SELECT CONVERT(varchar, DecryptByKey(Comments)) AS [Decrypted Comments], *
FROM HumanResources.JobCandidate
------
Great, I get it. But here's the rub. What is the best way to permanently decrypt the column and keep it in a cleartext state?
Running the following works, but keeps the column values in a varbinary (hexadecimal) state which is what we originally created:
UPDATE HumanResources.JobCandidate
SET Comments = DecryptByKey(Comments)
GO
But if I want to transform this varbinary(8000) column into a human-readable varchar column, the only thing I could come up with was a temp table solution:
UPDATE HumanResources.JobCandidate
SET Comments = DecryptByKey(Comments)
GO
DECLARE @temp varchar(1000)
SET @temp = (SELECT TOP 1 CommentsFROM Human Resources.JobCandidate)
CREATE TABLE #decrypt (Comment varchar(1000))
INSERT INTO #decrypt (Comments) VALUES (@temp)
GO
ALTER TABLE HumanResources.JobCandidate
ALTER COLUMN Comments varchar(1000)
GO
UPDATE HumanResources.JobCandidate
SET Comments = (SELECT Comments FROM #decrypt)
GO
DROP TABLE #decrypt
GO
--------
It works, but seems so inelegant to me.
Is there an easier way?
View 5 Replies
View Related
Jan 18, 2007
I have an encrypted column of data that is encrypted by a passphrase. The passphrase was encrypted by a symetric key in a key pair. The passphrase also is stored in a table. I can get the passphrase as needed to encrypt/decrypt the columns. I copied the production database to a new database for development. Subsequently I had to create a new symmetric/asymmetic key pair and recreated my passphrase with the new key pair. Now the passphrase will decrypt a text column but it will not decrypt two other columns which are of type varchar in the database. Here is an example:
DECLARE @pss varchar(30)
EXEC [dbo].[uspPassPhraseGet] @pss OUTPUT
SELECT DISTINCT contactid, uissueid, createdby, created_dt
,CONVERT(varchar(max),DecryptByPassPhrase(@pss, CONVERT(varchar(max),dbo.tbl_msg_app_legislativeinquiry.title), 1, CONVERT(varbinary, 23))) as title
,CONVERT(varchar(max),DecryptByPassPhrase(@pss, CONVERT(varchar(max),dbo.tbl_msg_app_legislativeinquiry.description), 1, CONVERT(varbinary, 23))) as description
,CONVERT(varchar(max),DecryptByPassPhrase(@pss, CONVERT(varchar(max),dbo.tbl_msg_app_legislativeinquiry.shortdesc), 1, CONVERT(varbinary, 23))) as shortdesc,
closed_dt, confidential, statusid, due_dt, deleted_dt,deletedbyid, highrisk, dbo.tbl_msg_app_legislativeinquiry.designator, dbo.tbl_ref_sys_status.description AS statusdesc
FROM dbo.tbl_msg_app_legislativeinquiry INNER JOIN
dbo.tbl_ref_sys_status ON statusid = dbo.tbl_ref_sys_status.ustatusid INNER JOIN
dbo.tbl_gbl_lkp_security ON uissueid = dbo.tbl_gbl_lkp_security.msgid AND
dbo.tbl_msg_app_legislativeinquiry.designator = dbo.tbl_gbl_lkp_security.designator
Like I said I can execute the uspPassPhraseGet stored procedure and I get my passphrase. It will correctly decrypt the dbo.tbl_msg_app_legislativeinquiry.description field which is great but the other two fields will not decrypt. When i copied the database over the encrypted fields do not display the same on the new database. The old database shows a box character followed by a bunch of junk (as expected). The new copied table on the new database shows only a single box (not the same as the original). Is there a known bug with copying a table with varchar fields that are encrypted to a new database? I tried to run a test and got the same result. I also tried to convert the varchar columns to text to see if that solved the problem and it didn't. The description field however is a text type column and it reads exactly as the original. The problem I think is that the Copy Database didn't actually copy my data correctly. How can I get the original encrypted data from the production into my development. I also tried just dropping the table and reimporting the table but that didnt take either. Scratching my head on this one.
View 5 Replies
View Related
Mar 28, 2007
I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.
How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?
Thanks in advance
View 9 Replies
View Related
