Troubleshooting SQL (either MSSS/Azure Data Studio/Workbench) Connection to azure sql databases

It almost certain that you would hit a road block while trying to connect to your sql tools to azure databases. The common error you will encounter is a firewall issue when trying to connect to your Azure SQL Database. Here’s how to resolve this:

Error Message:

Cannot connect to SQL Database. Please contact SQL server team for further support. Server: 'dullaserver.database.windows.net', Database: 'dulladatabase', User: ''. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access.

Cannot open server 'dullaserver' requested by the login. Client with IP address '51.104.24.186' is not allowed to access the server.  To enable access, use the 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.

 
This may happen if your data source only allows secured connections. If that's the case, please use a VNet integration runtime.

The Problem

Your Integration Runtime (IP: 51.104.24.186) is being blocked by the SQL Database firewall.

Solutions

1. Add Firewall Rule for Integration Runtime IP

1. Go to the Azure portal
2. Navigate to your SQL Server resource (“dullaserver”)
3. Under “Security”, select “Networking”
4. Add a firewall rule to allow the IP address 51.104.24.186
5. Make sure “Allow Azure services and resources to access this server” is enabled

2. Use Private Endpoint (More Secure)

For production environments, consider:

1. Setting up a private endpoint for your SQL Database
2. Using a VNet-integrated Integration Runtime
3. This avoids exposing your database to public internet

3. Alternative Solutions

• Managed Identity Authentication: Configure your linked service to use ADF’s managed identity instead of SQL authentication
• Azure Private Link: For fully private connectivity
• Self-hosted IR: If your data sources are on-premises or in private networks

4. Alternative Solutions

• Navigaate into your database in Azure
• open a new query
• copy and past this code and replace [dullaADF] with your connection name

Important Notes

• Firewall changes may take up to 5 minutes to take effect
• For production environments, avoid using public IP-based firewall rules long-term
• Consider reviewing your security requirements to determine the most appropriate connectivity method