Support Azure Active Directory Password MSSQL

[Jonatthu]

Please support this feature, my entire company needs it, I was about to purchase 48 licenses. But we are heavily relying on this feature.

As a reference this is how a typical connection string to support looks like:

Server=tcp:sqlsrv.database.windows.net,1433;Initial Catalog={database};Persist Security Info=False;User ID={username@email.com};Password={password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication=Active Directory Password;

Please anwser the questions below, it helps us to track the issue.

1. Which driver are you using and version of it (Ex: PostgreSQL 10.0): MSSQL 12 - 14 and 16 Azure Database (SQL)

2. Which TablePlus build number are you using (the number on the welcome screen, Ex: build 81): Version 4.1.2 (382)

3. The steps to reproduce this issue: No steps

Noted: If the bug is related to data, please attach an example SQL data.

[huyphams]

Hi @Jonatthu please do not remove issue template questions.

### Please anwser the questions below, it helps us to track the issue.

1. Which driver are you using and version of it (Ex: PostgreSQL 10.0):

2. Which TablePlus build number are you using (the number on the welcome screen, Ex: build 81):

3. The steps to reproduce this issue:

Noted: If the bug is related to data, please attach an example SQL data.

[Jonatthu]

@huyphams Done

[huyphams]

Have you tried to login with TablePlus @Jonatthu do you see any issue or error message?

[Jonatthu]

It ignores MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication=Active Directory Password; @huyphams

[huyphams]

do you see any error message @Jonatthu ? can you post it here

[Jonatthu]

It's just won't connect since I can not set those flags @huyphams,

If table plus allows me to set these flags: MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication=Active Directory Password;

it would work.

But table plus ignores it all.

The error is just that user credentials are invalid.

Also the username when is an email: 'myuser@gmail.com' table plus seems not to like the '@' character on the user field

[huyphams]

Thanks @Jonatthu I knew that you could not connect. The reason I ask for the message because I want to search the error message to see what was the real issue. So is the "credentials are invalid" only message that you saw, no error code, no other details?.

MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication=Active Directory Password;

Some if your params are just the preset variables and can be setup later with bootstrap commands so it is not important (for example MultipleActiveResultSets=False). SQL Server is complex. It has various drivers and each of them has a different input rule/format. (TablePlus uses freetds just in case you want to Google the syntax yourself).

Also the username when is an email: 'myuser@gmail.com' table plus seems not to like the '@' character on the user field

Please try to login with the syntax username\domain (use the \ character instead of the @).

[Jonatthu]

@huyphams

When I do with myemail/mycompany.com

Error: General SQL Server error: Check messages from the SQL Server
Adaptive Server connection failed (sqlsrv.database.windows.net)
General SQL Server error: Check messages from the SQL Server
Adaptive Server connection failed (sqlsrv.database.windows.net) 
Message: Msg: 40532, Line 0, State: 1, Level: 20
Cannot open server "mycompany.com" requested by the login.  The login failed.
TablePlus hint: The username requires the server name, please try again with username@server-name
Msg: 18456, Line 1, State: 1, Level: 14
Login failed for user 'myemail/mycompany.com'.

When I do with myemail@mycompany.com

Error: General SQL Server error: Check messages from the SQL Server
Adaptive Server connection failed (sqlsrv.database.windows.net) 
Message: Msg: 40532, Line 0, State: 1, Level: 20
Cannot open server "company.com" requested by the login.  The login failed.
TablePlus hint: The username requires the server name, please try again with username@server-name

With bootstrap commands:

Level: 20
Windows logins are not supported in this version of SQL Server.
Msg: 40607, Line 0, State: 1, L

[huyphams]

Thanks @Jonatthu Please use the \ and not the /

The Bootstrap commands is used after you login not before.

[Jonatthu]

@huyphams

Just did it also but same error

Adaptive Server connection failed (sqlsrv.database.windows.net) 
Message: Msg: 40607, Line 0, State: 1, Level: 20
Windows logins are not supported in this version of SQL Server.
Msg: 40607, Line 0, State: 1, Level: 20
Windows logins are not supported in this version of SQL Server.

But this works on my app since I am using VPN and also in Azure Data Studio.

[huyphams]

I found the issue: https://stackoverflow.com/questions/9818354/how-to-resolve-azure-windows-logins-are-not-supported-in-this-version-of-sql-se We must to find away to enable those flags in freetds.

[Jonatthu]

@huyphams Yes also this one: Authentication=Active Directory Password;

[huyphams]

I've checked the config file: https://www.freetds.org/userguide/freetdsconf.html the Trusted_Connection defalt is No - correct. the encryption will require if you use the protocol > 7.1 so please try to change it to 7.2 and other version as well.

[Jonatthu]

@huyphams This does not work ether, or not sure what exactly "extra" configs I am supposed to do by setting this version "72"

[Jonatthu]

@huyphams any updates on this?

[Jonatthu]

@huyphams Any updates on this?

[kinnedev]

@Jonatthu were you ever able to get this working?