Cannot open server requested by the login.

Applies to: Azure SQL Database (DTU model).

Date created: June 30, 2020.
 

Problem Description.
 

All of a sudden I found on an Azure Automation account that one of the jobs is failing. That job is in charge of creating a backup as bacpac of a production Azure SQL database (DTU-model). When I examined the job log, the following exception was found:

Exception:
The running command stopped because the preference variable "ErrorActionPreference" or common parameter is set to Stop: 0: There was an error that occurred during this operation : '<string xmlns="http://schemas.microsoft.com/2003/10/Serialization/">Error encountered during the service operation. ; Exception Microsoft.SqlServer.Management.Dac.Services.ServiceException:Unable to authenticate request; Inner exception System.Data.SqlClient.SqlException:Cannot open server 'databasename' requested by the login. Client with IP address 39;168.62.48.129; 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.;

Cause.

Azure Automation requires the Azure SQL Database firewall to have the setting "Allow Azure services and resources to access this server" set to on, but in this database the setting was changed to off.





 

Solution.

Set the setting "Allow Azure services and resources to access this server" to on the firewall settings of the database and the job will start to run successfully again.






 

.Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.