7

Is it possible to store an X509Certificate2 in a SQL Server table rather than pull a .p12 file from the file system? I'm sure you can but not sure how to go about this.

abatishchev
  • 98,240
  • 88
  • 296
  • 433
PositiveGuy
  • 46,620
  • 110
  • 305
  • 471

2 Answers2

11

This is definitely possible, the X509Certificate2 has a RawData property that can be saved into your SQL Database. To reconstruct the certificate you can use this constructor

var cert = new X509Certificate2(filename);
var data = cert.RawData;

// save data to database...

// Fetch data from database...

cert = new X509Certificate2(data);
Luke Girvin
  • 13,221
  • 9
  • 64
  • 84
Rohan West
  • 9,262
  • 3
  • 37
  • 64
  • so you still have to read the .p12 stream into an object then store right? I can't just open up the p12 file and copy its contents and throw it into a varchar field into a table called Cerficates and never have to read from a physical file again thereafter? maybe I'm naive – PositiveGuy Feb 11 '10 at 02:47
  • Ok, so RawData is a byte array. What MS SQL datatype would you use to store a byte array....binary? – PositiveGuy Feb 11 '10 at 02:49
  • I just want to read once and not have to store that .p12 file on the server. I think that's what the intention is. Our lead says not to read it from a file but rather the DB. I would assume that means I should be able to store that once in the DB and never have to read the file again? – PositiveGuy Feb 11 '10 at 03:12
  • That is correct, once you have read the .p12 you can save the byte array to the database, does your .p12 contain the private key too? If so, it would be better to store it in the certificate store. Other wise, if you just have the public key then that should be okay to save to the database. – Rohan West Feb 11 '10 at 08:51
  • It seems `RawData` doesn't contain the `FriendlyName` property. – Borislav Ivanov Jan 12 '23 at 12:14
6

Use .Export() then Convert.ToBase64String() and save as VARCHAR(MAX)

To save it:

var cert = new X509Certificate2(filename);
var stringOfCertWithPrivateKey = Convert.ToBase64String(cert.Export(X509ContentType.Pkcs12));

// Or as a regular cert, which will strip the private key out
var stringOfCertWithoutPrivateKey = Convert.ToBase64String(cert.Export(X509ContentType.Cert));

// Save either string as VARCHAR(MAX) in the DB, it's just a Base64/ASCII string now.

Then just restore (after getting it back from DB) with:

var certBytes = Convert.FromBase64String(stringOfCertWithPrivateKey);
var cert = new X509Certificate2(certBytes);

Using Export() is better than .RawData as you can choose to persist the Private key or not (using .RawData will always strip it).

You can store the result Base64 string in an sql database as a VARCHAR(MAX) type, because Base64 string comprise of only ASCII characters (Thanks to this answer)

jezpez
  • 266
  • 2
  • 8
  • Why the need for NVARCHAR ? see https://stackoverflow.com/questions/2397624/which-sql-data-type-to-store-base64-encoded-file – Peter Henry May 30 '22 at 10:19
  • 1
    At the time of answering this question I knew less about sql than today. I guess knowledge of NVARCHAR was seeping into this answer but you're right @PeterHenry that it's not relevant. I'll remove it. – jezpez Jun 21 '22 at 05:01