Securely Connect to Azure SQL Server from On-Prem Network via VPN Without Client IP Whitelisting RRS feed

  • Question

  • We are trying to setup Azure SQL Server access from On-Prem Network without the need to whitelist Client IPs. 

    We have added SQL Service Endpoint enabled Vnet to vNet/Subnet in the Azure SQL Server Firewall. On that particular Vnet we are connecting using a Point to Site VPN Tunnel from the client machine successfully in our on-prem network. However when we try to connect to that Azure SQL Server using SSMS from the client machine it is still asking us to whitelist the client IP, which we are trying to avoid. What are we missing? Please suggest.

    Tuesday, April 2, 2019 11:09 PM

All replies

  • We were trying to achieve this using VNet Service endpoints but looks like that doesn't extend to On-Prem Network.

    Is there any other way to achive the Azure SQL Server connectivity from On-Prem without IP whitelisting?

    Tuesday, April 2, 2019 11:14 PM
  • Which specific Azure SQL database service are you working with? Azure SQL Database (single instance or elastic pools) or Managed Instance? 

    I am assuming you are working with a single database deployment. 

    Use virtual network service endpoints and rules for database servers

    Yes, this service is a firewall service to allow VNet communication and further, restrict that communication to specific IPs. 

    "Virtual network rules are one firewall security feature that controls whether the database server for your single databases and elastic pool in Azure SQL Database or for your databases in SQL Data Warehouse accepts communications that are sent from particular subnets in virtual networks."

    If you wish to tunnel network connectivity from on-premise to the VNet service endpoint, you can use Site-to-Site VPN to accomplish this.

    The following if helpful to understand VNet functionality: What is Azure Virtual Network?

    Azure SQL Database Managed Instance is another Azure SQL PaaS offering but it is deployed with in VNet with no public service endpoints. It is all private address space. Any firewall settings are routing related and the configuration is part of extending your on-premise (private) address space to the Azure SQL Managed Instance. You can use Site-to-Site VPN to tunnel connectivity between on-premise and Azure VNet. You can also implement Azure ExpressRoute if you wish to implement a dedicated route for higher bandwidth scenarios.

    Azure SQL Database Single Instance/Elastic Pools do have public service endpoints and thus, the rules need to be established to restrict access and secure data via firewall ACL of public IPs.

    To answer your question, without a VNet and private addressing between on-premise and Azure SQL you will need to whitelist public IPs.

    Wednesday, April 3, 2019 8:07 PM
  • Thanks Mike! Yes we are using Azure SQL Database and not the Azure SQL MI.

    Just to confirm Site-to-Site VPN will allow me to avoid setting up Client IP whitelisting from On-prem connections to Azure SQL? I did try setting up Point to Site VPN connection got access to the VNet(Setup with SQL VNet Endpoints & Integrations with Azure SQL) from my client machine(On-Prem) but couldn't connect to Azure SQL Database without Client IP Whitelisting.

    I later on found out that the VPN Service Endpoints don't really extend to on-prem networks, hence defeating my purpose of setting up Point to Site VPN.

    So Site-to-Site VPN will help me in this case where Point-to-Site VPN couldn't?

    Thursday, April 4, 2019 3:08 PM
  • Correct! Site-to-Site is likely the solution here, as you are connecting an entire address space versus a single connection.
    Thursday, April 4, 2019 6:08 PM
  • Thanks Mike!! I will try and look into it.
    Thursday, April 4, 2019 9:54 PM