Error when creating mssql db with ecto

Question

I just started Elixir Phoenix new web app with:

mix phoenix.new phoenix_sample --database mssql

It generated config/dev.exs file with db configuration, I have modified to match my current ms sql server:

config :phoenix_sample, PhoenixSample.Repo,
  adapter: Tds.Ecto,
  username: "dev",
  password: "dev",
  database: "phoenix_sample_db",
  hostname: "localhost",
  pool_size: 10

I am able to login into ssms as dev user and connect to phoenix_sample_db database

But when I run: mix ecto.create

I always error out:

** (EXIT from #PID<0.46.0>) %Tds.Error{message: "tcp connect: nxdomain", mssql: nil}

17:44:04.805 [error] GenServer #PID<0.274.0> terminating
** (stop) %Tds.Error{message: "tcp connect: nxdomain", mssql: nil}
Last message: {:connect, [port: 1433, hostname: ".", database: "master", otp_app: :phoenix_sample, repo: PhoenixSample.Repo, adapter: Tds.Ecto, username: "dev", password: "dev", pool_size: 10]}
State: %{attn_timer: nil, env: %{trans: <<0>>}, ireq: nil, itcp: nil, opts: [port: 1433, hostname: ".", database: "master", otp_app: :phoenix_sample, repo: PhoenixSample.Repo,
adapter: Tds.Ecto, username: "dev", password: "dev", pool_size: 10], pak_data: "", pak_header: "", queue: {[{{:connect, [port: 1433, hostname: ".", database: "master", otp_app: :phoenix_sample, repo: PhoenixSample.Repo, adapter: Tds.Ecto, username: "dev", password: "dev", pool_size: 10]}, {#PID<0.46.0>, #Reference<0.0.2.504>}, #Reference<0.0.2.505>}], []}, sock: nil, state: :ready, statement: nil, tail: "", usock: nil}

Why it's not working and why ecto trying to connect to master db?

If ecto trying to create new database then I need to pass sa credentials to db user config?

See this: github.com/elixir-lang/ecto/issues/932 Note the part that says that nxdomain means it can't reach the host. Since you're pointed to localhost, it may be a firewall issue. — Onorio Catenacci
@OnorioCatenacci I have turned off the firewall, but still the same error — Sergiy Kozachenko
@Grievoushead I haven't used MSSQL with Phoenix and Elixir but like Onorio said nxdomain means that the host can't be reached. However I wanted to point out a flaw in your logic. You seem to want to run ecto.create when you already have the phoenix_sample_db created. Also I looked at TDS GitHub and this is mentioned: Tds Supports sql instances by passing instance: "instancename" to the connection options. Maybe you need to pass the instance name in the config? — Sasha Fonseca
Have you enabled TCP connections in MSSQL? Tds is trying to connect to port 1433 which is the default port in MSSQL but TCP connections are not enabled by default. — hectorsq
@hectorsq thanks, it solve it, somehow I though that shared memory protocol will be used by default. — Sergiy Kozachenko

Sergiy Kozachenko Sergiy Kozachenko · Accepted Answer · 2016-04-11T06:50:13

I was need to enable TCP/IP protocol in Sql Server Configuration Manager

After that I have restarted mssqlserver service and ran mix ecto.create with following config:

config :phoenix_sample, PhoenixSample.Repo,
  adapter: Tds.Ecto,
  username: "sa",
  password: "********",
  database: "test_ecto",
  hostname: "SERG-MAC"

Output:

Finally it works. Thanks to @hectorsq

Error when creating mssql db with ecto

1 Answers