How to fix access to sql server?

Question

My Error:

Cannot open server 'test-sqlserver' requested by the login. Client with IP address 'x.xx.xx.xx' 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.

I've added my IP tried to do all of them and it still doesn't let me connect. I would love expert help.

I thank you in advance.

Answer 1

@Erland Sommarskog I had to just delete the entire server and start from scratch and that worked. Thank you for you helpful comments. :)

Answer 2

Hi @James R

It sounds like you're dealing with some frustrating access issues when trying to connect to your SQL Server. The error you're seeing indicates that your IP address isn't currently allowed to access the server. Here are some steps you can try to resolve this issue:

Verify Firewall Settings:

• Go to the Azure Portal and select your SQL Database.
  • Navigate to the Networking section under Settings.
    • Make sure that your current IP address is listed in the allowed IP addresses. If needed, use the Add client IP button to ensure your current IP is included.
    Check the "Allow Azure Services and Resources to Access This Server" Option:

    - In the same **Networking** section, ensure the **Allow Azure services and resources to access this server** option is enabled. This setting allows other Azure services to connect to your SQL Database, which might be necessary depending on how you're connecting.
    
    **Dynamic IP Address Consideration**:
    
       - If your IP address is dynamic, it might change every time you connect to the internet. You can check your current IP address using a service like `https://whatismyipaddress.com` and compare it with what’s allowed in your SQL Database firewall. If your IP has changed, you might need to update the firewall rules again.
    
       **Test Connectivity**:
    
          - You can use PowerShell to test the connection with the following command (replace `{your-sql-server}` with your server name):
    
          ```powershell
          Test-NetConnection -ComputerName {your-sql-server}.database.windows.net -Port 1433
          ```
    
             - This will help you determine if there are any network-level issues preventing connection.
    
             **Enable Public Network Access** (if applicable):
    
                - If you prefer to keep public network access enabled, ensure that it is set to **Selected networks** in the firewall settings and that your IP is added.
    
                **Managed Virtual Network**:
    
                   - If you want to maintain strict firewall rules, consider using a Managed Virtual Network with private endpoints, as this allows secure connections without exposing your database to public network traffic.
    

If you've tried these steps and are still unable to connect, here are a few follow-up questions that can help diagnose the issue further:

• Are you using SQL Server Management Studio, Azure Data Studio, or another method to connect?
• Is your IP address static or dynamic?
• Have you checked if there are any network policies in your environment that might restrict outbound connections?
• Did you make any recent changes to your Azure environment, such as modifying firewall settings or changing server configurations?

I hope this helps! Let me know if you have any further questions or need more assistance.

References:

• IP firewall rules - Azure SQL Database and Azure Synapse Analytics
• Troubleshooting Connection Issues to Azure SQL Database
• Understanding and Configuring Allow Azure Services and Resources to Access This Server in Azure SQL Database