Solved

String or binary is truncated - varbinary(max) field

Posted on 2013-02-05
53
2,374 Views
Last Modified: 2013-02-08
I have an ASP.net code that uploads and saves files in the database (not filestream).

I'm using the stored proc below to encrypt the file. They're not huge files...for example, 13KB.

This method works for other varbinary columns I have but not for files. Not sure why I get string or binary truncated.

ASP.Net code:
 byte[] bytes = new byte[file.ContentLength];
                        file.InputStream.Read(bytes, 0, file.ContentLength);

Open in new window


Stored proc

 OPEN SYMMETRIC KEY MyTableKey DECRYPTION

BY CERTIFICATE EncryptMyCert
 
  INSERT INTO dbo.DocumentFiles (LetterFileId,
                                 NameFile,
                                 ContentType,
                                 Doc,
                                 FileSizeBytes, 
                                 UpdateUser, 
                                 updateDate)
     SELECT @fileId,
            ENCRYPTBYKEY(KEY_GUID('ArmsTableKey'),@FileName), --- this works
            @ContentType,
            ENCRYPTBYKEY(KEY_GUID('ArmsTableKey'),@doc), -- this doesnt work
           --  @doc,  --- this works
            @fileSizeBytes,
            @username,
            GETDATE()
            
   
  END

Open in new window

0
Comment
Question by:Camillia
  • 40
  • 13
53 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 38856271
isn't the return value from encryptbykey limited to 8000 bytes  ... is that what is causing the problem?

also what value do you have set for ansi padding (off) could give you problems....

vis http://msdn.microsoft.com/en-us/library/ms174361(v=SQL.105).aspx
0
 
LVL 7

Author Comment

by:Camillia
ID: 38856322
I have to check the ANSI padding. Not sure what I have. Let me see how I can check it.

Maybe I should encrypt the file in ASP.Net/Code and then save in database.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 38856550
probably somewhere in your connection properties...
0
 
LVL 7

Author Comment

by:Camillia
ID: 38856588
Not sure what you mean by "somewhere in your connection properties"...Let me look at the link you have.
0
 
LVL 7

Author Comment

by:Camillia
ID: 38856707
I turned on ANSI Padding " SET ANSI_PADDING ON;"

I turned on Profiler and I have ENCRYPTBYKEY(KEY_GUID('MyTableKey'),@doc)

I get the same error. I ran the same exact query by grabbing it from the Profiler and i get string or binary truncated.

I wonder  if I should encrypt in code, then save to database...

This is what's being passed (very long)

