Intro
Microsoft SQL is a widely used relational database management system. You can use Domo’s Microsoft SQL SSH Connector to pull data from your Microsoft SQL database. The Microsoft SQL SSH connector creates a secure, encrypted connection with your Microsoft SQL database to bring your data into Domo and start making better decisions. Use Domo’s prebuilt Microsoft SQL SSH connector to get data into Domo. For more information about the Microsoft SQL Server API, visit their website. (https://technet.microsoft.com/en-us/library/aa174556(v=sql.80).aspx). The Microsoft SQL SSH connector is a “Database” connector, meaning it retrieves data from databases using a query. In the Data Center, you can access the connector page for this and other Database connectors by clicking Database in the toolbar at the top of the window. You connect to your Microsoft SQL Server in the Data Center. This topic discusses the fields and menus that are specific to the Microsoft SQL SSH connector user interface. General information for adding DataSets, setting update schedules, and editing DataSet information is discussed in Adding a DataSet Using a Data Connector.Best Practices
Make sure you use a read-only account that has access only to the databases and tables you need to access.Prerequisites
To connect to a Microsoft SQL Server database and create a DataSet, you must have all of the following:- The user name and password of your Unix account
- The The UNIX Server host name to SSH tunnel through
- The SSH port number
- The SSH private key o f your UNIX account (DES or RSA), in PEM format
- And, the Host name, Database name, User name, Password, and Port number of your Microsoft SQL Server
Connecting to Your Microsoft SQL Server Database
This section enumerates the options in the Credentials and Details panes in the Microsoft SQL SSH Connector page. The components of the other panes in this page, Scheduling and Name & Describe Your DataSet, are universal across most connector types and are discussed in greater length in Adding a DataSet Using a Data Connector.Credentials Pane
This pane contains fields for entering credentials to connect to your database. The following table describes what is needed for each field:Field | Description |
|---|---|
SSH Hostname | Enter the UNIX Server hostname to SSH tunnel through. |
SSH Username | Enter the UNIX account username. |
SSH Password | Enter the UNIX account password. |
SSH Port | Enter the port number for t he UNIX port. |
SSH Private Key | Enter the SSH private key (DES or RSA), in PEM format. |
Microsoft SQL Server Host | Enter Microsoft SQL Server host name. |
Microsoft SQL Server Database | Enter Microsoft SQL Server database name. |
Microsoft SQL Server Username | Enter Microsoft SQL Server username. |
Microsoft SQL Server Password | Enter Microsoft SQL Server password. |
Microsoft SQL Server Port | Enter Microsoft SQL Server database port number. |
Details Pane
In this pane you create an SQL query to pull data from your database.Menu | Description |
SQL Query | Enter the Structured Query Language (SQL) query to use in selecting the data you want. For example:
|
Fetch Size | Enter the fetch size for memory performance. The default value will be 1000, if this field is left blank. Try decreasing the fetch size, if it throws an ‘out of memory for a value’ error. |
Other Panes
For information about the remaining sections of the connector interface, including how to configure scheduling, retry, and update options, see Adding a DataSet Using a Data Connector.Troubleshooting
- Ensure the port is open on the Windows server and that it is open and forwarded in the firewall.
- Make sure you can connect from a workstation in the client network.
- Network protocols must be enabled for remote connections to occur.