Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot open server 'azurserver' requested by the login

0
votes

I am connecting to Azure from SQL Server.

Task: Exporting Azure SQL Database data using bcp from SQL Server 2008 R2.

Step 1: Added IP Address in firewall settings in Azure.

Step 2: Running following script from SQL Server 2008 R2:

EXEC sp_configure N'show advanced options', 1
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure N'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE;

DECLARE @SQL NVARCHAR(MAX) = ''

SET @SQL = N'Execute xp_cmdshell ''bcp "SELECT * FROM Table;" queryout "D:\AzureTableData.txt" -c -t@_@ -S azure.database.windows.net -d DatabaseName -U [email protected] -P *****"''';

NOTE: IP address is not static at my system and because of this reason, I keep getting an error:

Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot open server 'azurserver' requested by the login. Client with IP address 'XXX.XXX.XXX.XX' 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.

I can't configure firewall settings every time at Azure Portal.

1
sql-serverazuresql-server-2008-r2

1 Answers

1
votes

You mean that Add client IP doesn't work for you, do you?

If you want to connect to the Azure SQL database for one time, you can set the firewall IP range from 0.0.0.0 to 255.255.255.255. enter image description here

But for security reasons, we don't suggest to do that. Azure SQL database could not set the dynamic IP firewall for now.

Hope this helps.