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
PS: Extra Info - Windows 10, 64 Bit - NOT Domain Joined, and logged in with a Microsoft Account
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 (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 (https://docs.microsoft.com/en-us/sql/connect/jdbc/setting-the-connection-properties?view=sql-server-2017)
Will explore further...
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é
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
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
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...
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:
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!
thanks for the info :)