Database Plugin - SQL Server Problems

[pcmanning]

Trying to get the database plugin to work with SQL Server. 

Have install 64-bit Archi on Windows 10, with a local SQL Server 2017 (Dev Edition) which I can connect to fine with SQL Studio, but not with Archi...  Error "Failed to check the database", "Please Verify the database configuration..." and check the sqljdbc_auth.dll file is in jre/bin (WHICH IT IS).  I've also downloaded the latest JDBC pack from Microsoft and used the 64bit dll for there as an alternative - no difference.

Connection String is this

jdbc:sqlserver://localhost:1433;databaseName=archi

So as about as simple as it gets -  any help please?

thanks
Paul

[pcmanning]

PS:  Extra Info - Windows 10, 64 Bit - NOT Domain Joined, and logged in with a Microsoft Account

[pcmanning]

And a bit more.  Having got home (and hence having access to a Azure SQL Database without a Firewall impeding things), I've got the connection working for that, so that proves the sqljdbc_auth piece is working?  This was with what effectively a SQL Authenticated user, NOT an Azure AD authenticated user.  Seems I'd need to set

authentication=ActiveDirectoryMSI  (is the plugin also seting a value here?)

As per this https://docs.microsoft.com/en-us/sql/connect/jdbc/connecting-using-azure-active-directory-authentication?view=sql-server-2017

Which won't even think about connecting.  May need another library - as per https://docs.microsoft.com/en-us/sql/connect/jdbc/setting-the-connection-properties?view=sql-server-2017

Will explore further...

[Hervé]

Hi Paul,

The sqljdbc_auth.dll file is able to transmit your local credential to a database where the Windows authentication mode is enabled but per my knowledge, it works only when you're connected to a Active Directory domain as the credentials is sent in clear format, but only a certificate is transferred to the database.

To be honest, I never experienced to connect to an Azure SQL Server database from a PC which is not connected to a domain.

But if you know the specific JDBC parameters that enables the use case, then you may use the expert mode where you can manually specify the connection string.

Hope this helps
Hervé

[pcmanning]

Hmm so more experimentation needed, the documentation lists all sorts of modes to pass to the sqljdbc_auth module, but when I add them in to the "expert" mode string things fail immediately.  I'll experiment further, but looks like there's another issue - the plugin has just lost all the database connections I had set up (3) - that's not good - have found the settings (attached - minus passwords - in plain text - may want to rethink that) which have the connections still in there, but clearly it's failed to read them??? 

If the login details are indeed just pasted to Windows, it should be possible to deal with the multiple login use cases - but since I can't see any useful logging anywhere, I'm running a bit blind!

Paul

[pcmanning]

So I thought I'd go simple until I can get the Comms guys to sort the firewall, so installed mysql on the local PC - all the mysql tools connect, but not Archi!  And surely it can't be simpler?

jdbc:mysql://localhost:3306/archi with pcmadmin as username and a password

Paul

[pcmanning]

OK likely operator error - have got a connection working against mysql on an Ubuntu VM now, required getting up to speed on some mysql rights!  May be the same on the local instance...

Will explore similar setup for SQL Server I think...  See if things behave better...

[pcmanning]

So getting a better understanding of what's possible with SQL Server Authentication.  For on premises, as you say either an AD domain is needed or you have to use SQL Auth only - nasty.  BUT, with Azure SQL databases, then Azure AD Authentication works, e.g:

Code Select Expand

jdbc:sqlserver://test.database.windows.net:1433;database=test;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;authentication=ActiveDirectoryPassword


As you may be aware you can test all this out with a free Azure trial Tenant - you'll have access to SQL, mysql, postgres and neo4j databases I believe... 

Not the speediest solution over my broadband though!

[Hervé]

thanks for the info