Use SQL Server Management Studio to Connect to DataBricks?

[Shawn_Eary]

The Notebook UI doesn't always provide the best experience for running exploratory SQL queries. Is there a way for me to use SQL Server Management Studio (SSMS) to connect to DataBricks? 

See Also:
https://learn.microsoft.com/en-us/answers/questions/740986/how-to-connect-from-ssms-or-azure-data-st...

1. I know I can use Visual Studio Code and Power BI, but I want to be able to use SSMS if that's possible.
2. There are a few lesser-known SQL Monitor tools that use JDBC to connect to DataBricks, but for various reasons some organizations may hesitate to approve employee use of such tools

[Kaniz]

Hi @Shawn_Eary, you cannot directly use SQL Server Management Studio (SSMS) to connect to Databricks. However, you can connect to an SQL Server database from Databricks using the SQL server format or the JDBC driver as provided in the Databricks documentation.

[-werners- · Accepted solution]

What you can do is define a SQL endpoint as a linked server.  Like that you can use SSMS and T-SQL.
However, it has some drawbacks (no/bad query pushdown, no caching).
Here is an excellent blog of Kyle Hale of databricks:

Tutorial: Create a Databricks SQL Linked Server in SQL Server | by Kyle Hale | Medium

Tutorial: Create a Databricks SQL External Data Source in SQL Server with PolyBase | by Kyle Hale | ...

If you are in the MS ecosystem, his blog is definitely worth checking out.

[Shawn_Eary]

That looks like a pretty good solution, but some organizations aren't going to give developers the permission to implement something like that.

Also, not only is it a pain to connect SQL Server Management Studio (SSMS) to Databricks, but right now, it seems to also be a pain to connect Microsoft Power Apps to Databricks. I'm not a fan of Microsoft Power Apps personally, but some organizations practically mandate use of Microsoft Power Apps.

Microsoft doesn't seem to be particularly willing to help with these incompatibility issues. I think they would rather push Fabric instead of promoting Azure Databricks.

If Databricks, Inc. isn't interested in improving compatibility with Microsoft tools, they may wind up losing customers. 

[-werners-]

I think MS wants to make a closed ecosystem for data.  If Databricks wants to go all-in on that it would mean dropping AWS/GCP. Bad decision IMO.