none
How to restrict internet on sql server and only allow to take backup to azure storage. RRS feed

  • Question

  • Hello,

    Is there any way to take backup to azure storage from on premises sql server with out opening to whole internet.

    I mean can i restrict connectivity to azure storage only for my sql server.

    when i search on web i am finding only the ways to take backup to azure storage from on premises server.

    Friday, August 9, 2019 10:08 AM

Answers

  • Ronen, sp_set_firewall_rule seems to be for Azure SQL Database only, and Phaendra is asking about an on-premise instance where sp_set_firewall_rule is not available (or applicable).

    I have not worked with backing up to Azure storage myself, but I don't see why you would need to open anything in the in-coming firewall at all. I assume that it is SQL Server that opens the connection to Azure storage, and not the other way round.

    If you have an out-going firewall, you may need to configure that firewall. If you need help with that, you should ask in a forum for that product. (Windows Firewall, to my knowing, is an in-coming firewall only.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Hi,

    >> Phaendra is asking about an on-premise instance 

    The question was not about "restrict on-premises server" but "restrict connectivity to azure storage", but I did a mistake as well...

    I did a mistake, since I was thinking about Azure Database automatically and the question is about azure storage😃

    I actually backing up to Azure storage myself from several virtual machines, and I think that this is great solution for some cases. I found that this does not fit for on-premises in our cases since the network latency is too big, but in technically (the procedure to backup) using SQL Server on VM is the same as from on-premises.

    Back to the question....

    Working with Azure Storage is done using a key and by default, storage accounts accept connections from clients on any network.

    In order to enable firewall and rules for specific IP in Azure Storage using the portal you should:

    1. Go to the storage account

    2. On the left menu click on "Firewalls and virtual networks"

    3. Change the default and select the option "Selected networks"

    4. Once you did it, you will get options to "Add IP ranges to allow access from the internet or your on-premises networks."

    In addition you have other rules you can add there


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


    Sunday, August 11, 2019 10:21 AM
    Moderator

All replies

  • Hello,

    Is there any way to take backup to azure storage from on premises sql server with out opening to whole internet.

    I mean can i restrict connectivity to azure storage only for my sql server.

    when i search on web i am finding only the ways to take backup to azure storage from on premises server.

    Good day Phaneendra,

    You should (almost) NEVER open the server to the "whole internet"!

    You should only open the firewall to your remote client specific IP.

    * Note! You can configure the server firewall with Transact SQL queries or by navigating to the server in the portal

    Using queries can be done only from client which can connect the server:

    -- Create server-level firewall setting for only IP 0.0.0.2  
    exec sp_set_firewall_rule N'Example setting 1', '0.0.0.2', '0.0.0.2';    
    -- Update server-level firewall setting to create a range of allowed IP addresses
    exec sp_set_firewall_rule N'Example setting 1', '0.0.0.2', '0.0.0.4';  
    -- Remove database-level firewall setting  
    EXECUTE sp_delete_firewall_rule N'Example setting 1';


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

    Sunday, August 11, 2019 3:23 AM
    Moderator
  • Ronen, sp_set_firewall_rule seems to be for Azure SQL Database only, and Phaendra is asking about an on-premise instance where sp_set_firewall_rule is not available (or applicable).

    I have not worked with backing up to Azure storage myself, but I don't see why you would need to open anything in the in-coming firewall at all. I assume that it is SQL Server that opens the connection to Azure storage, and not the other way round.

    If you have an out-going firewall, you may need to configure that firewall. If you need help with that, you should ask in a forum for that product. (Windows Firewall, to my knowing, is an in-coming firewall only.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, August 11, 2019 9:32 AM
    Moderator
  • Ronen, sp_set_firewall_rule seems to be for Azure SQL Database only, and Phaendra is asking about an on-premise instance where sp_set_firewall_rule is not available (or applicable).

    I have not worked with backing up to Azure storage myself, but I don't see why you would need to open anything in the in-coming firewall at all. I assume that it is SQL Server that opens the connection to Azure storage, and not the other way round.

    If you have an out-going firewall, you may need to configure that firewall. If you need help with that, you should ask in a forum for that product. (Windows Firewall, to my knowing, is an in-coming firewall only.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Hi,

    >> Phaendra is asking about an on-premise instance 

    The question was not about "restrict on-premises server" but "restrict connectivity to azure storage", but I did a mistake as well...

    I did a mistake, since I was thinking about Azure Database automatically and the question is about azure storage😃

    I actually backing up to Azure storage myself from several virtual machines, and I think that this is great solution for some cases. I found that this does not fit for on-premises in our cases since the network latency is too big, but in technically (the procedure to backup) using SQL Server on VM is the same as from on-premises.

    Back to the question....

    Working with Azure Storage is done using a key and by default, storage accounts accept connections from clients on any network.

    In order to enable firewall and rules for specific IP in Azure Storage using the portal you should:

    1. Go to the storage account

    2. On the left menu click on "Firewalls and virtual networks"

    3. Change the default and select the option "Selected networks"

    4. Once you did it, you will get options to "Add IP ranges to allow access from the internet or your on-premises networks."

    In addition you have other rules you can add there


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


    Sunday, August 11, 2019 10:21 AM
    Moderator