Some SQL, some .NET, and whatever else


Generate an x509 certificate with an SHA256 signature hash

When authenticating with a vendor using a custom webservice, the vendor requested that we use an x509 certificate with a 2048 byte key and an SHA256 hash (sometimes referred to as SHA2, though SHA2 actually refers to the group of hashes containing SHA256, 384, and 512). Since I'd used IIS to generate our certificate (IIS will only generate a certificate using an SHA1 hash), and it involved quite a bit of research to get a certificate with an SHA256 signature hash on it, I wanted to detail the steps here:

  1. First, download and install OpenSSL from Shining Light. The "Light" version of the package will do, since you're only using basic functionality.
  2. Generate your Certificate request (CSR), specifying an SHA256 signature hash
    1. You'll be prompted for a few certificate fields, including your state, company name, computer name on your certificate, etc. Enter these as they come up.
  3. This will generate two files - PrivateKey.key (which contains the un-encrypted version of your private key - protect this file, as somebody who obtains it along with your signed public key can impersonate you), and CertificateRequest.csr (your certificate signing request, which is not sensative).
  4. Though this isn't required, if you want to confirm what you're entered in the CSR, you can view the details using another OpenSSL command line
  5. Now that you have your CSR, submit it to whatever signing authority you use - for us, it was Verisign, but there are any number of different CAs out there that can sign it.
  6. Once your CA has signed the CSR, you'll get back either a binary p7b file (which we'll called SignedKeyFromCA.p7b) containing your certificate signed public key (and, possibly, the certificate chain your CA used as well), or either a binary or base64 CER file containing just your certificate. Whatever you receive back, you'll need to convert it to a Base64 CER (called SignedKeyFromCA.cer here), since that's what OpenSSL expects.
  7. To combine your private key with the signed public key to create a certificate:
    1. Since you're exporting your private key in this file, you'll be required to encrypt it with a password, which OpenSSL will prompt you for (twice).
  8. You've now got your signed key pair - SignedKeyPair.p12. You can either use this pkcs12 file in your code, or you can import in into your web server (assuming it supports SHA256 hashes). IIS7, for example, supports importing this certificate and using it for SSL, but just doesn't support generating a SHA2 CSR in the first place.

Enjoy! If you have any issues, please feel free to post a comment and I'll do my best to answer it!


Error TF255271 while upgrading TFS 2005 -> 2010

When upgrading TFS 2005 to TFS 2010 (using these instructions) and it worked great on my test computer, but when I went to migrate the production server, I received the following error:

Warning Message: [2011-05-12 20:12:14Z] Servicing step Register Integration Database failed. (ServicingOperation: UpgradePreTfs2010Databases; Step group: AttachPreTFS2010Databases.TfsFramework)
Warning Message: TF255271: The team project collection could not be created. The number of steps before the completion of project creation is: 216. The number of steps completed before the failure was 10.

The error message doesn't give any detail at all, so I opened the log file and found this near the bottom:

[Info   @20:12:19.133] [2011-05-12 20:12:14Z][Error] BisCreateSchema.sql Line 816 Error: Incorrect syntax near ','. (10 of 216)
[Info   @20:12:19.133] [2011-05-12 20:12:14Z][Informational] Microsoft.TeamFoundation.Framework.Server.CollectionServicingException: BisCreateSchema.sql Line 816 Error: Incorrect syntax near ','.
---> System.Data.SqlClient.SqlException: Incorrect syntax near ','.

Try as I might, I couldn't find the SQL file it referred to, and Google wasn't much help either - however, it seemed that the SQL file wasn't actually to blame, especially since the same upgrade process had run flawlessly on my test server a few days earlier. Then I realized that my test server was SQL 2008 and my production server was SQL 2005 - while I didn't read specifically anywhere that this was a problem, SQL 2005 isn't supported by TFS 2010.

After much digging, the cause of the error ends up being that the TFS upgrade tool (and TFS 2010 in general) doesn't support SQL Server 2005. Upgrading the database server to SQL Server 2008 and re-running the process corrected the error and allowed us to complete the migration.

However, I've read that SQL 2008 support on TFS 2005 is patchy, so this also obliterates your rollback, if you were planning on one 🙂 If you get this error, hope this helps!

Tagged as: , No Comments