How to Connect to SQL Server from Ruby

SQL Server is a Microsoft product and Ruby is not a Microsoft language, so unlike the first-party C# story, connecting from Ruby means going through FreeTDS, the open-source implementation of the TDS protocol that SQL Server speaks. In practice there is one gem that matters, it wraps FreeTDS, and the friction is almost always in the FreeTDS layer rather than the Ruby layer. This guide covers the driver, installing FreeTDS, the connection, parameterized queries, how to get a generated key back, encryption, named instances, the higher-level options, and the errors you will meet.

The one driver that matters

For almost everyone the answer is tiny_tds. It is maintained by the rails-sqlserver organisation, it is what the ActiveRecord SQL Server adapter is built on, and it handles the connect/query/iterate loop that most applications need. The ODBC path exists and works, but it means installing and configuring Microsoft's ODBC driver plus unixODBC, which is more moving parts for no gain unless you have a specific reason (for example, sharing an ODBC stack with other tools).

Installing FreeTDS and the gem

tiny_tds compiles against FreeTDS, so FreeTDS has to be present at build time:

• Debian/Ubuntu: sudo apt-get install freetds-dev
• macOS (Homebrew): brew install freetds
• RHEL/Fedora: sudo dnf install freetds-devel

Then:

gem install tiny_tds

If the build fails complaining it cannot find sybdb.h or libsybdb, the FreeTDS dev package is missing or the compiler can't see it. On Homebrew you sometimes need to point the build at the install prefix:

gem install tiny_tds -- --with-freetds-dir=$(brew --prefix freetds)

You do not need to edit FreeTDS's own freetds.conf to connect. tiny_tds takes the host, port, and credentials directly, so the classic FreeTDS "define a server alias in freetds.conf" step is optional. It's still useful for testing your setup with the tsql command-line tool that ships with FreeTDS.

A minimal connection

require "tiny_tds"
 
client = TinyTds::Client.new(
  username: "sa",
  password: "Your_password123",
  host:     "localhost",
  port:     1433,
  database: "appdb"
)
 
result = client.execute("SELECT 1 AS n")
result.each { |row| puts row["n"] }
 
client.close

execute returns a TinyTds::Result. You must consume it (iterate, or call .do / .each) before running the next statement on the same client, or you will get a "connection is busy" style error. Wrapping in each consumes it; for statements that return no rows, call .do:

result = client.execute("UPDATE users SET active = 1 WHERE id = 42")
result.do
puts result.affected_rows

Parameterized queries: the honest caveat

This is the one place where the Ruby SQL Server story is weaker than Python, Node, or C#. tiny_tds does not expose true server-side prepared statements with bind parameters through its public API. You build the SQL string yourself. That means you are responsible for escaping, and the correct tool is client.escape:

name = client.escape(user_input)
result = client.execute("SELECT id, email FROM users WHERE name = '#{name}'")

escape doubles single quotes, which is the correct escaping for a SQL Server string literal. It does not wrap the value in quotes for you, so you still add the surrounding quotes, and it only helps for string literals -- for numeric input, validate or cast it yourself (Integer(user_input)) rather than interpolating raw.

If you want real bound parameters, that is a strong reason to go through Sequel or ActiveRecord (below), both of which layer a parameterization story on top, or to use sp_executesql explicitly. Do not skip escaping and interpolate raw user input; SQL injection on SQL Server is as real here as anywhere.

Getting a generated key back

SQL Server auto-increment columns are IDENTITY. To read the value a row got, the portable and reliable approach is the OUTPUT clause, which returns the inserted values as a result set:

result = client.execute(<<~SQL)
  INSERT INTO users (email)
  OUTPUT INSERTED.id
  VALUES ('a@example.com')
SQL
new_id = result.each.first["id"]

