Provide a way to specify target server hostname or specify username when authenticating with Azure AD CLI

[mmalecki]

Wow, that was a mouthful to put in the title. Sadly I don't think there's a shorter way to go about this than providing a use-case, so here goes:

I'm looking for a way to connect to a Azure SQL server instance in a private network using sqlcmd, a proxy inside that network and Azure AD authentication. I am, however, unable to use that method without hard-coding a DNS alias for the server hostname pointing to 127.0.0.1 in /etc/hosts due to how SQL Server login works (?).

So, if I set up the proxy on port 11433, the following fails to log in:

SQLCMDSERVER="127.0.0.1,11433" sqlcmd -N -C --authentication-method=ActiveDirectoryAzCli

But the following works if I add an entry to /etc/hosts pointing my actual server hostname at 127.0.0.1 (still going through the same proxy, note the port):

SQLCMDSERVER="<server-name>.database.windows.net,11433" sqlcmd -N -C --authentication-method=ActiveDirectoryAzCli

In #484 the poster was able to solve this issue by specifying the hostname as part of the passed in username, like:

sqlcmd -N -C -U 'mail#<email>@<server-name>.database.windows.net' --authentication-method=ActiveDirectoryAzCli

But the -U switch doesn't seem to do anything in case of CLI authentication.

So, I'm looking for a way to specify the server name, preferably by an entirely different command line switch altogether, or to tack on that server hostname to the username. I tried taking a dive into this client, as well as the MSSQL library, and found a promising lead in form of HostDialer, but nothing that could work out of the box with the current state of things. I'd love to be corrected and just close this issue though ;).

[shueybubbles]

afaik the only way you can provide a different host name than the "real" DNS name for Azure SQL DB is via DNS aliases, per https://learn.microsoft.com/azure/azure-sql/database/dns-alias-overview?view=azuresql The Azure DB gateway looks at the host name provided by the client and rejects the connection if it's not the right one. IE any client driver is only going to use one host name and expect it to resolve, it won't have separate settings for an ip address and the real host name. Am I misunderstanding your need?

[mmalecki]

Oh, didn't know about this mechanism, that's neat! Sadly, it doesn't really solve my use-case. I'm looking to set up a proxy on localhost:11433, to a Azure SQL DB in the cloud, and connect with SQLCMDSERVER="127.0.0.1,11433". Apparently that is possible with other types of authentication (as shown in #484) by specifying a username containing the server name (which I guess is the part that the DB Gateway parses). However, that appears impossible when using --authentication-method=ActiveDirectoryAzCli. I believe the username passed via -U is getting overriden somewhere in the process of acquiring AD credentials. I can still achieve this by having a user add entries to /etc/hosts, pointing to 127.0.0.1, but that's a bit of a round-about solution.