DecryptByPassPhrase Not Decrypting Varchar Columns After Copying A Database
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:
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.
I have a string which I need to know where it came from in a database.I don't want to spend time coding this so is there a ready made scriptwhich takes a string as a parameter and searches all the tables whichcontain varchar type columns and searches these columns and indicate whichtables contain that string?Full text search is not enabled.--Tonyhttp://dotNet-Hosting.com - Super low $4.75/month.Single all inclusive features plan with MS SQL Server, MySQL 5, ASP.NET,PHP 5 & webmail support.
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?
I know this is no problem for anyone who has ever done a bit of programming in SQL, but for me it is a problem because I'm rather new in SQL. My question is: how to copy column in the same table in query? I need move one column to another and then to delete the first column (but not the whole column, just one part so I can't use Copy - Paste in Enterprise Manager). I know how to select the part that I want, all I need now is to copy columns.
Hello to all, this is my first post on this forum, and I hope you'll be able to solve my problem. I'm working in industrial field and only from time to time I have to work with SQL. My problem is the following: I have two tables. Both tables have same number of rows but different number of columns. Table 1 has 5, and table2 has three columns. I need SQL statement to copy two columns A and B from table 1 to table 2 with condition, for example table2.C <> 2?
I don't know if I was clear enough. Basically I need to copy data from two columns of one table to other. Tables have no common data which I can use to join tables. I tried something like this:
update table2 set (A,B)=(select A,B from table 1) where table2.c <>2 but don't know how to use syntax properly...
I am working with a SQL database that was migrated from MS Access and adapted for full-text search which involved creating a new table with a different design. I would like to copy three columns (YearGiven, MonthGiven, DayGiven) from the Documents table to the newly-created FullDocuments table so that I can delete the Documents table and four other tables with redundant data. Here are the two tables involved in the column copy:
FullDocuments Table FullDocID (Primary Key) DocNo SequenceNo SectionText YearGiven (empty €“ no data) MonthGiven (empty €“ no data) DayGiven (empty €“ no data)
After the copy column procedure I want the FullDocuments table structure to look like this:
FullDocuments Table FullDocID (Primary Key) DocNo SequenceNo SectionText YearGiven (full of transferred data) MonthGiven (full of transferred data) DayGiven (full of transferred data)
The problem is that the FullDocuments table contains approximately 4x as many rows as the Documents table. This is because each document has four types €“ the primary Text document and three supplemental documents (Background, Report, and Index). Whereas the Documents table has one row for each Document, the Full Documents table has four rows per document (the four document types). Instead of simply doing a copy and paste of columns based on a primary key and foreign key relationship (as described in the MSDN online books), I need to copy and paste based on the DocNo and SequenceNo columns. Thus the same date data (Year, Month, Day) will need to occupy four consecutive rows for each document in the FullDocuments table (as is now the case with the DocNo and SequenceNo).
I will end up with one table that meets the requirements for the full-text search (primary key and all the text to be searched) and eliminate five tables (Documents and the four document type text tables). This is a static database consisting of historical records so I am not concerned about input errors that can be associated with denormalization.
I tried exporting the data using the Export Wizard using the €śCopy data from one or more tables€? option. The transfer failed. The most relevant lines of the Error Report are:
·Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Cannot insert the value NULL into column 'FullDocumentID', table 'Documents.dbo.FullDocuments'; column does not allow nulls. INSERT fails.". (SQL Server Import and Export Wizard) ·Error 0xc0047022: Data Flow Task: The ProcessInput method on component "Destination - FullDocuments" (61) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. (SQL Server Import and Export Wizard)
·Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0202009. (SQL Server Import and Export Wizard) I then tried the €śWrite a query to specify the data to transfer€? option focusing on just the YearGiven column using various FETCH, INSERT AND UPDATE commands without any luck. Questions:
1. Is the query approach the best way to achieve my objective, or did I miss something in the €śCopy data€? option in the Export Wizard?
2. If the query approach is the best way, any suggestions on what the query will look like?
I have a table that imported as varchar. Most of these columns need to be in a numerical format. How can I convert a table with columns named column0 (needs to be int),column1 (stays varchar), column2(needs to be int), and column 3(needs to be int)?
I'm attempting to use T-SQL to strictly parse/pull Names from a string field like such: CN=John Doe,OU=xyz,DC=ituy,DC=qwer,DC=org...I would like the ultimate result to be JUST the full name John Doe (pretty much everything after the first = sign and before the first comma. I'm attempting combinations of REPLACE, STUFF, PATINDEX and SUBSTRING, but to no avail.
Hi all, I'm new at this SSIS but have been able to successfully create some simple packages. My situation is that at work we use a column to describe a status of applications. However, this makes for hellacious query because some of those statuses inherintly were one or more statuses previously. Example Admit = Admit Accept = Admit then Accept Withdraw Accept = Admit, Accept, then Withdraw Decline = Admit then Decline As you can see inherintly those were all admits at one point. So what I'd like to do is instead of having long queries for example to get all my "Admits", I'd rather query another table that has the following columns as bits: Admit Accept Withdraw That way I can query the admit column and get all my admits. How can I use SSIS to transform my "Decision" column into those bit columns? Thanks for any help or suggestions you have.
I have a table on two different servers, the only difference that I can see is that on server A columns first (varchar 32) and last (varchar 32) have ANSI_PADDING set ON and on server B those columns are OFF. No idea why this is true: I didn't specify that the table be set up this way and they both followed similar creation/upgrade paths. I execute "select last+first from <table>" on server A and the result looks like: <last1> <first1> <last2> <first2> ... On server B I get <last1><first1> <last2><first2> Now the docs say ANSI_PADDING has nothing to do with this behavior; in fact if I copy the data on server B to 2 new columns with ANSI_PADDING ON I get the same results. But that's the *only* thing that was different in syscolumns. What is causing the different output behaviors on these two servers? Thanks.
I have an application with highly compressable strings (gzip encodingusually does somewhere between 20-50X reduction.) My base 350MBdatabase is mostly made up of these slowly (or even static) strings. Iwould like to compress these so that my disk I/O and memory footprintis greatly reduced.Some databases have the ability to provide a compressedtable, compressed column, or provide a user defined function tocompress an indvidual Field with a user defined function[ala. COMPRESS() and DECOMPRESS() ].I could right a UDF with an extended prodcedure if I need to but I'mwondering if there are any other known methods to do this in MS SQLServer 2000 today?--Frederick Staatsfrederick dot w dot staats at intel dot com (I hate junk mail :-)
We have a customer that is running SQL2012 and we are seeing a weird result on a query when we run it on their db. It is based off of a table that has about 30 columns but in this case we only care about 2 of them.
[Number] [varchar](15) NOT NULL [Person_ID] [varchar](12) NULL
Here is the query we are doing: Select Number,Person_ID From TableName where LP='ABC123'
The result I get back is the following: Number:1 Person_ID:13864
The Person_ID should be a result of another table that created that Person_ID but it doesn't exist in that table. So we do not know where that 13864 is coming from. When we open that record through our application it shows Nothing for the Person_ID in that field.
When we do this query on our copy we get back Number:1 Person_ID:
Which is exactly what we should see as the result.
Could there be a sql server setting that is set on their server that could possibly be given us back 13864 for a NULL value?
Hi,This is probably an easy question for someone so any help would beappreciated.I have changed the columns in a table that where nvarchar to the samesize of type varchar so halve the space needed for them.I have done this a) becuase this is never going to be an internationalapplication, b) we are running out of space and c) there are 100million rows.I have done this with the alter table statement which seems to work butthe space used in the database hasn't altered.I'm presuming that the way the records are structured within the tablethere is just now more space free inbetween each page???Is there a way or re-shrinking just an individual table and free upsome of the space in there or am i missing the point somewhere?Thanks in advance,Ian
I know that if I have an nvarchar column I can use an equality like = N'supersqlstring' so it doesn't implicit cast as a varchar, like if I were to do ='supersqlstring'. And then I'll be a big SQL hero and all my stored procedures will run before a millisecond can whisper.
But if I'm comparing an nvarchar column to a varchar column, is it better to cast the varchar 'up' to an nvarchar or cast the nvarchar 'down' to a varchar?
For instance:
cast(a.varchar as nvarchar(100)) = an.nvarchar
or
cast(an.nvarchar as varchar(100)) = a.varchar
Leaving aside non-matching, like (at least I don't think) that SQL considers the varchar n to be equal to the nvarchar Ĺ„, what's the best way to handle this?
Pretend for a moment that each column contains a mixed letter and number ID with no accented or wiggly-squiggly Unicode characters; it's just designs clashing.
Is there a performance hitch doing it one way or another? Should I use COLLATE? Should one of the columns be altered?
create table #myfirst (id int, city varchar(20)) insert into #myfirst values (500,'Newyork') insert into #myfirst values (100,'Ediosn') insert into #myfirst values (200,'Atlanta') insert into #myfirst values (300,'Greenwoods') insert into #myfirst values (400,'Hitchcok') insert into #myfirst values (700,'Walmart') insert into #myfirst values (800,'Madida')
-- My Second Data
create table #mySecond (id int, city varchar(20),Sector varchar(2)) insert into #mySecond values (1500,'Newyork','MK') insert into #mySecond values (5500,'Ediosn','HH') insert into #mySecond values (5060,'The Atlanta','JK') insert into #mySecond values (7500,'The Greenwoods','DF') insert into #mySecond values (9500,'Metro','KK') insert into #mySecond values (3300,'Kilapr','MK') insert into #mySecond values (9500,'Metro','NH')
--Third Second Data
create table #myThird (id int, city varchar(20),Sector varchar(2)) insert into #myThird values (33,'Walmart','PP') insert into #myThird values (20,'Ediosn','DD') select f.*,s.Sector from #myfirst f join #mySecond s on f.city = s.city /* idcitySector 500NewyorkMK 100EdiosnHH */
i have doubt on two things
1) How Can i compare the City names, by eliminating 'The ' at the beginning (if there is any in second tale city) between first and second
2) after comparing first and second if there is no match found in second them want to compare with third table values for those not found
--i tried below to solve first doubt, it is working but want to know any other wasys to do it
select f.*,s.Sector from #myfirst f join #mySecond s on replace (f.city, 'THE ','')= replace (s.city, 'THE ','')
--Expected results wull be
create table #ExpectResults (id int, city varchar(20),Sector varchar(2)) insert into #ExpectResults values (200,'Atlanta','JK') insert into #ExpectResults values (100,'Ediosn','HH') insert into #ExpectResults values (300,'Greenwoods','DF') insert into #ExpectResults values (500,'Newyork','MK') insert into #ExpectResults values (700, 'Walmart','PP') insert into #ExpectResults values (800, 'Madidar','')
How to maintain Database User Permissions when copying the Database from One SQL Server to another(Either through backups or sp_detach). The reason is the login sid is different in the target server and as a result the database user is not able to map to the login existing in the target server. The only way I can correct this is through dropping and recreating the user's again and assign the permissions, or change the system catalog - sysusers to remap the login to the user in the database. I do not wish to use the sp_addalias as it is available only for backward compatibility. Is there a better way of doing this ?
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.
I am trying to copy one database to another using copy wizard for SQL Server 2005. The destination database is on another server/box. I get the following errors when executing the SSIS package: "The job failed. The Job was invoked by User abcd. The last step to run was step 1 (abcd_0_Step).".
"Executed as user: BILLSVRSYSTEM. ...ion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:30:55 AM Progress: 2008-05-05 11:30:56.81 Source: crmtest_BILLSVR_Transfer Objects Task Task just started the execution.: 0% complete End Progress Error: 2008-05-05 11:30:57.34 Code: 0x00000000 Source: abcd_abcd_Transfer Objects Task Description: Failed to connect to server crmtest. StackTrace: at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect() at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.OpenConnection(Server& server, ServerProperty serverProp) InnerException-->Login failed for user 'abcdabcd$'. StackTrace: at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.... The package execution fa... The step failed."
Can this be done? Is there something that needs to be set security wise for this to work?
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
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.
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?
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.
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();
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?
Hi all, I want to copy a database from my SQL Server system and install it on another. Can anyone suggest me how I can copy the same along with log info, login info, permissions, etc. and install it on another system. Thanks!
I am changing my hosting from one company to another company. How can I copy my full database along with views and stored procedures. I have only access to query analyzer and enterprise manager from where I am not able to backup the database on my local computer. As it is very urgent please suggest me a way to do this.
I created a DB on the server. i would like to make a copy of it (structure and data) on my local machine to play with. Then i try using the wizard i get this error:
Your SQL Server Service is running under the local account. You need to change your SQL Server Service account to have rights to copy over the net work.
Where do I go to change my rights to copy over the net work?
