Connection ERROR to SQL Server - Login failed

KNIME Analytics Platform
1

Hi,

I am running KNIME 2.10.3 and I downloaded the drivers; sqljdbc and sqljdbc4, and tried to connect to SQL database as:

jdbc:sqlserver://SERVER_NAME:1433;databaseName=DATABASE_NAME 

But I get an ERROR:

ERROR     Database Reader                    Execute failed: com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'USER_ID'. ClientConnectionId:de200380-72db-4b79-8ff2-747fcdc26993

 

I am trying to put a business case for KNIME but cannot get even a connection going.

Your suggestion would be most appreciated. 

I read somewhere about Microsoft Integrated Security, how can you test for this?

=============

Let me add that for me to connect from SAS to the SQL database via ODBC, I used the Win NT Network ID authentication, which means that I  don't have to type a password.

so with this in mind, I tried 

 

jdbc:sqlserver://SERVER_NAME:1433;databaseName=DATABASE_NAME;

integratedSecurity=true

But now I get an ERROR:

ERROR     Database Table Connector           Execute failed: com.microsoft.sqlserver.jdbc.SQLServerException: This driver is not configured for integrated authentication. ClientConnectionId:ed9fd232-8782-4ddd-a704-a793b1e72bf3

Now I did put the sqljdbc_auth.dll in the same folder as the sqljdbc drivers.

When I did this, I did not put or tick the user_id or password crendentials.

Has anyone had this problem before?

 

BTW, this is using SQL Server 2012

 

 

 

 

2

Does the sqljdbc_auth.dll has the same architecture as your KNIME installation? (32 bit or 64 bit)

3

Hi,

have you tried using the build in jdbc/odbc driver which allows you to connect to an odbc data source? In order to do so just select the sun.jdbc.odbc.JdbcOdbcDriver driver in the Database driver selection box of the Database Connector and enter the connection information.

If you want to use NTLM authentication with the sqljdbc4 driver you need to perform additional setup steps. Please refer to NTLM section of the authentication section in the MS SQL Server Type 4 JDBC Driver documentation for details about how to setup your client for NTLM authentication.

Bye,

Tobias

4

hi 

I am using KMINE 2.10.3 (32) bit version and have configure my ini file with the path to my DLL s: 

-Djava.library.path=C:\Program Files (x86)\KNIME_2.10.3\jre\bin\ext\

C:\Program Files (x86)\KNIME_2.10.3\jre\bin\ext\sqljdbc_auth.dll    [ the 32 bits version ] 

C:\Program Files (x86)\KNIME_2.10.3\jre\bin\ext\sqljdbc_xa.dll       [ the 32 bits version ] 

C:\Program Files (x86)\KNIME_2.10.3\jre\bin\ext\sqljdbc4.jar

 

when I clicked the Fetch Metadata, it was returning the Table and View structure as per attached file but after I clicked the Apply button and Execute the node ...it suggested for ERROR with invalid username or login as below message 

"ERROR     Database Reader                    Execute failed: com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'BASupport'."

 

Then I returned to the Configure -> Fetch Metadata again , and it showed the similar WARNING message below 

"WARN      DBReaderDialogPane                 Error during fetching metadata from database, reason: Login failed for user 'BASupport'."

 

So I cannot figure out what is going on with this ? it looks like the node didnt keep my username and password correctly or doing some sort of extra encryption ...

I really appriciate you helps. Thanks  

 

 

 

 

 

5

Hi,

I remember some pains with the official Microsoft driver as well - did you try the jTDS driver as an alternative? That got me going a while ago.

Cheers,
E

 

6

aborg,

How do I test if the architecture is 64bit or 32. assume that since is Microsoft, it would cater for both. I cannot recall asking me about the 32/64 otion during download.

 

 

7

hi 

I have found out the KMINE had encrypt the password based in the Preference->KMINE-> Master Key by chance , so when I disable the Master Key feature , it is working now .

but I have got another problem with another datasource with High Availability feature (primary and secondary nodes), which requires "ApplicationIntent=ReadOnly" attribute on the connection string

I have tried both jDTS and JDBC now, microsoft website suggested that JDBC already supported "ApplicationIntent=ReadOnly"  

http://technet.microsoft.com/en-us/library/hh510238(v=sql.110).aspx

I have check the latest version of DLL files and Jar file again , but still no luck 

(I am still using the 32 bit version)

Any ideas on connecting on how to connect to database (SQL Server 2012) with Application Intent attribute using Database Reader ?

Again running Java Snippet code below works perfectly fine 

String url = "jdbc:sqlserver://DC1UATSERVER:1433;databaseName=DBNAME;Username=User;Password=PASS;<em><strong>applicationIntent=ReadOnly</strong></em>";  
    logger.error( url );
    Connection conn = DriverManager.getConnection(url);
    logger.error("connection created");
    Statement st=conn.createStatement();
    String sql="SELECT TOP 100 ActivityTypeID, Description FROM Ref_ActivityType";

 

I also try different SQL / Java tool (SoapUI - SQL Squirrel) that use the same JDBC drivers, and they all are working just fine with the same ConnectionString  , the I can confirm there is not problem with the DLL or JAR files ...

 

Thanks and Regards

 

 

8

Hi Ergonomist,

 

I tried using the jTDS driver but I am getting error message:

ERROR     Database Reader       Execute failed: java.sql.SQLException: I/O Error: DB server closed connection.

 

Rhe url was:

jdbc:jtds:sqlserver://SERVER_NAME:1433/DATABASE_NAME;domain=au