exec usp_AddDocFiles @fileId=78,@FileName=0x43616D696C6C6174657374696E67666178322E646F6378,@ContentType='application/vnd.ms-word',@fileSizeBytes=12661,@username='admin',@doc=0x504B03041400060008000000210009248782810100008E050000130008025B436F6E74656E745F54797065735D2E786D6C20A2040228A000020000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000B4944D4F83401086EF26FE07B25703DB7A30C694F6A0F5A84DACF1BC2E43D9C87E6467FBF5EF1D4A4BAAA1A55ABD90C032EFFBCC0B3383D14A97D1023C2A6B52D64F7A2C02236DA6CC2C65AFD3C7F896451884C944690DA46C0DC846C3CB8BC174ED0023AA3698B2220477C739CA02B4C0C43A3074925BAF45A05B3FE34EC80F31037EDDEBDD70694D0013E25069B0E1E00172312F43345ED1E39AC443892CBAAF5FACBC52269C2B95148148F9C264DF5CE2AD4342959B77B0500EAF0883F15687EAE4B0C1B6EE99A2F12A8368227C78129A30F8D2FA8C6756CE35F5901C9769E1B479AE2434F5959AF356022265AECBA439D142991DFF410E0CEB12F0EF296ADD13EDDF5428C6790E923E76771E1AE3AAE9A4B6D8ABED76831028A4534CBEFE827157E8B855EE4458C2FBCBBF51EC897782E4341A53F15EC20989FF308C46BA1322D0BC03DF5CFB67736C648E59D2644CBC7548FBC3FFA2EDDD82A8AA631A39073E28685644DB88358EB47BCEEE0FAAED9641D6E2CD37DB74F8090000FFFF0300504B0304140006000800000021001E911AB7F30000004E0200000B0008025F72656C732F2E72656C7320A2040228A0000200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000008C92DB4A03410C86EF05DF61C87D37DB0A22D2D9DE48A17722EB038499EC01770ECCA4DABEBDA320BA50DB5EE6F4E7CB4FD69B839BD43BA73C06AF6159D5A0D89B6047DF6B786DB78B075059C85B9A82670D47CEB0696E6FD62F3C9194A13C8C31ABA2E2B38641243E226633B0A35C85C8BE54BA901C4909538F91CC1BF58CABBABEC7F457039A99A6DA590D6967EF40B5C758365FD60E5D371A7E0A66EFD8CB8915C807616FD92E622A6C49C6728D6A29F52C1A6C30CF259D9162AC0A36E069A2D5F544FF5F8B8E852C09A10989CFF37C759C035A5E0F74D9A279C7AF3B1F21592C167D7BFB4383B32F683E010000FFFF0300504B0304140006000800000021007C3B973922010000B90300001C000801776F72642F5F72656C732F646F63756D656E742E786D6C2E72656C7320A2040128A0000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000AC934D4F84301086EF26FE07D2BB14565D8DD9B21735D9ABAEF1DC2D5368849674C60FFEBD15B30ACAA2072E4D669ABECFD349BB5ABFD555F4021E8DB382A571C222B0CAE5C616823D6C6F4F2E5984246D2E2B6741B01690ADB3E3A3D51D5492C2212C4D835148B1285849D45C718EAA845A62EC1AB061473B5F4B0AA52F7823D5932C802F9264C97D3F836583CC68930BE637F9298BB66D13C87F673BAD8D826BA79E6BB03482E00844E1661832A52F8004DB77E2E0C9F8B8C2E280426D9477E834C5CAD5FC93FE41BD185E8C23B515E0A3A1F2466B50D4C7FFDC9AF2480F788C8CF91FA3E8C8BD4174F5147E39279EC20B816F7A57F26E4DA71CCEE774D0CED256EEAA9EC7576B4AE26C4E8957D8DDFF7A95BDE65E840F3E5CF60E0000FFFF0300504B030414000600080000002100882CBB92460200001806000011000000776F72642F646F63756D656E742E786D6CA4544D6FDB300CBD0FD87F30744F6CA7591618490AB4E9821E0614CB761E1459B68548A220C971BB5F3FCA8E9D6C298A7E5C6C49241FDF23252EAE1F958C0EDC3A017A49D2714222AE19E442974BF2EBE7B7D19C44CE539D53099A2FC91377E47AF5F9D3A2C97260B5E2DA4708A15DD618B62495F7268B63C72AAEA81B2BC12C3828FC98818AA12804E37103368F27499AB42B638171E730DF2DD507EAC8114E5DA281E11A73156015F56E0CB68C15B5FBDA8C10DD502F76420AFF84D8C9AC878125A9ADCE8E844603A1109275848EBF3EC25EA878266F17B93E56A0CD185B2E91036857097392F15E349458F5940E2F893828D9FB35269D5EE41B24BFA6076B4B1B6CC509F002EE9962E45D90925D1D427F4F5DFD1F314D5E1273EC48801838BC86C2BF397B268A0A3DC0BCAF34E7C5C517F191FBBDB1509B818E111F43BBD7FB012B3CCC37304B66EDCB3B97E6DE0470F174B715359C448A65F7A5064B77121935E9340A3792AC7058EC207F0A7F1335190E9BFCC79224C9CD55B25EDF91FE68CD0B5A4B1F2CB3793A474B88B0E1E357B754092969E4B9F37843A3823E4E1671B0846FEBB403D88781B0F5D47A0415394205744D15F2F9BD811BCAF624EEF874BE773A1F3CD1805026981D67FEC1F6C4CEB806A329B77FD0D4E0BC9C4CA66D860AD75FE6B86EC14DF99D86600F06CFA79D8B156585DAFAED0EBC0775DA4B5E9C592B4E738E93E8EBA4852F00FCD9B6AC7DBB3DA663201D667386329419425A16389F375604795268FE203C439657B3D68A3A3B89ADE4AE3978D68FF4D55F000000FFFF0300504B03041400060008000000210030DD4329A8060000A41B000015000000776F72642F7468656D652F7468656D65312E786D6CEC594F6FDB3614BF0FD87720746F6327761A07758AD8B19B2D4D1BC46E871E698996D850A240D2497D1BDAE38001C3BA618715D86D87615B8116D8A5FB34D93A6C1DD0AFB0475292C5585E9236D88AAD3E2412F9E3FBFF1E1FA9ABD7EEC70C1D1221294FDA5EFD72CD4324F1794093B0EDDD1EF62FAD79482A9C0498F184B4BD2991DEB58DF7DFBB8AD755446282607D22D771DB8B944AD79796A40FC3585EE62949606ECC458C15BC8A702910F808E8C66C69B9565B5D8A314D3C94E018C8DE1A8FA94FD05093F43672E23D06AF89927AC06762A049136785C10607758D9053D965021D62D6F6804FC08F86E4BEF210C352C144DBAB999FB7B4717509AF678B985AB0B6B4AE6F7ED9BA6C4170B06C788A705430ADF71BAD2B5B057D03606A1ED7EBF5BABD7A41CF00B0EF83A6569632CD467FADDEC9699640F6719E76B7D6AC355C7C89FECA9CCCAD4EA7D36C65B258A206641F1B73F8B5DA6A6373D9C11B90C537E7F08DCE66B7BBEAE00DC8E257E7F0FD2BADD5868B37A088D1E4600EAD1DDAEF67D40BC898B3ED4AF81AC0D76A197C86826828A24BB318F3442D8AB518DFE3A20F000D6458D104A9694AC6D88728EEE2782428D60CF03AC1A5193BE4CBB921CD0B495FD054B5BD0F530C1931A3F7EAF9F7AF9E3F45C70F9E1D3FF8E9F8E1C3E3073F5A42CEAA6D9C84E5552FBFFDECCFC71FA33F9E7EF3F2D117D57859C6FFFAC327BFFCFC793510D26726CE8B2F9FFCF6ECC98BAF3EFDFDBB4715F04D814765F890C644A29BE408EDF3181433567125272371BE15C308D3F28ACD249438C19A4B05FD9E8A1CF4CD296699771C393AC4B5E01D01E5A30A787D72CF1178108989A2159C77A2D801EE72CE3A5C545A6147F32A99793849C26AE66252C6ED637C58C5BB8B13C7BFBD490A75330F4B47F16E441C31F7184E140E494214D273FC80900AEDEE52EAD87597FA824B3E56E82E451D4C2B4D32A423279A668BB6690C7E9956E90CFE766CB37B077538ABD27A8B1CBA48C80ACC2A841F12E698F13A9E281C57911CE298950D7E03ABA84AC8C154F8655C4F2AF074481847BD804859B5E696007D4B4EDFC150B12ADDBECBA6B18B148A1E54D1BC81392F23B7F84137C2715A851DD0242A633F900710A218ED715505DFE56E86E877F0034E16BAFB0E258EBB4FAF06B769E888340B103D331115BEBC4EB813BF83291B63624A0D1475A756C734F9BBC2CD28546ECBE1E20A3794CA175F3FAE90FB6D2DD99BB07B55E5CCF68942BD0877B23C77B908E8DB5F9DB7F024D9239010F35BD4BBE2FCAE387BFFF9E2BC289F2FBE24CFAA301468DD8BD846DBB4DDF1C2AE7B4C191BA8292337A469BC25EC3D411F06F53A73E224C5292C8DE0516732307070A1C0660D125C7D44553488700A4D7BDDD3444299910E254AB984C3A219AEA4ADF1D0F82B7BD46CEA4388AD1C12AB5D1ED8E1153D9C9F350A3246AAD01C6873462B9AC05999AD5CC988826EAFC3ACAE853A33B7BA11CD1445875BA1B236B1399483C90BD560B0B0263435085A21B0F22A9CF9356B38EC6046026D77EBA3DC2DC60B17E92219E180643ED27ACFFBA86E9C94C7CA9C225A0F1B0CFAE0788AD54ADC5A9AEC1B703B8B93CAEC1A0BD8E5DE7B132FE5113CF312503B998E2C2927274BD051DB6B35979B1EF271DAF6C6704E86C73805AF4BDD476216C26593AF840DFB5393D964F9CC9BAD5C313709EA70F561ED3EA7B053075221D51696910D0D339585004B34272BFF7213CC7A510A5454A3B349B1B206C1F0AF490176745D4BC663E2ABB2B34B23DA76F6352BA57CA2881844C1111AB189D8C7E07E1DAAA04F40255C77988AA05FE06E4E5BDB4CB9C5394BBAF28D98C1D971CCD20867E556A7689EC9166E0A522183792B8907BA55CA6E943BBF2A26E52F48957218FFCF54D1FB09DC3EAC04DA033E5C0D0B8C74A6B43D2E54C4A10AA511F5FB021A07533B205AE07E17A621A8E082DAFC17E450FFB739676998B48643A4DAA7211214F623150942F6A02C99E83B85583DDBBB2C4996113211551257A656EC1139246CA86BE0AADEDB3D1441A89B6A929501833B197FEE7B9641A3503739E57C732A59B1F7DA1CF8A73B1F9BCCA0945B874D4393DBBF10B1680F66BBAA5D6F96E77B6F59113D316BB31A795600B3D256D0CAD2FE354538E7566B2BD69CC6CBCD5C38F0E2BCC63058344429DC2121FD07F63F2A7C66BF76E80D75C8F7A1B622F878A18941D840545FB28D07D205D20E8EA071B283369834296BDAAC75D256CB37EB0BEE740BBE278CAD253B8BBFCF69ECA23973D939B97891C6CE2CECD8DA8E2D343578F6648AC2D0383FC818C798CF64E52F597C740F1CBD05DF0C264C49134CF09D4A60E8A107260F20F92D47B374E32F000000FFFF0300504B030414000600080000002100070253B083030000C108000011000000776F72642F73657474696E67732E786D6CB456DB6EDB38107D5FA0FF60E8B98E2CD7CD06429C629BC4DD16F17651A51F40496399086F185256DCAFDF2129460D9206458B7D32399733B7C391CFDFDD4B313B005AAED53A2B4E16D90C54A35BAEBA75F6F576333FCB66D631D532A115ACB323D8ECDDC5AB3FCE87D2827364666704A16C299B75B677CE94796E9B3D48664FB40145CA9D46C91C5DB1CB25C3BBDECC1B2D0D73BCE682BB63BE5C2C4EB31146AFB31E553942CC256F505BBD73DEA5D4BB1D6F60FC491EF83371A3E7956E7A09CA85883982A01CB4B27B6E6C4293BF8A4625EE13C8E1A5220E5224BBA158BC6439963B686C1F3C7E263DEF605037602D0D488A58AE645C3DC014AB27400FAD3EA156E73176EEA1C8BD5884D394B9154FFC9F99769CE20DAF91611C3311C067219BF263A734B25A10A98662955D10A3BE692D67437960045E83751BEE32BA1BC0868646F43C5B65B937A4E2F4AE72CC01A9AD0121025F1B018CC087B243268969EB2C4A824F0B3BD60B77CBEACA6993C2FCB95C44C866CF90350EB032AC21B44BAD1C6A91EC5AFD8F7697C45AA4A68E1E81C33E9DC8E62ABE07F2504C5255513A727CAB5BF099F5C89F34EE878DF70EA119D49F50C3F38134BD5FE42D5069022A7714B0A1E42BFE0DFE52EDA7DE3A4EAF2630FD3732782901503EF2677AEDB747031B60AEA736FD4FC1C22436829B2D47D4F851B5C48DDF0D96A721FA71D2326C6D3A7CD1DAA531D09A3A2BCEAEAE632FBCD9A4592E894A97CF697EEC737D755A5C07FE51FC31AA2CFD22F9172FCEE3C98F7226230D2E99AC91B3D9D6AF1A22842C6BBC7BCF55D2D740AB16BED7547D9D94F3795458C984D810D793222420CB965B7305BB002BB60CBB0977B4C067A5F4AE3E3D60F9770AF801756F62B4019989234AE18AD56AC4E3CADD7099E4B6AFABE4A5685D7CA7EA55FBF9801E309FDA33948EBE3281EA374C756912A0E65F2B6F4A131558F92F116C9931F4A4C9A4EE8A752678B77785A7A7A35B4B5FA470A9BBE5A85B061DDDBC2E5C58E32B23EBF1E00DE291ACC6C3247B93646F2619EDDB68B79A646F93ECED243B4D32FA220EE59EDE13D272BBA3A5918E5EBED342E801DABF93709D3D11C526D83D334073F5BB8F48ADCB201897A19D1D4AB8A7CD0AAD5FB5D6F056B27B5AB48BE5A9771FAD053BEADE3DB2F53A6F6C1E49672D738CDCC3A81E39D3E868533FCE65285B6838D1B13ACA7A5AB5273171C1ADABC0D056761AA9E4B0085F07E4E9BFC7C57F000000FFFF0300504B03041400060008000000210017A0164E02010000AC01000014000000776F72642F77656253657474696E67732E786D6C8CD0C14A03311006E0BBE03B2CB9B7D99522B274B72052F12282FA006976761BCC64C24C6AAC4F6FDAAA205E7ACB24998F997FB9FA405FBD038BA3D0A9665EAB0A82A5C185A953AF2FEBD98DAA249930184F013AB50751ABFEF26299DB0C9B6748A9FC94AA28415AB49DDAA6145BADC56E018DCC2942288F23319A544A9E341A7EDBC599258C26B98DF32EEDF5555D5FAB6F86CF51681C9D853BB23B84908EFD9AC11791826C5D941F2D9FA365E22132591029FBA03F79685CF8659AC53F089D65121AD3BC2CA34F13E90355DA9BFA7842AF2AB4EDC31488CDC6970473B3507D898F6272E83E614D7CCB9405581FAE8DF7949F1EEF4BA1FF64DC7F010000FFFF0300504B030414000600080000002100B09DD828BB0700005E3D00001A000000776F72642F7374796C657357697468456666656374732E786D6CB49B6D53DB3810C7DFDFCC7D078FDF4348A0E4CA34ED50E803336D8F3630F75AB115A2C1B67C7E20709FFE5692AD183BB67763F7558963ED6F57BBFAAFA0D2BB0FCF61E03CF12415325AB8D3E313D7E191277D113D2CDCFBBBCF477FB94E9AB1C867818CF8C27DE1A9FBE1FD9F7FBCDB5EA4D94BC053070C44E9C536F616EE26CBE28BC924F5363C64E97128BC44A6729D1D7B329CC8F55A787CB295893F999D4C4FF44F71223D9EA640BB62D1134BDDC25CD8B426631E016B2D939065E9B14C1E26214B1EF3F808ACC72C132B1188EC056C9F9C9766E4C2CD93E8A270E8C83AA4865C18878A7FCA1149238A3D5C33F25A7A79C8A34C1327090FC00719A51B11EFC238D41A84B8295D7AEA0AE2290CCAF7B6F1F4ACC1B3216372709DB02DA46267B0616ECF64F8665018987950F9DD65B56E717AD2154C911165C2FA8071E135B3F4246422B2660E9B9AEAE4C27A1852DF5F1299C7D69D580CB376133D5A5B6A59123C3B39D72BAF1A5A4A32D058BACB0D8BB9EB84DEC5CD432413B60AC0A3EDF4CC5115E9BE07A9F0A577CDD72C0FB2547D4C6E93E263F149FFF3594659EA6C2F58EA097107120256420106BF5E46A970E11BCED2EC32156CEF971BF5D6DE6FBC34AB58FB287CE14E1431FD0F6C3EB160E1CE66E5932BE5C1AB67018B1ECA673C3ABA5F563D59B8F6D10AEC2E5C961C2D2F95B1890EB3FCB7126EFC2A78F8A45D8999072B0F386C9D7110215031C50984CAEE6C0E8A663EFCCAD5E4B23C9305441B0058D52C7CACCD38681328D5D228367CCBD7DFA4F7C8FD65065F2C5CCD8287F737B7899009C8E8C27DFB5631E1E19287E2ABF07DAE1A44F1EC3EDA089FFFB3E1D17DCAFDDDF39F9FB53C17163D994719B87F3ED75510A4FEA7678FC74A26C174C454867FA801A061908E0A473B948B9D37E6418DAA1FFE5B22A726877B291BCE544B73B4FF9D201D753E183453115503D07649BE9E0E377136DCC49BE12674F10E9B8BF9702F6023333423A6362A55894F6A263D537CD579387DDB51B26A44A38A7A47348AA67744A3467A47344AA27744A3027A473412DE3BA291DFDE118D74768EF09816AE7A159DEAD9402DEC3B9105D0277B946E3A50EA8A56E3DCB2843D242CDE38AAB1D6DDEE12CB65BECA70AE6A393D5C2C975922D576B36746A03BABA57BB0267F0AE30D4B05ECCAFB4003A7FE4E6D7D9C2F8980ED6B0FEA8D29BE464C7A63B2B785DD06CCE31B19F83C71EEF8B3C92861FC0FE92CCD2EA3D7B98169FD261E369903BB42D5727B61E72D93DE3E13C6FE3791EA39E8ECE6E72DA1F41947E5F0BCA52EDB8D7FE7BEC8C3726A10BB9173A3E78434D710DAC5EE293A53296AAEAEDE28540230219876410F41DB47F86F9A0BDDBECA31C67FD38A0EB48FF0DF34AE03EDEBFAE8CE2F5969AEE1CF2A0E6A79CDC96BF74A063259E741B9067AE5614E5EC116810B81BC88AD7D9448CCC92BF8957C3A979E07BFB961EA949C8B9D8E1228E474188A5E6CF858C849A9C9DE941011394135D68CC01AA6B5041059747FF127A1FE084C6D065AA5ED5EB377399FB6CC00B420D41EFA672EB3FE3DF4AC45F3B0949B08FE5C927207473B6D5979585A514FA6DF11723CACF11140C33A200134AC1512402DF5D1BEE7B13D110F19DE1C092CB22CDB2EA6CB0EADCC73B2325B10AD058CD43711FBAF96D5DB5E0BCDBE89A09013D4EC9B080A393BB55E66FB2682355ADF44B05ABA467B8EAA9A4A098ADC37AB20BB134044348E782340E3883702348E782340C3C5BB1F329E782358646DB09A5A156F0448BF42F955DF82AAE28D0091B5C1A85DF137A3B2EF692BDDBFDC8E20DE080A39414DF14650C8D969136F044BBF42A9841ACB4A1D82358E782340E3883702348E782340E3883702348E782340C3C5BB1F329E782358646DB09A5A156F04882C0F1654156F0448BF42D186BDE2AD57FD6F176F04859CA0A6782328E4ECD404D56E52112C72826A2C2BDE08967E85520C054B173725A871C41B11D138E28D008D23DE08D038E28D000D17EF7EC878E28D6091B5C16A6A55BC1120B23C585055BC1120B236EC156FBD187FBB782328E40435C51B412167A726A856E7102C72826A2C2BDE0896AE97C1E28D00E9570E0551221A47BC11118D23DE08D038E28D000D17EF7EC878E28D6091B5C16A6A55BC1120B23C585055BC1120B236EC156FBD467EBB782328E40435C51B412167A726A856BC112C72826A2C2B7508D638E28D00E9C21C2CDE08907EE500905E4594348D23DE8888C6116F0468B878F743C6136F048BAC0D5653ABE28D0091E5C182AAE28D0091B5419DB385F3A2E8E3A9D39622C09E33284F35A081B3962461814580BFF89A2770AB90F79F0E19082C2324105BCA031BE247291F1DDCC1EED3960241A3C42A10521FE97ED1A7742A17114EE71D3709EEFEBE72BE9A0B308D71BAA45E9FBC81DB43D5EB42FA7A92BA38047E662F315CD989CB93E5CA1A5C1052F7BA8A2B40FA4EE80D5C082AAEF5A8C1EA9E0FBCA82F55158FF5FFDB1654F819887A6013E56D80E5C18DA80E5471E0DD9E41D2C7DDEBE09653F1DA91DD958CD2CDE274FC6E0F65DE7B7546B3D3EF4C9D04EFF0599F14EF9C2347BF62B2DA74102E676997FA3C8494AD0273C50C7EB8897C88705BDCCE32C9F49F993105DF5FF120F8CEF485B44CC6EDAF067C9D996FA727BA03D64CAD6496C9B07D7CA20F886B4FF6198072A83A633EAA20DAEB24CAC3154F8AE3E6AD25A93A87BE89F6BA24CD59D79652C0CEF4CEB7F2A7F4FDFF000000FFFF0300504B03041400060008000000210084898E804D0100008D02000011000801646F6350726F70732F636F72652E786D6C20A2040128A00001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000009C92CB4EC3301444F748FC43E47DE2B815AF2849258ABAA21212452076967DDB5AF825DB34EDDFE3246D4845572C9D997B3C73E372B65732D981F3C2E80A912C47096866B8D09B0ABDAD16E93D4A7CA09A53693454E8001ECDEAEBAB92D98219072FCE587041804F2249FB82D90A6D43B005C69E6D41519F45878EE2DA3845433CBA0DB6947DD10DE0499EDF620581721A286E81A91D88E888E46C40DA6F273B0067182428D0C1639211FCEB0DE094BF38D02923A712E16063A763DC319BB35E1CDC7B2F0663D3345933ED62C4FC047F2C9F5FBBAAA9D0EDAE18A0BAE4AC600E6830AE9E5325A4A4C93B5D536B8C2BF1486C1729A90FCBB8F3B500FE78B8E0FFEB69C71CEC44FBDF6A52E2F131DEDD55ED03004F62F8A2AF7A52DEA7F3A7D502D5939C4C524252F2B0227705B929F2FCB38D7736DF96E93FA863C87F134F80BA4B7CFE80EA1F000000FFFF0300504B0304140006000800000021004E238D853B0700006D3A00000F000000776F72642F7374796C65732E786D6CB49BDF739B3810C7DF6FE6FE0786F7D4B19DC6D74CDD4E9AB6D7CCB4BDB44EE69E6590634D0171203749FFFA5BAD3021606037D0A7981FDA8F56BBFAAEEC48AFDFDEC791F75366B9D2C9D29FBE38F63D99043A54C9EDD2BFB9FE78F497EFE54624A188742297FE83CCFDB76FFEFCE3F5DD596E1E22997B6020C9CFE260E96F8D49CF26933CD8CA58E42F742A1378B8D1592C0C5C66B79358643F76E951A0E35418B55691320F93D9F1F1A95F98C92856F466A302F95E07BB582606DB4F321981459DE45B95E67B6B77146B773A0BD34C0732CFC1E93872F662A192D2CCF4A461285641A673BD312FC09989EBD1C49A82E6D363FC1447BE17076797B789CEC43A82C1BB9B9EF86F60E4421DBC971BB18B4C6E2FB3ABACB82CAEF0CF479D98DCBB3B1379A0D4350C29188815D8FA749EE4CA872752E4E63C57E2E0C3AD7DEBE0932037156BEF54A8FC8925E6BFC0E64F112DFDD96C7FE7C2F6E0C9BD4824B7FB7B3239BA59557BB2F4CB5B6BB0BBF44576B43AB7C626E8E6FE6FC5DDF489F370855D494500C1008ED81809490139623991B239385B40BEB88BEF3B3BAE62677401410300AB9A85CBDA8843AE40E6AC5C02C353B9F9AC831F325C1978B0F49105376F2EAF32A53348D2A5FFEA9565C2CD958CD5271586D2CE97E2DE4DB255A1FC772B939B5C868FF7BF7DC4E42F2C067A9718E8FEE902B320CAC30FF7814C6DDA82E944D8087FB50D2071201C150E7668A71E7BE36ED4A878F3BF3D72EA627890B295C2CE700FFBDF0942AF77834133EB51D501B4CBEAEB7CB88993E1265E0E3781C93B6C2C16C37B01BA3E34222E372A59490FAAD1814BBEEA38CC5F75A4AC6DD1C8A2DE168DA4E96DD1C891DE168D94E86DD1C880DE168D80F7B668C4B7B745239C9D2D0281C255CFA2398E0669625F2B1349DBBE5380A603A5AE2835DE95C8C46D26D2AD670B6BBDDB5D62B9DAAD0DADAB28A7CF17CB95C97472DB3B22509DEDD47DB6267F88D3ADC815AC927A867E3670E8AFEDAAC7FB3B53612FEAA54BBE864FB8303958C2AE2211C8AD8E429979D7F2DE4594D1FEABF6566E95D1DBB98161FDAC6EB7C65B6DB1E4F6C24E5B06BD7D249CFDCF2AC731E89C4CA72DAEF41927C5F0B4252FDB8D7F91A1DAC5FBA121AC464E9D9E33C25C436017BB87E8C486A839BB7ABDB001A0B8E0CA05DF05B44FE8BF2B2E7CFB36C694FEBB52F44CFB84FEBBC2F54CFB981FDDF1652BCD7BF8D2EA91A6D7823D772F74A4B3CD2EDACF815E7958B0677089A0B9C09EC4A57D92482CD833F8897C7AE74100DFDC2879CA8EC5A38E3228EC70380A4E36BA2FECA0D4646FCAF0881DA01A6BC6600DD35A06882DBADFE54F657F13E3160354E972ADD93B9DE72D23002588B486FEB6D3A67F0D3D6BD13C2AE532819F4B72E9D168F3969947A515F9E4EA1D23C6C30A1F0334AC023240C34A2103D4921FED6B9EB226D221C38B2383C596E5B28A61DA919579C156E612C42B0123D54DC2FAAB65F6B6E742B36E1228EC0035EB2681C28E4EAD96957593C01AAD6E12582D55A33D46554DE538C5AE9B5550B9122078348E781340E3883701348E781340C3C5BB1F329E7813586C6D2835B52ADE0410BEC2F9AA5F82AAE24D00B1B5C1A95DF19BD1BEEEA195EE2FB723883781C20E5053BC09147674DAC49BC0C25738995063955247608D23DE04D038E24D008D23DE04D038E24D008D23DE04D070F1EE878C27DE04165B1B4A4DAD8A3701C496871254156F02085FE168C341F1C659FFDBC59B406107A829DE040A3B3A35412D17A904163B40355629DE0416BEC249868285C9CD716A1CF12678348E781340E3883701348E781340C3C5BB1F329E7813586C6D2835B52ADE04105B1E4A5055BC0920B6361C146F9C8CBF5DBC091476809AE24DA0B0A35313D452E7082C76806AAC52BC092CCC97C1E24D00E12BCF05713C1A47BC091E8D23DE04D038E24D000D17EF7EC878E24D60B1B5A1D4D4AA7813406C79284155F12680D8DA7050BC718EFC76F12650D8016A8A3781C28E4E4D504BF126B0D801AAB14AA923B0C6116F02081373B0781340F8CA3340388B38611A47BC091E8D23DE04D070F1EE878C27DE04165B1B4A4DAD8A3701C496871254156F0288AD0D769F2DEC17256F4F9DB62401759FC17E570319386B0912155838F85D6E640687AC64FFEE9081C0BD870C624B7A505D7CA7F50F8FB6B17BDE922064945A474AE396EE07DCA5533988305F749C24B8FEE7C2FBE40EC034DA614A3DDD7903A787AAC785F078923D3804FD340F291CD949F73BCBAD35382064CF75154780F088DC251C082A8EF5D8C6F69C0FBC8887AA8ADBF87FDB820A9F81880D9BA8600BAC004E4475A08A0DEFE51E24DCEE5E07B7EC8AC78E3C1EC9D877B3D81DFFB88672EF3DD9A3D9D96F63778277F419778A778E9187AFB8A8363B0887B3B04B7D3D8490AD2377C40C3E5C262178088704F1BF662E98E1BD70A6E0F9858CA22F020FA4199DB6BF1AC98D714FA7C758016BA6D6DA181DB7B7CF708338F6E4900148876A67DCA575A23D4F925DBC96199CF0EA18F3AFDA560E3C89F63425DD5ED79654A08EF463DFF69FF237FF030000FFFF0300504B03041400060008000000210016E9414AC3010000A204000012000000776F72642F666F6E745461626C652E786D6CA492DD4EE3301085EF57DA77887C4FEDA481858814A1EE56E2662F56F000AEEB3416FE893C6EB37D7B26761A2E2A440B8E1425673C47339FCEFDC37FA3B3BDF4A09CAD493E63249356B88DB2DB9ABC3CAFAE6E490681DB0DD7CECA9A1C249087C5CF1FF77DD5381B20C37E0B9511356943E82A4A41B4D27098B94E5A2C36CE1B1EF0D76FA9E1FE75D75D09673A1ED45A69150EB460EC868C36FE1C17D7344AC8DF4EEC8CB421F6532F353A3A0BADEAE0E8D69FE3D63BBFE9BC13120077363AF919AEEC64939727464609EFC0356186CBD034111DACB03D67F1CB689219513D6DADF37CAD915D9F97643182CBFACA7283E2926BB5F62A163A6E1DC81C6B7BAE6BC20AB662D7F81E9E92CD8737A1838368B90719A68B2CC90D374A1F8E2AF40A20153A15447BD4F7DCAB61A05402B5C5C20ED6AC267F189E62B52249C96B52A2F0B89C9402874A271FEFCC2705938383459F7825BF8B3EA8A0CFD815E7A4293A27249E959190FD957DF6CF196E3F2052B01B24718D3C0632F38B88F8E81B095E40A4789CF6C74D96B8CAAFDBF2B8FF3B91BBCF89249FF3892CB9C168F00F480C04128981C865D9F81A89D36CB07262F34E22260113F59D6C8C2181C51B000000FFFF0300504B0304140006000800000021009E565CB1D6010000D303000010000801646F6350726F70732F6170702E786D6C20A2040128A00001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000009C534D6FDB300CBD0FD87F307C6FE464C550048C8A21C5D0C3B60688DB9E39994E84C99220A941B35F3FCA6E1CA7DB693A3D7E887C7AA4E0F6B533C58142D4CEAECAF9AC2A0BB2CA35DAEE56E563FDF5EAA62C6242DBA0719656E59162792B3F7E804D709E42D2140B2E61E3AADCA7E4974244B5A70EE38CC39623AD0B1D2636C34EB8B6D58AEE9C7AE9C826B1A8AACF825E13D9869A2B3F162C878ACB43FADFA28D53995F7CAA8F9E094BA8A9F30613C91F998E01313AA076094DAD3B9215BB470336B8A328E7200600CF2E34517E02310058EF31A04A2C9D9CDF809898F0C57BA31526D6547ED72AB8E8DA543CF4AF2FF27510D3146045B6A45E824EC74C626AC2376D071A03605A017701FDFE8DDB68C156A1A1353F5BB6682281383BE09E308F74839AE9C2212D0FA4920B45D4BF79A88BB2F88991B258ABF28041A34D2C5A4E1B8C1E1B1F5390B54E866B736CB07B384D9B627D9D05E45C069789D93970E0C025BBBE437C68F9A5E91F64E753B23D8781EA84CE048E3DDE555DBBCEA33D72F311B1C0BFE2A3AFDD5D5E94370D2F9D93A13FEBB4DF7A543C9C05CFEC3CFE4900B6BC23D4F03C4FE5CE0EB867B583C93DF9AEDD5173CAF93B9017EA69F8A3727E3DABF8F41B74F2F18E8E9F47FE010000FFFF0300504B01022D001400060008000000210009248782810100008E0500001300000000000000000000000000000000005B436F6E74656E745F54797065735D2E786D6C504B01022D00140006000800000021001E911AB7F30000004E0200000B00000000000000000000000000BA0300005F72656C732F2E72656C73504B01022D00140006000800000021007C3B973922010000B90300001C00000000000000000000000000DE060000776F72642F5F72656C732F646F63756D656E742E786D6C2E72656C73504B01022D0014000600080000002100882CBB924602000018060000110000000000000000000000000042090000776F72642F646F63756D656E742E786D6C504B01022D001400060008000000210030DD4329A8060000A41B00001500000000000000000000000000B70B0000776F72642F7468656D652F7468656D65312E786D6C504B01022D0014000600080000002100070253B083030000C1080000110000000000000000000000000092120000776F72642F73657474696E67732E786D6C504B01022D001400060008000000210017A0164E02010000AC010000140000000000000000000000000044160000776F72642F77656253657474696E67732E786D6C504B01022D0014000600080000002100B09DD828BB0700005E3D00001A0000000000000000000000000078170000776F72642F7374796C657357697468456666656374732E786D6C504B01022D001400060008000000210084898E804D0100008D02000011000000000000000000000000006B1F0000646F6350726F70732F636F72652E786D6C504B01022D00140006000800000021004E238D853B0700006D3A00000F00000000000000000000000000EF210000776F72642F7374796C65732E786D6C504B01022D001400060008000000210016E9414AC3010000A2040000120000000000000000000000000057290000776F72642F666F6E745461626C652E786D6C504B01022D00140006000800000021009E565CB1D6010000D303000010000000000000000000000000004A2B0000646F6350726F70732F6170702E786D6C504B0506000000000C000C0009030000562E00000000

