How to connect to a local instance of SQL Server

[MGAutomation]

How can I connect my Databricks AWS account to a local instance of SQL Server?

[szymon_dybczak]

Hi @MGAutomation ,

If by local instance you mean SQL Server deployed on-premises or in your local PC then i's possible but you need to establish network connectivity.
So you would have to deploy your databricks workspace into customer-managed vpc and then establish  a connection between your SQL server and the Databricks virtual private cloud (VPC) using VPN or AWS Direct Connect.

The hardest part will be networking here. Once you establish connection then you can just use jdbc or odbc driver to read/write data from SQL Server

[Isi]

Hello @MGAutomation  @szymon_dybczak 

You may also need to open the firewall of your on-premises SQL Server to the CIDR range of your Databricks VPC. This ensures that the EC2 instances used by Databricks have valid IPs that can reach your database.

If what you are looking for is mainly to query or analyze data, and you already have connectivity through another tool or VPC such as AWS DMS or Airbyte (whether for testing or in a corporate environment), the easiest approach would be to export directly with those extractors into S3 and then read it from there.

Keep in mind that you could also use Lakehouse Federation to create on-premise connections by configuring a federated catalog pointing to your SQL Server. For this you’ll need a Serverless or Pro SQL Warehouse if you’re using a SQL endpoint. This option will give you greater visibility and a more convenient way of working compared to a raw JDBC call, provided you have the right database permissions. Just be aware of the limitations that come with federation. Docs 

Hope this helps 🙂

Isi