I have had a hard time creating data connections with an Access 2013 App database. After a good few hours spent scouring the internet for a solution, and a good few more hours uncovering a “solution” that is underwhelming at best, I am happy to share with you my findings. I really hope that someone will leave a comment with a better solution at some point in the future.
This blog post will provide step-by-step guide on how to achieve a data connection from an Excel workbook (which can be hosted in SharePoint) to the SQL database behind an Access 2013 App. Once this is achieved, a good BI developer should have no trouble visualising the data captured via the Access App with the help of pivot tables, slicing and graphing.
The first step is to identify the server address and database to connect to along with the credentials required to authenticate.
- This can be done by navigating to the Access App, clicking the ‘settings’ icon, then clicking ‘Customize in Access’
Launching the app database in Access - Download the .accdw file and open it to launch access
- Click ‘FILE’ in the ribbon
- Click ‘Manage’
- In the drop-down menu ensure that ‘From Any Location’ and ‘Enable Read-Only Connection’ are highlighted with pink squares. If not, click them
Determining the Access database location and credentials - Click ‘View Read-Only Connection Information’
- Take note of Server, Database, UserName, and Password from this dialog as you will need them all later
Access connection information dialog
Next we use this information to create the data connection.
- Launch Excel
- Create a new external data connection ‘From Data Connection Wizard’
Launching the Excel data connection wizard - Click ‘Other/Advanced’, then ‘Next’
- Click ‘SQL Server Native Client 11.0’, then ‘Next’
- On the ‘Data Link Properties’ dialog, uncheck the ‘Blank Password’ box and check the ‘Allow saving password’ box, then input the server name, user name, password, and database
Configuring the data connection. Ensure you provide the database - Test the connection, you should see a dialog box with ‘Test Connection Succeeded’
- Note that it is when you attempt to make a data connection without providing the database that you get the following error which I bet lead to to this post:
Cannot open server ‘xxxxxxxxxx’ requested by the login. Client with IP address ‘00.000.000.000’ is not allowed to access the server. To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect. - You can now click ‘Ok’
- Uncheck the ‘Use Trusted Connection’ checkbox and replace the existing password with the correct one. Click ‘Ok’
- Select a table and click ‘Next’. You can get fancy here later, let’s just get it working first.
- Click ‘Finish’
- Click ‘Ok’
- The data connection will fail with the following error:
Initialization of the data source failed. Check the database server or contact your administrator. Make sure the external database is available, and then try the operation again. If you see this message again, create a new data source to connect to the database.
The final frustration!
On the next dialog, uncheck the ‘Use Trusted Connection’ checkbox and replace the existing password with the correct one. Click ‘Ok’.
The second time it works. This process of providing the connection credentials twice is required not only upon the creation of the connection but also every time the data needs to be refreshed. It makes for a rather poor UX and it is a pretty awful scenario to have to explain to a client.
I really want to believe that there is a setting (most probably under the ‘All’ tab on the ‘Data Link Properties’ dialog) that will workaround this issue however I am yet to find it. Please leave a comment if you find a solution to this issue.