How to access SQL database

Hi,

I am trying to connect to the SQL Database using integrated security = true. In this case I would not pass user name and password with the connection string. 

But the MainFactory.Database.CreateConnection() requires to pass username and password as the arguments.

I tried  MainFactory.Database.CreateWindowsAuthenticationConnection() but it would fail as Single Signon error.

Please guide me how I could just pass the server name and database name.  

 

I am using 15.1 ChemAxon.NET and  JChem.NET API

 

Thanks

Hi,

please try to download the proper bit version (32 or 64) of ntlmauth.dll from the net.

Try to place it to the System pathes if it is not already there

and please check these articles:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/fd649f47-4551-419f-a487-c3e28ba1be48/javasqlsqlexception-io-error-sso-failed-native-sspi-library-not-loaded?forum=sqldatabaseengine

http://stackoverflow.com/questions/6356612/jtds-driver-not-working-for-sql-sever-2008r2-and-denali-native-sspi-library-not

 

Please do not be surprised that the article is referring to Java world, in the underlying .NET API there are some java drivers in use.

 

Thanks,

Viktor

hamoriviktor wrote:

Hi, please try to download the proper bit version (32 or 64) of ntlmauth.dll from the net. Try to place it to the System pathes if it is not already there and please check these articles: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/fd649f47-4551-419f-a487-c3e28ba1be48/javasqlsqlexception-io-error-sso-failed-native-sspi-library-not-loaded?forum=sqldatabaseengine http://stackoverflow.com/questions/6356612/jtds-driver-not-working-for-sql-sever-2008r2-and-denali-native-sspi-library-not   Please do not be surprised that the article is referring to Java world, in the underlying .NET API there are some java drivers in use.   Thanks, Viktor

Hi Viktor,

I copied the dll to the bin folder and I also configured the firewall. But I still not able to connect to the SQL Database using the below code.  May I request to please help me retrieve the structure ? 

connection = MainFactory.Database.CreateConnection

I have the following installed on the development machine

1) JChem.NET 15.1

2) Chem Axon.NET 15.1

3) JRE 8 

is anything else I am missing in order to connect to the SQL Database?

I am trying to connect to a local  SQL Server 2014 databse with integrated security = true listening on port 1433.   

So i am not sure if I could even use MainFactory.Database.CreateConnection ?

There are no errors in the SQL Server logs or even in the Event Viewer logs. This got to be my environment issue or an issue with the MainFactory.Database. 

Please help...

 

Thanks

Regards

DJ

Hi,

 

have you also tried out to put ntlmauth.dll this dll into:

java.lang.System.getProperty("java.library.path")

 

?

 

Viktor

Hi,

and please let us know what this error says exactly?

 

Viktor

hamoriviktor wrote:

Hi,   have you also tried out to put ntlmauth.dll this dll into: java.lang.System.getProperty("java.library.path")   ?   Viktor

Hi Viktor,

I have set the JAVA_HOME to point to C:\jtds-1.3.0-dist\x64\SSO. So Not sure what else you mean by put ntlmauth.dll into java.lang.System.getProperty. I have only installed the JRE and not the full java EE.

Thanks

hamoriviktor wrote:

Hi, and please let us know what this error says exactly?   Viktor

Hi Viktor,

Thanks for your response. I have included the Message, Source and StackTrace if that helps.

Message : "Single-Sign-On is only supported on Windows. Please specify a user name."

Source :"IKVM.OpenJDK.Jdbc"

StackTrace:"   at java.sql.DriverManager.getConnection(String , Properties , ClassLoader )\r\n   at java.sql.DriverManager.getConnection(String url, CallerID )\r\n   at java.sql.DriverManager.getConnection(String url)\r\n   at ChemAxon.NET.IKVM.DB.JChemConnectionHandler.†††\r\n†††‰†‘(Object )\r\n   at ChemAxon.NET.IKVM.DB.JChemConnectionHandler..ctor(String databaseURL, Boolean autoCommit)\r\n   at ChemAxon.NET.IKVM.DB.Entities.AbstractDatabaseHandler.get_ConnectionHandler()\r\n   at ChemAxon.NET.IKVM.DB.Entities.AbstractDatabaseHandler.SetDefaultValues(String& catalog, String& schema)\r\n   at ChemAxon.NET.IKVM.DB.Entities.AbstractDatabaseHandler..ctor(String databaseURL)\r\n   at ChemAxon.NET.IKVM.DB.Entities.AbstractDatabaseHandler..ctor(IDatabaseURLConverter databaseURLConverter)\r\n   at ChemAxon.NET.IKVM.DB.Entities.CustomDatabaseHandler..ctor(IDatabaseURLConverter databaseURLConverter)\r\n   at ChemAxon.NET.API.DB.DBHandlerFactory.OpenCustom(IDatabaseURLConverter connection)\r\n  

