none
VARBINARY -vs- VARBINARY(MAX) truncate issue RRS feed

  • Question

  • Can someone tell me why the following statements give me this error (String or binary data would be truncated.) when using VARBINARY(MAX) but not VARBINARY:

    OPEN SYMMETRIC KEY sk_TestKey
    DECRYPTION BY CERTIFICATE cert_TestCertificate
      SELECT CONVERT(IMAGE, ENCRYPTBYKEY(KEY_GUID('sk_TestKey'), CAST(Data AS VARBINARY(MAX)) ) ) FROM MyTestTable

    but these following statements will work:

    OPEN SYMMETRIC KEY sk_TestKey
    DECRYPTION BY CERTIFICATE cert_TestCertificate
      SELECT CONVERT(IMAGE, ENCRYPTBYKEY(KEY_GUID('sk_TestKey'), CAST(Data AS VARBINARY) ) ) FROM MyTestTable

    I also get the same error if I try these statements as well:

    OPEN SYMMETRIC KEY sk_TestKey
    DECRYPTION BY CERTIFICATE cert_TestCertificate
      SELECT CONVERT(IMAGE,DECRYPTBYKEY(CAST(Data AS VARBINARY(Max)))) AS Data FROM MyTestTable

    or

    SELECT CONVERT(IMAGE,DecryptByKeyAutoCert(CERT_ID('cert_TestCertificate'),NULL,CAST(Data AS VARBINARY(Max)))) AS Data FROM MyTestTable

    Thursday, April 5, 2012 7:14 PM

