CODEWITHSUNDEEP
sql-serverazure
Emmanuel Paul
Emmanuel Paul

Reputation: 509

Client with IP address is not allowed to access the server Azure SQL database

I am facing the below error when I try to connect to the Azure SQL database of my free trial account. Can anyone help me how to set rules in my free trial account? I don't see any option to set them.

Cannot open server 'ccaausprddb001' requested by the login. Client with
IP address '125.16.236.150' 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. 
ClientConnectionId:f1ce6d56-c321-4458-a8dc-507f40e24108
com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open server 'ccaausprddb001' requested by the login. Client with IP address '125.16.236.150' 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.

Upvotes: 50

Views: 108004

Answers (10)

ITsDEv
ITsDEv

Reputation: 51

You should enable the "Allow Azure services and resources to access this server" when you access sqlserver from azure resources like azure app service , azure funtion etc.. enter image description here enter image description here

Upvotes: 2

gαηєѕнα к.ρ
gαηєѕнα к.ρ

Reputation: 11

Go to your azure sql server -> Security tab -> Firewalls and virtual networks -> Add Client Ip.

This will automatically add your current IP and resolve the issue

Upvotes: 1

Vasanthakumar Jagannathan
Vasanthakumar Jagannathan

Reputation: 863

Error: Client with IP address is not allowed to access the server Azure SQL database

I had this issue while connecting AZURE SQL Database,

Go to Firewall settings -> Click Add Client IP.

Above will add the current IP to in the list, now you should be able to login and query.

Upvotes: -1

Swinkaran
Swinkaran

Reputation: 1255

This can be solved by configuring the Firewall of the Azure SQL Server where the SQL database resides. I am using the new Microsoft Azure Portal. Please see the screenshot below, Selecting the SQL server from the listed resources Remember, you need to configure the Firewall and manage the IP addresses in SQL Server, not the SQL database.

Once you have selected the SQL Server,

  • Click the Settings -> Firewall
  • You will be requested Add a new Rule.
  • Add a name and enter the IP address(or the range) that will access the database. note: Azure automatically detects your current IP.

Follow these steps

Wait for 5 minutes and you are ready to go. I hope this helps.

Upvotes: 53

Deepti RamDas
Deepti RamDas

Reputation: 51

I followed similar steps commented by everyone which helped me a lot, still i was unable to connect. I found issue was with my IP address as it was not detecting properly after setting many times in Add IP rule, provided my machine IP by typing ipconfig in cmd prompt but no luck.

Tried using public IP via link - https://www.whatismyip.com/ it resolved the above issue.

Upvotes: 4

Arulmouzhi
Arulmouzhi

Reputation: 2254

ADD and EDIT Client IP Address in Azure SQL DB

enter image description here

we will see how to Add, Edit (and Delete) Client IP Addresses in Microsoft Azure Portal step by step for Azure SQL DB.

What is Azure Firewall?

Azure Firewall Services are managed cloud-based network security services that protects our Azure Virtual Network resources. It’s a supports high built-in availability and gives unrestricted cloud scalability.

enter image description here

To Add and Edit Client IP Address in Azure Portal

STEP 1-

If we connect SSMS (SQL Server Management Studio) in Azure SQL Db at work from home or outside the access-able range, the below popup would come after entering all credentials correctly. This is for security strengthen purposes and protects our server from unauthorized accesses.

enter image description here

STEP 2-

we have to connect with our Microsoft authorized account and password needs to be entered. This step is also for protecting our servers from unauthorized accesses.

enter image description here

STEP 3-

If we are already given with Azure Firewall access, then we can Add our Client IP (Internet protocol) Address here itself. Or else, we have to follow the below other steps in Azure Portal.

enter image description here

STEP 4-

Go to Google and Type ‘portal.azure.com‘ like below.

enter image description here

STEP 5-

In the Azure Portal, type and choose SQL databases.

enter image description here

STEP 6-

In the below step, Click the DB Name(DB name is hyperlinked here in this page).

enter image description here

STEP 7

In this Page, Server,DB,Subscription,Resource related details would show up. Click the Server Name (hyperlinked) like below.

enter image description here

STEP 8-

In this Portal page, Click the Firewalls and Virtual Networks (hyperlinked)

enter image description here

STEP 9-

In this Firewalls and Virtual Networks page, We can Add,Edit and Delete Client IP Address.

enter image description here

Like below,

1–> We can give new name in Rule name and have to fill Start IP and End IP. If we have Single IP, then paste them in both Start and End IP Places.

2–> We can Also Edit the Existing Rule Name and Start/End IP’s.

enter image description here

STEP 10-

We can also Delete any Existing Client IP Address (3 dots at the end – Click it, Delete Option Would Come like below).

enter image description here

Upvotes: 6

Nitish Kumar
Nitish Kumar

Reputation: 11

Working Solution: If IP is something like : 40.333.225.43

In my case I am using mysql.

So go to your mysqldatabase > Connection security

and set same ip in Firewall rules (StartIP and EndIP) and Save.

Upvotes: 1

Appu K
Appu K

Reputation: 1

This works not for all the issues as stated:
Alternatively you need to perform as suggested below:

Run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range.

Upvotes: -1

Chamila Maddumage
Chamila Maddumage

Reputation: 3866

You need to set up server-level firewall rules for your server in the Azure portal. Go to the database that you have created by clicking SQL databases in left hand menu and select set server firewall as following figure.

enter image description here

Under the firewall settings click add client IP and save it. It will automatically add your ID address and a server-level firewall rule is created for your current IP address.

enter image description here

Now you can login to your azure SQL server database using SQL server management studio.

Upvotes: 24

Milen
Milen

Reputation: 8867

Using portal.azure.com (recommended), select your sql server (hosting your database) and click Show firewall settings. Select Add client IP and press save.

Using manage.windowsazure.com, select SQL Databases from the list on the left side. Then Servers tab, from there go to the server hosting your database. Go to Configure Tab. Select Add to the allowed ip addresses arrow against your current client ip address.

Upvotes: 20

Related Questions