Open in new window

0
 
LVL 7

Author Comment

by:Camillia
ID: 38856763
This links says we cant encrypt varbinary(max) column but i'm doing it with other smaller columns. It does say the limit is 8000 bytes. The size of that file is 12661 bytes. That could be it??
0
 
LVL 7

Author Comment

by:Camillia
ID: 38856798
i think you're right. It's because there's a limit of 8000 bytes
http://www.simple-talk.com/blogs/2009/03/12/encrypting-large-values/
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 38856811
yes that was my first thought and comment, i wasnt sure if the encryption actually compressed the data as well or if it returns a scrambled byte for byte sequence ....
(or even expands in some cases)

have you considered trying to pass the "file" in 8000 byte chunks...?
0
 
LVL 7

Author Comment

by:Camillia
ID: 38856840
>>have you considered trying to pass the "file" in 8000 byte chunks...?
If I do that, does it mean the file is stored in 2 rows (x rows)...depending on the chunk it divides to? and if I do that, i have to calculate the difference in bytes and then when I read the file back...put the bytes back together?
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 38856973
well you could store it in multiple rows and then select it back as a single row from sql using concatenation in a cte perhaps...

or you could still store it in one row doing the chunking and looping round...

which comes back to my other question How does the encryption work?
does it always return a varbinary of the same length as the source....?

