Workspace Catalog Connection to 'on-prem' MS SQL Server

[MoodyDirk]

Hi, first post/question here ...

I'm trying to add a MS SQL Connection to my workspace (us-west-2) Catalog. Connection type is SQL Server, Host equal the public IP of that Server, port is the default 1433. (Server is a AWS EC2 instance)

All attempts to connect fail with the generic 'TCP?IP connection to the host XXXX, port failed ...'.

Portquery confirms that the Server is listening on the public IP and port. 

I'm pretty sure that the problem is that the workspace's public IP is not 'whitelisted' (EC2 Inbound Security rule) for the SQL Server. I've added all Databricks outbound IPs I could find, but no success.

How can I find my workspaces' outbound IP address?

Thank you for any feedback!

Dirk

[nayan_wylde · Accepted solution]

Databricks publishes outbound IP ranges per region. For  us-west-2 see the Databricks IP addresses and domains page – it lists the CIDR blocks used for outbound traffic from the control plane and clusters. You need to whitelist these ranges in your SQL Server security group

[BS_THE_ANALYST]

@MoodyDirk also, if you haven't checked out the docs for AWS x Databricks x SQL Server:
https://docs.databricks.com/aws/en/ingestion/lakeflow-connect/sql-server-source-setup 

All the best,
BS

[szymon_dybczak]

Hi @MoodyDirk ,

If you whitelist all IPs  used in us-west-2 from the page mentioned by @nayan_wylde  it should work. 

But the proper way from security perspective would be to deploy databricks workspace into customer managed vpc and provide stable outbound IP address (for instance using NAT Gateway).

That way you need to only add single IP address and that address is stable and managed by you. 

Databricks can change addresses they are using at any time.

[MoodyDirk]

Thank You to all responses.

I've added 44.234.192.32/28 and 52.27.216.188/32 as a (for now) All traffic inbound rule.

But no success ...

I'll keep looking and trying.

We're currently evaluating with a Serverless Cloud workspace, but at the end might go the AWS VPC route, installing our own AWS Databricks cluster.