How to: Make external connections to an Access Web App


You may want to access the data stored in your Access Web app from other applications. An Access Web App stores its objects and data in a SQL Azure database for SharePoint Online instances or in a local SQL Server for SharePoint on-premise installations. In order to connect to the Access Web app data on SQL, you need to use the SQL Server Native Client 11.0 ODBC driver and provide connection information from the Access Web App. Below are the steps to make the most commonly requested connections.

Note: If you experience connection issues after following these instructions, you will want to ensure your firewall is not blocking port 1433. See Security Guidelines for Azure SQL Database for more details.

Prerequisite: Download and install the SQL Native Client 11.0 driver from the Microsoft® SQL Server® 2012 SP1 Feature Pack. Within the download details, the only file you need to select is the sqlncli.msi.

Enable Connections for your Access Web App

  1. Open the Access Web App in the Access client
  2. Click the File tab > Info > click Manage within the Connections group
  3. If your Access App is on SharePoint Online, select From Any Location or From My Location to allow connections. Note: If you are on an IPv6 network, you will only have the option to connect From Any Location. In an on-premise installation, these options are not available.
  4. Select either Enable Read-Only Connection or Enable Read-Write Connection
  5. Depending on the previous selection, select either View Read-Only Connection Information or View Read-Write Connection Information
  6. Copy and paste the Server, Database, UserName and Password values into Notepad or Word, or leave the Connection Information window open so you can copy and paste the information from it later.

Create an ODBC Data Source Name (DSN) to Connect to the Access Web App on SQL

  1. Open the ODBC Data Source Administrator tool. There are many methods to access this tool so to eliminate confusion, I’ll provide the corresponding executable for the bitness of DSN you are looking to create. Note:The DSN bitness should match the bitness of your Office installation.

     

    Windows 32bit (x86) / Office 32bit (x86): %windir%\System32\odbcad32.exe

    Windows 64bit (x64) / Office 64bit (x64): %windir%\System32\odbcac32.exe

    Windows 64bit (x64) / Office 32bit (x86): %windir%\SysWow64\odbcad32.exe

     

    Note: %windir% typically points to the default location of Windows, or c:\windows

     

  2. On the User DSN or System DSN tab of the ODBC Data Source Administrator dialog, click Add
  3. Select the SQL Server Native Client 11.0 driver, click Finish
  4. Provide any name you like for this new DSN
  5. Enter the Server information gathered earlier from the Access Web App connection information, click Next
  6. Select the With SQL Server authentication… option
  7. Paste the UserName and Password from the Access Web App connection information, click Next
  8. Select the ‘Change the default database to’ checkbox
  9. Paste the database name* from the Access Web App connection information. Click Next

    *Do not click the down arrow for the database name if you are connecting to a database on SQL Azure as you will not be able to view the list of available databases and will encounter an error. It’s best to copy/paste your specific database name into this field.

  10. Click Finish, click Ok

Use the DSN within an Access desktop database

Now that you have created an ODBC DSN, you are able to use that connection information from many applications. Here are the steps to use this ODBC DSN to create linked tables within a traditional Access desktop database. For other applications, you will want to seek its help documentation on using ODBC connections.

  1. Create a new or open an existing Access desktop database
  2. On the ribbon, click the External Data tab
  3. Within the Import & Link group, click ODBC Database
  4. Select ‘Link to the data source by creating a linked table’, click Ok
  5. In the ‘Select Data Source’ dialog, click the Machine Data Source tab
  6. Select the ODBC DSN that you created, click Ok
  7. Provide the password from the Access Web App connection information, click Ok
  8. Select the tables you wish to link (Note: The tables named Access.<tablename> are the ones that contain the data.)
  9. Check the option Save Password if desired. This stores the password in the database file, but prevents you from having to enter the password each time you open the database
  10. Click Ok
  11. You should now see the linked table(s) in your desktop database (Note: You may rename the tables in the Access desktop database and remove the ‘Access_’ from the name.
  12. Doing so will not change the table names in the SQL database.)

[View:~/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-01-01-89/AccessAppExternalConnections.mp4:0:0]

Using SQL Server Management Studio (SSMS) to Connect to the Access App on SQL

In order to connect to the Access Web App from SSMS, you do not need to create an ODBC DSN, but you will need to first enable external connections by completing the section "Enable Connections for your Access Web App" above.

  1. Open SQL Server Management Studio
  2. In the 'Connect to Server’ dialog, use the connection information from the Access Web App to specify the server name
  3. Change the Authentication to ‘SQL Server Authentication’
  4. Provide the login and password also using the Access Web App connection information
  5. Click the Options >> button
  6. Select the Connection Properties tab
  7. Paste the database name using the Access Web App connection information for the ‘Connect to database’ option. *Do not use the dropdown arrow to attempt to locate the database name
Comments (12)

  1. Andrey Artemyev says:

    Just in case someone’s looking for a VBA way of creating a linked table to Access Web App data, here it is http://social.msdn.microsoft.com/Forums/office/en-US/0caa8980-1599-4a86-a7c3-c52fe3393db9/linking-my-webapp-tables-to-a-desktop-app

  2. Anonymous says:

    I can connect to my db with SSMS (2008 R2) OK but with 2012 (Developer edition) I get the error –

    "A connection was successfully established with the server, but then an error occurred during the login process (provider:SSL Provider, error: 0 – The message received was unexpected or badly formatted.)"

    and then SS crashes. No problem setting up a DSN or using DSNless connections in an ACCDB. Any ideas?

  3. Albert D. Kallal says:

    Thanks for this post. About the only tip I can add is if one launches the ODBC manager from inside of Access by selecting the External Data tab on ribbon and then clicking on ODBC then Access will “always” launch the correct bit version of the ODBC manager to create the DSN.

    Best regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

  4. mike says:

    I try to make a BCS connection to this database in SharePoint online. I struggle a bit. Can you help me out?

  5. jerry says:

    Can you provide any information on how to access the SQL instance that is generated with the installation of an Access Web Application that is published and then consumed via the App Store? I have a published application and am unable to locate information
    on how to connect to the SQL instance that is populated via the Access Web App.

    Thank you in advance!

  6. Joana Villas-Boas says:

    Is it possible to change the username auto-generated by access to read/right access to the Database?
    Is it possible to connect a Azure Subscritpion to the SQL Database generated by Access?

  7. Stiven says:

    Anyone know how I can connect to my Access Web app data from SharePoint Online?

  8. Prakash says:

    Hi is there a way to access this Access Web APP using C# or REST API

  9. Edward Moore says:

    Has anyone received answers to their questions? I can repeat a few of them, but if no one received answers over this many months I don’t see the point.

    rather disappointing.

  10. Mike Anderson says:

    This doesn’t work for my SharePoint.com Access Web database. I’m using the ReadWrite data connections information from my DB as shown in the video, and it won’t work for me.

    HOWEVER, if I use "Report on my database" I can then use the linked tables, etc. to create reports. The problem with this solution, however, is that saving the file as an ACCDE and having my users use the database via Access Reader app (downloaded from MS)
    – my users get the error ‘ODBC–connection to SQL Server Native Client 11.0(some name).database.windows.net’ failed. You’d think that the self-generated connection (that works for ME across multiple computers, but nobody else) would also work in the ACCDE
    file. But no, I guess not.

  11. Hi Mike,

    When you choose the "Report on my Data" option external connections will also be enabled with the default options of "From My Location – Allow connections to the database from this location" and "Enable Read-Only Connection". If you want to share the reporting
    database, you would need to change the "From My Location" option to "From Any Location". These connection options are located just below the "Report on my Data" button within the Manage – Connections button.

Skip to main content