mssql

Connecting Ruby and Microsoft SQL Server with ADBC

Instructions

Tip

If you already have a SQL Server instance running, skip the steps to run SQL Server in a Docker container.

Prerequisites

1. Install Ruby

2. Install dbc

3. Ensure the native Arrow GLib and ADBC GLib libraries required by red-adbc are installed and discoverable. If bundle install reports missing arrow, arrow-glib, or adbc-glib, use the platform-specific commands below.

   macOS with Homebrew

   brew install apache-arrow-glib apache-arrow-adbc-glib

   Debian/Ubuntu

   sudo apt install libarrow-glib-dev libadbc-glib-dev

   RHEL-compatible distributions

   sudo dnf install arrow-glib-devel adbc-glib-devel

   Windows with RubyInstaller/MSYS2 UCRT64

   pacman -S --needed mingw-w64-ucrt-x86_64-arrow mingw-w64-ucrt-x86_64-arrow-adbc-glib

   If you use a different MSYS2 environment, adjust the package prefix to match it; for example, use mingw-w64-x86_64-* from the MINGW64 shell.

4. Install Ruby dependencies:

   bundle install

   If you have multiple Ruby installations, ensure ruby and bundle resolve to the same installation before running this command.

5. Install Docker

Set up SQL Server

1. Start SQL Server in a Docker container:

   docker run \
      -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=Co1umn&r" \
      -p 1433:1433 --name mssql --hostname mssql \
      -d \
      mcr.microsoft.com/mssql/server:2025-latest

2. Create a table in SQL Server and load data into it:

   docker cp games.sql mssql:/tmp/games.sql
   
   docker exec -it mssql /opt/mssql-tools18/bin/sqlcmd \
     -S localhost -U sa -P 'Co1umn&r' -C -i /tmp/games.sql

Connect to SQL Server

1. Install the SQL Server ADBC driver:

   dbc install --level user mssql

2. Customize the Ruby script main.rb as needed

   • Change the connection arguments in database.set_option()
     • Change uri as needed, using query parameters to add more connection arguments, or keep it as is to use the data included with this example
   • If you changed which database you're connecting to, also change the SQL SELECT statement in connection.query()

Tip

To use Microsoft Entra ID for authentication, install the Azure CLI, log in using the az login command, and add fedauth= to your connection URI. For example:

sqlserver://my-database-endpoint.database.windows.net:1433?database=my-database-name&fedauth=ActiveDirectoryDefault

3. Run the Ruby script:

   bundle exec ruby main.rb

Clean up

1. Stop the Docker container running SQL Server:

   docker stop mssql
   docker rm mssql