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.
How’s things, I just seen that sometimes this webpage displays a 404 error. I figured that you would be keen to know. Best wishes
Thanks for letting me know. Sounds like a transient connection issue between this server (in Australia) and your device. I hope it isn’t happening frequently. If you have more information I would be glad to hear it. I have had some performance issues in the past but feel that they have reduced significantly recently.
Hi Paul,
You actually can. Go to the Data tab. In the Connections Group click Connections. Select your connection and hit Properties. Then, on the Definition tab, check “Save Password”. This will no longer prompt for passwords!
Hope this works for you.
SabbeRubbish
Thanks a lot for your input. I believe I tried this with no luck, but if it’s working for you then I’m sure it will work for others.
I’m on the same frustrating journey – I also couldn’t make the “save password” option work.
But it DOES work – if you refresh the workbook (using the typed-in password) BEFORE selecting Save Password in the Connection properties. Makes sense when you think about it. Then you can save the spreadsheet and it will not ask for a password when you reopen and refresh.
This also means an Office 365-based spreadsheet can be opened and refreshed on SharePoint with Excel Online, without having to get all your users to mess with passwords.
If you are using an ODC file for the connection, this breaks the link to the ODC file, but you can then re-export it to a new with-password ODC file and keep the advantages of centralised connection information.
Jonathan
Clever, thanks for this!
Was this issue ever resolved? I’m currently in the same dilemma, and I’ve been racking my brain trying to figure out how to get this message to stop appearing. I’m trying to pull in 18 external data sources… which means 18 messages…
Sorry I haven’t looked at this since I wrote the post, I have nothing more to share. Do any of the comments left by others help you out?
Argh… I wish. Still trying to get rid of these damn messages. My head hurts.
🙁 Sorry to hear that – Good luck!
Awesome, thanks much.
However…I got this working at home, but our office firewall blocks port 1433. Is there a way to create an Excel connection using 443 like the Access 2013 App connection?
Sorry, I haven’t looked into this before. Good luck! If you do get it working please post a comment with your solution, I like to read it. Cheers.