Accessing MSSQL databases from Excel 2011 on Mac OS X 10.7 Lion

Wow, a whole year since my last blog post! I ought to do something about that — I have some ideas for posts, I just need to make the time for them. Anyway, on with the point of this post.

I've recently had a need to access an MSSQL database at work, which is maintained by our organisation's central IT service. It has some benefits for our organisation – LDAP authentication is a good one, for example. However, support for MSSQL outside of the Microsoft sphere can be a bit sketchy! As I use a Mac, and would rather not use virtualisation or something else silly to access the data, I set about finding a solution to access the data natively.

After googling for some free software, and finding rather little besides Java apps, I settled on Excel 2011 using the Microsoft Query database bridge. This is a handy utility that allows you to build database queries, which accesses databases using ODBC.

Out of the box, Microsoft have been rather unhelpful. In order to use this feature, you need to install "compatible" ODBC drivers. Microsoft kindly link you to two companies that offer these drivers, but both cost money. Gee, thanks MS!

However, there is a cost-free and — even better! — open source solution for accessing MSSQL databases, right up to the latest SQL Server 2008. It's called FreeTDS (TDS means Tabluar DataStream, and it's the name of the protocol used by MSSQL and Sybase servers).

Finding instructions for configuring this FreeTDS/ODBC/Microsoft Query/Excel malarky proved difficult, but I've managed to piece together the following process. Hopefully it will be of use to others.

Firstly, you'll need to obtain the following:

1. MacPorts — I love this app, and you should too. Make sure it's working before starting this:
   

   # port version
   Version: 2.0.3

2. iODBC SDK from OpenLink — Mac OS X comes with iODBC, but Excel won't use it. You need to install this updated(?) version for it to work.
3. I will assume that you're already running Mac OS X and Microsoft Office. These instructions should work with any version, though they're tailored specifically for Lion (10.7) and Office 2011. They should also work with FileMaker, but I haven't tested this.

Starting with MacPorts, we'll install FreeTDS. MacPorts doesn't currently have the latest version (0.91 vs 0.82), but that doesn't seem to matter. If you're used to MacPorts, you should read this command carefully: this will make all the difference. Type the following into Terminal:

# sudo port install freetds +universal

The +universal is vital here, especially on Lion. On Lion, MacPorts will only build a 64-bit version of FreeTDS by default. The problem is, OpenLink's iODBC is 32-bit, and the two are incompatible. This option tells MacPorts to build both 32- and 64-bit versions, which makes iODBC much happier!

