Join an encrypted field to a non encrypted field

[gtjr921]

I have a Sql 2005 Table that has an encrypted field by Asymmetric encryption.
Data is imported to this table via an Access DB.
That data has sensitive data that is not encrypted.
in order to properly import the data the Stored Procedure in Sql checks for Sensitive (unencrypted) data in the access db and trys to match it with the encrypted data.

For some reason I just get null data, if i query the sql db and the access db separately i get the proper data.
I need it to Join so that data from access can be imported
Here are my queries

--working select query
select convert (Nvarchar(50),
DecryptByasymKey(Asymkey_ID('MYAsymmetricKey'),
MYEncryptedfield,N'MyASMPword')) As MYENCField
 from MySqlTable

--Broken Join to Access just gives null data
SELECT Access.MyUnEncryptedField,getdate()
FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0','c:\accessfield.mdb';
'admin';'',AccessTbl)
 as Access Inner Join SQLDB.dbo.MyInfo as MySQLDB On convert (Nvarchar(50),			DecryptByasymKey(Asymkey_ID('MYAsymmetricKey'),			mySQLDB.MyEncryptedField,N'ASMPword')) = Access.MyUnEncryptedField

 

[SQLDenis]

Does this work?

select * from (select convert (Nvarchar(50),
DecryptByasymKey(Asymkey_ID('MYAsymmetricKey'),
MYEncryptedfield,N'MyASMPword')) As MYENCField
 from MySqlTable) z join
(SELECT Access.MyUnEncryptedField,getdate()
FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0','c:\accessfield.mdb';
'admin';'',AccessTbl)
 as Access ) as a
on a.MyUnEncryptedField  = z.MYENCField

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[gtjr921]

No I still get null results

 

[SQLDenis]

do you have trailing spaces anywhere?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[gtjr921]

I do not believe so

 

[gtjr921]

I have been using this stored procedure, it has been working fine. I just recently encrypted the SQL Field and was trying to change the SP to decrypt the field to make the access DB.