This article guides ,
How to Resolve, Cannot open server ‘testdbserver’ requested by the login for Azure SQL Database or Resolve Client with IP address ” is not allowed to access the Azure SQL server.
What is Azure SQL Database?
Azure SQL database is pass(platform as Service) offering from Microsoft. You can use Azure SQL database once database is created you can directly use database for application using connection string as per type of application like java, ASP etc.
All Azure SQL Databases managed by Microsoft like Backup, database availability, Service Pack update etc.
|
How to connect Azure SQL Database using SSMS?
To connect Azure SQL database , you need SQL server name where Azure SQL database hosted along with system admin user we used to create Azure SQL server. Azure SQL Server name look like testdbserver.database.windows.net
Why we get below error while connecting to Azure SQL Database.
Cannot open server ‘testdbserver’ requested by the login for Azure SQL Database or Resolve Client with IP address ” is not allowed to access the Azure SQL server.
Whenever we create new SQL database by default access to SQL server is disabled on network. If we enabled public access Azure SQL Database will be accessible from everywhere. But this is not recommended as this is exposed to internet and it can be accessed by anyone which leads to attack on data in database easily.
Best practice to setup connectivity for Azure SQL Database.
Best practice is to setup Azure SQL Database accessibility is to create different firewall rules or create virtual networks and create all those resources where Azure SQL Database access required in same virtual network.
Option 1 : You can setup firewall rule for client machine using Azure Portal.
Go to Azure SQL Server. Select Security tab and in that select Networking. In that select Firewall rules and add respective Client IP or IP ranges as rule. Once you add IP Azure SQL database will be accessible from that client machine.
Option 2 : You can also run sp_set_firewall_rule on master database to create firewall rule.
-- Create server-level firewall setting for only IP 198.168.1.1
exec sp_set_firewall_rule N'Example setting 1', 198.168.1.1', '198.168.1.1';
-- Update server-level firewall setting to create a range of allowed IP addresses
exec sp_set_firewall_rule N'Example setting 1', 'IP 198.168.1.0', 'IP 198.168.1.255';