Connecting Phoenix app to MS SQL Azure database

I've been working through the same issue this week, connecting to an Azure SQL Database, and this is what I've got working.

You are seeing the first of 2 main problems I ran into:

1. Erlang installed without ODBC
2. Mssqlex adapter is missing required configuration options

#1: Erlang installed without ODBC

If you've installed Elixir and Erlang using brew, or something like it, you are getting an installation of Erlang that does not include erlang-odbc. In order to remedy this, you will need to install Erlang from source.

I recommend that you install both Erlang and Elixir from source using the method documented here:http://www.lambdacat.com/how-to-install-elixir-on-mac/

Just make sure that you remove the Kerl option, --without-odbc, before you build Erlang.

Now that you have Erlang installed correctly, the odbc_not_started error should go away... to be replaced by connection errors to the database.

#2: Mssqlex adapter is missing required configuration options

As of this moment, the mssqlex adapter does not support the following configuration options that Azure is looking for:

1. Encrypt
2. TrustServerCertificate
3. Connection Timeout

To get around this issue, I forked the mssqlex library and added those options in a branch. I am not committing to maintaining this so feel free to fork it yourself.

In order to use the modified mssqlex, replace the mix dependency with the github location and branch name:

{:mssqlex, git: "https://github.com/tvanhouten/mssqlex.git", branch: "azure-settings", override: true}

The override: true is important because mssql_ecto has a dependency on the mssqlex version in hex.

Now you can update your configuration:

config :my_app, MyApp.Repo,adapter: MssqlEcto,username: "<my_username>",password: "<my_password>",database: "test",hostname: "<my_server>.database.windows.net", pool_size: 10,encrypt: "yes",trust_server_certificate: "no",connection_timeout: "30

Other "Gotchas" to Look Out For

Azure requires TCP connection:

In order to connect to an Azure SQL Database, you will need to make sure that the hostname: option starts with tcp:. So, all together, it will look like hostname: "tcp:.database.windows.net"

Mssql_ecto does not support Ecto 2.2+:

If you're getting errors related to Ecto or Ecto.SubQuery, you probably need to make sure that you are using a version of Ecto that is supported by mssql_ecto. At this point in time, that means Ecto 2.1.x. Again, this requires a slight change to your mix dependencies:

{:ecto, ">= 2.1.0 and < 2.2.0"}

Dependency cache and lock issues:

If everything seems to be in order according to the above but it just doesn't seem like the new dependencies are being updated and compiled as expected, do the following:

1. Run mix deps.clean --all
2. Delete the mix.lock file from your root project directory
3. Run mix deps.get
4. Run mix deps.compile

That should do it!