linked server with mysql, type conversion error

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 2005 Forums

SSIS and Import/Export (2005)

linked server with mysql, type conversion error

Author

Topic

bigbelly
Starting Member

39 Posts

Posted - 2008-07-14 : 04:44:17

Hi

I have sql server 2005 linked with mysql. In mysql, there is a table with 2 columns, column A is type varchar with encrypted credit card information and the other B is type blob with encrypted CC information as well. I have no problem with decrypt column A using following code:

SELECT DecryptA FROM OPENQUERY(linkedmysql,  'SELECT AES_DECRYPT(A,''password'') as 'DecryptA' FROM MysqlTable)

However when I try the same way with column B. It gives error: "OLE DB provider "MSDASQL" for linked server "linkedmydql" returned message "Requested conversion is not supported.".

BTW, I tried to cast to char/varchar when I'm calling function AES_DECRYPT in the query, all with no luck. If I run the query directly on mysql's query analyer, I have no problem with column B as well.

Is it because SQL server doesn't have type blob? Did anyone encounter the same issue before?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-14 : 05:11:35

Were you trying to cast it to varchar(max)?

bigbelly
Starting Member

39 Posts

Posted - 2008-07-14 : 21:41:01

Sorry, I mean I tried

SELECT DecryptB FROM OPENQUERY(linkedmysql,  'SELECT CAST(AES_DECRYPT(B,''password'') AS CHAR) as 'DecryptB' FROM MysqlTable)

AND

SELECT DecryptB FROM OPENQUERY(linkedmysql,  'SELECT AES_DECRYPT(CAST(B AS CHAR),''password'') as 'DecryptB' FROM MysqlTable)

BTW, our mysql is version 4.0.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-07-14 : 22:28:07

Did you try queries without AES_DECRYPT function?

ytsun
Starting Member

9 Posts

Posted - 2008-07-14 : 23:26:54

It does work if like this:
SELECT DecryptB FROM OPENQUERY(linkedmysql, 'SELECT B as DecryptB FROM MysqlTable')

Even dont need cast().

Could sqlserver decrypt the code which encrypt by mysql?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-07-15 : 22:46:59

Don't think so, it doesn't even decrypt data that encrypted by some sql server's encryption function.

bigbelly
Starting Member

39 Posts

Posted - 2008-07-16 : 02:00:26

Actually what I plan to do is: Use mysql's AES_DECRYPT()function to decrypt the encrypted CC data from mysql DB. And then use SQL server's EncryptByPassPhrase() function to encrypt those decrypted CC data and insert them into SQL server's table.

I tried to use SSIS and it works. But why it doesn't work with linked server when mysql side column type is blob?

bigbelly
Starting Member

39 Posts

Posted - 2008-07-21 : 01:40:00

solved by using OpenRawSet instead of linked server.

Berggy
Starting Member

3 Posts

Posted - 2008-08-07 : 13:13:27

Sir BigBelly:
How did you link your SQLServer2005 to mysql?
as in which provider did you use when youo did the link?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-08-07 : 22:36:47

You can use mysql odbc driver.