none
Exposing Azure SQL Server through VPN Connection RRS feed

  • Question

  • Hello,

    I'm trying to open up connections to my Azure SQL server to anyone connected to a P2S VPN. 

    I've followed this article to set up a P2S VPN:
    https://docs.microsoft.com/en-us/azure/vpn-gateway/vpn-gateway-howto-point-to-site-resource-manager-portal

    I created a self signed cert following the instructions in Powershell. I installed the client cert locally, and uploaded the root certificate. Doc here:
    https://docs.microsoft.com/en-us/azure/vpn-gateway/point-to-site-vpn-client-configuration-azure-cert

    Following that I set up a firewall rule on my SQL Server to allow connections from a VNET, following these docs:
    https://docs.microsoft.com/en-us/azure/sql-database/sql-database-vnet-service-endpoint-rule-overview

    I downloaded the VPN client from the VNet Gateway I created and connected to the VPN. I verified I was connected with ipconfig.

    After this, my client machine still does not have access to the SQL Server. SSMS tells me 
    "Your client IP address does not have access to the server. Sign in to an Azure account and create a new firewall rule to enable access."
    Going through this will add a whitelisted IP for my client machine to Azure itself, but this is what I'd like to avoid. I need to expose a connection to this database to someone with a dynamic IP who will not have an Azure account. 

    Trying to connect through other means gives similar firewall errors. 

    I've tried simply turning off my local firewall to see if that was the issue but it hasn't changed any of the results. 

    Are there any common pitfalls to setting up something like this? I'm fairly new to both networking and Azure so any help is appreciated!

    Thanks,

    Tim

    Wednesday, April 18, 2018 4:45 PM

Answers

  • Hi Tim,

    Currently, Virtual network service endpoints, in your case, with Azure SQL, do not work across S2S or P2S VPN connections. There are two issues here - 

    1. Azure P2S VPN connections are split tunneled - the access to the Azure SQL (PaaS) service will be going through the Internet, not the P2S VPN tunnel if you want to access the Azure SQL PaaS service
    2. Even if you forced tunnel the traffic over the P2S connection, it would still not work as Azure VPN gateway currently does not support forward proxy or source NAT'ing functionality. So the access to another Azure PaaS service via the Azure VPN gateway will be dropped

    Thanks,

    Yushun [MSFT]

    • Marked as answer by tfrigge Monday, April 30, 2018 9:24 PM
    Monday, April 23, 2018 8:59 PM

All replies

  • Hi Tim,

    Currently, Virtual network service endpoints, in your case, with Azure SQL, do not work across S2S or P2S VPN connections. There are two issues here - 

    1. Azure P2S VPN connections are split tunneled - the access to the Azure SQL (PaaS) service will be going through the Internet, not the P2S VPN tunnel if you want to access the Azure SQL PaaS service
    2. Even if you forced tunnel the traffic over the P2S connection, it would still not work as Azure VPN gateway currently does not support forward proxy or source NAT'ing functionality. So the access to another Azure PaaS service via the Azure VPN gateway will be dropped

    Thanks,

    Yushun [MSFT]

    • Marked as answer by tfrigge Monday, April 30, 2018 9:24 PM
    Monday, April 23, 2018 8:59 PM
  • Thanks for the reply, that makes sense now.

    Do you know of any way I could then expose the SQL Server to the outside world besides whitelisting an IP? I have a client who will have a dynamic IP. 

    Thanks again,

    Tim

    Tuesday, April 24, 2018 2:39 PM
  • Initially, all Transact-SQL access to your Azure SQL server is blocked by the firewall. To begin using your Azure SQL server, you must specify one or more server-level firewall rules that enable access to your Azure SQL server. Use the firewall rules to specify which IP address ranges from the Internet are allowed, and whether Azure applications can attempt to connect to your Azure SQL server.

    For more details and step by step guide refer to this link.

    ------------------------------------------------------------------------------------------------------------

    If this answer was helpful, click “Mark as Answer” or Up-Vote. To provide additional feedback on your forum experience, click here

    • Proposed as answer by Zahid Faroq Monday, April 30, 2018 3:22 AM
    Monday, April 30, 2018 3:22 AM
  • Hi All,

    at this moment, aug 2018, is it still the case ?  To resume the solution to the 1st question (same problem as mine), do we need a static IP to access SQL Azure ?

    In the SQL Resource, when we add a subnet in the firewal rules, its' on only for accessing from Azure, not outside with a vpn that uses this subnet ?

    SQL Azure is only reachable thru subnets from Azure (a VM for exemple), not from a outside Azure (thru a vpn connexion) ?

    Thanks in advance to confirm it, may be a second time but I think it's not clear...

    Dominique

    Friday, August 3, 2018 5:04 AM
  • @DDIPINTO, To selectively grant access to just one of the databases in your Azure SQL server, you must create a database-level rule for the required database. Specify an IP address range for the database firewall rule that is beyond the IP address range specified in the server-level firewall rule and ensure that the IP address of the client falls in the range specified in the database-level rule.

    Refer to this link for more details on Azure SQL Database firewall rules

    Saturday, August 4, 2018 7:01 PM
  • Just checking in if you have had a chance to see our previous response. If this answers your query, do click “Mark as Answer” and Up-Vote for the same. And, if you have any further query do let us know.

    Monday, August 6, 2018 8:06 PM
  • Hello, sorry for my late response but I had other works.  I have tried and tried again but I'm still not sure that what I want is possible : using a p2s connexion to access my Azure Sql Server, with my wan Ip dynamic.

    If I refer to a previous answer and the page https://docs.microsoft.com/en-us/azure/sql-database/sql-database-firewall-configure and the text

    • Dynamic IP address: If you have an Internet connection with dynamic IP addressing and you are having trouble getting through the firewall, you could try one of the following solutions:

      • Ask your Internet Service Provider (ISP) for the IP address range assigned to your client computers that access the Azure SQL Database server, and then add the IP address range as a firewall rule.
      • Get static IP addressing instead for your client computers, and then add the IP addresses as firewall rules.

    I could think that I can't.  But when I read the pages on the services endpoints like https://docs.microsoft.com/en-us/azure/sql-database/sql-database-vnet-service-endpoint-rule-overview?toc=%2fazure%2fvirtual-network%2ftoc.json it seems that yes.

    I have a virtual netwotk, a subnet Frontend linked to an endpoint sql,  this subnet is added in the firewall rules of the sqlserver,  I have added the ips of my subnets, I have a gateway, a Network Security group with rules with the service tag sql and port 1433...  I have a p2s connexion that seems to work ... but I still can't have access to my database with the sql server management studio.  Always same error : my wan ip must be added to the firewall rules. Message :

    Cannot connect to laboxxxxxxx.database.windows.net.

    ------------------------------
    ADDITIONAL INFORMATION:

    Cannot open server 'laboxxxxxxx' requested by the login. Client with IP address '80.90.xx.xxx' 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. (Microsoft SQL Server, Error: 40615)

    Thanks for your attention. 

    Monday, August 13, 2018 12:18 PM
  • There are some limitations while using Azure SQL with Azure networking suggesting you check this link and make sure that the required configuration resides in current service limits. 

    Thursday, August 16, 2018 6:29 PM