Wednesday, April 18, 2007

Handy SQL Server Query: Date String For Filename

Here's a simple piece of code for creating a string in YYYYMMDDHHMMSS format, suitable for use as a filename:

REPLACE(REPLACE(REPLACE(CONVERT(char(19), GETDATE(), 120), ' ', ''), '-', ''), ':', '')

If you ran this at 1:23:05 PM on December 3, 2006, it would return "20061203132305".

SQL Server 2000 Database Doesn't Shrink After Removing Columns

If you've ever come across a situation where you've removed text, ntext or image columns from a SQL Server 2000 database, and the size of your database doesn't shrink even after compressing it, you may have run into the problem described here:

DBCC SHRINKFILE and SHRINKDATABASE commands may not work because of sparsely populated text, ntext, or image columns

The solution is to create a DTS package to transfer all of your database objects into a new database, and remove the old one, and then rename the new one to your original database name (if necessary). We did this recently and recovered 50% of the disk space wasted.

Hopefully this problem was solved in SQL Server 2005.

Using SQL Query Analyzer via RDP Can Be Slow

I won't bore you with how I discovered this phenomenon, but rest assured if you run Microsoft Query Analyzer on a remote computer using Remote Desktop Protocol (RDP), you may notice strangely slow execution times when returning a large number of rows in text-result mode. If you switch to grid results, or if you minimize the query analyzer while the query is running, you will not have this problem!

A real world example:

A SQL Query that returns 40,000 records. When running this query in text results mode via RDP, Query Analyzer consistently took 8-9 seconds to finish. When running in grid results mode, it took < 1 second! When minimized in text results mode, it took 2-3 seconds (most likely due to my slow reflexes minimizing the window). You may find that this is true for many programs that are outputting a large volume of text to the screen -- keep then minimized if possible. And it's not what you might initially think, that a large number of screen-draws are being performed, slowing things down. Nope, my query analyzer results were not scrolling down the screen visibly. This is just some weird behavior with RDP and certain text displaying screens. If anyone knows why this happens, feel free to comment below.

(Note: this behavior was specifically observed using RDP connected to a Windows 2003 Server running SQL Server 2000.)

TimeZones in JavaScript

If you want to obtain the user's timezone offset in JavaScript (that is, the difference between their timezone and Greenwich Mean Time, or GMT), use this snippet:

(new Date().getTimezoneOffset()/60)*(-1)

Monday, April 16, 2007

Syncing a Keychain from One Mac to Another

As a companion piece to our Entourage Encryption Setup guide, this document will explain how to take the keychain you created (or any keychain) and have a copy of it installed on another Mac.

You have two options. The first is easier, the second is more secure (and is cheaper).

