Splunk DB Connect: Why am I getting error "no schema found" after selecting my database in the Catalog field?

[chustar]

I am configuring a new Splunk instance to reference data stored in our MSSQL database.
I've configured Splunk with DB Connect v2 and it seems to be able to connect to our DB successfully.
I then tried to access the DB using the web interface. After selecting my database in the Catalog field, it says "no schema found". What is this Schema field?

[flakshack]

I got this error message and thought I would post my solution here in case it helps someone else.

It turns out that I had this problem, even when using an account I had configured as sysadmin on the server. For example, I could connect to the SQL server with SQL Server Management Studio with this account and could see the database and all the tables. However, when I connected with the same account in DB Connect\SQL Explorer, it would show all of the databases in the Catalog combo box, but I would get an error message for the Schema combo box, "Cannot get schemas."

I turned on DB Connect logging and would see the following error in the log file:

ERROR c.s.d.s.a.s.d.impl.DatabaseMetadataServiceImpl - Unable to get schemas metadata
com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name '[my_database].sys.schemas'.

I turned out that this problem was because I was using the MS SQL Server JDBC driver version 7. When setting it up, I figured I would ignore Splunk's guidance to use version 4.1 or 4.2 and use the latest available version. Turns out this was a bad idea.

In my testing if I copy the version 4.2 driver into the folder and restart, everything works great. Then if I copy the version 7 driver into the folder and restart, I get this error message (without any changes to permissions or anything).

Anyway, hope this helps someone.

[mikebanks]

I was having the same issue and your solution did the trick.

Thanks!

[ellanjie]

Thank you for this trick it helps me to avoid having that error. I did not ignore Splunk guidance to use the version it proposed.

I appreciate!

[chio37]

IMPORTANT
I am having the same problem "Cannot get schemas". I think this problem is related to permissions but i am not sure

[chio37]

IMPORTANT
Can someone help me to solve the problem "Cannot get schemas"?. I thing that this problem is related to permissions, but i am not sure

[Shyngys_Bolatbe]

Do you solve this problem?

[jay1077]

Don't use mysql as default DB while configuring the connection instead make a new db and new user in DB.

[harsmarvania57 · Accepted solution]

In DB, there are schemas available and each schema have different table. So in DB you need to provide schema access & table access to user from which splunk is trying to fetch data.

[chustar]

hi @harsmarvania57, your comment helped me find an answer! thanks

[saichandjawari]

Hi @harsmarvania57 .. May I know where exactly to edit permissions to get rid of the "schema error". Thanks!

[harsmarvania57]

Permissions on the Database side, talk with your Database Administrator.

[n00badmin]

Hey @chustar, can you elaborate on what you did?

I am facing the same issue..im using a different db...but perhaps it can set me on the right track

[chustar]

I didn't know that SQLServer has a difference between Databases and Schemas. I had to go into the server, find the user that splunk is using to sign in and give them permission to read the schema.

[harsmarvania57]

@chusar: Thanks, can you please accept my comment as an answer.

[chustar]

I don't think i can accept comments. Can you make it an answer instead?

[ppablo]

taken care of 🙂 cheers!

[harsmarvania57]

@ppablo_splunk Thanks

[chustar]

thanks @ppablo_splunk. the new title is so much better!

[ppablo]

No problem @chustar, I hope you find an answer soon!