Joing 2 Tables On An Encrypted Column
Mar 18, 2008
Hi all,
I am trying to join 2 tables in my database on an encrypted column. I am using a symmetric encryption, but because of the IV, a string encrypted at 2 different times produces 2 different encrypted strings. Therefore, I can not merely join the 2 tables on the encrypted columns. Has anyone any insight on techniques for solving this problem? The most naïve way, would be to decrypt both tables, join them, and re-encrypt them again. But, the tables are quite large, and this would be very processor (and time) intensive.
Any help would be greatly appreciated. Thanks in advance for any suggestions.
View 3 Replies
ADVERTISEMENT
Mar 11, 2008
I need a bit of help writting a select statment and joining 4 differant talbes. I have used inner joins between two different tables before but never more then two tables.
Table layout (listing only the columns I want to pull):
Abbrevations.Short
Abbrevations.Long
Avatars.Creator
Avatars.AvatarName
Avatars.FileName
Avatars.Game
Avatars.Section
Avatars.Width
Avatars.Height
FanInfo.Crator
FanInfo.ContactMethod
FanInfo.Address
FanRattings.Cration
FanRattings.Rating
How they relate:
Abbrevations.Short = Avatars.Game
Avatars.Creator = FanInfo.Creator
Avatar.AvatarName = Ratings.Cration
Any help would be really helpful. Thanks
--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia
View 3 Replies
View Related
Apr 2, 2008
hi,
i have a table(Measurement) with the following columns
SensorId,Maximumvalue,MinimumValue,GroupId,SystemId,DeviceId.
so in the above column SensorId is ForeignKey of the Table Sensor which havs two columns(SensorId,SensorName)
so in the above column GRoupId is ForeignKey of the Table Group which havs two columns(GroupId,GroupName)
SystemId is ForeignKey of the Table System which havs two columns(SystemId,SystemName)
DeviceId is ForeignKey of the Table Device which havs two columns(DeviceId,DeviceName)
so my result should be as like as follows:
SensorId,SensorName,Maximumvalue,MinimumValue,GroupName,SystemName,DeviceName.
instesd of displaysing GroupId, SystemId,DeviceId i need to diplay the SensorName,GroupName,SystemName,DeviceName. so please give me some example query to join multiple tables please
View 2 Replies
View Related
Mar 21, 2006
I have two tables I need to join but there are 2 fields which theycould be joined on.Using the example Tablles, TableA and TableB below;TableAID1 ID2 Qty1 Null 42 A 5Null B 6TableBID1 ID2 QtyNull A 63 B 64 Null 7Null C 8I want to create TableC which will look like this;ID1 ID2 TableA.Qty Tableb>Qty1 Null 4 Null2 A 5 63 B 6 64 Null Null 7Null C Null 8Any ideas?Regards,Ciarán
View 2 Replies
View Related
Feb 28, 2002
I need to write a sql query which is a master-detail query. Here's the example structure of tables:
Master table:
ColID as longint, ColA as int, ColB as int, ColPartID as longint, ColPartName as longint
Child table -- Wheel:
ColID as longint, ColA as int, ColB as int
Child table -- Door:
ColID as longint, ColA as int, ColB as int
Child table -- Window:
ColID as longint, ColA as int, ColB as int
..... etc
From the master table, it needs to join with its child in order to get the detailed information. However, there're more than one child table for it to join. In other words, the query has to choose the correct child table to join for each row selectively. The use of correct child depends on one of the columns in its master table (ColPartName).
My question is: Does it worth of me keep finding a solution for this query or should I abandon this? I really need some advice, please.
Many thanks,
Leonard
View 1 Replies
View Related
Aug 17, 2006
To do this successfully do I need to backup the Service master, Database master, and database itself from the the Source server, then restore all three of them on the destination server?
(I'm concerned that restoring the source Service Master key to a new target server with an existing sql 2005 install will screw things up big time.)
TIA,
Barkingdog
View 1 Replies
View Related
Apr 28, 2000
Hi there,
I was wondering if anyone knew of a way to encrypt a table?
I now you can encrypt Stored Procedures, triggers and view, but
can you encrypt a table??
Let me know
Thanks
Susan
View 1 Replies
View Related
May 16, 2007
Is it possible to do a wildcard search on an encrypted column?
Many thanks!
View 6 Replies
View Related
Jul 11, 2011
I have a table having 1 column whose value have to update,the column is encrypted using symmetric encryption. Value in column is 0 in char form, I have to increment it, when retrieving it return 0 to increment i have to convert it to int,it becomes 48 after increment it is 49 after conversion to char it becomes 1. It is going right upto 9 after 10 it jumps to very large number on increment.
View 5 Replies
View Related
May 14, 2015
I am trying to run a test migration of our application from V8 to V9. To do this I am creating an empty database by running the Tasks -> Script database
Once that is done, I restore it to our dev server and run SQLCompare against it to generate the change scripts
The problem I am facing is that there are a few tables that have encrypted triggers on them and I cannot include them in the generate scripts. I want to take the table structure, including indexes, but I am not interested in the triggers. There are only 5 tables so I could script them by hand, but this means that I cannot automate the process.
As a result of the missing tables SQLCompare will try to generate a CREATE TABLE rather than an ALTER TABLE DDL
The live migration won't be a problem because this will be a database restore and migration - my problem is the CREATE TABLE DDLs in the script.
View 6 Replies
View Related
Jun 2, 2006
Dear All,
I inserted a record in table on DB created on SQLServer 2005 and found out that the one of the column values is shown as '??????' instead of showing the encrypted value that I sent with the insert statement.3
............................ Can anyone tell me how to get rid of this?
Thanks and regards,
Z Z.
View 5 Replies
View Related
Jan 25, 2007
Hi,
I have database with encrypted column data in my customer server. Sometime, I might need to backup their database back to office for troubleshooting.
How could I backup/restore master key, symmetric and asymmetric key created for my database?
Thank you
View 6 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
May 11, 2015
I want to enforce a unique constraint on a column which must be encrypted in MSSQL 2005 using Cell Level Encyption (CLE).
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'itsaSECRET!!!3£3£3£!!!'
   CREATE CERTIFICATE ERCERT WITH SUBJECT = 'A cert for use by procs'
   CREATE SYMMETRIC KEY ERKEY
   WITH ALGORITHM = AES_256
   ENCRYPTION BY CERTIFICATE ERCERT
[Code] ....
The output makes it obvious why the constraint has 'not' been enforced.
       Email
      -------
   1 | 0x00703529AF46D24BA863A3534260374E01000000328909B51BA44A49510F24DF31
C46F2E30977626D96617E2BD13D9115EB578852EEBAE326B8F3E2D422230478A29767C
   2 | 0x00703529AF46D24BA863A3534260374E01000000773E06E1B53F2C57F97C54370FECBB45B
C8A154FEA5CEEB9B6BB1133305282328AAFAD65B9BDC595F0006474190F6482
   3 | 0x00703529AF46D24BA863A3534260374E01000000C9EDB1C83B52E60598038D832D34
D75867AB0ABB23F9044B7EBC76832F22C432A867078D10974DC3717D6086D3031BDB
But, how do I work around this?
View 8 Replies
View Related
Apr 30, 2007
I find that joining to the same table twice is OK, but as soon as you do it 3 or more times you get a massive performance hit.
Does anyone know the reason for this? Whats special about 3?
What's the best approach to do this sort of thing?
(I've used the SQL Server 2005 Tuning Advisor to add indexes for the query).
Rather than:
Select ..., sum(a1.<column>), sum(a2.<column>), sum(a3.<column>) from master_table
left join table_1 a1 on ...
left join table_1 a2 on ...
left join table_1 a3 on ...
group by ...
I have to select all the table and filter it using case:
Select ...,
sum(case when table_1.<column> = '...') as a1,
sum(case when table_1.<column> = '...') as a2,
sum(case when table_1.<column> = '...') as a3,
from master_table
left join table_1
group by ...
View 1 Replies
View Related
Feb 23, 2006
Hi All,Bit of a newbie question i'm afraid, so sorry if this is a reallystupid question,I am not sure if I am trying to do this in the right place as I am sureSQL Server has a far better way of doing thisWhat I am trying to do is join the contents of several columns andpresent the entry into a new column. the entries of all the columns Iam trying to join are different types such as dates and numbers. I wantto join all columns as a text string. To make it just a even moretricky I wish to insert a # between all fields.Just in case my poor description has got you thinking What the F*&Khere is an example of what I am trying to doC1 C2 C3 C4 C5Ian 1234 22/02/2006 123456789 Ian#1234#22/02/2006#1234567890I have been trying to do this in the Formula in the design veiw of thetable.Any Advice would be fantastic.Many ThankIan
View 1 Replies
View Related
Jan 26, 2006
hi there,
i have the following joining table (many-to-many relationship)...
CREATE TABLE [dbo].[products_to_products_swatch] (
[products_to_products_swatch_id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[product_id] [int] NOT NULL ,
[products_swatch_id] [int] NOT NULL
) ON [PRIMARY]
GO
question: do i need to include a primary key in this table - being that it is a joing table?
thanks
mike
View 2 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
Mar 2, 2008
Does anyone have a script that can drop the Identity columns from all the tables in a database? Thanks
View 1 Replies
View Related
Oct 14, 2015
I have the following table
Table Name EmployeeInformation
EmployeeID EmployeeFirstName EmployeeLastName
  1       |John            |Baker
  2       |Carl             |Lennon
  3       |Marion           |Herbert
Table Name PeriodInformation
PeriodID PeriodStart PeriodEnd
  1     |1/1/14    |12/30/14
  2     |1/1/15    |12/30/15
[code]...
I want a query to join all this tables based on EmployeeID, PeriodID and LeaveTypeIDÂ sum of LeaveEntitlement.LeaveEntitlementDaysNumber based on LeaveTypeID AS EntitleAnnaul and AS EntitleSick and sum AssignedLeave.AssignedLeaveDaysNumber based on LeaveTypeIDÂ Â AS AssignedAnnaul and AS AssignedSick and subtract EntitleAnnaul from AssignedAnnual based on LeaveTypeIDÂ Â AS AnnualBalance and subtract EntitleSick from AssignedSick based on LeaveTypeIDÂ Â AS SickBalance
and the table should be shown as below after executing the query
EmployeeID, EmployeeFirstName, EmployeeLastName, PeriodID, PeriodStart, PeriodEnd, EntitleAnnual, AssignedAnnual, AnnualBalance, EntitleSick, AssignedSick, SickBalance
View 4 Replies
View Related
May 13, 2008
Hi,
I need to create a table (Named as C) with a foreignkey column. That column should references with a primarykey column in table A and a primarykey column in table B. Is this possible?
Thanks in advance.
ramesh.p
View 1 Replies
View Related
Apr 9, 2007
when we built the login control by built in login control of vs2005 the passwords are saved in the database which is automatically created.how can i get the unencrypted form of these passwords from database.
View 2 Replies
View Related
Jan 27, 2005
Hi all,
I may have a requirement to send data from a SQL Server at site A to an Oracle server at site B. These sites have no network connection between them, and the current suggestion is to use ftp, but the transfer (or username and password) will not be encrypted.
If I create a DTS package transferring data from site A, will that transfer be encrypted?
If not, is there an option with SQL Server DTS to ensure that the data is sent in an encrypted form?
Thanks in advance.
View 9 Replies
View Related
Feb 23, 2004
Hi,
How can I save encryped data in SQL2000? Also, how can I retrieve it?
View 2 Replies
View Related
Jan 8, 2008
Hi,
I wonder if it is possible to retrieve encrypted password somehow?
Like I have saved password (blueRays) in encrypted formate. How can I retrieve it "where password='blueRays' "?
Regards
View 4 Replies
View Related
Jul 20, 2005
In a Multi Server Job under SQL Server Agent (MSX), we have a series ofsteps like:DTSRun /~Z0x8E8635E6BBA...The ~Z means it's an encrypted hexidecimal string. The person who createdthis step is no longer accessible, and the string can't be triviallydecrypted, so I don't know what this runs.Someone once mentioned that I could go to Query Analyzer, run it, andwatch the execution plan. However, if I start the job, the executionplan simply walks through the steps required to find the job, and doesn'tgo into the details of the job. I don't know how to run the DTSRuncommand directly within Query Analyzer.Is there a way I can use the execution plan to determine what package thisDTSRun line is executing? Please give me the appropriate command orcommands.-Adam
View 1 Replies
View Related
May 9, 2007
Can anyone tell me how to find out if a .sdf file is encrypted? Im using C# and would like to know if the db that I am trying to open is encrypted. I would then like to attempt to open the db.
Any help would be appreciated!
View 1 Replies
View Related
Dec 13, 2007
Hi All,
I have mirroring set up in a test environment and it works great however I need to know/understand how the endpoints are encrypted. I have them set to use the AES algorithm; however I can not tell from BOL what keys they are using. I know the service master key is the root encryption key and endpoints sit at the server level (same level as service masterkey) but I am not sure if the endpoints use it to perform the encryption or not; do they and if not what do they use?
Thanks in advance,
Coleman
View 3 Replies
View Related
Jun 15, 2007
I have 2 tables and I would like to somehow merge them and have an ID (primary key) automatically assigned while its being merged.
Ive tried exporting the tables into MS Access and doing it there but these tables have a HUGE amount of data in them and it basically kills whatever machine its ran on.
View 2 Replies
View Related
Oct 16, 2006
How can I find all the tables with a specific column name?
View 3 Replies
View Related
Mar 7, 2005
Is there a way that you could get the column names for each table in a database using 1 query?
something like:
tbl colname
t1 catID
t1 catName
t2 prodID
t2 prodDesc
t3 cartID
...
...
I know it would be long, but I would just be searching through the saved output for specific names.
View 1 Replies
View Related
Oct 19, 2006
hello I am trying to get a distinct on one column and 2 tables but it doesnt work
Table1
ID_Table1
Name1
Number1
Table2
ID_Table2
ID_Table1
Name2
I want to get : ID_Table1, DISTINCT(Name1), Name2
WHERE Name1 LIKE 'A%'
how can I do it ?
thank you
View 4 Replies
View Related
Apr 13, 2012
I have a requirement where one column of a table is supposed to have two foreign key constraints i.e. it can be referring to two different columns in two tables.
View 1 Replies
View Related
