Search code examples
sql-serverjdbcconnection-stringaws-glue

SQL Server named instance connection string in AWS Glue

I researched for a while, but didn't find anything for this particular problem:

I'm trying to create a connection in AWS Glue with a named instance SQL Server (on premises)

I tried a lot of variations of connection string formats, but none worked. e.g.:

  • jdbc:sqlserver://HOST\INSTANCE_NAME;databaseName=DB_NAME - AWS console doesn't let me save, because it doesn't have a port (:port)
  • jdbc:sqlserver://HOST\INSTANCE_NAME:1433;databaseName=DB_NAME - AWS console lets me save, but it fails to connect because it escapes everything that comes after the backslash
  • jdbc:sqlserver://HOST\\INSTANCE_NAME:1433;databaseName=DB_NAME - Using two backslashes fixed the previous problem, but now AWS thinks that the port is part of the instance name, thus fails to connect
  • jdbc:sqlserver://;instanceName=INSTANCE_NAME;serverName=HOST:PORT;databaseName=DB_NAME - I really though that this would work, because AWS recognized that I'm trying to connect to a named instance, but it didn't understand the host:port (it thinks its a IPV6 address)

I tried a couple of more variations but you get the point.

I believe this is a particular issue on how AWS Glue breaks down the connection string internally

Any help on finding a working connection string that AWS Glue understands (with named instance) is extremely appreciated.

Solution

  • You can specify a port number instead of the instance name in the connection string.

    A named instance is usually configured to listen on a port other other than 1433 so you need to determine the actual port number the instance is listing on for the connection string specification. The port is listed in Server Configuration Manager under "SQL Server Network Configuration-->Protocols-->TCP/IP-->IP Addresses". The port number is also listed in the SQL Server error log "Server is listening on..." messages.

    Example connection string with only the non-default port number:

     jdbc:sqlserver://HOST:12345;databaseName=DB_NAME