Remember also that the OLE DB Provider for SQL Server was un-deprecated in October 2017. The documentation for the Odbc.DataSource and OleDb.DataSource M functions has more detail on how these connectors can be used and how connection string properties can be set. For example here’s the UI for a new ODBC connection in SSDT:
![connection string sql server connection string sql server](https://www.mikesdotnetting.com/images/2014-04-26_00-06-55.png)
What are the alternatives then? Well you can use the OLE DB and ODBC connectors instead:īoth of these connectors do allow you to set your own connection string properties. While your average Power BI user is unlikely to even notice this, for SSAS Tabular developers it could be a big problem: complete control over the connection string is often necessary in enterprise BI scenarios. It turns out that the restriction on using your own connection string properties in the built-in SQL Server connector is a deliberate design decision on the part of the Power Query team because, behind the scenes, they use different providers in different circumstances to optimise performance, and because allowing arbitrary connection string properties might make maintaining backwards compatibility difficult in the future. …and which are documented in the Sql.Databases() M function here.
![connection string sql server connection string sql server](https://s33046.pcdn.co/wp-content/uploads/2021/09/perfmon-and-connection-pool.png)
It may not be immediately obvious, but you cannot set your own connection string properties when connecting to SQL Server using the built-in SQL Server connector from either Power BI or a modern data source in Azure SSAS/SSAS Tabular 2017:Īll you can do is configure the options that are available in the UI, which in the current version of SSDT looks like this: