Accessing MSSQL in VMWare Fusion from Mac host

Jun 15th, 2013

Disclaimer: I wrote this in haste because it's Saturday but I wanted to at least document things a little, so my grammar is horrible and I left out a bunch of stuff. Well, this should help quite a bit anyway :)

I had to get data out of a MSSQL database and into a MYSQL database. I had VMWare fusion with Windows 7 already set up.

In Windows:

Then I downloaded and installed MS SQL Server Express With Tools along with the accompanying Service Pack 1. When installing the MSSQL server, make sure to install it using mixed mode authentication so you can log in using an IP and a user/pass. The username seems to always be 'sa' so you won't see an option to change it, only the option to set your password. After installing, you should open up the Server Configuration tool and make sure that all devices/services/whatever have TCP/IP enabled.

Next, open up ports 1433-1434 in the Windows Firewall (add an inbound rule).

Open up a command prompt and type ipconfig. Get the ipv4 address, you'll need it later in MYSQL Workbench.

Switch over to the Mac:

Next, I needed to build the 32-bit version of FreeTDS which allows the Mac to talk to MSSQL. I use homebrew. But instead of just doing 'brew install freetds', I had to edit the recipe - 'brew edit freetds' which will open up freetds.rb in an editor. Below you can see my modified recipe. Notice how I've added the CFLAGS and CXXFLAGS line to make sure this is a simple i386 build. Also notice the '--enable-msdblib' line that I added. If you don't add the CFLAGS then you might get an error later saying something like 'found file /blah/blah but it is the wrong arch'.

require 'formula'
 
class Freetds < Formula
  homepage 'http://www.freetds.org/'
  url 'http://mirrors.ibiblio.org/freetds/stable/freetds-0.91.tar.gz'
  sha1 '3ab06c8e208e82197dc25d09ae353d9f3be7db52'
 
  depends_on "pkg-config" => :build
  depends_on "unixodbc" => :optional
 
  def install
    ENV['CFLAGS']=ENV['CXXFLAGS']="-arch i386"
    args = %W[--prefix=#{prefix}
              --with-openssl=/usr/bin
              --with-tdsver=7.2
              --enable-msdblib
              --mandir=#{man}
            ]
 
    if build.include? "with-unixodbc"
      args << "--with-unixodbc=#{Formula.factory('unixodbc').prefix}"
    end
 
    system "./configure", *args
    system 'make'
    ENV.j1 # Or fails to install on multi-core machines
    system 'make install'
  end
end

Beyond that, I'm not sure if anything else I did really mattered. You might have to edit freetds.conf (can't remember where the file is located at but I copied it and pasted it as ~/.freetds.conf; notice how it's a hidden file in my user directory but the original is not hidden.) The only thing I have uncommented in freetds.conf is:

port = 1433
tds version = 7.2

I use TDS 7.2 as that is what works with SQL Server 2008, as you will see when you open the migration wizard in MySQL Workbench... open it now. The wizard is a little buggy. Sometimes you will get connection errors and you have to just go back a little bit in the wizard and then go forward again. Also, at the final step, the wizard couldn't populate the tables but it was able to make a script that you can edit and then run from the terminal. This was ok because you can run the script later as long as the schema hasn't changed, and it will re-import the data; you can edit the script file so that it will truncate tables before importing.

I almost forgot. MSSQL has these text fields called 'ntext' that are similar to LONGTEXT in MYSQL. The Workbench migration wizard creates the tables as varchar(16) though! Much too small! You'll have to manually convert them to LONGTEXT. How I did it: the workbench wizard first creates the schema and the import script, then I alter the schema with PHPMyadmin, then I run the import script.

I also had this problem where MSSQL can have many many varchar fields with large maximum values but MYSQL can only handle something like 65000 total chars in varchar fields per row. So, you might have to convert some of the varchar fields to TEXT fields using the wizard's editor. After using the wizard's editor to edit the schema that will be created, you might get a connection error on the next step; to avoid this, before continuing to the next step, go back a step (or two) and then go forward and you should get a good connection and the schema should be created.

Um, yeah... that's it for now. Sorry for the bad writeup; hopefully it's better than not writing anything.

Article Type

General