If you are trying to connect to Azure SQL database and getting the following error message when connecting using SQL management studio and getting the status code = 5029
“status code = 5029, Failed to refresh data source: xxx due to the following error: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot open server ‘xxx’ requested by the login. Client with IP address ‘x.x.x.x’ 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.
Unable to connect to the Microsoft SQL Server server “xxx.database.windows.net”. Check that the server is running and that you have access privileges to the requested database.”
This is due to the firewall that is turned on and is not allowing a certain source IP – the process of adding an IP is very simple and the below steps will walk you thru adding the IP in the Azure portal
log into the Azure portal by going to the following link “https://portal.azure.com”Â
Once you are logged in – click on the “SQL databases” on the left pane and it will take you all the online databases that are in your tenant
This will show all the database links that you have configured on the subscriptions – click on the database that is rejecting the connection
This will take you to the next overview screen and you will see “Set server firewall” on the top middle portion – clicking on it will take you to the IP address portion of it where you will be able to add the new IP address
You now see the firewall settings where you can add the client IP’s
- Â Add the rule name like Home Network
- Add the start IP and the end IP and if it is a single IP then you can add the same in both columns
This is best practice to secure your databases in the cloud by source IP so you know where the traffic is coming from
I hope this helps if you ever run into this situation