Cannot connect to SQL Server using Microsoft Excel Power Query

1
votes

I'm having trouble connecting to a SQL Server 2008 R2 data source using Excel 2013 Power Query.

I have created a new Power Query query, and created a new SQL Server datasource. I have specified the server name and database, but have not specified any specific query.

I am using SQL Server authentication to login to the database, and the connection is encrypted.

When I attempt to set up the connection in Excel Power Query, I get an error message: DataSource.Error:

No further error details are supplied.

I have checked that the SQL Server allows remote connections, and I have tested the SQL Login by using the sqlcmd utility to connect to the database server and database and run a basic SELECT query.

If anyone can shed any light on what I'm doing wrong, I'd be very grateful.

2
sql-serverexcelpowerquery

2 Answers

1
votes

For anyone reading this in 2017:

If you are using Windows 7 and above, use the driver listed in the ODBC Data Sources. The only case where I had to install a client and driver, was when I wanted to connect to an oracle database.

To setup and connect to SQL server, I opened ODBC Data Sources, selected 'Add', chose the SQL server option (not native client versions) and added the server and database details where prompted. Then test the connection from ODBC data sources.

My SQL server connections usually require full TNS info all the way from server name, then database name despite the claim that it's optional.

0
votes

Do you have the odbc driver?
I have run into this problem previously when I tried to connect remotely.