How to use ODBC driver to connect to Splunk from MS PowerPivot

[serpin]

I am able to fetch Splunk data into Excel, following the ODBC App documentation.
Now I need to do the same but into a PowerPivot (running as a plugin inside excel), but I couldn't make it work:

On PowerPivot I choose:

Get External Data >>
From other Sources >>
Others (OLEDB/ODBC) >>
Build... >>
Provider = MS OLE DB Provider for ODBC Sources >>
Datasource name = Splunk ODBC >>
set User and password >>
Test Connection ...>> Test Connection Succeeded>>
OK >>
(connection String = "Provider=MSDASQL.1;Persist Security Info=False;User ID=pinhesm2;DSN=Splunk ODBC)"
Next >>
Error: "Failed to connect to the server. Reason: ERROR [HY000] [Splunk]ODBC Not enough information provided to establish a connection to the data source and specified to not prompt for more information.
ERROR [HY000] [Splunk]ODBC Not enough information provided to establish a connection to the data source and specified to not prompt for more information."

Do you know if its possible and how to set it up?

Thanks

[ywu_splunk · Accepted solution]

It looks like that no password is provided in the connecting string. Did you have password saved with the ODBC DSN? If not, could you try to save the password with the DSN? You can do that with ODBC administrator. There's a shortcut for it in the Splunk ODBC Driver program group.

What happened might be that the application (PowerPivot) using a "no prompt" ODBC connection option, thus, the driver has no way to get the password.

[serpin]

Thanks, that was it.

For the reference, it's necessary to change the procedure to:

Get External Data >> From other Sources >> Others (OLEDB/ODBC) >> Build...
Provider >> Provider = MS OLE DB Provider for ODBC Sources >> Conection >> Use Connection String >> Build ...
Machine Datasource Name = Splunk ODBC >> set User and password >> OK ...
Set username and Password again >> Check Allow saving Password >> OK

[skylasam_splunk]

What version of Excel and PowerPivot are you using? Are you able to retrieve data just using Excel alone?

[serpin]

I'm using versions
- Microsoft Excel 2010
- Microsoft SQL Server 2012 version of PowerPivot

Yes, I can extract data via ODBC using Excel alone

Thanks