Hi DJ,

 

I think you do not need Java Runtime installed at all. But make sure that ntmauth.dll be search properly on system pathes especially from "java library pathes".

 JAVA_HOME to point to C:\jtds-1.3.0-dist\x64\SSO : I think this is not a good step since JAVA_HOME means where your java wants to be running from e.g where Java.exe is located,

the dll needs to be copied across to be under your java_Home/bin, this is what the article describes .

Or make C:\jtds-1.3.0-dist\x64\SS folder visible for java environment variables. e.g. with SET PATH.

We will be adding some screen shots per our testing against MS SQL Server shortly ...

Viktor

hamoriviktor wrote:

Hi DJ,   I think you do not need Java Runtime installed at all. But make sure that ntmauth.dll be search properly on system pathes especially from "java library pathes".  JAVA_HOME to point to C:\jtds-1.3.0-dist\x64\SSO : I think this is not a good step since JAVA_HOME means where your java wants to be running from e.g where Java.exe is located, the dll needs to be copied across to be under your java_Home/bin, this is what the article describes . Or make C:\jtds-1.3.0-dist\x64\SS folder visible for java environment variables. e.g. with SET PATH. We will be adding some screen shots per our testing against MS SQL Server shortly ... Viktor

Hi Viktor,

I should had indicated before that i have copied the ntmauth.dll into the bin folder in the Program Files --> java --> JRE--> Bin folder.  I have alos add the the dll into the PATH environment as well.

I do not nee any errors in the Windows Event Log nor I see any Error in the SQL server logs. 

Thanks

Sorry, maybe I missed that lines.

OK then your environment is ok. Will attach some screenshots from our test environment shortly ...

Everything is working fine:

 

  private void button1_Click (object sender, EventArgs e) {

        var connection = MainFactory.Database.CreateWindowsAuthenticationConnection("localhost", 1433, "test");

         var databaseHandler = MainFactory.Database.OpenCustom(connection);

         var tables = databaseHandler.GetTables();

         IList<string> tableNames = new List<string>();

         foreach (var table in tables) {

            tableNames.Add(table.Name);

         }

         var names = new string[tableNames.Count];

         tableNames.CopyTo(names, 0);

         listBox1.Items.AddRange(names);

 

      }

 

So this code should be working if you set the following MS SQL database settings (security/authetication).

You need to copy across our installed dll to the your application output folder (or bin pathes we have already discussed)

Otherwise you will get a security exception.

Please see the screenshots below:

Hi Viktor,

I am still getting the same error. I couldn't work on this task much but let do some analysis as its working for you and not for me. I did copy the dll into the project and its in my bin folder. rest of the config are same. Nothing make sense so far. Will get back to you if I am not able to resolve.

 

Thanks

Hi Viktor,

I am still getting the same error. I have zipped the project and attached. May I request to please unzip and run the project ( change the DB name). if you are able to successfully connect to the database than we can come to conclusion that it is environment issue. 

How do I enable Debug Log for ChemAxon. That should point me what is missing in my environment or would it be possible for you to take a look at my environment?

Let me know what would be your next action?

Thanks

Hi,

thank you for the sample code. We will debug it against our test environment and be back to you soon.

Viktor

Tested your sample code and it still works fine for us.

Maybe the ntlmauth.dll we deploy is not comaptible with your operating system? Please find another version in your system.

Further ideas, but I am afraid it is irrelevant:

The preferable .NET framework settings in the project file is 3.5 (not the client profile).

Framework 4, 4.5 is also ok, but officially the 3.5 is the preferable.

We did a contra test if we logged in a user that is not available as a MSSQL user and make your code running a NORMAL! login exception comes immediately that the WIndows authentication does not allow this user to  log in.

So I think this problem is still somewhere around your ntlmauth.dll. So it is configuration issue I think.

 

Coluld you please do a test with a normal Microsoft component and test the single sign log in against your SQL service  ithout involving our tool to have proof that your environment is viable?

 

Thanks,

Viktor

Hi Viktor,

Thanks for your response. I do not use client profile and app I am building is targeting .Net 4.5.1. 

This rules out all other possibilities.

I think I had not mentioned it before than I am developing my app on Windows Server 2012 R2. Will this be an issue?

But I didn't understand the  below suggestion, are you suggesting me to use Microsoft ODBC Driver to test the Single Sign on against the SQL Server. If Yes than I have done this and works good. I have also tested it with Entity Framework 6 and also works.

Could you please do a test with a normal Microsoft component and test the single sign log in against your SQL service �ithout involving our tool to have proof that your environment is viable?

Also I downloaded the dll from the sourceforge.net and tested. This also fails.

Can you setup a WebEx and see if you can figure out anything?

Thanks