not sure where you'd find an answer for that?

if you do encryption in .net does that avoid the 8000 byte limitation?
0
 
LVL 7

Author Comment

by:Camillia
ID: 38856998
I encrypt  letters and I read it back and it works fine. It's large files that's not working. I need to encrypt because of HIPPA compliant application...

>>or you could still store it in one row doing the chunking and looping round...
I think I've read this somewhere. Do you have an example? I can try it...
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 38857188
declare @estring varbinary(max),@i bigint,@l bigint

select @i=1,@l=datalength(@doc)
OPEN SYMMETRIC KEY MyTableKey DECRYPTION

BY CERTIFICATE EncryptMyCert
 
while @i<=@l
begin
         select @i=@i+8000
                 ,@estring=stuff(@estring,@i,0,ENCRYPTBYKEY(KEY_GUID('ArmsTableKey'),substring(@doc,@i,case when @i+8000<@l then 8000 
                     else @l-@i end))
end

 INSERT INTO dbo.DocumentFiles (LetterFileId,
                                 NameFile,
                                 ContentType,
                                 Doc,
                                 FileSizeBytes, 
                                 UpdateUser, 
                                 updateDate)
     SELECT @fileId,
            ENCRYPTBYKEY(KEY_GUID('ArmsTableKey'),@FileName), --- this works
            @ContentType,
        @estring,
           -- ENCRYPTBYKEY(KEY_GUID('ArmsTableKey'),@doc), -- this doesnt work
           --  @doc,  --- this works
            @fileSizeBytes,
            @username,
            GETDATE()
            

Open in new window

0
 
LVL 7

Author Comment

by:Camillia
ID: 38857212
Thanks, my brain is fried over this :) Let me try and thanks for sticking with this.
0
 
LVL 7

Author Comment

by:Camillia
ID: 38857286
I get Incorrect syntax near the keyword 'end'.

I played around with the paranthesis but not sure what's causing it
0
 
LVL 7

Author Comment

by:Camillia
ID: 38857304
Syntax error fixed

 SELECT @i = @i + 8000 ,
                @estring = STUFF(@estring, @i, 0,
                                 ENCRYPTBYKEY(KEY_GUID('ArmsTableKey'),
                                              SUBSTRING(@doc, @i,
                                                        CASE WHEN @i + 8000 < @l
                                                             THEN 8000
                                                             ELSE @l - @i
                                                        END)))

I get:
Implicit conversion from data type varchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query.

On that select line
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 38857340
hmm
then try it as

SELECT @i = @i + 8000 ,
                @estring =@estring+
                                 ENCRYPTBYKEY(KEY_GUID('ArmsTableKey'),
                                              SUBSTRING(@doc, @i,
                                                        CASE WHEN @i + 8000 < @l
                                                             THEN 8000
                                                             ELSE @l - @i
                                                        END))
0
 
LVL 7

Author Comment

by:Camillia
ID: 38857455
No errors when i ran the SP, I will test tonight and post back. Thanks again for your help.
0
 
LVL 7

Author Comment

by:Camillia
ID: 38857832
I get an error. I put debug steps and ran Profiler and this is what I see:

before
1
12658
 
after
8001
12658
 
before
8001
12658
 
Msg 537, Level 16, State 5, Procedure usp_AddDocFiles, Line 33
Invalid length parameter passed to the LEFT or SUBSTRING function.
after
16001
12658

Open in new window


while @i<=@l
BEGIN
        PRINT 'before'
        PRINT @i
        PRINT  @l
        PRINT @estring
        
        SELECT @i = @i + 8000 ,
                @estring =@estring+
                                 ENCRYPTBYKEY(KEY_GUID('ArmsTableKey'),
                                              SUBSTRING(@doc, @i,
                                                        CASE WHEN @i + 8000 < @l
                                                             THEN 8000
                                                             ELSE @l - @i
            
                                                        END))
            print 'after'                                              
            PRINT @i
            PRINT  @l   
            PRINT @estring                                         
                                                        
end

Open in new window


This is what i'm passing:

