Unable to connect from DBeaver on Mac to SQL Server that uses Active Directory authentication

[artgoldberg]

System information:

• Operating system: MacOS Big Sur: 11.2.3 (20D91)
• DBeaver version: 21.1.0.202106090921
• Additional extensions: none

Connection specification:

• Database name and version: Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)
  Sep 24 2019 13:48:23
  Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor)

• Driver name: Microsoft JDBC Driver for SQL Server, with Azure drivers installed
  

• Do you use tunnels or proxies (SSH, SOCKS, etc)?: Yes, F5 Big-Ip

Describe the problem you're observing:

Working remotely, I wish to connect DBeaver on a Mac to SQL Server that uses an Active Directory service to authenticate and authorize users.

I've tried multiple approaches to connect and authenticate, including those recommended in issue 3647 and issue 9751.

Nevertheless, I am unable to authenticate. Here's one error I've received.

Steps to reproduce, if exist:

Based on the configuration, these seem to be the settings needed:
Authentication: Active Directory - Password
Driver properties:

• authentication: ActiveDirectoryPassword
• trustServerCertificate: true # to avoid SSL errors

Using them reproduces my error. Curiously, the username string in the error message is usually empty, but not always (like above). I wonder if DBeaver is failing to use it.

What settings should be used to connect with my configuration?

[uslss]

@artgoldberg did you try to connect using e.g. SMSS?
Probably something Is going wrong with user settings
https://docs.microsoft.com/en-us/answers/questions/133709/login-failed-for-user-39lttoken-identified-princip.html

[artgoldberg]

Thanks @uslss . I'm using a Mac and "SSMS is available only as a 32-bit application for Windows". I started trying to use Azure Data Studio, but it doesn't support Active Directory - Password authentication.

However, the document you reference provides a hint. While it's terribly written, it seems to suggest that the name of the user granted rights to access a SQL Server database must exactly match the User Principal Name (UPN) for the user in the Active Directory server used to authenticate the user. Is this correct? Please let me know, as that would be important information if it is true, and Googling does not answer the question.

Thanks
Arthur

[artgoldberg]

Hello @uslss .
Is it possible to configure DBeaver running on a Mac to connect to an SQL Server instance that uses Active Directory authentication? Have your users achieved this, and, if so, how have they configured the DBeaver, rights on the SQL Server instance, and the user's Active Directory entry?

Thanks
Arthur

[LonwoLonwo]

Hello @artgoldberg
You have SQL Server database and driver, but also you have Azure drivers.
Have you tried to use the Connection dialog for Azure Database?

[artgoldberg]

Thanks @LonwoLonwo

No, I've not tried an Azure database. But I have no problem using SQL Server Authentication to access SQL Server, so I do not need to try that. The key challenge here is to get Active Directory authentication working.

What do you think Serge @serge-rider ?

Thanks
Arthur

[LonwoLonwo]

#4990

[artgoldberg]

Thanks @LonwoLonwo , I've tried #4990 and it didn't work.

However, colleagues of mine enabled connection from DBeaver on Mac to SQL Server by using Kerberos to access the Active Directory authentication. It's complicated. I can provide details if you like.

[LonwoLonwo]

Yes @artgoldberg
It will be very helpful!

[jnahmias]

What I did is to set up the Kerberos configuration in /etc/krb5.conf on the Mac for the AD realm(s) aka domain(s). Then, it's just a matter of setting the properties correctly:

databaseName=master
authenticationScheme=JavaKerberos
encrypt=true
integratedSecurity=true
serverName=mssqlserver.example.org
trustStoreType=KeychainStore
userName=me@EXAMPLE.ORG
password=REDACTED

Another alternative, if you want true SSO, is to run kinit me@EXAMPLE.ORG first. Then you can leave off the userName and password properties.

[artgoldberg]

Thanks @jnahmias , that's great!

[artgoldberg]

To make the answer by @jnahmias work the Mac's Keychain must store a trusted root certificate for the institution where the SQL Server runs.

[GolamRashed]

I am having similar issues for MFA, is there a way to connect properly to Synapse DWH?

[Maxifer22]

Hi team
I have a problem when I try to conecct the database

[LonwoLonwo]

Go to "Edit Driver Settings," click the "Libraries" tab, then click "Add Artifact," then add the Maven dependency declaration:

com.microsoft.azure msal4j 1.11.3

[curlvino]

hey guys , i can even see the configuration settings in #13386 (comment). all I see is