Issue Connecting to Azure SQLServer Public Endpoints

[jacobreeves]

Hi Everyone

This tsql call allows successful connection to an Azure SQLServer public endpoint:

tsql -H azure-host.public.some-guid.database.windows.net -U username -p 3342 -P password

However, if a connection is configured using freetds.conf

[azuredb]
host = azure-host.public.some-guid.database.windows.net
port = 3342
tds version = 8.0

and this command is run

tsql -S azuredb -U username@azure-host -P password

The following error is reported

Msg 18456 (severity 14, state 1) from azure-host.public.some-guid.database.windows.net Line 1:
"Login failed for user 'username@azure-host'."
Error 20002 (severity 9):
Adaptive Server connection failed
There was a problem connecting to the server

The questions are:

• What is the correct way to connect to an Azure public endpoint using the -S flag. This question is driven by our need to use ActiveRecord / TinyTds with the Azure public endpoint. TinyTds exhibits the exact same behavior.
• The user in the sqlserver db is just 'username'. Why is it required to pass 'username@azure-host'?

For reference:
tsql -C

Compile-time settings (established with the "configure" script)
                            Version: freetds v1.00.91
             freetds.conf directory: /usr/local/etc
     MS db-lib source compatibility: no
        Sybase binary compatibility: no
                      Thread safety: yes
                      iconv library: yes
                        TDS version: 7.3
                              iODBC: no
                           unixodbc: no
              SSPI "trusted" logins: no
                           Kerberos: no
                            OpenSSL: yes
                             GnuTLS: no
                               MARS: no

Thanks for the great library.

[fziglio]

Well,

tds version = 8.0

is, as documented, perfect if you are connecting to Microsoft SQL Server 2000.

[jacobreeves]

Thanks for the response @fziglio. I tried the command with

tds version = 7.4

and got the same result.

Do you know why tsql -H accepts a user without hostname appended and tsql -S does not? Based on the code, it looks like there are different execution pathways but I wasn't able to track down exactly what is different. Any help is appreciated.

[fziglio]

I would do a step back. As the command

tsql -H azure-host.public.some-guid.database.windows.net -U username -p 3342 -P password

is working I would try to transform this using freetds.conf. That is I would write a freetds.conf like

[myserver]
host = azure-host.public.some-guid.database.windows.net
port = 3342

and use the command

tsql -S myserver -U username -P password

That should work too (if not I would assume tsql is not picking up that freetds.conf you are using, maybe it's on the wrong directory). Newer FreeTDS versions prefer the usage of auto as TDS version but maybe if the above command is working and you don't want to update FreeTDS version you could change freetds.conf to

[myserver]
host = azure-host.public.some-guid.database.windows.net
port = 3342
tds version = 7.4

(looking at your tsql -C output the default version if 7.3).

[jacobreeves]

Again, thanks for your help. I have tried your suggestion with the following results

freetds.conf file has this section:

[myserver]
        host = azure-host.public.some-guid.database.windows.net
        port = 3342
        tds version = 7.4

The configuration requires a TDS version. When auto is set, the connection will time out.

Command:
tsql -S myserver -U username -P password

Result:

locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Msg 40532 (severity 20, state 1) from myserver:
	"Cannot open server "database.windows.net" requested by the login.  The login failed."
Error 20002 (severity 9):
	Adaptive Server connection failed
There was a problem connecting to the server

Command:

tsql -S myserver -U username@azure-host -P password

Result:

locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Msg 18456 (severity 14, state 1) from azure-host.public.some-guid.database.windows.net Line 1:
	"Login failed for user 'username@azure-host'."
Error 20002 (severity 9):
	Adaptive Server connection failed
There was a problem connecting to the server

One other thing I've noticed after looking at the debug logs and the code. It appears there is a different pathway when the -S flag is used vs -H. In tsql at line 598 (in version 1.00.91) this code is used to set the host:

       if (servername) {
		if (!tds_set_server(login, servername))
			goto out_of_memory;
		if (confile) {
			tds_set_interfaces_file_loc(confile);
		}
		/* else we specified hostname/port */
	} else {
		if (!tds_set_server(login, hostname))
			goto out_of_memory;
		tds_set_port(login, port);
	}

Servername is correctly resolved to the host / port values from the file so that part is working but I am unable to determine what else might be changing based on the different code pathways.

[aditandrau]

@fziglio Do you have any updates on this issue?
We are also having the same issue - receiving "Login failed for user ..." when trying to connect to an Azure SQL Server.

[fziglio]

I would try to update FreeTDS. Did you try https://www.freetds.org/userguide/logging.html ? I don't understand why you need to append the hostname to the username.