locked
Storing Base64 data in SQL Server RRS feed

  • Question

  • I have a requirement to store attachments in SQL Server. I cannot go with FILESTREAM or FILETABLE because the attachment has to be encrypted using the same key we use to Encrypt other application data. I am planning to implemet Base64 encodingto Encode the attachment first and encrypt the Encoded data and store it.

    My Table structre is

    AttachmentID int
    Extension    varchar(20)
    FileData varbinary(max) 

    I hope 10MB attachment size won't harm the model as varbinary(max) would allow data will 2GB. Even encrypting base64 data of a 10MB file won't be mroe than 2GB.

    Regarding Performance.. YES.. Need to think about it.

    Let me know Pros and Cons of this model. Any other idea to implement the same?

    Friday, February 22, 2013 8:49 AM

Answers

  • varbinary(max) is for binary data.  base64 is a technique for converting binary data to string.  It's not necssary here, as you can store your encrypted binary data directly in a varbinary(max) column.  

    File Table is cool if you want to sometimes access the data as files in a file share.  Otherwise, simply use varbinary(max).  Don't worry about performance initially.  You can always change the storage strategy for your varbinary(max) column later, by

    -pushing it entirely off row 

    or

    -putting it on a seperate filegroup

    or

    -switching to filestream storage

    .  

    David


    David http://blogs.msdn.com/b/dbrowne/


    Saturday, February 23, 2013 5:30 PM

All replies

  • >I cannot go with FILESTREAM or FILETABLE because the attachment has to be encrypted

    FILETABLE is your best choice. Not clear why you think varbinary(max) column is better.

    FileTable with encryption:

    http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/68fef4cb-9835-4869-b86f-bb7b85899ac8


    Kalman Toth Database & OLAP Architect
    Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012



    • Edited by Kalman Toth Friday, February 22, 2013 1:04 PM
    Friday, February 22, 2013 1:00 PM
  • The file needs to be Encrypted before coming to DB server. We don't want to use any other 3rd oarty tool.

    I thought, I can convert it to Base64, then Encrypt and save it in varbinary(max)

    • Marked as answer by gnans19 Friday, February 22, 2013 1:31 PM
    • Unmarked as answer by gnans19 Friday, February 22, 2013 1:31 PM
    Friday, February 22, 2013 1:31 PM
  • >The file needs to be Encrypted before coming to DB server

    So encrypt it and store it in a FileTable encrypted.  I fail to see the issue here.


    Kalman Toth Database & OLAP Architect
    Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    Friday, February 22, 2013 1:33 PM
  • How Do I Encrypt the file?
    Friday, February 22, 2013 5:13 PM
  • >How Do I Encrypt the file?

    You stated above that your company has a private encryption tool?

    You can encrypt it anyway you like and store it in FileTable.

    FileTable is like any Windows folder, you can just drop any file into it: image, video, Word, Excel, encrypted, non-encrypted.


    Kalman Toth Database & OLAP Architect
    Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


    Friday, February 22, 2013 8:29 PM
  • kalman,

    Sorry for the confusion here. That fact is we use some encryption algorithm at the webserver(asp.net, .dll file) to encrypt the application data before storing it in sql sever. We don't want dbA or any SQL Server server admins to view any of the data. Similarlywe want to implement this base64+encryption technique.

    Saturday, February 23, 2013 4:22 PM
  • varbinary(max) is for binary data.  base64 is a technique for converting binary data to string.  It's not necssary here, as you can store your encrypted binary data directly in a varbinary(max) column.  

    File Table is cool if you want to sometimes access the data as files in a file share.  Otherwise, simply use varbinary(max).  Don't worry about performance initially.  You can always change the storage strategy for your varbinary(max) column later, by

    -pushing it entirely off row 

    or

    -putting it on a seperate filegroup

    or

    -switching to filestream storage

    .  

    David


    David http://blogs.msdn.com/b/dbrowne/


    Saturday, February 23, 2013 5:30 PM