By using AWS re:Post, you agree to the AWS re:Post Terms of Use
AWS re:Postre:Post

AWS Glue – Data connections – SQL server InstanceName ignored in JDBC URL

0

Hello All, Thanks for reading my question !

Does anybody know what is the correct correct JDBC syntax to connect AWS GLUE to and SQL Server instance (on premise, so outside of RDS) ?

In the following AWS GLUE Data connection JDBC URL, the instancename “\INSTANCENAME” is ignored : jdbc:sqlserver://192.168.0.1**\INSTANCENAME**:1433/DBNAME

In the connexion string above :

  • Server IP Address is : 192.168.0.1
  • SQL Server instanceName is : INSTANCENAME
  • Connexion port is : 1433
  • Database name is : DBNAME

**HINTS : **

  • Athena's Lambda function is able to connect to the same server using the following JDBC connection String : sqlserver ://jdbc:sqlserver://192.168.0.1;instanceName=INSTANCENAME;databaseName=DBNAME;encrypt=false
  • I'm able to connect to the same server using another user on the same SQL Server’s default (blank) instance, even adding \INSTANCENAME to the JDBC URL. This is how I was able to determine that “\INSTANCENAME” is ignored, but also, that there is no network issue.
  • The Cloudwatch logs says that the Login / password / URL are false, because user AWS_Athena doesn’t exist on default (blank) instance, only on instance INSTANCENAME.

Attempted & failed solutions :

  • Gonzalo Herreros EXPERT
    10 months ago

    In principle there is nothing special about Glue, in principle that should be passed directly to the MSSQL JDBC driver. Have you testing using the driver directly using the JDBC API?

  • AndreLIU
    10 months ago

    Hi Gonzalo, yes I have tried to connect to the JDBC driver, downloaded on Microsoft's website, and placed on S3. But no success.

  • Gonzalo Herreros EXPERT
    10 months ago

    That's not exactly what I mean, I mean trying to use that JDBC url directly on the driver to make sure nothing is mixed up by the Glue connection and it's purely a MSSQL issue, e.g. java.sql.DriverManager.getConnection(connectionUrl) (prepending spark._jvm. if calling it from Python instead of scala)

  • AndreLIU
    10 months ago

    It's not an MSSQL issue Gonzalo. This is confirmed by the fact that I am able to connect with JDBC to the same DB instancename using Lambda located on the same VPC + Subnet + SG.

  • Gonzalo Herreros EXPERT
    10 months ago

    Then it looks like the url or configuration is getting messed before reaching the driver. Maybe try to pass a "url" with the JDBC url to the connection options, in theory it should override the one from the connector.

Topics
DatabaseAnalytics
Tags
Microsoft SQL ServerAWS Glue
Language
English
AndreLIU
asked a year ago124 views
No Answers

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions

Relevant content