Need help from Knime and/or db experts.
I am not even sure I understand what our problem exactly is, so I will try to describe everything that seems relevant to the case.
Good thing: I have configured a Databese reader node in Knime to work with the microsoft SQL server via integrated sequrity. It perfectly connects and reads data from one of the databases(DB1) on the server.
Here is the string I use:
Strange thing 1: Now when collegue(with different windows account) wants to use the same node with the same settings, he is able to connect to something, but it is not DB1. "Fetch metadata" button shows some strange set of tables like "spt_fallback_db", "spt_fallback_dev"...
Strange thing 2: When I change the database URL to give it a different database name (DB2) which I have no acces to, KNIME does not show me an error, instead continue to connect to DB1 and fetch data from there.
The same if the name is not existing or absent like:
Unfortunately I do not have access to any other DBs on the server, so can not check if it actually will connect me to different one if acces is present. Can it be that something is wrong in my configuration, but i am just lucky to get some defaults from the server?
Any hints on what to try next?
is there hint in the log files? Did you increase the logging level to DEBUG to see whether you can get more insights into what is happening?
These are both strange behaviors. The first one seems to be related to the DB having been automatically moved to a different server and then bounced back leaving some fallback tables around.
The second issue seems caching related, but it is difficult to tell without some extra tests.
What I would do in your shoes is to use an opensource Java-based SQL client (like http://squirrel-sql.sourceforge.net/) and troubleshoot your connection (and the one from your colleague) through it, then switch back to KNIME once you have figured it all out.
You can troubleshoot directly in KNIME too, but given the strange nature of your problems it may be easier to do it this way.
Thanks for reply Marco!
I have the logging on the debug level, but it seems to bring no light to the situation.
For example for the second strange case (no DB name) . Operations as usual it seems:
5:1 : 2016-06-29 09:09:45,358 : DEBUG : KNIME-WFM-Parent-Notifier : Database driver com.microsoft.sqlserver.jdbc.SQLServerDriver retrieved from driver map
2016-06-29 09:09:45,359 : DEBUG : pool-3-thread-6 : DatabaseConnectionSettings : Database Reader : 5:1 : Opening database connection to "jdbc:sqlserver:/serverUrl:1433;/databaseName=;integratedSecurity=true;encrypt=false;loginTimeout=30;"...
2016-06-29 09:09:45,712 : DEBUG : KNIME-Worker-27 : DBReaderImpl : Database Reader : 5:1 : Executing SQL statement as executeQuery: SELECT top 10 * FROM myTable
2016-06-29 09:09:45,723 : DEBUG : KNIME-Worker-27 : DBReaderImpl : Database Reader : 5:1 : Reading meta data from database ResultSet...
2016-06-29 09:09:45,739 : DEBUG : KNIME-Worker-27 : DBReaderImpl : Database Reader : 5:1 : Parsing database ResultSet...
For the first strange case(different user) log looks similar, just after "executing SQL statement" goes error telling that "myTable" is not a valid objectname
I tried with the Squirrel client, but was not able to connect (driver is not configured for integrated sequrity it said, and it does not accept my username/password directly), I will try to configure it when i have more time...
(just to add - i have no problem connecting with sql server management studio)
I believe there is a default DB associated to your login, but a different one (or none at all) for your colleague. That would explain why you can always connect, even if no DB is indicated, while he cannot connect. I would check all your respective credentials and default DB association.
Open Sql Server Management Studio. Go to object Explorer -> Security -> Logins. Right click on the login and select properties. And in the properties window change the default database and click OK
That is what I suspected, the default database! Super, thank you for the hint where to set it. It helped and workflow works now.
It solves the second mistery with the abcent database name, but not exacly explains why did not it work when there is no default but we give the exact database name in the connection string. Any ideas what can be wrong with it?
Updated: Oh, I just changed the default database for my account, to see how it will influence the connectivity and now I can not connect to the DB in the Studio anymore or change the default DB back. Need to go to the support now I guess.
But what is interesting though - KNIME continues to work!
I believe something is wrong with the way the access rights are set, including for the single databases. Have your sysadmin look into them, I am sure he/she can fix the issue.
If only I had here any sysadmin to talk to. Problem is, with all infrastructural issues one have to go the official way with submiting tickets etc. Than somebody form differnt timezone will look in to it. May be. In some days. Or weeks? And I am afraid they would not bother with the fact that some third party software connects only to dafault DB and not to the selected one. So one has more chance to learn and do things himself than wait for a sysadmin.
Bet nevermind! I solved the mistery! :)
The connection string was just wrong.
Somehow I had this slash symbol after the port (...1433;/databaseName....) which I guess was the reason why server compleetly ignored databaseName parameter. With this corrected now I can select the database name with no problem.
Thanks for being with me in this journey :)
Well, glad you spotted and fixed it. It is true that sometimes we are the source of our own problems! ;-)