Guide: Using Microsoft SQL with Databox
Access your SQL query results right on your mobile device, big screen or computer
Microsoft SQL Server 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).
Microsoft markets at least a dozen different editions of Microsoft SQL Server. These are all aimed at different audiences and for workloads ranging from small single-machine applications to large Internet-facing applications with many concurrent users.
What will we accomplish in this tutorial?
In this article, we will first configure the SQL Server for remote TCP access, add a sample database and remote user, and run a query on a sample AdventureWorks database.We will then connect to the database via Databox andfinally we’ll visualize the data. The end result will be a databoard displaying the data from the Microsoft SQL Server database.
For this tutorial, we’ll use SQL Server version 2017. Configuration is similar for versions 2016 and 2012.
1. Configure TCP access to SQL Server
- First open Sql Server Configuration Manager, select our DB instance and make sure TCP/IP is enable
- Make sure that in Advanced settings, port 1433 (or some other custom port) is entered:
- The service must be restarted for new settings to take effect.
- Double check the service in listening on the port by running netstat - an command in cmd.exe:
Make sure port forwarding in your firewall is setup, so that the port (1433 in our deault case) is reachable from Databox’s connector IP 220.127.116.11. Otherwise, Timeout errors will occur when connecting remotely.
For the demo database, full-text extraction for full text search feature is needed. Be sure to add this feature if you haven’t already from SQL Server Installation Center.
Login to the default db with “Windows Authentication mode.” Right click on the server name and select “Properties.” Click on the Security tab and make sure the server is in “SQL Server and Windows Authentication mode”:
We’ll download the AdventureWorks sample database from GitHub: AdventureWorks.zip.
Unpack it in the folder
c:\Samples\AdventureWorks and open the instawdb.sql file in your SQL Server. Choose the “SQLCMD Mode” option from the Query menu.
Change the two vars if the sql files location is different from the default location, or if you want the database to be named to some other value. Press F5 for the script to execute.
In a minute or so, the status line at the bottom will reflect that there are no errors.
If the query was not executed successfully, double check all of the steps above.
2. Configure remote SQL user
To access the database remotely, we’ll add a new user with limited privileges and enable the user on our new database.
- You’ll first need to expand the Security tab. Right click on “Login” and choose “New Login”:
- Choose a login name, “SQL Server authentication” type and password.
- Click on “User Mapping” and map the database to this new user. Below, check the role db_datareader to give this user access to read the table or view data, but not write it.
For further security considerations, please consult the official Microsoft SQL Server documentation.
That’s it! We’ve successfully set up a demo database and a remote user. Our server should be remotely accessible now. In the next step, we’ll be moving over to Databox to establish the connection.
3. Connect the Microsoft data source to Databox
Our database is now ready. The next step is to connect it and test that it’s returning the data we need for our visualizations.
- To do this, you’ll first need to log in to the Databox app at https://app.databox.com/. Navigate to the ‘Data Manager’ menu item, and select the “+New connection” button.
- Filter this next screen by typing “ MSSQL” in the Search box on the righthand side. The MSSQL connector will now filter to the top.
- Hover over the connector tile and click on the “ Connect” ribbon. This will prompt the connect window to appear:
Enter the arbitrary name for this connection and all of the additional information you’re prompted to add.
Click “ Activate”. If all went well, you should see the message “[name] connected” after a few seconds. If that doesn’t happen, double check that you’ve followed the article correctly. If a timeout error occurs, we couldn’t connect to the server. This is possibly due to a firewall issue. In this case check if you have forwarded the port and enabled it for our public IP.
If all else fails, contact support ( [email protected]), so we can debug the issue.
4. Running a custom query
We will write a custom query to test this connection. Databox will run the query and display the data returned in a tabular format. There are a couple of things we need to make sure of:
- The query must limit a query result to 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)
Now lets create a new custom query to pull the data into Databox:
- In Databox, navigate to the Data Manager and select the Query Builder submenu item. Click the “ +New Query” button.
- Choose the newly connected MSSQL datasource in the dropdown, and enter the following query:
SELECT TOP 20 p.ModifiedDate AS date, p.Name, sd.OrderQty FROM Sales.SalesOrderDetail sd INNER JOIN Production.Product p ON sd.ProductID = p.ProductID ORDER BY sd.OrderQty DESC
- Click “Run query”. After a few seconds, you will see some results in the Data Preview section.
Databox summarizes the results by Dimension and adds values.
- Since we’re successfully receiving results from the remote database, you’ll want to give the query an appropriate name and click “Save query”.
Great! You have now validated the connection and run a test query. Databox has successfully pulled the data from the SQL Server database. Once we save the custom query, data will be fetched regularly (either once an hour or once daily depending on your subscription package).
In the last step we will visualize the data using the Designer tool.
5. Visualize Your Data with the Databox Designer
Now that the database is connected, we will use the Designer to query, shape and display the data in a format that’s most appropriate for our needs:
- Go to the ‘Databoards’ menu item in the app. You can either choose an existing databoard or create a new one. Once you’re in the designer, click on the “Visualization Type” icon on the left hand side.
- For this example, we will display our data in a tabular format. Drag & drop the Table block onto your databoard.
- Click on the new table block and enter the properties in the right hand properties panel so it looks like this:
We have selected our “Testing MSSQL” data source, chosen the “MSSQL Custom Query 1” metric and added “All Time” and “Today” as date range intervals in the properties panel.
Our table now looks like this:
Well done! Your database is now connected to Databox. Queries can be executed and then displayed on your mobile device, TV or computer.
Go ahead and explore further. Add more custom queries, create new datablocks, play around with different types of visualizations... Make the perfect databoard that you always needed but didn't know how to create.
Ready to try it for yourself? Signup for free today!
Remember: we’re always glad to help if you run into any obstacles!
- Make sure all data entered is correct and the roles are appropriately set.
- 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 numeric value
- Make sure your firewall is open to our connector IP and port is correctly forwarded to the server.