none
Azure Firewall with SharePoint BCS Connection RRS feed

  • Question

  • Hi, i have created Azure PaaS SQL Database for global viewing. 

    I have then created a BCS Connection to SharePoint Online, however, when i try to create the external content type, it says my IP Address cannot access the server.  I can easily add in my IP address and the solution will work. 

    My question is, as users will be accessing this SharePoint list globally, what is the solution regarding bypassing all global users from the azure firewall ?

    Obviously, adding each users IP address into Azure SQL is not an solution. 

    Can anyone assist?

    Thanks. 

    Monday, March 25, 2019 9:23 AM

All replies

  • Hi, i have created Azure PaaS SQL Database for global viewing. 

    I have then created a BCS Connection to SharePoint Online, however, when i try to create the external content type, it says my IP Address cannot access the server.  I can easily add in my IP address and the solution will work. 

    My question is, as users will be accessing this SharePoint list globally, what is the solution regarding bypassing all global users from the azure firewall ?

    Obviously, adding each users IP address into Azure SQL is not an solution. 

    Can anyone assist?

    Thanks. 

    Good day 

    >> Obviously, adding each users IP address into Azure SQL is not an solution. 

    True, but you can (DO NOT DO IT!!! Can does not mean should) open the fire wall for all IPs,or for a range of IPs which fit your country for example. The Firewall rules allows to use range of IP and not only single IP

    Some options you have:

    1. Don't connect from the client directly but from the shared server/application, which mean you only need to open the Firewall IP of the server/application. This is how it should work usually. It is not the end client that connect the Azure Database but the application that you use. 

    Note! I am not familiar with Business Connectivity Services in SharePoint and you might want to get another opinion in the SharePoint forum, but this is how I understand that it is working. The connection is done from the SharePoint to the sql server andnot from the "end user"to the sql server.
    https://manojahuja95.wordpress.com/2014/05/22/sharepoint-2013-use-bcs-to-connect-with-sql-server/

    2. You can use Azure Virtual networks and configure free access from that Virtual networks in the firewall (under the IP rules in the Azure portal you have option to add Virtual networks)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Monday, March 25, 2019 9:48 AM
    Moderator
  • Hi, im not clear on your response. 

    Are we implying that if users want to connect to the data via SharePoint, they cannot do so from their own workstation?

    Thanks

    Wednesday, October 9, 2019 4:59 PM
  • Hi, i have created Azure PaaS SQL Database for global viewing. 

    I have then created a BCS Connection to SharePoint Online, however, when i try to create the external content type, it says my IP Address cannot access the server.  I can easily add in my IP address and the solution will work. 

    My question is, as users will be accessing this SharePoint list globally, what is the solution regarding bypassing all global users from the azure firewall ?

    Obviously, adding each users IP address into Azure SQL is not an solution. 

    Can anyone assist?

    Thanks. 

    Are you using Azure SQL Database or Azure SQL Managed Instance? Managed Instance defaults to a private service endpoint and is not reachable outside of the VNet except for the VNet where SharePoint is deployed. Please see: Deploy Azure SQL Managed Instance with SharePoint Servers 2016 and 2019

    In quickly ramping us on what BCS is though, it appears that BCS is a hub like service that allows access to external data sources. If this is the case then yes, each user will need to have their Public IP added to the Allow List for the Azure SQL Database, if they wish to access content that the BCS service has redirected the user to the specific data store to access directly.

    As Ronen has stated, you can add IP Ranges that are specific to your organization instead of adding a bunch of individual IPs. You can add an additional layer of security by adding Point-2-Point VPN between your on-premise gateway and your Azure deployed infrastructure. 

    Wednesday, October 9, 2019 10:27 PM
    Moderator
  • Wow...
    this was given to you about 6 months ago, and you only came to check the response now?!?

    The idea is that users connect to the SharePoint server and not to the database. The SharePoint application connect to the database. 


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Wednesday, October 9, 2019 10:27 PM
    Moderator
  • Hi, thanks for this. 

    Its an Azure SQL Database. I understand the Managed Instance wont be reachable outside the VNET. 

    So, adding the IP ranges into the Azure firewall is the preferred way to do it.

    Thanks for your help.  

    Thursday, October 10, 2019 7:44 AM
  • Better late than never ;)

    This is for SPO, not on prem. Your link you sent on your previous post is for on-prem. 

    Thursday, October 10, 2019 7:46 AM
  • Ronen - Yes, in a standard multi-tier architecture the web/application layer is isolated from the database layer and clients cannot connect to the database layer directly. In the case of SharePoint BCS, this traditional architecture is not exactly followed, where the client is redirected to the data source for direct access of the content. So, there needs to be some management of the Allow List of the Azure SQL Database.

    VStar19 - I am not seeing any documentation that indicates that SharePoint is fully supported with Azure SQL Database. Please seeHosting SharePoint Content Databases in SQL Azure

    I was trying to provide what scenario is supported. 

    Please let us know if you have additional questions.

    Mike

    Thursday, October 10, 2019 4:32 PM
    Moderator
  • Thanks for clarification 👍
    +5


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Thursday, October 10, 2019 4:59 PM
    Moderator