Connecting Phoenix app to MS SQL Azure database

Question

I am trying to connect a phoenix application to MS SQL. After looking around online I came across a couple of adapters called mssql_ecto & mssqlex.

I have added them to the project following the instructions in the readme, installed odbc and checked that the db is online but I am now getting the following error..

[error] Mssqlex.Protocol (#PID<0.13069.0>) failed to connect: ** (Mssqlex.Error) odbc_not_started

My app is configured as below..

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

My environment is as follows..

• MacOS Sierra 10.12.5
• Elixir version: 1.4.4

There is an issue in the mssql_ecto repo already, and I have tried the suggestions from there, but it still has not worked.

If anyone has managed to connect their phoenix/elixir application to MSSQL on macOS and could provide some instructions it would be greatly appreciated (even if it was done in a completely different way to my approach 😉 ).

Side note: Tried it in node with the same db and was able to connect to and query the db.

Answer 1

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!