Answers

  • Hi Erland,

    This was posted as a bug here: http://connect.microsoft.com/SQLServer/feedback/details/243966/encryption-with-rsa-512. Laurentiu mentioned the limits here: http://blogs.msdn.com/b/lcris/archive/2008/01/11/sql-server-2005-why-you-should-not-encrypt-data-with-certificates.aspx.

    The limitation and the fact that ENCRYPTBYASMKEY returns NULL when it's exceeded, should appear in Books Online, but it doesn't seem to.

    Sunday, April 8, 2012 1:09 AM
  • Kalman,

    I've tested your script with big data and it returns NULL for 54 characters and more.

    Here is my test:

    USE Test;
    GO
     
    -- DROP TABLE dbo.UserLogin
    CREATE TABLE dbo.UserLogin (
      UserLoginID INT    IDENTITY ( 1 , 1 )    PRIMARY KEY,
      LoginName   CHAR(30)    NOT NULL,
      [PassWord]  VARBINARY(MAX)    NOT NULL,
      IsActive    BIT    NOT NULL CONSTRAINT DF_UserLogin_IsActive DEFAULT ((1)),
      CreateDate  DATETIME NOT NULL CONSTRAINT DF_UserLogin_CreateDate DEFAULT (getdate()),
      ModifyDate  DATETIME NOT NULL CONSTRAINT DF_UserLogin_ModifyDate DEFAULT (getdate()),
      ModifiedBy  CHAR(6)    NOT NULL CONSTRAINT DF_UserLogin_ModifiedBy DEFAULT ('system'))
    GO
     
    -- drop ASYMMETRIC KEY Asym_PassWord
    CREATE ASYMMETRIC KEY Asym_PassWord WITH ALGORITHM = RSA_512 
    ENCRYPTION BY PASSWORD = N'secreT007!'
     
    DECLARE  @CipherString VARBINARY(MAX);
     
    SELECT @CipherString = EncryptByAsymKey(AsymKey_ID('Asym_PassWord'),N'SecretPass!01');
     
    INSERT INTO UserLogin
               (LoginName,
                [PassWord])
    VALUES     ('administrator',@CipherString);
    GO
     
    DECLARE  @CipherString VARBINARY(MAX), @pwd varbinary(max);
    set @pwd = convert(varbinary(max),replicate('A',54))
     
    SELECT @CipherString = EncryptByAsymKey(AsymKey_ID('Asym_PassWord'),@pwd);
    print @CipherString
     
    INSERT INTO UserLogin
               (LoginName,
                [PassWord])
    VALUES     ('operator',@CipherString);
    GO
     
    SELECT *
    FROM   UserLogin
    GO
     
    -- Following query can be used to test an entered password:
    SELECT      LoginName,
                PassWordDecrypted= convert(nvarchar(128),
                DecryptByAsymKey(AsymKey_ID('Asym_PassWord'),
                [PassWord], N'secreT007!' ))
    FROM UserLogin
    WHERE LoginName = 'operator'
    GO
     
    SELECT      LoginName,
                PassWordDecrypted= convert(nvarchar(128),
                DecryptByAsymKey(AsymKey_ID('Asym_PassWord'),
                [PassWord], N'secreT007!' ))
    FROM UserLogin
    WHERE LoginName = 'administrator'
    GO
    -- Cleanup
    --DROP TABLE dbo.UserLogin
    ------------

    53 characters work, 54 characters return NULL.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, April 5, 2012 10:43 PM
    Moderator
  • I tried using RSA_2048, and that upped the limit to 245 characters.

    I don't know why this happens. I would have understood it the resulting cipher string had exceeded 8000 bytes, but that seems unlikely. However, here is an interesing observation: when algorithm is RSA_512, the key length for 53 characters is 64 bytes. For RSA_2048 the key length for 256 characters it's 256 bytes.

    As Kalman notes, there is no information in Books Online to be found. I don't know if this is a restriction withg asymmetric keys in SQL Server or limitation with asymmetric-key algorithms in general.

    However, as I understand it, you are not supposed to do this. You are supposed to encrypt your data with symmetric keys, because encryption and decryption with symmetric keys are much more efficient. You only use asymmetric keys to protect your symmetric keys.

    I ran a test with a symmetric key, password-protected using AES_256, and I found that the upper limit in this case is 7943 characters (with a key length of 7988 characters.) 7944 to 8000 characters give NULL back. 8001 or more characters gives the error "String or binary data would be truncated".


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, April 6, 2012 7:34 PM
  • Great to see you around Steve!

    Laurentiu's blog post clears up everything.

    I've filed
    https://connect.microsoft.com/SQLServer/feedback/details/735926/topic-for-encryptbyasymkey-fails-to-mention-that-return-value-may-be-null-if-input-is-too-long
    to have the documentation updated.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, April 8, 2012 10:01 AM
  • Erland,

    Thanks for the update.

    Here's some info  (http://blogs.msdn.com/b/yukondoit/archive/2005/11/24/496521.aspx) you and others may find useful that did solve my problem.  The solution was initially was created for SQL 2005 but it will work with SQL 2008 as well.

    I just replaced the built-in functions (EncryptByKey and DecryptByKey) I was calling, with the user defined one's (dbo.EncryptLob, dbo.DecryptLob) they created as shown below and it worked well.  The user defined functions will call EncryptByKey and DecryptByKey for you.

    OPEN SYMMETRIC KEY sk_TestKey
    DECRYPTION BY CERTIFICATE cert_TestCertificate
     UPDATE MyTestTable
     SET Data = CONVERT(IMAGE, dbo.EncryptLob(KEY_GUID('sk_TestKey '), CAST(Data AS VARBINARY(MAX))))
     
    OPEN SYMMETRIC KEY sk_TestKey
    DECRYPTION BY CERTIFICATE cert_TestCertificate
        SELECT CONVERT(IMAGE,dbo.DecryptLob(CAST(Data AS VARBINARY(Max)))) AS Data FROM MyTestTable

    • Marked as answer by Stephanie Lv Tuesday, April 10, 2012 8:53 AM
    Monday, April 9, 2012 4:07 PM

