The below message is returned on my Development Server.
I am running the same version of SQL Server on my local machine
Local connection is Windows, Dev Server is SQL Server Auth.

The same T-SQL Script ran against local works.
Configuration between the two SQL Server Databases should be Identical.

Is there any config settings that I should verify?  I tried to lookup
System.Data.SqlServer.TrancationException but I could not find this assembly
in any documentation anywhere.

=========== ERROR ===========================
System.Data.SqlServer.TruncationException: Trying to convert return value or
output parameter of size 16 bytes to a T-SQL type with a smaller size limit
of 2 bytes.
System.Data.SqlServer.TruncationException:
   at System.Data.SqlServer.Internal.CXVariantBase.StringToWSTR(String
pstrValue, Int64 cbMaxLength, Int32 iOffset, EPadding ePad)

Hello gaharlan,

What this appears to be is that you are returning a value larger that the
return type or a parameter allows. Please post the code the for the method
in which this exception occurs and let's take it from there.


---
Thanks,
Kent Tegels, DevelopMentor
http://staff.develop.com/ktegels/

Code below.
But it still doesn't make any since if it works on my local SQL Server
(Enterprise) why do I get the error on the Development (Enterprise)

   public static string Decrypt(string CryptoProviderType,int BitSize,string
Key,string Value) {
      byte[] bArray;
      byte[] blockKey;
      byte[] blockIV;
      int iTmp = 0;
      string sTmp = null;
      System.Security.Cryptography.SymmetricAlgorithm cryptoProvider = null;
      System.Security.Cryptography.ICryptoTransform cryptoTransform = null;
      System.Security.Cryptography.CryptoStream cryptoStream = null;
      System.IO.MemoryStream memoryStream = null;
      System.IO.StreamReader streamReader = null;

      // Since the selected algorithm may not be Symmetric try it.
      try {
         switch(CryptoProviderType) {
            case "DES":
               cryptoProvider =
System.Security.Cryptography.SymmetricAlgorithm.Create("DES");
               break;
            case "TDES":
               cryptoProvider =
System.Security.Cryptography.SymmetricAlgorithm.Create("TripleDES");
               break;
            case "RC2":
               cryptoProvider =
System.Security.Cryptography.SymmetricAlgorithm.Create("RC2");
               break;
            case "RijnDael":
               cryptoProvider =
System.Security.Cryptography.SymmetricAlgorithm.Create("RijnDael");
               break;
         }
         cryptoProvider.Mode = System.Security.Cryptography.CipherMode.CBC;
      }
      catch(System.Security.Cryptography.CryptographicException ce) { throw
ce; }
      catch(System.Exception ex) { throw ex; }

      cryptoProvider.BlockSize = BitSize;
      cryptoProvider.KeySize = BitSize;

      try {
         if(BitSize == 128) { iTmp = 16; }
         if(BitSize == 192) { iTmp = 24; }
         if(BitSize == 256) { iTmp = 32; }

         blockKey = new byte[iTmp];
         blockIV = new byte[iTmp];

         // Change supplied key to a byte array
         bArray = stringToByte(BitSize,Key);
         // Hash the supplied Key
         bArray = hashOfByteArray(bArray);

         for(iTmp = 0;iTmp < blockKey.Length;iTmp++) {
            blockKey[iTmp] = bArray[iTmp];
         }

         // Change supplied Vector to byte array
         bArray = stringToByte(BitSize,"Initialization Vector");
         // Hash the supplied Vector
         bArray = hashOfByteArray(bArray);
         for(iTmp = 0;iTmp < blockIV.Length;iTmp++) {
            blockIV[iTmp] = bArray[iTmp];
         }

         bArray = System.Convert.FromBase64String(Value);
         // Create Decryptor
         cryptoTransform = cryptoProvider.CreateDecryptor(blockKey,blockIV);
         // Create Memory Stream
         memoryStream = new System.IO.MemoryStream(bArray,0,bArray.Length);
         //memoryStream.Position = 0;
         // Create Crypto Stream
         cryptoStream = new
System.Security.Cryptography.CryptoStream(memoryStream,cryptoTransform,System.Security.Cryptography.CryptoStreamMode.Read);
         // Read from Crypto Stream
         streamReader = new System.IO.StreamReader(cryptoStream);

         try {
            Value = streamReader.ReadToEnd();
            Value = Value.TrimEnd(new char[] { '\0' });
         }
         catch { }

         streamReader.Close();
         memoryStream.Close();
         cryptoStream.Close();
         cryptoTransform.Dispose();

         return Value;
      }

Show quote

Hide quote

Hello gaharlan,

Agreed, this does seem odd, but, can you also post the create assembly and
create function statement T-SQLs?

Also, I'm curious as to why you're choosing to do encryption/decryption via
SQLCLR when T-SQL in 2005 already has such functionality (see EncryptBy...
in BOL)?

Thanks,
Kent

A Couple of Reasons.
    1.  I already had encryption/decryption routines in a .NET C# Utility
DLL, so basicly just had to create a new class and change the declarations.
    2.  Unfamiliar/Relitivily new in SQL Server 2005.  The last time I
worked on SQL Server was over 10 years ago.  I have been mostly working on
and with Oracle and I am a Certified Oralce DBA Master.

I will investagate the T-SQL EncryptBy...
Thanks.

Show quote

Hide quote

T-SQL and Error being Reported.

DECLARE @test_dt VARCHAR(4000);
DECLARE @test_pw1 VARCHAR(4000);
DECLARE @test_pw2 VARCHAR(4000);
DECLARE @test_pw3 VARCHAR(4000);

SELECT @test_dt = CONVERT(VARCHAR,last_login),@test_pw1 = password FROM
PW_Users WHERE user_id = 'ramico';

PRINT 'Encryption Key: ' + @test_dt;
PRINT 'Encrypted Password: ' + @test_pw1;
PRINT '';

SET @test_pw3 = dbo.Encrypt256(@test_dt,'password');
PRINT 'Reencrypted: ' + @test_pw3;

SET @test_pw2 = dbo.Decrypt256(@test_dt,@test_pw1);

PRINT 'Decrypted Password: ' + @test_pw2;
GO

================== Message returned in MS SQL Server Manager
Encryption Key: Sep  9 2006  9:09AM
Encrypted Password:
TgTb5hgzYHgeJwUPSVR4Myg4SsbnhlE9GHOkr/BQlqMB1ABc0a/lmm5otLlRI+aKeBofTR4ElaPFMrxAkPZzcy/XzBy+Y0SVspKcKDlmgynUvHvX78R+WPtgX1k6klIG

Msg 6522, Level 16, State 2, Line 12
A .NET Framework error occurred during execution of user defined routine or
aggregate 'Encrypt256':
System.Data.SqlServer.TruncationException: Trying to convert return value or
output parameter of size 256 bytes to a T-SQL type with a smaller size limit
of 2 bytes.
System.Data.SqlServer.TruncationException:
   at System.Data.SqlServer.Internal.CXVariantBase.StringToWSTR(String
pstrValue, Int64 cbMaxLength, Int32 iOffset, EPadding ePad)
..

Msg 6522, Level 16, State 2, Line 15
A .NET Framework error occurred during execution of user defined routine or
aggregate 'Decrypt256':
System.Data.SqlServer.TruncationException: Trying to convert return value or
output parameter of size 16 bytes to a T-SQL type with a smaller size limit
of 2 bytes.
System.Data.SqlServer.TruncationException:
   at System.Data.SqlServer.Internal.CXVariantBase.StringToWSTR(String
pstrValue, Int64 cbMaxLength, Int32 iOffset, EPadding ePad)

Show quote

Hide quote

Ok its working now.

Initially I was copying the DLL out to the Development Server and executing
T-SQL script to Create Assembly from DLL and the Functions from the Assembly.
When doing this I received no errors, except that it would error when
attempt was made to execute a function.
I deployed the SQL Server Solution from my workstation out to the Dev
server.  Once I did that it worked.  I tried to find on the Dev box where the
DLL's were being stored but could not find them, so I don't know why its
working now and wasn't before.

Any insite into why this worked would be greatly appreciated
Thanks

Show quote

Hide quote

sql statement (how to)
Update Information in SQL from a VBS script
Return a City in a column on max count from another column in a group by
SQL statement increase question
Help me performance optimize