exec usp_AddDocFiles @fileId=83,
@FileName=0x6661782074657374657220322E646F6378,
@ContentType='application/vnd.ms-word',
@fileSizeBytes=12658,
@username='admin',
@doc=0x504B03041400060008000000210009248782810100008E050000130008025B436F6E74656E745F54797065735D2E786D6C20A2040228A000020000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000B4944D4F83401086EF26FE07B25703DB7A30C694F6A0F5A84DACF1BC2E43D9C87E6467FBF5EF1D4A4BAAA1A55ABD90C032EFFBCC0B3383D14A97D1023C2A6B52D64F7A2C02236DA6CC2C65AFD3C7F896451884C944690DA46C0DC846C3CB8BC174ED0023AA3698B2220477C739CA02B4C0C43A3074925BAF45A05B3FE34EC80F31037EDDEBDD70694D0013E25069B0E1E00172312F43345ED1E39AC443892CBAAF5FACBC52269C2B95148148F9C264DF5CE2AD4342959B77B0500EAF0883F15687EAE4B0C1B6EE99A2F12A8368227C78129A30F8D2FA8C6756CE35F5901C9769E1B479AE2434F5959AF356022265AECBA439D142991DFF410E0CEB12F0EF296ADD13EDDF5428C6790E923E76771E1AE3AAE9A4B6D8ABED76831028A4534CBEFE827157E8B855EE4458C2FBCBBF51EC897782E4341A53F15EC20989FF308C46BA1322D0BC03DF5CFB67736C648E59D2644CBC7548FBC3FFA2EDDD82A8AA631A39073E28685644DB88358EB47BCEEE0FAAED9641D6E2CD37DB74F8090000FFFF0300504B0304140006000800000021001E911AB7F30000004E0200000B0008025F72656C732F2E72656C7320A2040228A0000200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000008C92DB4A03410C86EF05DF61C87D37DB0A22D2D9DE48A17722EB038499EC01770ECCA4DABEBDA320BA50DB5EE6F4E7CB4FD69B839BD43BA73C06AF6159D5A0D89B6047DF6B786DB78B075059C85B9A82670D47CEB0696E6FD62F3C9194A13C8C31ABA2E2B38641243E226633B0A35C85C8BE54BA901C4909538F91CC1BF58CABBABEC7F457039A99A6DA590D6967EF40B5C758365FD60E5D371A7E0A66EFD8CB8915C807616FD92E622A6C49C6728D6A29F52C1A6C30CF259D9162AC0A36E069A2D5F544FF5F8B8E852C09A10989CFF37C759C035A5E0F74D9A279C7AF3B1F21592C167D7BFB4383B32F683E010000FFFF0300504B0304140006000800000021007C3B973922010000B90300001C000801776F72642F5F72656C732F646F63756D656E742E786D6C2E72656C7320A2040128A0000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000AC934D4F84301086EF26FE07D2BB14565D8DD9B21735D9ABAEF1DC2D5368849674C60FFEBD15B30ACAA2072E4D669ABECFD349BB5ABFD555F4021E8DB382A571C222B0CAE5C616823D6C6F4F2E5984246D2E2B6741B01690ADB3E3A3D51D5492C2212C4D835148B1285849D45C718EAA845A62EC1AB061473B5F4B0AA52F7823D5932C802F9264C97D3F836583CC68930BE637F9298BB66D13C87F673BAD8D826BA79E6BB03482E00844E1661832A52F8004DB77E2E0C9F8B8C2E280426D9477E834C5CAD5FC93FE41BD185E8C23B515E0A3A1F2466B50D4C7FFDC9AF2480F788C8CF91FA3E8C8BD4174F5147E39279EC20B816F7A57F26E4DA71CCEE774D0CED256EEAA9EC7576B4AE26C4E8957D8DDFF7A95BDE65E840F3E5CF60E0000FFFF0300504B0304140006000800000021007042FB03AC0200002808000011000000776F72642F646F63756D656E742E786D6CCC55C96EDB3010BD17E83FB0BCC7921DD74D85D801D22CC8A140D0A4E782A62889303790B455F7EB3BA416AB511038C9A51789E470DEBC997924CF2F7E4B8176CC3AAED5124F2729464C519D73552EF1CFC79B93338C9C272A27422BB6C47BE6F0C5EAE387F33ACB35DD4AA63C0208E5B2DAD025AEBC375992385A3149DC44726AB5D3859F502D135D149CB2A4D6364F66E9348D23633565CE41BC6F44ED88C32D9C1CA369C314C42AB495C4BB89B6652289DD6CCD09A01BE2F99A0BEEF7809D2E3A18BDC45BABB296D0494F28B8640DA1F6D779D85116CFC46D3CAFDA0AC4888965023868E52A6E0E69BC150D52AC3A4ABB9792D849D1EDABCD743E8AD7A77C4C0FAE2CA9A11507C011DC33C5C81B27299A3A84FE1EBAFA14719ABE944CDB9100D1733886C2BF313B269270D5C3BCAD34C3E2C289788FBE6FADDE9A9E8EE1EF43BB539B1E2B1CCC57304B17F1E40D5373AF02181DDD878A188691A4D95DA9B4256B018CEAE91C0545E2155C166B9DEFC3DFA03A83CB26FFB1C4693AFB3A3F9D5FE26EE98A15642B7CB49CCD6F168DA70D6EF192C99C2114808D658ED91DC3AB47E63CC8159D27B06515BE7137A84F17D7D602B0DF1BF0708609F1E089F53809700DE6CA37EE47795FAB7CE81B6EA9311F349D9DA216F5FF2175547E83EA8CCA575A2207F6B67A86F0921D85DDD7EE59E4DEFA52A711777D61EF906544883DAAE03242BE02139CFF8DFBF484CC5AEB4D78202275D002CF415A416D8A4810C5AF5B7D49E8A6E96AB717C8F43B412AA0281304E318F5F7414F4FB51B355D3EFC01530DEFE76C368F112A187F3E8371949B29BF9328466D607DDE6CB1BCAC40EBDD74ADBDD7F23017AC18582B4672062FD39759842FB4F68369B9F571DA86A35A40453A750697C802DEEB5BCB437A822B76CF3D0596A78B68853C9B1463CACD6185B5EE895FFD050000FFFF0300504B03041400060008000000210030DD4329A8060000A41B000015000000776F72642F7468656D652F7468656D65312E786D6CEC594F6FDB3614BF0FD87720746F6327761A07758AD8B19B2D4D1BC46E871E698996D850A240D2497D1BDAE38001C3BA618715D86D87615B8116D8A5FB34D93A6C1DD0AFB0475292C5585E9236D88AAD3E2412F9E3FBFF1E1FA9ABD7EEC70C1D1221294FDA5EFD72CD4324F1794093B0EDDD1EF62FAD79482A9C0498F184B4BD2991DEB58DF7DFBB8AD755446282607D22D771DB8B944AD79796A40FC3585EE62949606ECC458C15BC8A702910F808E8C66C69B9565B5D8A314D3C94E018C8DE1A8FA94FD05093F43672E23D06AF89927AC06762A049136785C10607758D9053D965021D62D6F6804FC08F86E4BEF210C352C144DBAB999FB7B4717509AF678B985AB0B6B4AE6F7ED9BA6C4170B06C788A705430ADF71BAD2B5B057D03606A1ED7EBF5BABD7A41CF00B0EF83A6569632CD467FADDEC9699640F6719E76B7D6AC355C7C89FECA9CCCAD4EA7D36C65B258A206641F1B73F8B5DA6A6373D9C11B90C537E7F08DCE66B7BBEAE00DC8E257E7F0FD2BADD5868B37A088D1E4600EAD1DDAEF67D40BC898B3ED4AF81AC0D76A197C86826828A24BB318F3442D8AB518DFE3A20F000D6458D104A9694AC6D88728EEE2782428D60CF03AC1A5193BE4CBB921CD0B495FD054B5BD0F530C1931A3F7EAF9F7AF9E3F45C70F9E1D3FF8E9F8E1C3E3073F5A42CEAA6D9C84E5552FBFFDECCFC71FA33F9E7EF3F2D117D57859C6FFFAC327BFFCFC793510D26726CE8B2F9FFCF6ECC98BAF3EFDFDBB4715F04D814765F890C644A29BE408EDF3181433567125272371BE15C308D3F28ACD249438C19A4B05FD9E8A1CF4CD296699771C393AC4B5E01D01E5A30A787D72CF1178108989A2159C77A2D801EE72CE3A5C545A6147F32A99793849C26AE66252C6ED637C58C5BB8B13C7BFBD490A75330F4B47F16E441C31F7184E140E494214D273FC80900AEDEE52EAD87597FA824B3E56E82E451D4C2B4D32A423279A668BB6690C7E9956E90CFE766CB37B077538ABD27A8B1CBA48C80ACC2A841F12E698F13A9E281C57911CE298950D7E03ABA84AC8C154F8655C4F2AF074481847BD804859B5E696007D4B4EDFC150B12ADDBECBA6B18B148A1E54D1BC81392F23B7F84137C2715A851DD0242A633F900710A218ED715505DFE56E86E877F0034E16BAFB0E258EBB4FAF06B769E888340B103D331115BEBC4EB813BF83291B63624A0D1475A756C734F9BBC2CD28546ECBE1E20A3794CA175F3FAE90FB6D2DD99BB07B55E5CCF68942BD0877B23C77B908E8DB5F9DB7F024D9239010F35BD4BBE2FCAE387BFFF9E2BC289F2FBE24CFAA301468DD8BD846DBB4DDF1C2AE7B4C191BA8292337A469BC25EC3D411F06F53A73E224C5292C8DE0516732307070A1C0660D125C7D44553488700A4D7BDDD3444299910E254AB984C3A219AEA4ADF1D0F82B7BD46CEA4388AD1C12AB5D1ED8E1153D9C9F350A3246AAD01C6873462B9AC05999AD5CC988826EAFC3ACAE853A33B7BA11CD1445875BA1B236B1399483C90BD560B0B0263435085A21B0F22A9CF9356B38EC6046026D77EBA3DC2DC60B17E92219E180643ED27ACFFBA86E9C94C7CA9C225A0F1B0CFAE0788AD54ADC5A9AEC1B703B8B93CAEC1A0BD8E5DE7B132FE5113CF312503B998E2C2927274BD051DB6B35979B1EF271DAF6C6704E86C73805AF4BDD476216C26593AF840DFB5393D964F9CC9BAD5C313709EA70F561ED3EA7B053075221D51696910D0D339585004B34272BFF7213CC7A510A5454A3B349B1B206C1F0AF490176745D4BC663E2ABB2B34B23DA76F6352BA57CA2881844C1111AB189D8C7E07E1DAAA04F40255C77988AA05FE06E4E5BDB4CB9C5394BBAF28D98C1D971CCD20867E556A7689EC9166E0A522183792B8907BA55CA6E943BBF2A26E52F48957218FFCF54D1FB09DC3EAC04DA033E5C0D0B8C74A6B43D2E54C4A10AA511F5FB021A07533B205AE07E17A621A8E082DAFC17E450FFB739676998B48643A4DAA7211214F623150942F6A02C99E83B85583DDBBB2C4996113211551257A656EC1139246CA86BE0AADEDB3D1441A89B6A929501833B197FEE7B9641A3503739E57C732A59B1F7DA1CF8A73B1F9BCCA0945B874D4393DBBF10B1680F66BBAA5D6F96E77B6F59113D316BB31A795600B3D256D0CAD2FE354538E7566B2BD69CC6CBCD5C38F0E2BCC63058344429DC2121FD07F63F2A7C66BF76E80D75C8F7A1B622F878A18941D840545FB28D07D205D20E8EA071B283369834296BDAAC75D256CB37EB0BEE740BBE278CAD253B8BBFCF69ECA23973D939B97891C6CE2CECD8DA8E2D343578F6648AC2D0383FC818C798CF64E52F597C740F1CBD05DF0C264C49134CF09D4A60E8A107260F20F92D47B374E32F000000FFFF0300504B030414000600080000002100D406ABD7710300009708000011000000776F72642F73657474696E67732E786D6CB456DB6EDB38107D5FA0FF20E8B98E24C7495B214EB14DE1DD16F176B14A3F8012C73211DE405256DCAFEF9014A3A6C906C116FB646A2E676E87435FBCBF133C3B80B14CC9755E9D947906B25394C97E9D7FBDD92CDEE699754452C29584757E049BBFBF7CF5DBC5585B700ECD6C8610D2D6A25BE77BE7745D14B6DB8320F6446990A8DC292388C34FD3178298DB412F3A253471AC659CB963B12CCBF37C8251EB7C30B29E201682754659B573DEA556BB1DEB60FA491EE62571A3E747D50D02A40B110B031C7350D2EE99B6094DFC57342C719F400ECF1571103CD98D55F99CE554EEA80CBDF778497ADE411BD581B53820C163B98230790F53AD1E01DDB7FA045B5DC4D8858742F7AA0CA73973CB1FF93F31ED38C56BD61A62E29891003E0BD1D59F7AA90C6939926AAC56F92532EA9B52221B6B0DA6C321211DABB3BCF00A2C46ED1A471CA0DA6AE03CF0B3E340106CAC7B4304326B9D4749F0A1B023037737A46D9CD268742098F39B651921BB3D31A473601A4D3A44BB52D219C5931D557F2977852C35D8C4C92370D6A713D9DB44FEA3872402AB88D289D35B45C1673618F6A851FFDA68EF10B2C47E841A9E0EA4F0BE1A46014BE3D0B823870D26DFB06FF0BBA49F07EB18DE92C0EC5FC8E0B90440FAC85FF076DF1C356C80B801DBF43F050B93D870A6B7CC18653E498ADCF8D560451AA21F272E3F6AD3E11FA55C1A43592EDFAE36E7D330BCD98B34EF56A7AB0FBE7F1865C216B55F0F7F9BCB8B78F203CB441CF61511AD6124DBFA05825EA26ECDED072693BE055CA0F0A3A619DAA45C2CA2C20AC2F906199D1481E6A2A6CCEA8FB00BB07C4B4C3FE34E16E64929DE9ECFF758FE3682F9C3A841C768A3213A0E2285AB56AB098F4977CD4492DBA16D9297C425F0836A90F4CBC178C0626ECF583B7C3B02A1AF89EC53BF412EBE36DE14E7C64DE3DF17D812ADF1E2A249DB57EB9CB37EEF2A4F42875F14DF99F0D1F6CB49B70C3AFCF2BAF0413A5F195A4F076F108F68351D66D969929DCE32DCA2D16E35CBCE92EC6C969D2719BE7363BDC75B637085DDE26A48472FDF29CED508F4CF245CE78F44B109764F34E05CFD8643EAAA3A08A69567B3430D77B83F813287CFB76654903B5CA7E5F2DCBB4FD69C1CD5E01ED87A9D37D60FA419258EA07B18D503E740F19F72196B0A1D433A3647D1CE0BF52426CE99750D68DCBD4E192C39ACBBD70179FE4771F91D0000FFFF0300504B03041400060008000000210017A0164E02010000AC01000014000000776F72642F77656253657474696E67732E786D6C8CD0C14A03311006E0BBE03B2CB9B7D99522B274B72052F12282FA006976761BCC64C24C6AAC4F6FDAAA205E7ACB24998F997FB9FA405FBD038BA3D0A9665EAB0A82A5C185A953AF2FEBD98DAA249930184F013AB50751ABFEF26299DB0C9B6748A9FC94AA28415AB49DDAA6145BADC56E018DCC2942288F23319A544A9E341A7EDBC599258C26B98DF32EEDF5555D5FAB6F86CF51681C9D853BB23B84908EFD9AC11791826C5D941F2D9FA365E22132591029FBA03F79685CF8659AC53F089D65121AD3BC2CA34F13E90355DA9BFA7842AF2AB4EDC31488CDC6970473B3507D898F6272E83E614D7CCB9405581FAE8DF7949F1EEF4BA1FF64DC7F010000FFFF0300504B030414000600080000002100B09DD828BB0700005E3D00001A000000776F72642F7374796C657357697468456666656374732E786D6CB49B6D53DB3810C7DFDFCC7D078FDF4348A0E4CA34ED50E803336D8F3630F75AB115A2C1B67C7E20709FFE5692AD183BB67763F7558963ED6F57BBFAAFA0D2BB0FCF61E03CF12415325AB8D3E313D7E191277D113D2CDCFBBBCF477FB94E9AB1C867818CF8C27DE1A9FBE1FD9F7FBCDB5EA4D94BC053070C44E9C536F616EE26CBE28BC924F5363C64E97128BC44A6729D1D7B329CC8F55A787CB295893F999D4C4FF44F71223D9EA640BB62D1134BDDC25CD8B426631E016B2D939065E9B14C1E26214B1EF3F808ACC72C132B1188EC056C9F9C9766E4C2CD93E8A270E8C83AA4865C18878A7FCA1149238A3D5C33F25A7A79C8A34C1327090FC00719A51B11EFC238D41A84B8295D7AEA0AE2290CCAF7B6F1F4ACC1B3216372709DB02DA46267B0616ECF64F8665018987950F9DD65B56E717AD2154C911165C2FA8071E135B3F4246422B2660E9B9AEAE4C27A1852DF5F1299C7D69D580CB376133D5A5B6A59123C3B39D72BAF1A5A4A32D058BACB0D8BB9EB84DEC5CD432413B60AC0A3EDF4CC5115E9BE07A9F0A577CDD72C0FB2547D4C6E93E263F149FFF3594659EA6C2F58EA097107120256420106BF5E46A970E11BCED2EC32156CEF971BF5D6DE6FBC34AB58FB287CE14E1431FD0F6C3EB160E1CE66E5932BE5C1AB67018B1ECA673C3ABA5F563D59B8F6D10AEC2E5C961C2D2F95B1890EB3FCB7126EFC2A78F8A45D8999072B0F386C9D7110215031C50984CAEE6C0E8A663EFCCAD5E4B23C9305441B0058D52C7CACCD38681328D5D228367CCBD7DFA4F7C8FD65065F2C5CCD8287F737B7899009C8E8C27DFB5631E1E19287E2ABF07DAE1A44F1EC3EDA089FFFB3E1D17DCAFDDDF39F9FB53C17163D994719B87F3ED75510A4FEA7678FC74A26C174C454867FA801A061908E0A473B948B9D37E6418DAA1FFE5B22A726877B291BCE544B73B4FF9D201D753E183453115503D07649BE9E0E377136DCC49BE12674F10E9B8BF9702F6023333423A6362A55894F6A263D537CD579387DDB51B26A44A38A7A47348AA67744A3467A47344AA27744A3027A473412DE3BA291DFDE118D74768EF09816AE7A159DEAD9402DEC3B9105D0277B946E3A50EA8A56E3DCB2843D242CDE38AAB1D6DDEE12CB65BECA70AE6A393D5C2C975922D576B36746A03BABA57BB0267F0AE30D4B05ECCAFB4003A7FE4E6D7D9C2F8980ED6B0FEA8D29BE464C7A63B2B785DD06CCE31B19F83C71EEF8B3C92861FC0FE92CCD2EA3D7B98169FD261E369903BB42D5727B61E72D93DE3E13C6FE3791EA39E8ECE6E72DA1F41947E5F0BCA52EDB8D7FE7BEC8C3726A10BB9173A3E78434D710DAC5EE293A53296AAEAEDE28540230219876410F41DB47F86F9A0BDDBECA31C67FD38A0EB48FF0DF34AE03EDEBFAE8CE2F5969AEE1CF2A0E6A79CDC96BF74A063259E741B9067AE5614E5EC116810B81BC88AD7D9448CCC92BF8957C3A979E07BFB961EA949C8B9D8E1228E474188A5E6CF858C849A9C9DE941011394135D68CC01AA6B5041059747FF127A1FE084C6D065AA5ED5EB377399FB6CC00B420D41EFA672EB3FE3DF4AC45F3B0949B08FE5C927207473B6D5979585A514FA6DF11723CACF11140C33A200134AC1512402DF5D1BEE7B13D110F19DE1C092CB22CDB2EA6CB0EADCC73B2325B10AD058CD43711FBAF96D5DB5E0BCDBE89A09013D4EC9B080A393BB55E66FB2682355ADF44B05ABA467B8EAA9A4A098ADC37AB20BB134044348E782340E3883702348E782340C3C5BB1F329E782358646DB09A5A156F0448BF42F955DF82AAE28D0091B5C1A85DF137A3B2EF692BDDBFDC8E20DE080A39414DF14650C8D969136F044BBF42A9841ACB4A1D82358E782340E3883702348E782340E3883702348E782340C3C5BB1F329E782358646DB09A5A156F04882C0F1654156F0448BF42D186BDE2AD57FD6F176F04859CA0A6782328E4ECD404D56E52112C72826A2C2BDE08967E85520C054B173725A871C41B11D138E28D008D23DE08D038E28D000D17EF7EC878E28D6091B5C16A6A55BC1120B23C585055BC1120B236EC156FBD187FBB782328E40435C51B412167A726A856E7102C72826A2C2BDE0896AE97C1E28D00E9570E0551221A47BC11118D23DE08D038E28D000D17EF7EC878E28D6091B5C16A6A55BC1120B23C585055BC1120B236EC156FBD467EBB782328E40435C51B412167A726A856BC112C72826A2C2B7508D638E28D00E9C21C2CDE08907EE500905E4594348D23DE8888C6116F0468B878F743C6136F048BAC0D5653ABE28D0091E5C182AAE28D0091B5419DB385F3A2E8E3A9D39622C09E33284F35A081B3962461814580BFF89A2770AB90F79F0E19082C2324105BCA031BE247291F1DDCC1EED3960241A3C42A10521FE97ED1A7742A17114EE71D3709EEFEBE72BE9A0B308D71BAA45E9FBC81DB43D5EB42FA7A92BA38047E662F315CD989CB93E5CA1A5C1052F7BA8A2B40FA4EE80D5C082AAEF5A8C1EA9E0FBCA82F55158FF5FFDB1654F819887A6013E56D80E5C18DA80E5471E0DD9E41D2C7DDEBE09653F1DA91DD958CD2CDE274FC6E0F65DE7B7546B3D3EF4C9D04EFF0599F14EF9C2347BF62B2DA74102E676997FA3C8494AD0273C50C7EB8897C88705BDCCE32C9F49F993105DF5FF120F8CEF485B44CC6EDAF067C9D996FA727BA03D64CAD6496C9B07D7CA20F886B4FF6198072A83A633EAA20DAEB24CAC3154F8AE3E6AD25A93A87BE89F6BA24CD59D79652C0CEF4CEB7F2A7F4FDFF000000FFFF0300504B0304140006000800000021000CB2902C4C0100008702000011000801646F6350726F70732F636F72652E786D6C20A2040128A00001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000009C925F6B833014C5DF07FB0E92774DEC9F314453D8469F5628AC63636F21B9B5A1268624ABEDB75FD4D6D96D4F035FE239F79773AEE68BA3AAA20358276B5DA034212802CD6B217559A0D7CD32BE4791F34C0B56D51A0A74028716F4F626E726E3B585B5AD0D582FC14581A45DC64D8176DE9B0C63C777A0984B824307715B5BC57C38DA121BC6F7AC043C21E40E2BF04C30CF700B8CCD404467A4E003D27CDAAA03088EA10205DA3B9C2629FEF67AB0CAFD39D02923A792FE6442A773DC315BF05E1CDC47270763D3344933ED6284FC297E5F3DBF745563A9DB5D714034173CE31698AF2D5D335942B4923B56C97D8E474ABBC58A39BF0A0BDF4A100FA79FE6DF8676C6C241B65F8CA6391E1FC3AD5DC9FE6A1051889DF5252FCADBF4F169B3447442D2491C1E32DFA4B36C3ECB08F968B35DCDB735FA17EA9CF0DFC40B807689AF7F1DFA050000FFFF0300504B0304140006000800000021004E238D853B0700006D3A00000F000000776F72642F7374796C65732E786D6CB49BDF739B3810C7DF6FE6FE0786F7D4B19DC6D74CDD4E9AB6D7CCB4BDB44EE69E6590634D0171203749FFFA5BAD3021606037D0A7981FDA8F56BBFAAEEC48AFDFDEC791F75366B9D2C9D29FBE38F63D99043A54C9EDD2BFB9FE78F497EFE54624A188742297FE83CCFDB76FFEFCE3F5DD596E1E22997B6020C9CFE260E96F8D49CF26933CD8CA58E42F742A1378B8D1592C0C5C66B79358643F76E951A0E35418B55691320F93D9F1F1A95F98C92856F466A302F95E07BB582606DB4F321981459DE45B95E67B6B77146B773A0BD34C0732CFC1E93872F662A192D2CCF4A461285641A673BD312FC09989EBD1C49A82E6D363FC1447BE17076797B789CEC43A82C1BB9B9EF86F60E4421DBC971BB18B4C6E2FB3ABACB82CAEF0CF479D98DCBB3B1379A0D4350C29188815D8FA749EE4CA872752E4E63C57E2E0C3AD7DEBE0932037156BEF54A8FC8925E6BFC0E64F112DFDD96C7FE7C2F6E0C9BD4824B7FB7B3239BA59557BB2F4CB5B6BB0BBF44576B43AB7C626E8E6FE6FC5DDF489F370855D494500C1008ED81809490139623991B239385B40BEB88BEF3B3BAE62677401410300AB9A85CBDA8843AE40E6AC5C02C353B9F9AC831F325C1978B0F49105376F2EAF32A53348D2A5FFEA9565C2CD958CD5271586D2CE97E2DE4DB255A1FC772B939B5C868FF7BF7DC4E42F2C067A9718E8FEE902B320CAC30FF7814C6DDA82E944D8087FB50D2071201C150E7668A71E7BE36ED4A878F3BF3D72EA627890B295C2CE700FFBDF0942AF77834133EB51D501B4CBEAEB7CB88993E1265E0E3781C93B6C2C16C37B01BA3E34222E372A59490FAAD1814BBEEA38CC5F75A4AC6DD1C8A2DE168DA4E96DD1C891DE168D94E86DD1C880DE168D80F7B668C4B7B745239C9D2D0281C255CFA2398E0669625F2B1349DBBE5380A603A5AE2835DE95C8C46D26D2AD670B6BBDDB5D62B9DAAD0DADAB28A7CF17CB95C97472DB3B22509DEDD47DB6267F88D3ADC815AC927A867E3670E8AFEDAAC7FB3B53612FEAA54BBE864FB8303958C2AE2211C8AD8E429979D7F2DE4594D1FEABF6566E95D1DBB98161FDAC6EB7C65B6DB1E4F6C24E5B06BD7D249CFDCF2AC731E89C4CA72DAEF41927C5F0B4252FDB8D7F91A1DAC5FBA121AC464E9D9E33C25C436017BB87E8C486A839BB7ABDB001A0B8E0CA05DF05B44FE8BF2B2E7CFB36C694FEBB52F44CFB84FEBBC2F54CFB981FDDF1652BCD7BF8D2EA91A6D7823D772F74A4B3CD2EDACF815E7958B0677089A0B9C09EC4A57D92482CD833F8897C7AE74100DFDC2879CA8EC5A38E3228EC70380A4E36BA2FECA0D4646FCAF0881DA01A6BC6600DD35A06882DBADFE54F657F13E3160354E972ADD93B9DE72D23002588B486FEB6D3A67F0D3D6BD13C2AE532819F4B72E9D168F3969947A515F9E4EA1D23C6C30A1F0334AC023240C34A2103D4921FED6B9EB226D221C38B2383C596E5B28A61DA919579C156E612C42B0123D54DC2FAAB65F6B6E742B36E1228EC0035EB2681C28E4EAD96957593C01AAD6E12582D55A33D46554DE538C5AE9B5550B9122078348E781340E3883701348E781340C3C5BB1F329E7813586C6D2835B52ADE0410BEC2F9AA5F82AAE24D00B1B5C1A95DF19BD1BEEEA195EE2FB723883781C20E5053BC09147674DAC49BC0C25738995063955247608D23DE04D038E24D008D23DE04D038E24D008D23DE04D070F1EE878C27DE04165B1B4A4DAD8A3701C496871254156F02085FE168C341F1C659FFDBC59B406107A829DE040A3B3A35412D17A904163B40355629DE0416BEC249868285C9CD716A1CF12678348E781340E3883701348E781340C3C5BB1F329E7813586C6D2835B52ADE04105B1E4A5055BC0920B6361C146F9C8CBF5DBC091476809AE24DA0B0A35313D452E7082C76806AAC52BC092CCC97C1E24D00E12BCF05713C1A47BC091E8D23DE04D038E24D000D17EF7EC878E24D60B1B5A1D4D4AA7813406C79284155F12680D8DA7050BC718EFC76F12650D8016A8A3781C28E4E4D504BF126B0D801AAB14AA923B0C6116F02081373B0781340F8CA3340388B38611A47BC091E8D23DE04D070F1EE878C27DE04165B1B4A4DAD8A3701C496871254156F0288AD0D769F2DEC17256F4F9DB62401759FC17E570319386B0912155838F85D6E640687AC64FFEE9081C0BD870C624B7A505D7CA7F50F8FB6B17BDE922064945A474AE396EE07DCA5533988305F749C24B8FEE7C2FBE40EC034DA614A3DDD7903A787AAC785F078923D3804FD340F291CD949F73BCBAD35382064CF75154780F088DC251C082A8EF5D8C6F69C0FBC8887AA8ADBF87FDB820A9F81880D9BA8600BAC004E4475A08A0DEFE51E24DCEE5E07B7EC8AC78E3C1EC9D877B3D81DFFB88672EF3DD9A3D9D96F63778277F419778A778E9187AFB8A8363B0887B3B04B7D3D8490AD2377C40C3E5C262178088704F1BF662E98E1BD70A6E0F9858CA22F020FA4199DB6BF1AC98D714FA7C758016BA6D6DA181DB7B7CF708338F6E4900148876A67DCA575A23D4F925DBC96199CF0EA18F3AFDA560E3C89F63425DD5ED79654A08EF463DFF69FF237FF030000FFFF0300504B03041400060008000000210016E9414AC3010000A204000012000000776F72642F666F6E745461626C652E786D6CA492DD4EE3301085EF57DA77887C4FEDA481858814A1EE56E2662F56F000AEEB3416FE893C6EB37D7B26761A2E2A440B8E1425673C47339FCEFDC37FA3B3BDF4A09CAD493E63249356B88DB2DB9ABC3CAFAE6E490681DB0DD7CECA9A1C249087C5CF1FF77DD5381B20C37E0B9511356943E82A4A41B4D27098B94E5A2C36CE1B1EF0D76FA9E1FE75D75D09673A1ED45A69150EB460EC868C36FE1C17D7344AC8DF4EEC8CB421F6532F353A3A0BADEAE0E8D69FE3D63BBFE9BC13120077363AF919AEEC64939727464609EFC0356186CBD034111DACB03D67F1CB689219513D6DADF37CAD915D9F97643182CBFACA7283E2926BB5F62A163A6E1DC81C6B7BAE6BC20AB662D7F81E9E92CD8737A1838368B90719A68B2CC90D374A1F8E2AF40A20153A15447BD4F7DCAB61A05402B5C5C20ED6AC267F189E62B52249C96B52A2F0B89C9402874A271FEFCC2705938383459F7825BF8B3EA8A0CFD815E7A4293A27249E959190FD957DF6CF196E3F2052B01B24718D3C0632F38B88F8E81B095E40A4789CF6C74D96B8CAAFDBF2B8FF3B91BBCF89249FF3892CB9C168F00F480C04128981C865D9F81A89D36CB07262F34E22260113F59D6C8C2181C51B000000FFFF0300504B030414000600080000002100B2F1AF5380010000DA02000010000801646F6350726F70732F6170702E786D6C20A2040128A00001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000009C524D4FE33010BD23F11FA2DC5BA72BC42234355A15AD38F051A901CE237B925838B6659B8FFEFB9D90360471DB9CE6BD993CBF79365C7DF4B678A3988C77EB72B5ACCA829CF2DAB8765D3ED67F17176591323A8DD63B5A977B4AE5953C3D816DF4816236940A9670695D7639874B2192EAA8C7B4E4B6E34EE3638F99616C856F1AA3E8DAABD79E5C16BFAAEA5CD04726A7492FC224588E8A976FF97F45B55783BFF454EF031B9650531F2C6692F7831D0B6222A0F6196D6D7A9215D313802DB694E40AC458C0B38F9A311363059B0E23AACCD9C9F30B1033087F42B04661E650E59D51D127DFE4E2E173FD62F81DC47C0438921DA9D768F27E703187706BDCE8632CD857C43662E80EE626043B859636BCB76CD02602F145C0C6F701DD5EDED0BBA59C175B542F187571E0D9FE616038EF253D86DA5F0F791D94BE93B3D59F4DEE7601155BFCFD2D845903769C1469DEEA28F745C00DDF50B4C3991CA06B491F677E3686589FC6A72A5767CB8ABFCF1C8F1C5FD5F486E43F000000FFFF0300504B01022D001400060008000000210009248782810100008E0500001300000000000000000000000000000000005B436F6E74656E745F54797065735D2E786D6C504B01022D00140006000800000021001E911AB7F30000004E0200000B00000000000000000000000000BA0300005F72656C732F2E72656C73504B01022D00140006000800000021007C3B973922010000B90300001C00000000000000000000000000DE060000776F72642F5F72656C732F646F63756D656E742E786D6C2E72656C73504B01022D00140006000800000021007042FB03AC02000028080000110000000000000000000000000042090000776F72642F646F63756D656E742E786D6C504B01022D001400060008000000210030DD4329A8060000A41B000015000000000000000000000000001D0C0000776F72642F7468656D652F7468656D65312E786D6C504B01022D0014000600080000002100D406ABD771030000970800001100000000000000000000000000F8120000776F72642F73657474696E67732E786D6C504B01022D001400060008000000210017A0164E02010000AC010000140000000000000000000000000098160000776F72642F77656253657474696E67732E786D6C504B01022D0014000600080000002100B09DD828BB0700005E3D00001A00000000000000000000000000CC170000776F72642F7374796C657357697468456666656374732E786D6C504B01022D00140006000800000021000CB2902C4C010000870200001100000000000000000000000000BF1F0000646F6350726F70732F636F72652E786D6C504B01022D00140006000800000021004E238D853B0700006D3A00000F0000000000000000000000000042220000776F72642F7374796C65732E786D6C504B01022D001400060008000000210016E9414AC3010000A20400001200000000000000000000000000AA290000776F72642F666F6E745461626C652E786D6C504B01022D0014000600080000002100B2F1AF5380010000DA02000010000000000000000000000000009D2B0000646F6350726F70732F6170702E786D6C504B0506000000000C000C0009030000532E00000000

