Access Web App (2013) – Client with IP address is not allowed access to the server

When you create an Access Web App in SharePoint Online, in the back-end a SQL Azure database is created.
From your Access Client, you can enable Read-Only or Read-Write connections to that SQL Azure database.
For this, you need to go to the File tab in the Access client and click on Manage and click “Enable Read-Only Connection” (3rd option):
Manage Data Connections

To get the connection details (server name, user name and password) you must click on “View Read-Only Connection Information”, which shows you exactly what you need to connect form (e.g.) SQL Server Management Studio (SSMS):
SQL Server Connection Information

So far, so good. But then the issues start, as I’ve noticed all around the internet.
When using this information in SSMS, we get the following error:
Connect to Server
“Client with IP address ‘xxxxxxx’ is not allowed to access the server. To enable access, …”

This is not an unusual Azure error, I’ve seen similar errors like this before and they are all caused by the Azure firewall being enabled (which is a good thing, mind you).
As this is an “auto-hosted” app, the instance is managed by Microsoft and you cannot log on to it using the Azure portal.
What most forgot however, is that there are extra things to tick in the Access client:
Modify Access Settings

Notice the “From My Location” and “From Any Location” menu items. Check whichever applies to you and retry SSMS. You may need to give it some time (with me, a few seconds do the trick).
Now, I can connect to the Azure DB from SSMS:
Object Explorer

As mentioned by Raj in the comments, make sure you enter both server name and database name when trying to connect to the Azure database. We are talking about auto-hosted databases in Azure SQL Databases, provisioned by Microsoft. The (virtual cloud) server is provisioned by Microsoft and you have NO control over it (except its contents & structure from within Access). Hence it makes some kind of sense that you cannot connect to the server and just select any database. You must specify exactly which database you want to connect to.

I hope this helps anyone!

Advertisements

26 thoughts on “Access Web App (2013) – Client with IP address is not allowed access to the server

  1. just a note: you MUST enter the database name in the Connection Properties tab (appears when you hit the “Options>>” button in the lower right hand side of the Connect to Server window.

  2. Thank you so much, SabbeRubbish!
    I was at the point of giving up completely when I finally saw this. Even Microsoft Support failed me and were referring me to their complicated Business Connectivity Services (BCS) to make a simple database connection. I knew that was wrong. May you live forever!

  3. Thanks… this saved me working out how to do this. There are a lot of customers with Office 365 who don’t have an Azure subscription!!

    • Hi Chris,

      Thanks for your comment. To use Access Web Apps you don’t need an Azure subscription. The Azure subscription is silently included in your Office 365 subscription. You cannot modify anything in this (shared) subscription however.

  4. Question: Can you point me to a discussion/overview of when and how an Access 2013 database with links to SQL tables refreshes data? These read only databases are great. But I wish I understood the refresh/sync protocol. For example, lets say I have a database with 150GB of data that has additional data appended once a week. Will the linked Read Only Access DBs automatically refresh when opened or do we need to do a manual refresh? When it does a refresh does it completed re-update or use some last modify logic to just update changes?

    Later,

    Chris M.

  5. Hi Chris,

    I think you misunderstood the idea of Access Web Apps. These web apps are not readonly connections to existing data on Azure or anything. They are just simple relational databases with user-friendly forms. You CAN import data from SQL/Access/Excel into it, but it will not refresh data from the original source. Instead, it will copy that data into the Azure database and read from there on.

  6. Many thanks for this useful information!

    I have a project that involves a standard desktop Access solution, and a web app with a couple of data entry pages, both connecting to the same SQL database. Initially I created a SQL database in Azure and hooked up my desktop application, before I realised that you can’t hook the web app into an existing database, but rather it creates its own ‘behind the scenes’.

    Once I worked that out I recreated the Azure database via the app creation/import process, and then had to find it and connect to it so I can use the desktop stuff, which is where this post came in handy – I was having trouble working out why it wasn’t appearing as a database in my SQL Azure subscription!

    I will probably blog this when I get some time, so I hope you won’t mind if I link people to this page?

    This may help out Chris above?

    Cheers
    David

    • Thank you for your comment, David!
      I’m happy you found some answers, however, this post is not about Access Desktop apps being published to SQL databases, but rather apps in SharePoint that use the Access brand to store data directly inside auto-hosted SQL Azure databases.

      • Indeed, but it was the auto-hosted bit that I was unclear on. I originally assumed that you could create a database, and then use it as the source for your web app – that’s where I went wrong, and I expect that a lot of people used to having control of the whole process will find the same issue! MS have made it nice and easy to create something, but to use it a little more flexibly (ie with a desktop or external application as well) is not so intuitive.

        Maybe I should have read the manual first! 😉

    • Hi Martin,

      Thank you for your questions.
      1. The best way to export it is to connect to the underlying SQL Azure database using SQL Server Management Studio. Given that you’ve allowed your IP access like I describe in this article, you can just connect using the read-only user and credentials in SSMS.
      2. I don’t know, but I would expect those service levels to be comparable to basic SQL Azure plans. Like with most customers, I would however strongly advise to have your own back-up solution on top of Microsoft’s SLA’s. Don’t forget that backuping up the schema and data is *not* the entire AWA. For that you also need to backup the views, macro’s (if any), and other configuration options. I haven’t found a good way of automating this yet however.

      • Thanks Sebastian.
        A bit more about 1 and 2 above.

        1. Connecting to SSMS works fine though many functions seems to be strictly limited. Could not export Bacpacks files nor run any profiler nor analyse!? Our app loads initialy very slowly. OnStart macro is called after 20 seconds. With normal azure db there are tolls to analyse, rebuild indexes, set flags and more. But here I have no clue how to analyse bad loading time or why some queries work slow? This might thought be a subject for news thread …?
        2. Saving the app manualy from Access client sucks as doing this manualy for our customers takes time. Have u at least managed to back up schema and data in SSMS or other tool than Access? And imported back to AWA db on Azure after? We did not manage it to do in anyhow…

  7. Hi Martin,

    1. I would expect that you could programmatically read the database structure. However you will indeed not be able to restore it, as you don’t have any ALTER permissions on the database. So, still, best way to backup (even though tedious) is through Access.
    2. As in 1., don’t count on backup/restore of the schema. You would however do this with the data, but that would require some ETL process or custom script to do that. You have CRUD permissions as the Write-user, so you should be able to get that going. I’m no expert on ETL though. Our customers that are using Access Web Apps typically choose to do regular backups manually as they cannot justify the extra cost of ETL or other development.

    • Thanks. This access web app could be such a great tool for more advanced users if they opened up more control for modifications on Azure. Would be great for building complex projects, copy those localy in order to modify further. Also possibility to switch to sql scripting mode to modify/adapt queries forelated only graphical mode leaves you wonder what is done under the hood… Will write feedback to Microsoft. 🙂

  8. Hi,

    How is everyone backing up their database? I can only do it using the MS Access application itself. Not very handy for doing automatic backups. Any thoughts?

    • Hi Craig,

      There’s a lot of confusion and uncertainty surrounding this topic.
      Basically, you’ll have to stick with the basic SLA’s that your on prem or Microsoft (Online) provide.
      But this is of course far from ideal, I understand that.

      I’m bringing out a blog post soon about this topic, hang on, but don’t expect too much new information, just a good overview of about 4 ways to back up the Access web apps.

  9. Thanks for this post. I setup my Access App with access to MY location only by mistake. This post helped me realize why i was getting “Cannot open server ___________ requested by the login. Client with IP address ___________ is not allowed access in Visual Studio. Appreciate it!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s