Tag: syndicated

  • Creating Your Own Certificates

    Did you know that you don’t need to go to Digicert or Thawte, or any other company to get a certificate to use in SQL Server? You can create your own certificate.

    Why you would want to do this is a longer discussion, but suffice it to say that if your environment allows for self-signed certificates, you have a couple options for creating these in SQL Server and Windows. I’ll show you how easy this can be using these two methods:

    • makecert
    • CREATE CERTIFICATE

    Please be careful if you plan on creating your own certificates. The value of a certificate and asymmetric keys comes in the hierarchy of trust for these certificates and if you do not have a strong hierarchy, you could potentially be making your security worse, rather than better.

    Makecert

    The Windows Software Development Kit (SDK) contains a number of utiltiies, one of which is makecert. It’s a command line tool that creates certificates for you, and It’s easy to use.

    I downloaded the SDK, extracted it, and then fired up a command prompt, running this:

    makecert -sv "c:\EncryptionPrimer\MyHRCert.pvk" -pe -a sha1 -b "01/01/2012" -e "12/31/2012" -len 2048 -r -n CN="HR Protection Certificate" c:\EncryptionPrimer\MyHRCert.cer

    This code creates a private key file (MyHRCert.pvk) and a public key certificate (MyHRCert.cer)

    You can click the link and read the parameters, but it’s really that simple. When you create this certificate, you can use the FROM FILE options for CREATE CERTIFICATE to load this certificate into your SQL Server.

    CREATE CERTIFICATE

    I guess technically you are using the CREATE CERTIFICATE in either case here, but this section looks at the actual creation of the certificate by SQL Server.

    CREATE CERTIFICATE is standard DDL, like so many other commands in SQL Server. The parameters are similar to those for makecert. Here’s a statement that matches up with the one above.

    create certificate MySalaryCert
       ENCRYPTION BY PASSWORD = N'R3allyToughP@ssword4You'
       WITH SUBJECT = 'HR Protection Certificate',        
       START_DATE = '20120101',
       EXPIRY_DATE = '20121231';

    Note that you don’t need to specify the algorithm or other parameters. SQL Server handles that for your. You also don’t need to specify the two files here. The database engine stores these keys inside the database. You should make a backup of them, and you can use the BACKUP CERTIFICATE command to do this.

  • Hardware Fun

    This has been a bit of a hardware week for me, which is strange. I used to love hardware, building my own computers, picking out the components, and making something work out of a pile of parts.

    Now I just want my tools to work.

    Our Windows Home Server died a few months ago. This is the third time I’d lost a boot drive in the WHS server, though to be fair I was reusing a few older drives in it. However when you lose the boot drive, not only do you need to reload WHS, but it can’t reload your content from the old drives. The drive extender technology is flawed, which is why it might be cut loose from the WHS 2011 product.

    I’ve delayed actually rebuilding things, but with my wife traveling one week, the kids occupied, I took it apart and connected the drives one at a time to check how things were working. They all connected and seemed to work, which was strange. Perhaps bad blocks?

    I reconnected the 160GB drive as the boot drive, since that was the smallest, added in two 1TB drives in a RAID 1 array, and a 1TB drive as a spare. I booted to WHS 2011, but it promptly failed. This is a Dell E521, an AMD x64 CPU, but it didn’t want to load. No problem, I was planning on virtualizing anyways.

    I booted to Windows 7 x64, and got through the initial install, but on boot, the 160GB drive failed. Aha! I removed that, replaced with a 1TB drive, and reloaded Windows 7.

    Only to find that there aren’t x64 drivers for a number of components for Win 7. Grrrr. This is why I don’t like to mess with hardware. I want stuff to just work.

    I fell back to Windows XP, x64 (since I want 64 bit guests) and got that installed. I had RAID drivers, but for some reason Windows setup doesn’t want to install them or load onto a RAID set. That feels like a waste of the purpose of hardware RAID, but whatever.  I got Windows loaded, patched, drivers installed, and added Virtual Box 4.

    Then the harder part. I created a guest on my R1 array, which too time for VirtualBox to format the drive. Once it was done, however, I booted up a guest, installing WHS 2007 on there and connecting to the network. I killed the host firewall, and bridged the network adapter. Not sure which one fixed things, but I don’t do anything on the host, and it’s firewalled from the outside world.

    Now I had two spare 2TB drives from the old system, where I copied off the pictures, video, and music to my desktop. A long transfer process in the background added them to the WHS server, and I have a home network again.

    It’s RAID protected, so I should be OK for now, but we’ll see what happens. I have an external enclosure with 2 2TB drives in it, but I don’t want to add them for now. At least until I need more space.

  • Computed Columns and Divide by Zero

    Edit: This was a poor example of using the divide by zero handling. I was trying to alter something I’d done in the past and this didn’t work well. I will rewrite this soon with a better example.

    I have a few posts on computer columns, the basics of computed columns, using CASE in a computed column, and UDFs in computed columns, but there was another use that someone pointed out to me recently: catching divide by zero errors.

    Suppose you have a column that is determining a percentage of profit for some sales. I’ll create a table and include some values:

    CREATE TABLE MySales
    ( salesid int , Product VARCHAR(20) , Cost numeric(10,4) , Price numeric(10,4) , profit AS (price - cost)/ cost
    ) GO INSERT MySales SELECT 1, 'Bike', 150.45, 180.99
    INSERT MySales SELECT 1, 'Shoes', 23.55, 45.99
    INSERT MySales SELECT 1, 'Soda', 0.25, .99

    If I look at the values in the table, I get back these items:

    /*------------------------
    SELECT Product
    , Cost
    , Profit
     FROM MySales
    ------------------------*/
    Product              Cost                                    Profit
    -------------------- --------------------------------------- ---------------------------------------
    Bike                 150.4500                                0.202991026919242
    Shoes                23.5500                                 0.952866242038216
    Soda                 0.2500                                  2.960000000000000

    Things work great, and everything seems to be fine. However, what if we get some free products that we can sell, say something we made out of scraps, or were given to us as a gift with essentially no cost?

    INSERT MySales SELECT 1, 'Keychain', 0, .75

    It works. However if I select data:

    SELECT Product
    , Cost
    , Profit
     FROM MySales

    I get this:

    Product              Cost                                    Profit
    -------------------- --------------------------------------- ---------------------------------------
    Bike                 150.4500                                0.202991026919242
    Shoes                23.5500                                 0.952866242038216
    Soda                 0.2500                                  2.960000000000000
    Msg 8134, Level 16, State 1, Line 2
    Divide by zero error encountered.

    The computation occurs on query, and it doesn’t work well.

    However we can fix this with a function in our computed column. There are a couple choices here. I can use ISNULL and a CASE to build a formula, I could use COALESCE, or I could use NULLIF.

    NULLIF returns null if two values are equal. If a NULL is acceptable in my table, I could use that. I could end up with:

    CREATE TABLE MySales
    ( salesid int , Product VARCHAR(20) , Cost numeric(10,4) , Price numeric(10,4) , profit AS (price - cost) / NULLIF(cost ,0) ) GO INSERT MySales SELECT 1, 'Bike', 150.45, 180.99
    INSERT MySales SELECT 1, 'Shoes', 23.55, 45.99
    INSERT MySales SELECT 1, 'Soda', 0.25, .99
    INSERT MySales SELECT 1, 'Keychain', 0, .75
    GO SELECT Product
    , Profit
     FROM MySales

    and get back:

    Product              Profit

    ——————– —————————————

    Bike                 0.202991026919242

    Shoes                0.952866242038216

    Soda                 2.960000000000000

    Keychain             NULL

    NULL is a good marker here, but your application needs to handle this and let the user know there is an issue.

    You could use COALESCE, which returns the first non-null value. I could see any of these as being a valid formula:

    , profit AS (price - cost) / COALESCE(cost ,0) 

    which uses “0” profit margin as a marker, or even something like:

    , profit AS (price - cost) / COALESCE(cost , 999)

    which uses 999. Lots of times we’ve used a large, nonsense number as a marker that lets someone know there is a strange value here.

    If we converted this to a varchar, it’s possible that we could even use words in there, but I wouldn’t recommend that as downstream uses of this column might involve other calculations.

    That’s a short look at how a computed column can solve an easy, common issue: divide by zero.

    Credit to Atif Shehzad, whose article I found while researching this.

  • A few quick time calculations

    Have you ever needed to do a quick time calculations of the amount of hours/minutes/seconds that have passed? Suppose you needed to get the total number of minutes that have passed for a total time of ‘2:24’.

    There are some easy ways to do this, and the normal calculation that you might make is to multiple hours by 60 and then add minutes, so something like:

    DECLARE @t TIME, @n INT SELECT @t = '2:24' SELECT @n = DATEPART( hh, @t) * 60
              + DATEPART(mi, @t) SELECT @n

    That returns 144, which is the correct value (60 * 2 = 120, adding 24). However there’s an easier, and cleaner, way.

    SELECT DATEDIFF(mi, 0, @t)

    You can let SQL Server do the math, grabbing the DATEDIFF function and using 0 as a starting point.

    Number of seconds in a day?

    DECLARE @t TIME, @n INT, @d DATETIME SELECT @t = '11:59:59PM' SELECT DATEDIFF(ss, 0, @t) + 1