Open in new window

0
 
LVL 7

Author Comment

by:Camillia
ID: 38857869
I added one more debugging step and I see a negative number..

while @i<=@l
BEGIN
        PRINT 'before'
        PRINT @i
        PRINT  @l
        PRINT @estring
        
        PRINT @l - @i
        
        SELECT @i = @i + 8000 ,
                @estring =@estring+
                                 ENCRYPTBYKEY(KEY_GUID('ArmsTableKey'),
                                              SUBSTRING(@doc, @i,
                                                        CASE WHEN @i + 8000 < @l
                                                             THEN 8000
                                                             ELSE @l - @i
            
                                                        END))
            print 'after'                                              
            PRINT @i
            PRINT  @l   
            PRINT @estring  
            PRINT @l - @i                                       
                                                        
end

Open in new window


Output

before
1
12658
 
12657
after
8001
12658
 
4657
before
8001
12658
 
4657
Msg 537, Level 16, State 5, Procedure usp_AddDocFiles, Line 35
Invalid length parameter passed to the LEFT or SUBSTRING function.
after
16001
12658
 
-3343

Open in new window

0
 
LVL 7

Author Comment

by:Camillia
ID: 38859764
I think somehow a comparison should be done to break that loop if the value is negative or the bytes have all been read...
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 38860018
the loop ends when the @i value exceeds the length....