.Mac Sync. Transfer over the internet using .Mac (easier but not as safe, and expensive if you don't already use .Mac, although there are 60-day trials of .Mac available).

  1. Edit your keychain settings in the Keychain Access program so that it syncs the keychain up with your .Mac account.

  2. Sync all your Macs up with .Mac, and you will have all your public/private keys on all your systems.

  3. Turn off the sync feature for this keychain once you've finished syncing up all your Macs.
... or ...

Sneakernet. Transfer by hand using a burned CD or USB key.
  1. Make a copy of the .keychain file in your ~/Library/Keychains folder (the ~ represents your home directory, usually your name). If you followed our Entourage Encryption guide, this file will be named "Encrypted Email.keychain."

  2. Burn this copy to CD, and safely transport it to the Mac you want to sync it to.

  3. Securely erase the COPY (not the original) .keychain file once you've finished burning it to CD, using the "Secure Empty Trash" from the Finder menu. (You may also try to skip the entire copy/erase step by burning the .keychain file directly from your Keychains folder, although we received CD Burn errors when we tried this method.)

  4. Copy the .keychain file into the ~/Library/Keychains folder on the destination computer.

  5. Open the Keychain Access program on the new computer.

  6. Select the Edit menu, and then Keychain List.

  7. Press the + button at the bottom/left side of the window.

  8. Locate the .keychain file you copied over, and select it.

  9. The keychain is now installed into your Mac.

  10. Destroy the CD you used to copy the keychain.
Your data is only as safe and secure as your keychain file... be careful with it!

If you notice any mistakes, please let us know by commenting on this article.

Setting up Email Encryption in Entourage (S/MIME)

This document assumes you have already installed Entourage 2004 and configured it to work with Exchange Server.

We will be using Thawte to provide us with a personal X.509 certificate and public/private keypair that can be used by Entourage to encrypt email, which can be read by anyone else that uses S/MIME encryption.

IMPORTANT: If you have previously installed Thawte certificates and keys into your Mac OS keychain, you should consider now whether you want to use those, or whether you want to start over from scratch. If you never got email encryption working before, I suggest starting over. In this case, you want to open the Keychain Access program, make sure you have your default keychain highlighted (named "login"), and delete any certificates or keys from Thawte. Also check to see if you have an existing keychain named Encrypted Email, and do the same thing in this keychain (since this is probably something you setup last time we tried to do this).

If you are positive your keys and certs are installed properly, you can skip ahead to step 7 and setup Entourage to use them.

  1. After you've made sure your certs and keys from Thawte are cleared out of Keychain Access as described above, browse to Thawte's Secure Your Email page using Safari. (We specify Safari because it simplifies installing the certificates into your Mac OS Keychain, which is where Entourage looks for it's encryption keys.)

  2. On the right side of the page, click on Join or Login, depending on whether or not you've already established an account with Thawte or not. Already Registered at Thawte? Just Login to your existing account. New to Thawte? Click the Join option, and fill out the form with your personal information. When answering non-personal questions, the default answers are usually fine. Follow the on-screen instructions until you're finished. Be sure to use the email address you plan on sending/receiving encrypted email with, and choose a very strong password for your new account.

  3. Once logged in to Thawte, you want to go to the My Emails >> New Email Address page, and add any other email address aliases you may need to use with your certificate. They will send you an email with a URL you need to copy and paste into Safari, to approve any email address aliases you add here.

  4. Now proceed to Thawte's Certificates >> Request a Certificate page. Press the Request button, and follow the on-screen instructions, leaving all defaults, except where it prompts you for your email address. Here you want to check the boxes next to any email address you want associated with this certificate.

    IMPORTANT: While requesting your certificate, be sure to only click each button on each page only one time. Multiple clicks could cause Safari to install multiple keys, which will cause problems for you later.

  5. Once you follow all prompts, you'll see a notice indicating you have requested a certificate and it is pending. Behind the scenes, Safari has added two keys to your keychain, which will be used later. Now keep an eye on the Certificates >> View Status page at Thawte. Refresh it every once in a while until the "pending" status of your certificate request changes to "issued." (Or monitor your email inbox for notification from Thawte.) Once the status changes, click on the word "Navigator" next to the word "issued."

  6. Click the Fetch button. You'll be prompted to download a .exe file, say YES. Even though this appears to be a Windows program, Safari interprets it and adds two certificates to your keychain. (Now you have a private key, a public key, a "certificate authority" certificate, and a personal certificate in your default keychain.)

    Note: You should securely delete the .exe file now, by putting it into the Trash, and then selecting "Secure Empty Trash" from the Finder menu. Use this same method to clean up after any of the temporary files you may generate during this entire procedure.

  7. Open Keychain Access from the Utilities subfolder of Applications, and you should see two certificates and two keys listed in the All Items pane. Of the certificates, one is named "Thawte Personal Freemail Issuing CA," which represents a root certificate authority, and the other is your personal certificate that should show your email address. For the private keys, you should see "Key from" for both.

    Notice in the Kind column, one is a private key and the other a public key. Encryption in Entourage works by sharing your public key with everyone you want to receive encrypted email from, and keeping your private key with PRIVATE.

    You can and probably should rename these two keys to include your email address as part of the name. E.g. " Private Key" and " Public Key." The reason is if you want to add more certificates for other email addresses that you use later on, you may become confused when you see multiple keys with generic names.

    Note the Keychain column on the right shows both of these certificates and both keys are located in your default keychain, named "login." For added security, we will next move these into a new, more secure keychain.

  8. While still in the Keychain Access program, click the Show Keychains button on the lower, left part of the window. You should see that you are currently in the "login" default keychain. Go to the File menu and select New Keychain. Enter a name of your choice. (For this document we'll call it Encrypted Email.)

  9. You will be prompted for a password -- make your password very strong. It's all that prevents someone from decrypting and reading your email, or forging new encrypted email from your account, if they gain access to your computer.

    Notice the two weak points to this system: your Thawte password, and your new keychain password, both of which allow access to your encryption certificate if they are cracked. Take this opportunity to strengthen these passwords if you initially chose poorly. Passwords made up only of words in the dictionary, even transposed, are easily cracked. In addition, consider enabling FileVault to encrypt your home folder in Mac OS if you haven't yet.

  10. Select the original "login" keychain again if it is no longer selected, and click the All Items pane. Drag both of the Thawte certificates and both Thawte keys into your new Encrypted Email keychain.

  11. Click on the Edit menu, and select Change Settings for Keychain "Encrypted Email." You will likely want to set it to lock after a short period of inactivity and when sleeping (both of these should be the default settings). If you make any changes, click Save. Moving these items into this separate keychain allows you to segment these items which need to be severely guarded, from your normal day-to-day security items which may not be as important to protect.

    Now your keychain containing your encryption certificates has a strong password and will timeout when it hasn't been used for a short period of time. Later, when reading encrypted email, you should be prompted to login to your keychain to decrypt the first message during any of these "sessions." If not, your keychain settings are not properly configured and should be reviewed.

  12. Start Entourage, click on the Entourage menu, and select Account Settings.
    Double-click your Exchange account which should be listed in the default Mail pane. This opens your account's settings.

  13. Select the Security pane. Under Signing Certificate, press the Select button, and choose Thawte Freemail Member from the dropdown, then press the Choose button to save the selection. Do the same thing in the Encryption Certificate area. Unless you want every single message to be signed and encrypted, the rest of the defaults on this pane are fine. Press the OK button to save your new account settings. If you don't see Thawte Freemail Member in the dropdown, view the Troubleshooting section below.

    Now you're ready to encrypt and decrypt email, but you need to send your certificate to everyone that will need to send you encrypted email, and you need to request those same people send you their certificate, so you can send them encrypted email.

  14. Start a new email message, address it to the people you want to communicate with using encrypted email, and write a quick note letting them know you're sending them your encryption certificate, and wish for them to do the same for you. Now click the Message menu, select Security, and then Digitally Sign Message (NOT encrypt). Click Send, and your message will be sent in plain text with your certificate attached.

  15. When you receive email from someone with their certificate, click on the View Details link that is displayed near the top of their email message, and press the Add to Contacts button to add their certificate to their listing in your address book. This will enable you to send them encrypted email in the future.

    IMPORTANT: After you click on the Add to Contacts button, go into your address book entry for that person, and click on the Certificates pane. Check and make sure you only have one certificate listed for that person. If there is more than one, you will need to go through each one and make sure to delete all of them but the latest certificate. If you don't see any certificates for this person, check the troubleshooting section below.
You are now finished and should be able to communicate privately with anyone else that also uses S/MIME for email encryption... FOR ONE YEAR. That is when your Thawte certificate expires (for security reasons). After that, you will need to follow this same basic process to obtain and install a new certificate.

We also have a document explaining how to sync your keychain to other macs, so that you can use these same certs on all your macs.

If you notice any problems or mistakes in this document, please add a comment.


Unable to Add Certificate to Contacts Address Book Entry. If you are viewing someone's certificate in Entourage, and you click Add to Contacts, but it never really adds their certificate to your entry for them in your address book, there is a workaround. While viewing their certificate details, drag the picture of the certificate to your desktop. This should create a .cer file on your desktop. Open Keychain Access, and drag this .cer file into your default keychain (usually named "login"). Restart Entourage, and you should now see the certificate shows up when viewing the Security tab of that person's entry in your address book.

Your Certificate Isn't Available In Entourage (New Keychain Doesn't Show Up). In step 13 above, you are trying to select your new Thawte certificate as your encryption and signing certificate, but it isn't showing up in the dropdown. This usually happens when your new keychain is not being handled correctly by Keychain Access and/or Entourage. Some workarounds to try:
  • Open Keychain Access, go to the Edit menu, and select Keychain List. Click the + plus sign in the lower left corner, and locate your new keychain, and select it. Press OK to close the Keychain List window, and quit out of Keychain Access. Restart Entourage. Try step 13 again and see if the Thawte certificate is available now in the dropdown list.

  • If that doesn't work, open Keychain Access, and rename your keychain so that it contains no spaces in the name. Then repeat the steps above to make sure it has been added to your Keychain List. Usually one of these two methods works.

Other Documents Related to This Topic