Skip to content

Connect Microsoft SQL Server to Infograph

What it brings in: Every table and view the user can read.

Connect Microsoft SQL Server 5 steps to set up

What you need

  • A Microsoft SQL Server database that accepts TCP/IP connections from the internet, or an SSH server that can reach it.
  • Permission to create a login and a database user, and to change who can connect: your firewall, security group or Windows Firewall.
  • A server that allows SQL Server Authentication: Infograph signs in with a username and password.
  • Infograph's sync servers connect from 51.222.152.247.

Your connection string

Each part is one thing you fill in, and each can fail on its own.

Microsoft SQL Server· Connection string
Server=tcp:db.example.comhost: fix for "We couldn't find that host. Check the host name, then try again.",1433port: fix for "The host refused the connection. Check the port, and that your database accepts connections from Infograph's address.";Initial Catalog=my_databasedatabase: fix for "That database does not exist on this server.";User ID=;Password=;
AddressSign inDatabaseSelect a label for its fix

Steps

  1. Allow Infograph through your firewall

    Add the addresses below to those allowed to reach your database's port, usually 1433. Where that rule lives depends on your host, for example Windows Firewall, an AWS security group or an Azure SQL server-level IP firewall rule.

    51.222.152.247/32

  2. Turn on TCP/IP and SQL Server Authentication

    In SQL Server Configuration Manager, expand SQL Server Network Configuration, select Protocols for your instance and enable TCP/IP. In SSMS, right-click the server, select Properties, and on the Security page choose SQL Server and Windows Authentication mode. Restart SQL Server after either change. A hosted server, such as Amazon RDS or Cloud SQL, already has both on.

  3. Create a read-only user

    Infograph only reads. Give it its own login so you can see and revoke its access in one place. Run this as an admin, with your own password and database name.

    CREATE LOGIN infograph WITH PASSWORD = '[strong-password]';
USE [database];
CREATE USER infograph FOR LOGIN infograph;
ALTER ROLE db_datareader ADD MEMBER infograph;

  4. Copy your connection string

    Infograph reads a SQL Server string in ADO.NET form (Server=...;), as below, and in ODBC and JDBC form or as a sqlserver:// URL. Write the port after a comma, as host,1433. Or press Enter manually and type the host, port, database, username and password.

    Server=tcp:db.example.com,1433;Initial Catalog=my_database;User ID=user;Password=password;

  5. Paste it in Infograph and choose your tables

    Open Microsoft SQL Server in Infograph, paste the string under Connection string and press Connect. Infograph encrypts every SQL Server session, so there is no SSL setting. Pick the tables you want and Infograph starts loading them. Tables outside the dbo schema show as schema.table.

    Allow 51.222.152.247 through your firewall so Infograph can connect.

    Connect

Troubleshooting

Each entry is the message Infograph shows when a connection fails. Open it for the fix.

This host is not reachable from Infograph. Use your database's public address.

The address is private, such as localhost, 10.x or 192.168.x, so only your own network can reach it. Use the database's public address, or connect through an SSH server under Advanced.

We couldn't find that host. Check the host name, then try again.

The host name does not resolve. Copy it again from your provider, and check it is the public address rather than an internal one.

The host refused the connection. Check the port, and that your database accepts connections from Infograph's address.

The server answered but nothing accepted the connection on that port. Check the port, and that TCP/IP is enabled in SQL Server Configuration Manager. A named instance often listens on its own port rather than 1433: find it under TCP/IP properties and enter it as the port.

The server closed the connection. Check that it allows Infograph's address, and whether it requires an encrypted connection.

The server answered, then hung up before Infograph could sign in. Check its firewall allows 51.222.152.247, and that the host and port are the database's own rather than a proxy in front of it.

The host did not answer in time. Check the host and port, and that your firewall allows Infograph's address.

Usually a firewall dropping the connection. Allow 51.222.152.247 on your database's port, in Windows Firewall as well as any cloud firewall. A HOST\INSTANCE name also needs UDP 1434 open, so enter the instance's port instead. If the database is on a private network, connect through an SSH server under Advanced.

The username or password was not accepted.

SQL Server refused the login (error 18456). Check the username and password. In a Server=...; string, put a password containing ; in double quotes. In a sqlserver:// URL, characters such as @, # or / must be percent-encoded, so @ becomes %40. If they are right, check the server allows SQL Server and Windows Authentication mode rather than Windows Authentication mode only, and that the login is enabled.

Those credentials were not accepted.

SQL Server reports a wrong password and a database the login cannot open the same way. Check the username and password first, then the database name, and that the login has a user in that database. The server must allow SQL Server and Windows Authentication mode.

That database does not exist on this server.

SQL Server signed you in but could not open the database. Check its spelling and case against the list from SELECT name FROM sys.databases. If the name is right, the login has no user in that database: run the CREATE USER and ALTER ROLE lines from the steps in it.

Through an SSH server

Infograph could not reach your SSH server. Check its address and port, and that it allows connections from Infograph's address.

Check the SSH server's public address and port, and that its firewall allows 51.222.152.247 on that port, usually 22.

Your SSH server only offers old encryption settings. Infograph needs a current OpenSSH with an Ed25519, ECDSA or RSA (SHA-2) host key.

Your SSH server only offers old encryption settings. Update OpenSSH on it. It needs an Ed25519, ECDSA or RSA (SHA-2) host key.

Your SSH server refused Infograph's key. Check the key is in authorized_keys for that user.

Add Infograph's public key, shown in the form when you turn on SSH, to ~/.ssh/authorized_keys for the SSH user you entered. Check that user exists and that the file is readable only by them.

Your SSH server's identity changed. If you expected this, trust the new server on the connection.

Infograph remembers your SSH server's key from the first connect, and the server now shows a different one. If you rebuilt or replaced the server, open the connection and press Trust the new server. If you did not, ask whoever runs the server before you trust it.

This connection has not trusted an SSH server yet. Open the connection and use Re-discover streams to trust it.

The connection has not checked your SSH server yet. Open the connection and press Re-discover streams. Infograph then remembers the server's key.

Your SSH server would not forward to the database host. Check the permitopen rule and the database host and port.

The SSH server would not pass the connection on to the database. In authorized_keys, the permitopen rule must name the database host and port exactly as you entered them. In sshd_config, AllowTcpForwarding must not be no. The database must also accept connections from the SSH server, for example in its firewall or security group.

Common problems

These happen in Microsoft SQL Server itself, where Infograph cannot see them.

A table is missing from the list

Infograph lists only the tables and views the user can SELECT from, in the database you entered. Add the user to db_datareader in that database, or grant SELECT on the schema or table, then press Re-discover streams on the connection.