If you just want to export all the tables in a SQL database in to separate CSV files, then this is a quick and easy way of doing it.
- Execute below query which generates BCP commands
SELECT ‘bcp ‘ + st.NAME + ‘ out c:\Target\’ + st.NAME + ‘.csv -c -r -d ‘ + DB_NAME() + ‘ -U user@??????.database.windows.net -S tcp:?????.database.windows.net -P ?????? FROM sys.tables st
- Paste the result set into text file. Make batch file and schedule it. (It can also be run in CMD manually)
I did some quick research on the SQL Server Firewall “Allow Access to Azure Services” option in Azure today.
And I sorry to say that my fears were right, that by setting this option does pose a significant security risk and leaves the SQL Server vulnerable.
Here is the extract of the article I found from Gaurav Hind at Microsoft
Access within Azure: This can be toggled by “Allow access to Azure services” Yes/No button on the portal (Firewall settings page). Please note, enabling this feature would allow any traffic from resources/services hosted in Azure (not just your Azure subscription) to access the database.
The big question now is how do you plug this gap in the firewall? One possible solution is to build a virtual network within Azure or Filter network traffic with network security groups, this is beyond the scope of this article.