tds icon indicating copy to clipboard operation
tds copied to clipboard

Connect to SQL server over TCP

Open santoshturamari opened this issue 1 year ago • 4 comments

Hello,

I want to run Tds with TCP protocol. Because our application has to communicate with remote SQL server using TCP. Is there any option to pass the protocol?

Please clarify.

@matreyes @josevalim

Thanks, Santosh T

santoshturamari avatar Jun 14 '23 14:06 santoshturamari

Hi @santoshturamari,

This library uses TCP to connect to SQL server. If you have trouble to connection, please verify on what TCP port remote SQL server listens for connection. If that port is not 1433, you have to set it in this library in mix config like below

import Mix.Config

config :your_app_name, :tds_conn,
  hostname: "some-remote-hostname", # <--- change to IP address or hostname that SQL server is running on
  port: 1433, # <---- Change to port SQL server listens
  username: "...",
  password: "...",
  database: "database-name"

mjaric avatar Jun 14 '23 20:06 mjaric

Hi @mjaric Thanks for the reply. I have already tried this, also I have tried {:ok, pid} = Tds.start_link([hostname: ********, instance: ******, username: ********, password: *********, database: ********, port: 1433])

Always I got this error. ** (DBConnection.ConnectionError) connection not available and request was dropped from queue after 10044ms. This means requests are coming in and your connection pool cannot serve them fast enough. You can address this by:

  1. Ensuring your database is available and that you can connect to it
  2. Tracking down slow queries and making sure they are running fast enough
  3. Increasing the pool_size (although this increases resource consumption)
  4. Allowing requests to wait longer by increasing :queue_target and :queue_interval

See DBConnection.start_link/2 for more information

But when I tried to connect via Python, pymssql or execute python script in Elixir using erlport, it got connected seamlessly.

Thanks, Santosh T

santoshturamari avatar Jun 14 '23 21:06 santoshturamari

Is your remote SQL server server in Azure?

mjaric avatar Jun 15 '23 13:06 mjaric

No, but after the trigger, when we checked the firewall logs, the requests always originated from 1434 port which is UDP port, even though I have mentioned 1433. Hence requests were always failing. But when I used pymssql from Python, the communication was proper.

santoshturamari avatar Jun 15 '23 13:06 santoshturamari