All Apps and Add-ons

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

serpin
Explorer

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

Tags (2)
1 Solution

ywu_splunk
Splunk Employee
Splunk Employee

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.

View solution in original post

ywu_splunk
Splunk Employee
Splunk Employee

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
Explorer

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

0 Karma

skylasam_splunk
Splunk Employee
Splunk Employee

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

serpin
Explorer

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...