Java - Adding SQL Azure Firewall rules programmatically

Question

I have a SQL Database in Microsoft Azure.I want to access this SQL database from a java application in eclipse. The Azure portal has a provision for adding client IP address to firewall settings of Azure SQL Database.Only these IP addresses are allowed to access the database.And now I am stuck in coding to allow access to a given ip address programmatically . Can anyone help?

This is the Exception i am getting :

com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open server 'pluginserver' requested by the login. Client with IP address '105.241.8.90' is not allowed to access the server. ClientConnectionId:7c605cae-5bb6-452e-a606-243b1fab304f

While researching i got code to do the same in c#.But i want the code in java.

public void AddFirewallRule(FirewallRule rule)
    {
        using (SqlConnection conn = new SqlConnection(this.ConnectionString))
        using (SqlCommand cmd = conn.CreateCommand())
        {
            conn.Open();
            cmd.CommandText = "sp_set_firewall_rule";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = rule.Name;
            cmd.Parameters.Add("@start_ip_address", SqlDbType.VarChar).Value = rule.startIPAddress.ToString();
            cmd.Parameters.Add("@end_ip_address", SqlDbType.VarChar).Value = rule.endIPAdress.ToString();
            cmd.ExecuteNonQuery();
        }
    }

Related: Client with IP address is not allowed to access the server Azure SQL db — kenorb
– kenorb, Commented Jul 31, 2019 at 9:10

Peter Pan · Accepted Answer · 2016-02-22 08:38:49Z

1

Per my exprience, the exception means that it need to firstly open server socket via TCP/IP to login before doing some actions, but the firewall didn't allow your IP address to access. That's contradict with each other.

The issue was caused by using JDBC driver in Java to create a firewall rule for Azure SQL Database. The Java/JDBC specification not includes this feature.

On Azure, you can only create or update a firewall rule in Java via two ways: REST API or Azure SDK for Java (not via JDBC for MS-SQLServer).

You can refer to the offical doc https://azure.microsoft.com/en-us/documentation/articles/sql-database-configure-firewall-settings-rest/ to know how to configure the firewall for SQL Database.

For creating a firewall rule, you can try to using the Class HttpClient of Apache HttpComponent or the Class HTTPConnection of JDK to request the REST API Create or Update Firewall Rule.

Or you can try to using the Interface FirewallRuleOperations got by the Interface SqlManagementClient, please see the package com.microsoft.azure.managemant.sql in Javadocs of Azure SDK for Java.

For using Azure SDK for Java, you need to add the maven repositories below into your Eclipse porject.

<dependency>
    <groupId>com.microsoft.azure</groupId>
    <artifactId>azure-svc-mgmt</artifactId>
    <version>0.9.2</version>
</dependency>
<dependency>
    <groupId>com.microsoft.azure</groupId>
    <artifactId>azure-svc-mgmt-sql</artifactId>
    <version>0.9.2</version>
</dependency>

answered Feb 22, 2016 at 8:38

Peter Pan

24.2k4 gold badges31 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Simranjit Kaur Over a year ago

Thanks a lot for your help..Peter Pan.I have corrected the exception successfully.

Collectives™ on Stack Overflow

Java - Adding SQL Azure Firewall rules programmatically

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related