Does Power BI support Microsoft OLE DB provider for ODBC for data access?

Power BI supports only the .NET Framework OLE DB Provider for ODBC and does not support the Micrsoft OLE DB Provider for ODBC (MSDASQL).

If you create a Data Link file such as this one (ExcelNov.udl)  I created from an EXCEL file:

Provider=MSDASQL.1;Persist Security Info=False; Data Source=Excel64Test;Initial Catalog=C:\USERS\OWNER\DESKTOP\BLOG2017\MSSS2017\suiggy.xlsx

You cannot connect to EXCEL file using the Connection string such as the above. First of all, it does not support the attribute, Persist Security Info=false. Even if you remove this attribute, you will not be able to connect.

Review the following screen shots attempting to connect to EXCEL file using its Data Link, ExcelNov.udl.

Accessing the OLDDB option in GetData.

Using the connection string:

Removed the Security Information and trying to connect.

The ExcelNov.udl file is OK and the connection is successful but not for Power BI.

How do you connect to SQL Server 2016 using OLE DB?

You would use OLE DB connectivity. You could use a file reference in your programming step to connect using the connection string.

OLE DB and ODBC connectivity are useful connectivity options that needs no emphasizing.

This post describes that you can connect to SQL Server 2016 in just a few steps.

Step 1:
Launch SQL Server Instance if it has not started, using Control Panel or otherwise.

Step 2: Create a new text document from desktop. Right click desktop  (empty region) and click New | Text Document.

Rename the document. If it is New Text Document.txt change it to, for example, SQLServer2016.udl (as in the present case).

You may get a warning that changing the file name extension may become unusable. Click Yes.
The document gets saved to the desktop with the name and extension you provided. The icon of the document will also be changed.

Step 3: Configuring the connection

Right click the somename.udl you created to display the following:

DataLink_00

The file properties window has four tabs, Provider, Connection, Advanced and ALL.

In the Provider tabbed page you need to enter the Server Name or use the Refresh button to find all the regsitered servers as shown.

DataLink_02

You have two options in connecting to a SQL Server instancce, Use Windows NT Integrated Security or use Specific Username and password.

The SQL Server Instance OHANA is configured for Windows Login. Click the first option. You need not provide username/password (they will be greyed out).

Select the database on the server using the drop-down as shown. Choose AdvenutreWorks2015.

DataLink_03

If the SQL Server has not started you may get these warnings when you try to browse for the SQL Server.

DataLink_02

After choosing the Server and the database you are basically done. You can test the connection by clicking the button and you would get this reply.

DataLink_04

If you open the *.udl file (herein SQLServer2016.udl ) in a text editor such as Notepad you will see the following:

============

[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=AdventureWorks2014;Data Source=HODENTEK8\OHANA

========