This article explains how to connect your Microsoft SQL database to Magento BI in a four-step process. This process requires some technical expertise related to server connections and SQL, and may require support from developers on your team.
We support Amazon RDS, EC2, Microsoft SQL Azure, and most other cloud server providers. If you have a question on your particular host, submit a support ticket asking us to provide this information.
Our system needs to run SELECT queries on your database. We do this initially to get a snapshot of your database structure and then regularly overtime to keep our data up to date. Our updates are incremental, and we restrict update frequency and time to prevent any unwanted load on your server.
The best way to do this is for us to connect to your database server over TCP/IP. Create a user for us that can only run SELECT queries (and, optionally, can only select data from the tables you specify). This needs to be done for each of your servers that you will be connecting to Magento BI.
Connecting Microsoft SQL to Magento BI:
- Make sure your server allows connections over TCP/IP and mixed mode authentication.
- Make sure your firewall will allow our server's dedicated IP to connect. You can find the IP address we will use to connect to your server on the connections section of your "settings" page.
- Create a user that we will use to log into your database server. You have two options; either via UI or via a query:
- Query (second example)
- Input the server IP address, username and password in Magento BI under Manage Data > Connections.
- Log into Magento BI and go to Manage Data > Connections in your sidebar (only admin users have access to this page).
Then, click the "Add a Data Source" button. Select to connect a Microsoft SQL database and enter your credentials in the fields on the new connections page. Please note if you are using Windows Azure, you must specify a database name as well.