Wednesday, October 12, 2011

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.

12 comments:

Anonymous said...

Good Job. It worked fine!

Anonymous said...

It really works great!

Anonymous said...

Works like a dream.
So frustrating that MS haven't improved on web and database data imports in Office 2011, especially with web queries - ridiculous that you can unzip the XML and hack about with URLs but there is still no mechanism within the app without first creating iqy files.

Anonymous said...

Thank's man!
After 3 days of trying everything, iODBC/ unixODBC, freeTDS, compile and re-compile, your explanation worked perfectly!
Thanks!

Nicolas said...

Hello,

Thanks for posting this help.
I'm trying to get a mac osx 10.7 connect to a mysql database using their driver. Have it working on a couple osx 10.6 macs at work, but microsoft query seems to be incompatible on osx 10.7 (PowerPC compatibility was deprecated) so I'm curious on how were you able to make it run.

Marcelo said...

Hello,

Thank you for the tips. I'm using it for postgresql odbc driver on 10.6 and it works ok.

I just confirm what Nicolas said about mac os x 10.7. It seems that the issue is the lack os ppc i386 support.

Anonymous said...

Like Nicolas I am running on 10.7 and I don't seem to be able to get the iODBC test to run successfully. I definitely used 'universal' and the tsql tests runs okay.

Any ideas on where to go from here?

Anonymous said...

I am getting the error Unknown host name while do "Test" from iODBC Apdmin.

Help please

Amol Avhad said...

Hello All,

I completed all the steps as suggested but when I try to connect using tsql command, I get below error message:

locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Error 20009 (Severity 9):
Unable to connect: Adaptive server is unable or does not exist
OS error 61, "Connection Refused"
There was a problem connecting to the server.

Can someone give me hand in this? Thanks,
Amol

Anonymous said...

Hi i need help - i am trying to view a excel macro file both in windows and mac. In windows, it worked perfectly (its automated) but when i opened it on MAC it says can't find library. Is there anyone who could help me fix this? I have no background on macro, i am just a user.

Thanks,

Kyle Hankinson said...

If your looking to use MSSQL from your mac, you can give SQLPro for MSSQL (http://macsqlclient.com) a try.

Heather Cecil said...

Hi,

I don't know if you will be able to help me or not. I'm new to MACs and using data warehouses. I have successfully connected. However, I have no idea how to use microsoft query. Help please. Do you have any suggested reading sources?

Thanks, Heather

hcecil@hmc.psu.edu