locked
Connection to Azure SQL Database via VPN Gateway and Private Endpoint RRS feed

  • Question

  • We are trying to access our azure sql database via VPN. Therefore we set up a private network, a vpn gateway an a private endpoint for our sql database service.

    We properly connected our windows client to the vpn gateway, the connection is established properly and the client has the IP 172.20.20.1 (like it is configured on the vpn gateway)

    But when we try to connect from our client software to the sql datebase with the following connection string, our request is not routed through the VPN-Gateway and as a result of that the azure firewall blocks our public ip address:

    Connection String: Server=tcp:{our_id}.privatelink.database.windows.net,1433;Initial Catalog={Databasename};Persist Security Info=False;User ID={username};Password={password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=True;Connection Timeout=30;

    Error: System.Data.SqlClient.SqlException: "Cannot open server requested by the login. Client with IP address '217.255.225.130' 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."

    When we try to do a DNS request with "nslookup {our_id}.privatelink.database.windows.net" it times out.

    Does anybody have an idea of what the problem could be?
    Wednesday, May 27, 2020 8:37 PM

All replies

  • Hi Hoebd,

    What is the route from your on-premise LAN to your Azure VPN Gateway? Are you using ExpressRoute or Site-to-Site/Point-to-Site VPN? You stated VPN so I am assuming Point-to-Site VPN and so, you should have the Azure VPN client running and that appears to be the case:

    We properly connected our windows client to the vpn gateway, the connection is established properly and the client has the IP 172.20.20.1 (like it is configured on the vpn gateway)

    The use of Private Link has a couple meanings but in the case of Azure, a Private Link is between two VNETs hosted in Azure. The set-up a private link (the second interpretation of using a private route using a private address space) from on-premise to Azure requires ExpressRoute or VPN (either P2S or S2S).  

    The following is intended to help you understand Azure Private Link for Azure SQL Database:

    How to set up Private Link for Azure SQL Database

    The DNS entry for the FQDN private route expression does not exist outside of Azure. You should only have to add Microsoft.SQL NSG to the VPN Gateway VNET, and add a route table to direct traffic to the private IP address for your Azure SQL Database service endpoint. You should also disable public service endpoint. The traffic is clearly going over the internet. Can you run: traceroute <private IP of Azure SQL Database>

    I think the issue is that you are not calling the correct endpoint for your Azure SQL (logical) Server but, the Private Link doc has a section outlining some testing methods from an Azure VM but you could adapt these for your VPN connected host. Please also see the limitations section as there are some relevant links. 

    Please let me know if you require additional assistance with this. I provided more information that you likely are requesting but, this is a very common ask given the Covid-19 situation and wanted to provide a comprehensive response so as to be useful by others. In your case, the FQDN host name you are using I believe is to be used by a Private Link within Azure. Happy to jump on a call and assist but the networking blade for your Azure SQL (logical) Service should provide you the correct FQDN host name.

    Additional information:

    Site-to-Site VPN involves a VPN appliance on-premise to establish a full-time VPN tunnel between you LAN and your Azure VPN Gateway.

    Point-to-Site VPN involves a specific VPN client that needs to be installed on each client needing access to Azure over a protected VPN tunnel to your Azure VPN Gateway.

    ExpressRoute is a dedicated private route that is terminated between your LAN and an Azure VNET and extends your on-premise private address space to your Azure deployed services. VPN is optional.

    Just a note: with this whole Covid-19 scenario and may resources working from home, there is a requirement to protect connectivity from each individual's home. This means the employee creates a VPN connection to the employer's on-premise data center and then requests to Azure hosted services are routed via the data center to Azure (Site-to-Site VPN or ExpressRoute). Or the employee leverages Point-to-Site VPN to connect directly with Azure from their home to access Azure hosted services.

    Friday, May 29, 2020 7:12 PM
  • Please let me know if you have additional questions or need specific help with your deployment.

    Regards,

    Mike

    Monday, June 1, 2020 7:43 PM