Guide: Using Microsoft SQL Server with Databox

Microsoft SQL is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications. These applications may run on either the same computer or on another computer across a network, including the internet.

HOW TO

    Microsoft SQL connections are available in Plus and Business Accounts. Agency Basic Accounts have the ability to use Microsoft SQL in the Agency Account only.

How to prepare your Microsoft SQL Database

IN THIS SECTION

How to configure TCP access to your Microsoft SQL Server

1
 First, we'll open the SQL Server Configuration Manager and will select our Database instance. It's important to make sure TCP/IP is enabled

add_user_popup@2x

2
 Open Advanced Settings and validate that Port 1433 is selected. You may also enter a custom Port here. 

add_user_popup@2x

3
 If you’ve made any changes, restart your Microsoft SQL server to make sure it's fully accepted the updates

add_user_popup@2x

4
 Run the command  netstat - an in cmd.exe to ensure the service is listening to the Port

add_user_popup@2x

Make sure Port forwarding is set up for your Firewall. In our example, Port 1433 must be reachable from Databox's public IP (52.4.198.118). If this is not done, timeout errors will occur when attempting to connect remotely. 

5
 For our Database, Full Text Extraction must be enabled. This can be configured from the  SQL Server Setup window in the Features Selection section.

add_user_popup@2x

6
 Login to the default Datase with Windows Authentication mode. Right click on the Server Name and select Properties. Click on the Security tab and set Server Authentication as SQL Server and Windows Authentication mode.

add_user_popup@2x

7
 For our example, we'll download the AdventureWorks sample Database from GitHub:  AdventureWorks.zip

We'll unpack this in the folder  c:\Samples\AdventureWorks and open the instawdb.sql file in our SQL Server. Choose the SQLCMD Mode option from the Query Menu.

add_user_popup@2xIf the SQL file is not stored in the default location or if you want to customize the Database name, update the two vars as you see fit.

8
 Execute the script by pressing F5

add_user_popup@2x

9
 The Status message at the bottom will inform us if there are any errors. If not, we will receive the following message: 

add_user_popup@2x

If the query was not executed successfully, please go back and re-check all steps above, or contact Databox Support for additional assistance.

How to set up a remote Microsoft SQL user

To access the database remotely, we’ll add a new user with limited privileges and enable the user on our new database

1
 First, we'll expand the Security tab by right clicking on Login and selecting New Login
2
 We'll enter Remote as a Login Name, SQL Server Authentication as the Authentication Type, and a password

add_user_popup@2x

3
 Next, we'll click on User Mapping and map the Database to our new user. To do this, we'll select db_datareader so the user can read/ view the Table, but not write to it. 

add_user_popup@2x

For further security considerations, feel free to consult Microsoft's SQL Server Documentation.

How to connect Microsoft SQL to Databox

Once our Database is properly prepared, we'll connect it to Databox.

add_user_popup@2x

To connect Microsoft SQL to Databox, go to Data Manager > + New connection. Type SQL in the search bar and click on the green Connect button. 

This will open the  Activation window, which is where you will be prompted to enter your connection data and authorize the Databox app. 

add_user_popup@2x

Enter an arbitrary name for this connection and all of the additional information you’re prompted to add.

Click Activate. If a timeout error occurs, Databox couldn’t connect to the server. This is possibly due to a Firewall issue. In this case, check to ensure you have forwarded the Port and enabled it for Databox's public IP (52.4.198.118).

How to use the Query Builder for Microsoft SQL

The Query Builder for Microsoft SQL allows you to create Custom Metrics using data from your Database to use in Databoards, Alerts, Goals, etc. Learn more about the Query Builder for Microsoft SQL here.

Additional Information

  • The query must limit the query result to a maximum of 1000 rows. LIMIT the query if you’re not sure if you will exceed this number.
  • The query must contain a column named “data” (using AS when SELECT-ing data is perfectly acceptable)
  • One column must contain the Date value. You can cast any column to name “date” using the “AS” SQL syntax
  • One column should always contain the Metric Value
  • Make sure your Firewall is open to Databox's public IP and your Port is correctly forwarded to the server
  • Query results are limited to 1000 rows. If you expect your query to return over 1000 rows or if you're not sure how many rows will be returned, be sure to LIMIT the query
  • The query must contain a column named "Date." If you need to, use AS in your SELECT statement to satisfy this requirement
  • "Null" values will not be recognized. Use a COALESCE function when needed. Learn more here.
Did this answer your question? Thanks for the feedback There was a problem submitting your feedback. Please try again later.

Still need help? Contact Us Contact Us