When this finishes (which can take a long time if you've not used MacPorts before), the script will tell you where some important files are:

****************************************************************
Configuration file freetds.conf does not exist and has been created using
    /opt/local/etc/freetds/freetds.conf.sample
Configuration file locales.conf does not exist and has been created using
    /opt/local/etc/freetds/locales.conf.sample
Configuration file pool.conf does not exist and has been created using
    /opt/local/etc/freetds/pool.conf.sample
****************************************************************

You'll need to edit some of these files, with your favourite text editor, to tell FreeTDS how to connect to your MSSQL server.

freetds.conf:

# My MSSQL server
[smilerMSSQL]
   host = mssql.awm.me.uk
   port = 1433
   tds version = 7.0
   encryption = required
   client charset = UTF-8

These lines should be appended to the end of the file - there's no need to edit the lines already there.

The first line is just a comment, which I've used to assign some meaning to this section of the file. Next is the server's name. You will use this to refer to the server in iODBC later, so pick something sensible. I don't know if symbols are permitted here, so I've avoided them.

Next is the address of the server itself. (I've used a dummy address here.) It should be a full-qualified DNS name or IP address. The port will be 1433 for any MSSQL server (if this needs changing, you should know as much). tds version tells FreeTDS which protocol version to use. There's a reference available for this, but if you can't get the latest version to work, drop it down a version and try again. The difference between versions is pretty trivial anyway! I'm using SQL Server 2008, which support TDS 7.2, but I could only get it to work once I'd dropped this to 7.0 (I don't know why).

Lastly, I've required encryption, because I'm accessing confidential records on a work network. You can also request encryption here, which will use encryption if its available, but drop to an unencrypted connection automatically if not. I've also specified that I'm using UTF-8, because I like UTF-8. You could use ISO-1 here.

You can also edit the locales.conf file, to specify the language and date formatting that SQL Server/FreeTDS should use when returning error messages and data. If there isn't a section in this file for your locale, it should default to US English (and dates, which make no sense to me!).

At this stage, it is advisable to test the configuration of FreeTDS, to make sure that it's working. There is a handy utility included, called tsql, which will simply not complain if everything is working. Run it like this:

# tsql -S smilerMSSQL -U 'AWM\smiler'

This is a made-up example where I'm authenticating with a domain (LDAP) login. Make sure you use quotes if you're doing this. If you're just a normal user on the server itself, just use your username without quotes. tsql will print the locale that it's using, then prompt for your password. If there are no problems, you'll get a '1>' prompt. Nothing seems to work at this prompt except quit, but you've proved it works.

Next, install the iODBC SDK from OpenLink; it's a straightforward Mac OS X install package. Note that the latest version is for Mac OS 10.5 and 10.6, but it works fine in Lion. If you're picky about excess software, and you're only using this for Excel and/or FileMaker, you can customise the installer to only install the "Frameworks" and "Administrator".

Configuration for this is done via the GUI, which has been installed for you in /Applications/Utilities/OpenLink ODBC Administrator. Run this, select the ODBC Drivers tab and click on Add a driver.

You can put whatever you like for the description really, but MSSQL FreeTDS seems like a sensible name to me. In the Driver file name box, you need to put the complete path to the libtdsodbc.so file installed by MacPorts. The default path for this is /opt/local/lib/libtdsodbc.so, but it will be different if you installed MacPorts elsewhere. Note that this is actually a symlink to the library, which allows it to be changed if the library is updated. I'd suggest using the symlink. Now select OK.

If that went well, it should now appear in the list of drivers in the main window, including the version number. If you don't see a version number (it's just #.##), then there's something wrong. I know this, because I first installed the driver as 64-bit only and I got hashes! Go back to the MacPorts step, and remember to include +universal.

Now we tell iODBC about the actual database. Select the User DSN tab and click Add. You'll see a list of drivers (probably only the one) - select it in the list and click Finish.

In this dialog, you enter the parameters of the server to tell ODBC how to connect. The Data Source Name (DSN) is whatever you want to identify it by, and the Comment field permits you to add a more detailed explanation. The important stuff goes in the keyword/value pairs section at the bottom.

Servername is the name you used in the freetds.conf file earlier, inside the square brackets — in this case, smilerMSSQL. iODBC will pass this to FreeTDS, which will then know where to look in the configuration file. Database is the name of the database you want to use on the server. Trace tells FreeTDS not to log anything. Click Ok to confirm. iODBC should obediently add the DSN to the list.

Now for another test. Select your new DSN in the list, then click on Test. This should display a username and password prompt. Username formatting is the same as earlier — DOMAIN\username (this time without quotes!) unless you're a 'normal' user. If this works, iODBC will display a simple message telling you that it worked. If it doesn't work, you'll get one (or more) messages with complicated errors. In this case, retrace the steps above to work out where you went wrong. Did you use +universal?

Finally, your data source is ready to be used in Excel/FileMaker. In Excel 2011, open a blank workbook, select the Data tab in the Ribbon and click on Database. This will display a very familiar looking window where you should select the DSN you created, then fill in your username and password. Now you can construct your query!

I hope this is helpful to people. It took me a few hours to work all this out. If something strange has happened, post in the comments and I'll attempt to provide some assistance.

Multiple Google accounts on Android

Not long after I bought an Android phone (the HTC Legend if you're interested), I decided to sign up for a Google Apps account. Fundamentally, I did this for email. I own a domain (awm.me.uk) which I wanted to use for email, and my primary account at the time was my University account, soon to expire after I graduate! So I thought this would cure all my ills.

Unfortunately, I couldn't get this Google Apps account to sync with my Android phone, despite the promise that it should from all official places. The problem: the sync setup wizard doesn't work after the first run when you first switch on the phone. In some cases, it doesn't even work after a factory reset, but that's a different story!

So Googling about this problem today, I came across a ticket for the problem on Google Code. I voiced my frustration, and then a kind user pointed me to his solution: the YouTube app!

So how, I hear you ask, can the YouTube app help?

1. Open the YouTube app, press menu and select 'My account'
2. If you've used the app before, it's likely you'll be signed in with your default Google account automatically. No problem: press menu>Log out, then go back to step one.
3. You'll see an option to sign in with a different account, or jump straight to a log on screen. Do your business with the Google Account you want to add to your phone.
4. Here's the interesting bit: if it's a Google Apps account, it may appear not to work. For me, it claimed my username or password were incorrect (they weren't). But wait…
5. Press home, then menu and select 'Settings'>'Accounts & sync'. Notice anything new?
6. Select your account in the list, then check all the service you'd like to sync. As if by magic, they'll start to work with all the apps on your phone!

So there you have it. I hope you find this tip as useful as I did! And with any luck, Google might actually fix this in the future…

The Liberal Democrats: truth and myth

If you live in the UK, you've probably found it hard to escape the news about the upcoming election. And rightly so in my opinion: it's the most significant event of this year, and has the potential to be the most important election of recent decades. In the wake of several political scandals and the “financial crisis”, there's everything to play for, and I'm pleased to see the Liberal Democrats making considerable headway after last week's Leaders' Debate.

Last night, my housemate remarked that he mostly likes the Lib Dems, but doesn't support their pro-Euro policy. This seems to be one of the common complaints about Lib Dem policy, so I decided to check it out this morning:

We believe that it is in Britain’s long-term interest to be part of the euro. But Britain should only join when the economic conditions are right, and in the present economic situation, they are not. Britain should join the euro only if that decision were supported by the people of Britain in a referendum.

The important point here is the Lib Dems' conviction that a referendum must decide the fate of the Euro in Britain, no matter how much they may want it, and that Britain is in no fit state, economically, to enter it now.

Interestingly, the Lib Dems' chancellor has been widely recognised as the best man for the job, and yet the party still supports joining the Eurozone. Personally, I don't think we're ready to join the Euro, if at all, and I'm pleased that the Lib Dems recognise this. I just hope that this minor point doesn't blind others of the Lib Dems' refreshingly honest and progressive manifesto. No party's policies are perfect, but the Lib Dems' are, in my opinion, the closest to attempting to fix what's wrong with this country's politics, policing, education and economy.

Which is why I'm disappointed (though not at all surprised) by news that some media mogols have been abusing their power.

In other related news, please check out TwitVoteUK. It hopes to plot actual voting intentions, attributed directly to actual constituencies, instantly avoiding the flawed uniform swing polling theory. It has a long way to go though: the results are hardly representative at the moment, so please get it known to your friends.

Open-source spell checkers

A friend of mine recently put out a call for help, looking for open-source spell checking APIs. I knew of a few, so I've assembled a list here.

• GNU Aspell The most widely used open-source spell checker that I know of. Supersedes Ispell apparently.
• Hunspell Used by Firefox 3, OpenOffice.org and Google. It's been ported, or had bindings written, for many languages include Java
• JaSpell I thought this was a Java port of Aspell, but it doesn't mention Aspell, so it must be original.
• Jazzy A Java implementation of Aspell's algorithms. Interesting article about it here.
• Speller Pages This looks interesting: a JavaScript spell-checker for web pages.
• After the Deadline I think this is also online based, but there's a Python library. Does a lot more than just spell checking: take a look at the page for more info.

Controlling Spotify

I love Spotify, but unfortunately it doesn't include an AppleScript dictionary. This is a little limiting when it comes to controlling it, but Mac OS X has a useful work around: GUI scripting.

With GUI scripting, you can use the "System Events" application to effectively click any item on the screen, including those of non-AppleScript applications. Using this principle, I modified my iTunes global Play/Pause script as follows to also control Spotify:

tell application "System Events"
 -- Check whether iTunes or Spotify is running
 set iTunes_instances to count (every process whose name is "iTunes")
 set Spotify_instances to count (every process whose name is "Spotify")
end tell

if iTunes_instances > 0 then
 -- iTunes support AppleScript, so is easy to control:
 tell application "iTunes" to playpause
else if Spotify_instances > 0 then
 -- Spotify doesn't. We want to keep it in the background, so check which app's active.
 set ActiveApp to (path to frontmost application as Unicode text)
 -- Bring Spotify forward so we can control it.
 tell application "Spotify" to activate
 
 -- Activate its "Play/Pause" control
 tell application "System Events"
  tell process "Spotify"
   click menu item 1 of menu "Playback" of menu bar 1
  end tell
 end tell
 
 -- Restore the previously-active app to the front
 tell application ActiveApp to activate
end if

So there you have it: a way to control Spotify without having to touch the mouse. Things will flash momentarily on the screen, but this shouldn't be too disruptive as you're telling the script to run anyway. I use QuickSilver to run this script whenever I hit F8. Very useful for working.