@l-@i should be negative in the  after case after the processing of the last segment...

try it this way

while @i<=@l
BEGIN
        PRINT 'before'
        PRINT @i
        PRINT  @l
        PRINT @estring
       
        PRINT @l - @i
       
        SELECT
                @estring =@estring+
                                 ENCRYPTBYKEY(KEY_GUID('ArmsTableKey'),
                                              SUBSTRING(@doc, @i,
                                                        CASE WHEN @i + 7999 <= @l
                                                             THEN 8000
                                                             ELSE @l - @i +1            
                                                        END))
       set @i = @i + 8000
            print 'after'                                              
            PRINT @i
            PRINT  @l  
            PRINT @estring  
            PRINT @l - @i                                      
                                                       
end
0
 
LVL 7

Author Comment

by:Camillia
ID: 38860080
No error not but @estring comes out as null. I execute the SP, row is inserted but the value is null...

I added "SELECT @estring" to the loop. It prints it twice and both times it's null...
0
 
LVL 7

Author Comment

by:Camillia
ID: 38860096
This is what I see with the select-debug steps I placed

while @i<=@l
BEGIN
            
        SELECT 
                @estring =@estring+
                                 ENCRYPTBYKEY(KEY_GUID('ArmsTableKey'),
                                              SUBSTRING(@doc, @i,
                                                        CASE WHEN @i + 7999 <= @l
                                                             THEN 8000
                                                             ELSE @l - @i +1            
                                                        END))
       set @i = @i + 8000 
           
            SELECT @l - @i  
            SELECT @i
            SELECT @estring                                     
                                                        
end 

Open in new window


4657  -- this is select @l - @i
8001 -- this is @i
null  -- this is @estring

-3343 --  this is select @l - @i
16001 -- this is @i
null -- this is @estring
0
 
LVL 7

Author Comment

by:Camillia
ID: 38860109
I wonder if @estring needs to be initialized first to blank
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 38860189
yes probably - sorry
0
 
LVL 7

Author Comment

by:Camillia
ID: 38860203
Yeah, when I do this, it prints the value. Looking into how to set a varbinary(max) to blank...

 select  ENCRYPTBYKEY(KEY_GUID('ArmsTableKey'),
                                              SUBSTRING(@doc, @i,
                                                        CASE WHEN @i + 7999 <= @l
                                                             THEN 8000
                                                             ELSE @l - @i +1            
                                                        END))
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 7

Author Comment

by:Camillia
ID: 38860232
I tried this
SET @estring = CONVERT(VARCHAR(MAX),@estring)

but get
Implicit conversion from data type varchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query.
0
 
LVL 7

Author Comment

by:Camillia
ID: 38860282
I tried this bust still comes out as null
SET @estring = 0x000
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 38860334
ok brute force try this

declare @temp varbinary(8000)

  SELECT
                @temp =
                                 ENCRYPTBYKEY(KEY_GUID('ArmsTableKey'),
                                              SUBSTRING(@doc, @i,
                                                        CASE WHEN @i + 7999 <= @l
                                                             THEN 8000
                                                             ELSE @l - @i +1            
                                                        END))
 set @i=@i+8000
 set @estring=@temp
while @i<=@l
BEGIN
           
        SELECT
                @estring =@estring+
                                 ENCRYPTBYKEY(KEY_GUID('ArmsTableKey'),
                                              SUBSTRING(@doc, @i,
                                                        CASE WHEN @i + 7999 <= @l
                                                             THEN 8000
                                                             ELSE @l - @i +1            
                                                        END))
       set @i = @i + 8000
           
            SELECT @l - @i  
            SELECT @i
            SELECT @estring                                    
                                                       
end
0
 
LVL 7

Author Comment

by:Camillia
ID: 38860380
No, still null. I printed @temp as well and still null. I think they need to be initialized but I tried
set @estring = 0 and set @temp = 0 and still null

declare @temp varbinary(8000)
SET @temp = 0
  SELECT 
                @temp =
                                 ENCRYPTBYKEY(KEY_GUID('ArmsTableKey'),
                                              SUBSTRING(@doc, @i,
                                                        CASE WHEN @i + 7999 <= @l
                                                             THEN 8000
                                                             ELSE @l - @i +1            
                                                        END))
 set @i=@i+8000
 
 SELECT @temp
 
 set @estring=@temp
while @i<=@l
BEGIN
            
        SELECT 
                @estring =@estring+
                                 ENCRYPTBYKEY(KEY_GUID('ArmsTableKey'),
                                              SUBSTRING(@doc, @i,
                                                        CASE WHEN @i + 7999 <= @l
                                                             THEN 8000
                                                             ELSE @l - @i +1            
                                                        END))
       set @i = @i + 8000 
           
            SELECT @l - @i  
            SELECT @i
            SELECT @estring                                     
                                                        
end 

Open in new window

0
 
LVL 7

Author Comment

by:Camillia
ID: 38860846
This comes out as null (the @temp part). I added select @temp in the while loop and that came out as null as well. I just dont know how to init a varbinary(max) field so I could init @temp or @estring...

 SELECT
                --@temp =
                                 ENCRYPTBYKEY(KEY_GUID('ArmsTableKey'),
                                              SUBSTRING(@doc, @i,
                                                        CASE WHEN @i + 7999 <= @l
                                                             THEN 8000
                                                             ELSE @l - @i +1            
                                                        END))
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 38860963
just a thought you have opened the encrytion key before (during the session) running this t-sql?  

