.NET 1.1 and .NET 2 Perceives DB data differently

I'm hoping someone can help me out - at least by pointing me to who I can ask, if not answering the question directly.

I have some encrypted values in a SQL Server 2000 database that I unencrypt and use in a website that I just converted from .NET 1.1 to .NET 2.

The data is pulled from the database using standard ADO with no changes between the .NET 1.1 version and the .NET 2 version - yet for some data entries, when the identical value is pulled by the .NET 2 code it is changed or shorted.

For example:

1.1 code traces out a value pulled from the db as:

??淨琳d搈把???媑????????

The same value pulled from the database by .NET 2 looks like this:

??淨琳d搈把媑?????

Do you know why the database value would be interpreted diferently by .NET 2 than by .NET 1.1? How can I bring this in sync so that both 1.1 sites and 2 sites can use the same data?

Now that's a thought. It would explain a shortening.

The column both are pulling from is an nvarchar... and the nvarchar/varchar distinction is not, as far as I know, found in C# strings. So would ADO.NET 2 convert an nvarchar to a varchar while the exact same code in ADO.NET 1.1 does not?

How would I test to see if this is happening?

I think though as a guess that both items are returning nvarchar characters.. while its not visible in what I cut and pasted into this site, there were chinese- and greek-looking characters evident in both versions of the encrypted data which would seem to me like they were both coming out of the database as nvarchar.

|||

Now that's a thought. It would explain a shortening.

The column both are pulling from is an nvarchar... and the nvarchar/varchar distinction is not, as far as I know, found in C# strings. So would ADO.NET 2 convert an nvarchar to a varchar while the exact same code in ADO.NET 1.1 does not?

How would I test to see if this is happening?

I think though as a guess that both items are returning nvarchar characters.. while its not visible in what I cut and pasted into this site, there were chinese- and greek-looking characters evident in both versions of the encrypted data which would seem to me like they were both coming out of the database as nvarchar.

|||oops... I think something just stuttered. :-)|||

I found out the answer... and it is NOT related to ADO.

Why I don't know why the encrypted data appeard differently in .NET 2 when debugged or traced out from the database, I did discover that the .NET 2 encryption has tightened what it will tolerate as acceptable unicode. .NET 1.1 would allow UTF8 encoding... and if it didn't exactly match, it would allow a fudge estimate. .NET 2 does not allow that any more. To get around this, rather than using typical encoding, one needs to make sure that 64 base encoding is used when creating string data from encrypted values.

Information on this can be found here:

http://blogs.msdn.com/shawnfa/archive/2005/11/10/491431.aspx

and here:

http://blogs.msdn.com/shawnste/archive/2005/03/24/401871.aspx

I needed to change my encryption code to use Convert.ToBase64String and Convert.FromBase64String rather than relying on the standard encoding classes. Once I did that, the .NET 2 code could encrypt and decrypt with consistancy with itself, but I could not maintain backwards compatability with the encrypted data already in the database - when it included the .NET 1.1 fudging.

|||

I do not know exactly but it is possible that if encrypt/decrypt on web ASP code it is possible that both version use different key to encrypt/decrypt data, You probably have to make your own key and do encryption/decryption on SQL server side which will always work the same way.

Thanks

|||

Thanks for answering!

Both use the same key to decrypt. But what's interesting here is that the difference is evident before the decryption is run. The raw data from the database looks different (and is shorter) when pulled by ADO .NET 2 from when its pulled by ADO .NET 1.1.

|||

Is it possible that one is returned as varchar and second nvarchar?

Thanks

|||

Now that's a thought. It would explain a shortening.

The column both are pulling from is an nvarchar... and the nvarchar/varchar distinction is not, as far as I know, found in C# strings. So would ADO.NET 2 convert an nvarchar to a varchar while the exact same code in ADO.NET 1.1 does not?

How would I test to see if this is happening?

I think though that both items are returning nvarchar characters.. while its not visible in what I cut and pasted into this site, there were chinese- and greek-looking characters evident in both versions of the encrypted data which would seem to me like they were both coming out of the database as nvarchar.