Prefer OUTPUT INSERTED.id over SELECT SCOPE_IDENTITY() or @@IDENTITY. @@IDENTITY in particular is a known footgun: it returns the last identity generated in the session across any table, so a trigger that inserts into another table will hand you the wrong value. SCOPE_IDENTITY() avoids the trigger problem but still relies on a separate round trip; OUTPUT gives you the value directly from the insert.

Transactions

tiny_tds has no transaction helper of its own -- you issue the T-SQL directly:

client.execute("BEGIN TRANSACTION").do
begin
  client.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1").do
  client.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2").do
  client.execute("COMMIT").do
rescue => e
  client.execute("ROLLBACK").do
  raise e
end

Sequel and ActiveRecord wrap this in a block that commits on success and rolls back on any raised exception, which is worth having if you do this often.

Encryption

Because tiny_tds talks to SQL Server through FreeTDS, encryption is a FreeTDS capability, not a Ruby one. tiny_tds exposes an encryption option:

client = TinyTds::Client.new(
  username: "sa", password: "Your_password123",
  host: "db.example.com", database: "appdb",
  encryption: :require
)

:require forces the connection to negotiate TLS and fails if the server won't. This needs a FreeTDS build with OpenSSL/GnuTLS support (the standard packages have it). Note that FreeTDS's TLS validation is weaker than the .NET client's: it encrypts the channel but is not as strict about certificate chain and hostname verification as Microsoft.Data.SqlClient with Encrypt=true is. If you connect to Azure SQL, encryption is mandatory server-side, so you need at least :require; treat the channel as protected against passive sniffing, and put network controls (private endpoints, firewall rules) around it rather than relying on FreeTDS certificate validation alone.

Named instances and ports

If you connect to a named instance (SERVERNAME\SQLEXPRESS) rather than a default instance, SQL Server resolves the instance's dynamic port through the SQL Server Browser service on UDP 1434. FreeTDS's handling of this is inconsistent, and the reliable fix is to skip discovery entirely: find the instance's actual TCP port (in SQL Server Configuration Manager) and connect to host + that explicit port. Passing an explicit port is the single most common fix for "it connects from SSMS but not from Ruby" on named instances.

Sequel

Sequel uses tiny_tds under the hood via its tinytds adapter and gives you parameterization, a transaction block, and a dataset API:

require "sequel"
 
DB = Sequel.connect(
  adapter:  "tinytds",
  host:     "localhost",
  port:     1433,
  database: "appdb",
  user:     "sa",
  password: "Your_password123"
)
 
# Parameterized -- Sequel handles escaping
DB[:users].where(name: user_input).select(:id, :email).all
 
DB.transaction do
  DB[:accounts].where(id: 1).update(balance: Sequel.-(:balance, 100))
  DB[:accounts].where(id: 2).update(balance: Sequel.+(:balance, 100))
end

Sequel still needs FreeTDS and the tiny_tds gem installed; it is a layer on top, not a replacement.

ActiveRecord (Rails)

For Rails, add both gems:

# Gemfile
gem "tiny_tds"
gem "activerecord-sqlserver-adapter"

Match the adapter's major version to your Rails major version (adapter 8.x for Rails 8.x); a mismatch is the most common source of load errors. Then in config/database.yml:

production:
  adapter:  sqlserver
  host:     db.example.com
  port:     1433
  database: appdb
  username: appuser
  password: <%= ENV["DB_PASSWORD"] %>
  encryption: require

ActiveRecord gives you migrations, connection pooling (pool in database.yml, sized so pool × process/worker count stays under the server's connection limit), and parameterized queries for free. It is the right choice if you are in Rails; the raw tiny_tds client is the right choice for a script or a non-Rails service where you don't want the ORM weight.

Common errors

Querying without writing connection code

If your goal is to explore a SQL Server database rather than build a service, a GUI client skips FreeTDS setup entirely. Mako connects to SQL Server with AI-assisted SQL -- see our SQL Server GUI client guide. Connecting from another language? See How to Connect to SQL Server from Python, from Node.js, and from C#.

Mako connects to SQL Server with AI-powered autocomplete. Try it free at mako.ai.