Not able to connect to Azure SQL

[davidkiss-nuvalence]

• pgloader --version

  pgloader version "3.6.3~devel"
  compiled with SBCL 1.4.16.debian
  

• did you test a fresh compile from the source tree?

  No

• did you search for other similar issues?
  Yes

• how can I reproduce the bug?

I tried migrating data from Azure SQL to Postgres using docker (see below command), but pgloader wasn't able to connect to Azure SQL due to use of unsupported USE command:

    sudo docker run --rm -v /tmp:/tmp -e TDSDUMP=/tmp/pgloader/freetds.log -e TDSVER=7.0 --name pgloader dimitri/pgloader:latest pgloader --debug --verbose --no-ssl-cert-verification mssql://<mssql username>:<mssql pwd>@<azure sql instance>.database.windows.net/<mssql db name> postgresql://<postgres username>:<postgres pwd>@<postgres hostname>/<postgres db name>

In the /tmp/pgloader/freetds.log file I found below error:

USE statement is not supported to switch between databases. Use a new connection to connect to a different database.

This is after pgloader sent below command to the mssql db:

SET ARITHABORT ON;SET CONCAT_NULL_YIELDS_NULL ON;SET ANSI_NULLS ON;SET ANSI_NULL_DFLT_ON ON;SET ANSI_PADDING ON;SET ANSI_WARNINGS ON;SET ANSI_NULL_DFLT_ON ON;SET CURSOR_CLOSE_ON_COMMIT ON;SET QUOTED_IDENTIFIER ON;USE [pgloader]

where pgloader is the mssql db name I used.

According to the Azure SQL docs, the USE command is not supported to switch to a different database:
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/use-transact-sql?view=sql-server-ver15

In Azure SQL Database, the database parameter can only refer to the current database. If a database other than the current 
database is provided, the USE statement does not switch between databases, and error code 40508 is returned.

• pgloader output you obtain

• data that is being loaded, if relevant
  N/A

• How the data is different from what you expected, if relevant
  I expect pgloader to be able to connect to Azure SQL

[martinwiboe]

I'm also seeing this issue

[kvallab2]

We are running 3.6.6 and still getting the same issue while connecting to Azure SQL. Is there any fix to this issue?

[drog]

Same issue with 3.6.7 and a Azure SQL Server 2012

$ export TDSDUMP=~/tmp/freetds.log
$ pgloader --debug --dry-run mssql_instance.load

logs

dbutil.c:76:msgno 40508: "USE statement is not supported to switch between databases. Use a new connection to connect to a different database."
dblib.c:5094:default_err_handler 0x12f8043a0, 16, 20018, -1, 0x10130e6b8, 0x0dblib.c:5060:dbdead(0x12f8043a0) [alive]
token.c:2514:tds_process_info() returning TDS_SUCCESS
token.c:570:processing result tokens.  marker is  fd(DONE)
token.c:2100:tds_process_end: more_results = 0
		was_cancelled = 0
		error = 1
		done_count_valid = 0
token.c:2119:                rows_affected = 0
token.c:2122:tds_process_end() state set to TDS_IDLE
util.c:179:Changed query state from READING to IDLE
util.c:83:logic error: cannot change query state from IDLE to PENDING
util.c:179:Changed query state from IDLE to IDLE
dblib.c:4882:dbsqlok() end status is 0 (FAIL)

[dimitri]

I believe the problem lies in the MS SQL driver that pgloader depends on. Specifically see:

https://github.com/archimag/cl-mssql/blob/045602a19a32254108f2b75871049293f49731eb/src/connection.lisp#L73

Could you please open an issue there and follow-up when we have a fix for it?

[bestekov]

Created the above issue. Though is there anything that can be done in the meantime to workaround this issue?

Although I also do not see any /tmp/pgloader/freetds.log file like the original report mentioned... so not sure if this is truly the cause of the issue?

I am running pgloader in a docker container.

I am just getting:

2022-11-10T05:05:26.180000Z ERROR mssql: Failed to connect to mssql at "xxx.database.windows.net" (port 1433) as user "xxx": %dbsqlexec fail

[bestekov]

Hmm... the maintainer of that dependency says they are not maintaining it anymore.... any other option for fixing Azure connectivity?

[michivi]

Stumbled upon the same issue ourselves. archimag/cl-mssql#10 fixes it in our case.

[jahangiranwari]

Here's an updated pgloader Docker image with the fix.

[geohernandez]

Hi jahangiranwari

I pulled this Docker image, however, when I tried to use pgloader, I got this error message:
pgloader --version
qemu-aarch64: Could not open '/lib/ld-linux-aarch64.so.1': No such file or directory

Any idea of how to solve, I am using Docker (Docker version 25.0.3, build 4debf41) on Win11.

Additionally, I created a Debian VM and got issues too.

[geohernandez]

Hi jahangiranwari

My colleague Esteban found the issue, apparently, you built the image on ARM (maybe Mac) and it was not compatible with x86, so basically it was recompiled using the pgloader exe from Dimitri and your code. In this link, we have the functional image for x86, all the credit to you.

docker pull esbalo/pgloader
docker run --rm -it esbalo/pgloader:1.0.0 pgloader --version