All replies

  • Try using varbinary(8000) in your code instead.

    See also

    http://blog.waldenl.com/2009/12/computing-md5-hash-of-varbinarymax-in.html


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, April 5, 2012 9:40 PM
    Moderator
  • I can understand why you get the error when you use varchar(MAX): the data is too long for the function, and raises an error.

    When you CAST(Data AS varbinary) this means varchar(30), but in this case truncation happens silently. Which means that you lose information when you encrypt. Which of course is bad.

    The topic for enscryptbykey does not say that it does not support the MAX data types - only that the return type is varbinary(8000) which is a hint. It seems that if you want to encrypt long data, you will need to break it up in chunks.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, April 5, 2012 10:08 PM
  • Why image data type? It is obsolete. BOL: "ntext , text, and image
    data types will be removed in a future version of Microsoft SQL Server. Avoid
    using these data types in new development work, and plan to modify applications
    that currently use them. Use nvarchar(max),  varchar(max), and varbinary(max)
    instead. "

    Encryption article:

    http://www.sqlusa.com/bestpractices2008/password-encryption/


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Thursday, April 5, 2012 10:19 PM
    Moderator
  • Kalman,

    I've tested your script with big data and it returns NULL for 54 characters and more.

    Here is my test:

    USE Test;
    GO
     
    -- DROP TABLE dbo.UserLogin
    CREATE TABLE dbo.UserLogin (
      UserLoginID INT    IDENTITY ( 1 , 1 )    PRIMARY KEY,
      LoginName   CHAR(30)    NOT NULL,
      [PassWord]  VARBINARY(MAX)    NOT NULL,
      IsActive    BIT    NOT NULL CONSTRAINT DF_UserLogin_IsActive DEFAULT ((1)),
      CreateDate  DATETIME NOT NULL CONSTRAINT DF_UserLogin_CreateDate DEFAULT (getdate()),
      ModifyDate  DATETIME NOT NULL CONSTRAINT DF_UserLogin_ModifyDate DEFAULT (getdate()),
      ModifiedBy  CHAR(6)    NOT NULL CONSTRAINT DF_UserLogin_ModifiedBy DEFAULT ('system'))
    GO
     
    -- drop ASYMMETRIC KEY Asym_PassWord
    CREATE ASYMMETRIC KEY Asym_PassWord WITH ALGORITHM = RSA_512 
    ENCRYPTION BY PASSWORD = N'secreT007!'
     
    DECLARE  @CipherString VARBINARY(MAX);
     
    SELECT @CipherString = EncryptByAsymKey(AsymKey_ID('Asym_PassWord'),N'SecretPass!01');
     
    INSERT INTO UserLogin
               (LoginName,
                [PassWord])
    VALUES     ('administrator',@CipherString);
    GO
     
    DECLARE  @CipherString VARBINARY(MAX), @pwd varbinary(max);
    set @pwd = convert(varbinary(max),replicate('A',54))
     
    SELECT @CipherString = EncryptByAsymKey(AsymKey_ID('Asym_PassWord'),@pwd);
    print @CipherString
     
    INSERT INTO UserLogin
               (LoginName,
                [PassWord])
    VALUES     ('operator',@CipherString);
    GO
     
    SELECT *
    FROM   UserLogin
    GO
     
    -- Following query can be used to test an entered password:
    SELECT      LoginName,
                PassWordDecrypted= convert(nvarchar(128),
                DecryptByAsymKey(AsymKey_ID('Asym_PassWord'),
                [PassWord], N'secreT007!' ))
    FROM UserLogin
    WHERE LoginName = 'operator'
    GO
     
    SELECT      LoginName,
                PassWordDecrypted= convert(nvarchar(128),
                DecryptByAsymKey(AsymKey_ID('Asym_PassWord'),
                [PassWord], N'secreT007!' ))
    FROM UserLogin
    WHERE LoginName = 'administrator'
    GO
    -- Cleanup
    --DROP TABLE dbo.UserLogin
    ------------

    53 characters work, 54 characters return NULL.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, April 5, 2012 10:43 PM
    Moderator
  • I confirm the malfunction at 54, 1000,  & 2000.

    I don't know why it fails. Anyone?


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Friday, April 6, 2012 5:29 PM
    Moderator
  • Have you tried other algorithms? I don't know myself if this is a limitation of 512 algorithm or a bug? BTW, what do you mean by 1000 & 2000? I also first tested with big numbers and finally narrowed it down to 53 being the breaker. 53 works, 54 and up returns NULL.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Friday, April 6, 2012 5:32 PM
    Moderator
  • Yes 54 & up fails. NCHAR 27 & up fails.

    I did not see this limitation in BOL.


    Kalman Toth SQL SERVER & BI TRAINING

    Friday, April 6, 2012 6:44 PM
    Moderator
  • I tried using RSA_2048, and that upped the limit to 245 characters.

    I don't know why this happens. I would have understood it the resulting cipher string had exceeded 8000 bytes, but that seems unlikely. However, here is an interesing observation: when algorithm is RSA_512, the key length for 53 characters is 64 bytes. For RSA_2048 the key length for 256 characters it's 256 bytes.

    As Kalman notes, there is no information in Books Online to be found. I don't know if this is a restriction withg asymmetric keys in SQL Server or limitation with asymmetric-key algorithms in general.

    However, as I understand it, you are not supposed to do this. You are supposed to encrypt your data with symmetric keys, because encryption and decryption with symmetric keys are much more efficient. You only use asymmetric keys to protect your symmetric keys.

    I ran a test with a symmetric key, password-protected using AES_256, and I found that the upper limit in this case is 7943 characters (with a key length of 7988 characters.) 7944 to 8000 characters give NULL back. 8001 or more characters gives the error "String or binary data would be truncated".


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, April 6, 2012 7:34 PM
  • Hi Erland,

    This was posted as a bug here: http://connect.microsoft.com/SQLServer/feedback/details/243966/encryption-with-rsa-512. Laurentiu mentioned the limits here: http://blogs.msdn.com/b/lcris/archive/2008/01/11/sql-server-2005-why-you-should-not-encrypt-data-with-certificates.aspx.

    The limitation and the fact that ENCRYPTBYASMKEY returns NULL when it's exceeded, should appear in Books Online, but it doesn't seem to.

    Sunday, April 8, 2012 1:09 AM
  • Great to see you around Steve!

    Laurentiu's blog post clears up everything.

    I've filed
    https://connect.microsoft.com/SQLServer/feedback/details/735926/topic-for-encryptbyasymkey-fails-to-mention-that-return-value-may-be-null-if-input-is-too-long
    to have the documentation updated.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, April 8, 2012 10:01 AM
  • Thanks everyone for your input.

    Kalman, the reason for the IMAGE data type at this point is to support legacy applications until we convert them to current technology.

    Erland, I am a C# progammer not a DBA to give you a little background about me so can you tell me why the EncryptByKey and DecryptByKey functions do not support the larger data types?  It is my understanding that the storage capacity identified with [(MAX)] is suppose to exceed 8000 bytes.  There is nothing that I can see for those various data types that tells me I need to break it down into chucks.  I'm curious to know now, thanks.

    Naomi, you are correct my alternative is to put the encryption in my code but as I noted in my comment to Kalman we are working towards moving that legacy code to current technology which in this case is C++ but I know C#.  I have a few changes ahead and the more I can handle on the DB side the easier it will be to migrate our code to current standards.

    Monday, April 9, 2012 12:30 PM
  • You can use CLR function in the database. This way you combine both words - database and C#. The link I posted should help.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, April 9, 2012 1:38 PM
    Moderator
  • Erland, I am a C# progammer not a DBA to give you a little background about me so can you tell me why the EncryptByKey and DecryptByKey functions do not support the larger data types? 

    I'm afraid that I don't have any better answer than "because the sky is blue". It is just the way it is. It is not unlikely that it is due to that there are limitation in the cryptographic algorithms, but I am not very well versed in those. But it could also be a case "we did not come around to it".


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, April 9, 2012 2:05 PM
  • Erland,

    Thanks for the update.

    Here's some info  (http://blogs.msdn.com/b/yukondoit/archive/2005/11/24/496521.aspx) you and others may find useful that did solve my problem.  The solution was initially was created for SQL 2005 but it will work with SQL 2008 as well.

    I just replaced the built-in functions (EncryptByKey and DecryptByKey) I was calling, with the user defined one's (dbo.EncryptLob, dbo.DecryptLob) they created as shown below and it worked well.  The user defined functions will call EncryptByKey and DecryptByKey for you.

    OPEN SYMMETRIC KEY sk_TestKey
    DECRYPTION BY CERTIFICATE cert_TestCertificate
     UPDATE MyTestTable
     SET Data = CONVERT(IMAGE, dbo.EncryptLob(KEY_GUID('sk_TestKey '), CAST(Data AS VARBINARY(MAX))))
     
    OPEN SYMMETRIC KEY sk_TestKey
    DECRYPTION BY CERTIFICATE cert_TestCertificate
        SELECT CONVERT(IMAGE,dbo.DecryptLob(CAST(Data AS VARBINARY(Max)))) AS Data FROM MyTestTable

    • Marked as answer by Stephanie Lv Tuesday, April 10, 2012 8:53 AM
    Monday, April 9, 2012 4:07 PM
  • Here's some info  (http://blogs.msdn.com/b/yukondoit/archive/2005/11/24/496521.aspx) you and others may find useful that did solve my problem.  The solution was initially was created for SQL 2005 but it will work with SQL 2008 as well.

    Good to see that you found the ultimate answer and also a solution!


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, April 9, 2012 7:37 PM