(as that bit is now missing from the code fragments we've been passing...)
0
 
LVL 7

Author Comment

by:Camillia
ID: 38860977
The @temp section works if it's inside the while-loop. It prints a value and it's not null. It's when adding them up that @estring comes out null

while @i<=@l
BEGIN
               
          ---- **** this prints a value      
        select  ENCRYPTBYKEY(KEY_GUID('ArmsTableKey'),
                                              SUBSTRING(@doc, @i,
                                                        CASE WHEN @i + 7999 <= @l
                                                             THEN 8000
                                                             ELSE @l - @i +1            
                                                        END)) 
        SELECT 
                @estring =@estring +
                                 ENCRYPTBYKEY(KEY_GUID('ArmsTableKey'),
                                              SUBSTRING(@doc, @i,
                                                        CASE WHEN @i + 7999 <= @l
                                                             THEN 8000
                                                             ELSE @l - @i +1            
                                                        END))
       set @i = @i + 8000 
                                              
                                                        
end 

Open in new window

0
 
LVL 7

Author Comment

by:Camillia
ID: 38861011
I run the stored proc and look at the table. The stored proc has the encryption key. When I look at the table, that column is null...

Now, I just changed the the while section to this as test and now I dont get null. It's that @estring that's making it null. I looked at the table and that column is not null...but of coruse, the data is not correct because I have a "test' added to it...


 SELECT
                @estring = CAST('test' AS VARBINARY(MAX)) +
                                 ENCRYPTBYKEY(KEY_GUID('ArmsTableKey'),
                                              SUBSTRING(@doc, @i,
                                                        CASE WHEN @i + 7999 <= @l
                                                             THEN 8000
                                                             ELSE @l - @i +1            
                                                        END))
0
 
LVL 7

Author Comment

by:Camillia
ID: 38861067
I initialized it like below but still null. But if I do like above (ID: 38861011), I wont get null


SET @estring = CONVERT(varbinary(MAX),'test')
while @i<=@l
BEGIN
               
       SELECT
                @estring =  @estring +
                                 ENCRYPTBYKEY(KEY_GUID('ArmsTableKey'),
                                              SUBSTRING(@doc, @i,
                                                        CASE WHEN @i + 7999 <= @l
                                                             THEN 8000
                                                             ELSE @l - @i +1            
                                                        END))
       set @i = @i + 8000
                                             
                                                       
end
0
 
LVL 7

Author Comment

by:Camillia
ID: 38861099
Also tried this


SET @estring = CONVERT(varbinary(MAX),'')

while @i<=@l
BEGIN

       SELECT
                @estring =  ENCRYPTBYKEY(KEY_GUID('ArmsTableKey'),@estring) +
                                 ENCRYPTBYKEY(KEY_GUID('ArmsTableKey'),
                                              SUBSTRING(@doc, @i,
                                                        CASE WHEN @i + 7999 <= @l
                                                             THEN 8000
                                                             ELSE @l - @i +1            
                                                        END))
       set @i = @i + 8000
                                             
                                                       
end
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 38861138
ok try initialising @temp and @estring like this

set @temp=convert(varbinary(8000),'')
set @estring=convert(varbinary(max),'')
print datalength(@temp)
print @temp
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 38861148
cross over
0
 
LVL 7

Author Comment

by:Camillia
ID: 38861199
I tried it below.

set @temp=convert(varbinary(8000),'')
set @estring=convert(varbinary(max),'')
print datalength(@temp)  --- comes as zero
print @temp
--- comes out as 0x

while @i<=@l
BEGIN

       SELECT
                @estring =  @estring +
                                 ENCRYPTBYKEY(KEY_GUID('ArmsTableKey'),
                                              SUBSTRING(@doc, @i,
                                                        CASE WHEN @i + 7999 <= @l
                                                             THEN 8000
                                                             ELSE @l - @i +1            
                                                        END))
        --SET @temp = @estring    
        --SELECT @temp                                            
       set @i = @i + 8000
                                             
                                                       
end
0
 
LVL 7

Author Comment

by:Camillia
ID: 38861211
Ok, if i do this ...not the extra space, then the datalength is one

set @temp=convert(varbinary(8000),' ')
set @estring=convert(varbinary(max),' ')
print datalength(@temp)  --- this comes out as one
print @temp
0
 
LVL 7

Author Comment

by:Camillia
ID: 38861258
If I add @temp instead of @estring...then it prints a value. Adding @esting back..makes it null


set @temp=convert(varbinary(8000),' ')
set @estring=convert(varbinary(max),' ')
print datalength(@temp)
PRINT datalength(@estring)
print @temp
PRINT @estring

while @i<=@l
BEGIN

       SELECT
                @estring =  @temp +
                                 ENCRYPTBYKEY(KEY_GUID('ArmsTableKey'),
                                              SUBSTRING(@doc, @i,
                                                        CASE WHEN @i + 7999 <= @l
                                                             THEN 8000
                                                             ELSE @l - @i +1            
                                                        END))
           PRINT @estring                                            
                                         
       set @i = @i + 8000
                                             
                                                       
end
0
 
LVL 7

Author Comment

by:Camillia
ID: 38861261
So weird, I tried this as well and still null

set @temp=convert(varbinary(8000),' ')
set @estring=convert(varbinary(8000),' ')
print datalength(@temp)
PRINT datalength(@estring)
print @temp
PRINT @estring

while @i<=@l
BEGIN

       SELECT
                @estring =  @estring +
                                 ENCRYPTBYKEY(KEY_GUID('ArmsTableKey'),
                                              SUBSTRING(@doc, @i,
                                                        CASE WHEN @i + 7999 <= @l
                                                             THEN 8000
                                                             ELSE @l - @i +1            
                                                        END))
           PRINT @estring                                            
                                         
       set @i = @i + 8000
                                             
                                                       
end
0
 
LVL 7

Author Comment

by:Camillia
ID: 38861298
Another thing I tried


set @temp=convert(varbinary(8000),' ')
set @estring=convert(varbinary(8000),' ')

print datalength(@temp)
PRINT datalength(@estring)
print @temp
PRINT @estring

while @i<=@l
BEGIN

       SELECT
                @estring =  @temp +
                                 ENCRYPTBYKEY(KEY_GUID('ArmsTableKey'),
                                              SUBSTRING(@doc, @i,
                                                        CASE WHEN @i + 7999 <= @l
                                                             THEN 8000
                                                             ELSE @l - @i +1            
           
                                                        END))
           PRINT '@estring'                                            
           PRINT @estring  
           PRINT '@temp before assignment'
           PRINT @temp
           SET @temp = @estring
           PRINT '@temp after assignment'
           PRINT @temp                                          
                                         
       set @i = @i + 8000
                                             
                                                       
end

Output is below. All come out as blank except for '@temp before assignment'


1
1
0x20
0x20
@estring
 
@temp before assignment
0x20
@temp after assignment
 
@estring
 
@temp before assignment
 
@temp after assignment

Open in new window

0
 
LVL 7

Author Comment

by:Camillia
ID: 38861307
If I do this, it prints a value but somehow i need to put the chucks back

@estring =  --@temp  + -- this is removed
                                 ENCRYPTBYKEY(KEY_GUID('ArmsTableKey'),
                                              SUBSTRING(@doc, @i,
                                                        CASE WHEN @i + 7999 <= @l
                                                             THEN 8000
                                                             ELSE @l - @i +1            
           
                                                        END))
0
 
LVL 7

Author Comment

by:Camillia
ID: 38861399
If I remove the ecnryption, then I get values back. And Then I do the encrption in the Insert statement which means I'm back to square one...same error! Not sure what else to do....

  SELECT
                @estring =@estring +
                                -- ENCRYPTBYKEY(KEY_GUID('ArmsTableKey'),
                                              SUBSTRING(@doc, @i,
                                                        CASE WHEN @i + 7999 <= @l
                                                             THEN 8000
                                                             ELSE @l - @i +1            
                                                        END) --)
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 38861456
found this article http://blogs.msdn.com/b/yukondoit/archive/2005/11/24/496521.aspx
which suggests that there is an overhead on the encrypted output...

so the chunking would get more complex...we'd need to store 2 lengths(?) source and encrypted then the encrypted data  in each chunk and so forth...

to initially just check if it is because we are passing 8000 bytes of data...

lets alter the encryptby part to

set @temp=convert(varbinary(8000),'')
set @estring=convert(varbinary(8000),'')
print datalength(@temp)
PRINT datalength(@estring)
print @temp
PRINT @estring

while @i<=@l
BEGIN

       SELECT
                @estring =  @temp +
                                 ENCRYPTBYKEY(KEY_GUID('ArmsTableKey'),
                                              SUBSTRING(@doc, @i,
                                                        CASE WHEN @i + 7899 <= @l
                                                             THEN 7900
                                                             ELSE @l - @i +1            
           
                                                        END))
           PRINT '@estring'                                            
           PRINT @estring  
           PRINT '@temp before assignment'
           PRINT @temp
           SET @temp = @estring
           PRINT '@temp after assignment'
           PRINT @temp                                          
                                         
       set @i = @i + 7900
                                             
                                                       
end

ie trying it in 7900 byte chunks since the article suggested 7920 was a probably max...

but it may be time to accept that this encryption method needs to be re-thought on your part and a custom approach adopted... on the trail of this article there was some reference to using a custom approach...

http://social.msdn.microsoft.com/Forums/en/sqlsecurity/thread/088d092c-5402-476e-824f-5bd95ac258b7
0
 
LVL 7

Author Comment

by:Camillia
ID: 38861464
Thanks, let me read the article. Maybe I should encrypt the file in code and then store. But let me read and try what u have.
0
 
LVL 7

Author Comment

by:Camillia
ID: 38861484
And that limit is probably correct. I was playing around with this and when I changed the value from 7999 to 5000 (after I saw your post)...that's when I got values


declare @temp varbinary(8000)
set @temp= ENCRYPTBYKEY(KEY_GUID('ArmsTableKey'),convert(varbinary(8000),' '))

set @estring= ENCRYPTBYKEY(KEY_GUID('ArmsTableKey'),convert(varbinary(8000),' '))

   SELECT @temp = @temp + ENCRYPTBYKEY(KEY_GUID('ArmsTableKey'),SUBSTRING(@doc, 1,5000)) -- try 5000 after i saw the post
 
 SELECT '@temp'
 SELECT @temp
 
 SELECT '@estring - before assignment'
  SELECT @estring
 
 set @estring=@estring + ENCRYPTBYKEY(KEY_GUID('ArmsTableKey'),@temp) -- this didnt work. When it was @temp, it worked
  SELECT '@estring - after assignment'
 SELECT @estring
0
 
LVL 7

Author Comment

by:Camillia
ID: 38861527
Yes, that one worked..ID: 38861456....you're the best :) Let me test some more. I'll leave this open for one more day. Thanks so much for sticking with this.
0
 
LVL 7

Author Closing Comment

by:Camillia
ID: 38866525
Thanks so much for your help.
0
 
LVL 7

Author Comment

by:Camillia
ID: 38866879
dang it, after all this work...when I read the file back in code...it's corrupted :(
0
 
LVL 7

Author Comment

by:Camillia
ID: 38866885
ah, i wonder if i need to read it back in chunks as well. When I saw the file, it's 84851 bytes. When I read it back, it's  5892.


OPEN SYMMETRIC KEY ARMSTableKey DECRYPTION

BY CERTIFICATE EncryptArmsCert


select CONVERT(VARCHAR(MAX),DECRYPTBYKEY(NameFile)) AS NameFile,
       ContentType, 
       DATALENGTH(doc),
     
       CONVERT(VARBinary(MAX),DECRYPTBYKEY(Doc)) AS Doc
       from dbo.DocumentFiles where id=14

Open in new window

0
 
LVL 7

Author Comment

by:Camillia
ID: 38868419
I'm close...when I save the doc, it's datalength is 4804. when I read it back, it's 4761. Something must be missing when I read it back...this is what i have

 declare @estring varbinary(max),@i bigint,@l bigint
select @i=1
SELECT @l= datalength(doc) FROM dbo.DocumentFiles WHERE Id = @Id

declare @temp varbinary(8000)

set @temp=convert(varbinary(8000),'')
set @estring=convert(varbinary(8000),'')

 SELECT 'before loop'
 SELECT '@l'
 SELECT @l
 
while @i<=@l
BEGIN

       SELECT 'in loop'
       SELECT 
                @estring =  @temp + 
                                              SUBSTRING(CONVERT(VARBinary(MAX),DECRYPTBYKEY(Doc)), @i,
                                                        CASE WHEN @i + 7899 <= @l
                                                             THEN 7900
                                                             ELSE @l - @i +1            
           
                                                        END)
                   FROM dbo.DocumentFiles WHERE Id = @Id
                                                     
                                          
       set @i = @i + 7900 
                                              
                                                        
end 

SELECT DATALENGTH(@estring)

Open in new window

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the fileā€¦
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now