with or without the domain=au 

Am I suppose to setup any other parameter in Windows 7?

Thanks for the suggestions.

9

Hi,

we do have an SQL Server for testing but not configured to use readOnly or Windows authentication that's why it is hard for us to replay the problem. However when I add ApplicationIntent=ReadOnly to the end of the jdbc URL I can still connect to our db.

I have created a workflow that uses a Java Snippet node to establish the connection to the database in the same way as KNIME does except for the password encription. The node also collects several information about the driver settings, the connection and the database (this can be enabled/disabled via the showMetaInfo and printTableInfo boolean flags in line 45 and 47). Please enter your connection settings into the Table Creator node and add the additional libraries in the Java Snippet node to execute the workflow and send me the result.

Thanks for your help,

Tobias

 

10

Hi,

you can have a look in the knime.ini file for a line that starts with --launcher.library

For windows 64bit the line will look like:

--launcher.library plugins/org.eclipse.equinox.launcher.win32.win32.x86_64_1.1.100.v20110502

If the bold part is only win32.x86 you have the 32 bit version.

For details and other ways to get the information have a look at the stackoverflow article.

Bye,

Tobias

11

Tobias,

I run your workflow and edited the password etc. please find the attached file

Thanks,

Tom

 

12

Hi,

Did you systematically test the other options you mentioned before? Specifically "integratedSecurity=true" for SSO authentication. In case you use SSO, you'll also need to make sure to have the platform-specific library (i.e., DLL for Windows) in the Java path somewhere.

Other than that I suppose with Tobias working on it, you'll get this resolved fairly soon either way. :)

Cheers
E

13

Hi,

thanks for the file. Unfortunately it does not give much information. All I can see is that the library is loaded corretly but the user authentication failed. Have you tried to connect to the database via ODBC using the JDBC-ODBC driver? This should work the same way as with SAS.

If you want to use NTLM authentication in jdbc you need to make sure that the dll that is shipped with the MS jdbc driver is found by KNIME. There are several options to make the dll available to KNIME which I have copied from the driver documentation:

By default, the driver looks for the NTLM authentication DLLs in a directory on the Windows system path defined by the PATH environment variable. If you install the driver in a directory that is not on the Windows system path, perform one of the following actions to ensure the driver can load the DLLs:

  • Add the WL_HOME/server/lib directory to the Windows system path, where WL_HOME is the directory in which you installed WebLogic Server.
  • Copy the NTLM authentication DLLs from WL_HOME/server/lib to a directory that is on the Windows system path, where WL_HOME is the directory in which you installed WebLogic Server.
  • Set the LoadLibraryPath property to specify the location of the NTLM authentication DLLs. For example, if you install the driver in a directory named "DataDirect" that is not on the Windows system path, you can use the LoadLibraryPath property to specify the directory containing the NTLM authentication DLLs:

    jdbc:bea:sqlserver://server3:1521;DatabaseName=test;LoadLibraryPath=C:\DataDirect\lib;User=test;Password=secret

You can also try to use the workflow I have send to you to test the NTLM authentication. Just add the integratedSecurity=true option to the url and leave the password empty. If you get the error: Failed to load the sqljdbc_auth.dll you can try another option on how to make the dll available to KNIME. Only when you can connect to the database via NTLM add the ApplicationIntent=ReadOnly parameter to the connect string.

Bye,

Tobias

14

hi Tobias

 

I have run  the getJDBCInfo workflow with result attached ...

 

I have add this extra line my kmine.ini file ,

-Djava.library.path=C:\Program Files (x86)\KNIME_2.10.3\jre\bin\ext\

 

this is the location of sqljdbc_auth.dll, sqljdbc4.jar and sqljdbc_xa.dll files

 

Regards

15

Hi,

thanks for the update. Based on the results from the jdbc test you should now be able to connect to the database with the NTLM authentication and the ApplicationIntent=ReadOnly. Do you still experience any problems?

Bye,

Tobias

16

hi tobias

 

Yes. this is still a problem, as i mentioned in the earlier post , I dont have problem making connection from Java Sniplet , but to make the database connection with ApplicationIntent=ReadOnly is still a problem using the Database Reader / Connector which I cannot debug further 

The testing and logging that are shown/printed from Java Sniplet can only give us confirmation that the problem does not lie on the Library (jar , dll files)  nor Path (locations of files) , rather it is more on the Database Reader node is "doing something" extra or using different way to connect to database 

but how do I trace or debug this further (on the Database Reader) ? 

 

Regards

 

 

 

 

 

17

Tobias,

Sorry, for the long wait. I am going to get the IT  guys have a look as it is gettin a bit more technical than I can handle.

Cheers,
 

18

Hi,

you can check which jdbc url is used to connect to the database when looking into the KNIME log file and searching for "Opening database connection to". After this string the jdbc url is displayed which is passed to the Java DriverManager to establish the connection. This string should look exatly the same like the string in the jdbc test workflow.

To open the KNIME Log file open KNIME and go to View->Open KNIME Log. If you still have problems with the connection please send me the log file that contains the connection error.

Thanks

Tobias

19

Tobias,

After an investigation, we decided to create a use SQL Authentication as the management of .dll was getting the support team some concern, it was  easier to organise one DB to assign access. 

It was fine and we will leave it at that for now.

Thanks for your help. I think we can start doing some user role out and testing.

20

Hi,

thanks for the update. I'm glad that you can finally access the sql server. Let me know if you need further information.

Bye,

Tobias