Unable to connect Azure SQL Server as connection for AWS Glue

Question

Im trying to connect Azure SQL server as source for AWS Glue jobs.

During preview, I am faced with error for the IP not being able to access the server. Noticed that the Client IP of AWS Glue keeps changing which is difficult to know which range of IP to use when setting up the Azure SQL Server network allow list. Any recommended solutions? ![Enter image description here](/media/postImages/original/IMjY2xGYFuTZ6eMs41l_yb3w)

error:

```
Py4JJavaError - An error occurred while calling o101.getSource. : com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open server 'azuresqlservertest2024' requested by the login. Client with IP address '3.239.124.77' is not allowed to access the server. To enable access, use the Azure Management Portal or run sp_set_firewall_rule on the mast*r 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:316c74e9-309b-4010-83d7-6045d8d5fc87 at com.microsoft.sqlserver.jdbc.SQLServerException.makeFr*mD*baseError(SQLServerException.java:265) at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(tdsparser.java:283) at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:129) at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:37) at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:5505) at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:4256) at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$000(SQLServerConnection.java:88) at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:4194) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7417) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3455) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:2945) at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:2595) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:2438) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1462) at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:915) at org.apache.spark.sql.execution.datasources.jdbc.connection.BasicConnectionProvider.getConnection(BasicConnectionProvider.scala:49) at org.apache.spark.sql.execution.datasources.jdbc.connection.ConnectionProviderBase.create(ConnectionProvider.scala:102) at org.apache.spark.sql.jdbc.JdbcDialect.$anonfun$createConnectionFactory$1(JdbcDialects.scala:122) at org.apache.spark.sql.jdbc.JdbcDialect.$anonfun$createConnectionFactory$1$adapted(JdbcDialects.scala:118) at org.apache.spark.sql.execution.datasources.jdbc.JDBCRDD$.getQueryOutputSchema(JDBCRDD.scala:63) at org.apache.spark.sql.execution.datasources.jdbc.JDBCRDD$.resolveTable(JDBCRDD.scala:58) at org.apache.spark.sql.execution.datasources.jdbc.JDBCRelation$.getSchema(JDBCRelation.scala:242) at org.apache.spark.sql.execution.datasources.jdbc.JdbcRelationProvider.createRelation(JdbcRelationProvider.scala:37) at org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:350) at org.apache.spark.sql.DataFrameReader.loadV1Source(DataFrameReader.scala:228) at org.apache.spark.sql.DataFrameReader.$anonfun$load$2(DataFrameReader.scala:210) at scala.Option.getOrElse(Option.scala:189) at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:210) at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:171) at com.amazonaws.services.glue.marketplace.connector.CustomDataSourceFactory$.loadSparkDataSource(CustomDataSourceFactory.scala:115) at com.amazonaws.services.glue.marketplace.connector.CustomDataSourceFactory$.loadDataSource(CustomDataSourceFactory.scala:40) at com.amazonaws.services.glue.GlueContext.getCustomSource(GlueContext.scala:239) at com.amazonaws.services.glue.GlueContext.getSourceInternal(GlueContext.scala:1063) at com.amazonaws.services.glue.GlueContext.getSource(GlueContext.scala:860) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at py4j.reflection.MethodInvoker.invoke(MethodInvoker.java:244) at py4j.reflection.ReflectionEngine.invoke(ReflectionEngine.java:357) at py4j.Gateway.invoke(Gateway.java:282) at py4j.commands.AbstractCommand.invokeMethod(AbstractCommand.java:132) at py4j.commands.CallCommand.execute(CallCommand.java:79) at py4j.GatewayConnection.run(GatewayConnection.java:238) at java.lang.Thread.run(Thread.java:750)
```

re:Post Agent · Answer

The issue you're experiencing with connecting Azure SQL Server as a source for AWS Glue jobs is related to network access restrictions. The error message indicates that the client IP address (in this case, an AWS Glue IP) is not allowed to access the Azure SQL Server.

To resolve this issue, you have a few options:

1. VPC Endpoint: Instead of relying on public IP addresses, you can set up a VPC endpoint for AWS Glue to establish a private connection between your VPC and Azure SQL Server. This approach provides a more secure and stable connection.

2. IP Range Allowlist: If using a VPC endpoint is not feasible, you can try to determine the IP range used by AWS Glue in your region and add that range to the Azure SQL Server's firewall rules. However, as you've noticed, this can be challenging due to the dynamic nature of AWS Glue's IP addresses.

3. Azure Service Endpoint: On the Azure side, you can configure a service endpoint for Azure SQL that allows secure access from your AWS VPC. This would require setting up Azure ExpressRoute or a VPN connection between AWS and Azure.

4. Temporary Firewall Rule: As a temporary solution for testing, you could open the Azure SQL Server firewall to allow all IP addresses. However, this is not recommended for production use due to security risks.

5. Azure SQL Managed Instance: If your setup allows, consider using Azure SQL Managed Instance instead of Azure SQL Server. It provides more flexible networking options and can be easier to integrate with AWS services.

Before implementing any of these solutions, ensure that your AWS Glue job's IAM role has the necessary permissions to access the Azure SQL connection details stored in AWS Secrets Manager.

Remember to review and adjust your network security groups and firewall rules on both AWS and Azure sides to allow the necessary traffic while maintaining security best practices.  
**Sources**  
[Troubleshooting connection issues in AWS Glue - AWS Glue](https://docs.aws.amazon.com/glue/latest/dg/troubleshooting-connection.html)  
[Azure SQL connections - AWS Glue](https://docs.aws.amazon.com/glue/latest/dg/aws-glue-programming-etl-connect-azuresql-home.html)  
[Creating a Azure SQL connection - AWS Glue](https://docs.aws.amazon.com/glue/latest/dg/creating-azuresql-connection.html)  
[AWS Glue connection properties - AWS Glue](https://docs.aws.amazon.com/glue/latest/dg/connection-properties.html)

Gary Mclean · Answer

If your not using site to site VPN between AWS and azure and using public IP address as you are in your screen shot, then connect your Glue job to your VPC and use a NAT gateway. You will have an elastic IP associated which will never change.

White list the NAT gateway public IP. If you have multiple AZs and NAT gateways, white list each NAT gateway.

Didier Durand · Answer

Hi,

I think that this article gives you the solution you're after: assigning a fixed public IP address to your Glue client so that you can easily allowlist it on the Azure side.

See https://repost.aws/knowledge-center/glue-route-etl-traffic-static-ip

Best,

Didier

Unable to connect Azure SQL Server as connection for AWS Glue

Add your answer

Relevant content