Adaptive Server connection failed

[NidhiGoyal1403]

Issue report

Hi ,

I am trying to connect Azure managed sql server with postgres using tds_fdw, but unable to connect. Below is the error am getting.

NOTICE: DB-Library notice: Msg #: 40532, Msg state: 1, Msg: Cannot open server "1433" requested by the login. The login failed., Server: 1433, Process: , Line: 0, Level: 20 ERROR: DB-Library error: DB #: 20002, DB Msg: Adaptive Server connection failed (servername:1433), OS #: 0, OS Msg: Success, Level: 9

Also , when i trying to connect the same server using tsql, it works fine.

root@a2bb186db7b1:/etc/freetds# tsql -H servername -U username -P password -p 1433 locale is "en_US.utf8" locale charset is "UTF-8" using default charset "UTF-8" 1>

any suggestions are welcome.

Operating system

On recent GNU/Linux distributions, you can provide the content of the file /etc/os-release root@a2bb186db7b1:/etc/freetds# cat /etc/os-release PRETTY_NAME="Debian GNU/Linux 10 (buster)" NAME="Debian GNU/Linux" VERSION_ID="10" VERSION="10 (buster)" VERSION_CODENAME=buster ID=debian HOME_URL="https://www.debian.org/" SUPPORT_URL="https://www.debian.org/support" BUG_REPORT_URL="https://bugs.debian.org/"

Replace this with the content

Version of tds_fdw

From a psql session, paste the outputs of running \dx test_middle=# \dx List of installed extensions Name | Version | Schema | Description ---------+---------+------------+----------------------------------------------------------------------------------- plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language tds_fdw | 2.0.3 | public | Foreign data wrapper for querying a TDS database (Sybase or Microsoft SQL Server)

If you built the package from Git sources, also paste the output of running git log --source -n 1 on your git clone from a console

Replace this with the output

Version of PostgreSQL

From a psql session, paste the output of running SELECT version();

test_middle=# select version(); version

PostgreSQL 13.1 (Debian 13.1-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

Version of FreeTDS

root@a2bb186db7b1:/etc/freetds# dpkg -l|grep freetds ii freetds-bin 1.00.104-1+deb10u1 amd64 FreeTDS command-line utilities ii freetds-common 1.00.104-1+deb10u1 all configuration files for FreeTDS SQL client libraries ii freetds-dev 1.00.104-1+deb10u1 amd64 MS SQL and Sybase client library (static libs and headers)

## Logs

_Please capture the logs when the error you are reporting is happening, as well as commands with their outputs if you are reporting a problem build or installing_

_For problems using tds_fdw on PostgreSQL how to do it will depend on your system, but if your PostgreSQL is installed on GNU/Linux, you will want to use `tail -f` with the log of the PostgreSQL cluster_

_For MSSQL you will need to use the SQL Server Audit Log_

Replace this with the commands and outputs

## Sentences, data structures, data

_This will depend on the exact problem you are having and data privacy restrictions_

_However the more data you provide, the more likely we will be able to help_

_As a bare minimum, you should provide_

* _The SQL sentence that is failing_
* _The data structure on the PostgreSQL side and on the MSSQL side_

Replace this with the SQL sentences, data structures, etc

[juliogonzalez]

As the template says:

## Sentences, data structures, data

_This will depend on the exact problem you are having and data privacy restrictions_

Please provide that information. To me it looks like you are providing the port as hostname.

Our Continuous Integration uses an Azure manage MSSQL, and works fine: https://github.com/tds-fdw/tds_fdw/blob/master/tests/tests/postgresql/001_create_server.sql

[NidhiGoyal1403]

I am creating foreign server with below command :

CREATE SERVER mssql_fdw_1 FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername 'sqlmi-common-databases-d-eu2.f106ca4afa6a.database.windows.net', tds_version '7.0', database 'Target', port '1433);

while tryinng to connect to foreign table, below is the output: select * from t3; NOTICE: DB-Library notice: Msg #: 40532, Msg state: 1, Msg: Cannot open server "1433" requested by the login. The login failed., Server: 1433, Process: , Line: 0, Level: 20 ERROR: DB-Library error: DB #: 20002, DB Msg: Adaptive Server connection failed (sqlmi-common-databases-d-eu2.f106ca4afa6a.database.windows.net:1433), OS #: 0, OS Msg: Success, Level: 9

[juliogonzalez]

Well, one problem I see is that port is missing a ', so I wonder if PostgreSQL (or tds_fdw) are probably unable to parse the options and consider 1433 is the server.

[NidhiGoyal1403]

Corrected this ...but the issue remains same:

test_middle=# CREATE SERVER mssql_fdw_2 FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername 'sqlmi-common-databases-d-eu2.f106ca4afa6a.database.windows.net', port '1433', tds_version '7.1', database 'Target', msg_handler 'notice'); CREATE SERVER test_middle=# CREATE USER MAPPING FOR postgres SERVER mssql_fdw_2 OPTIONS (username 'nidhi', password ''); CREATE USER MAPPING test_middle=# create FOREIGN TABLE t1( i int) SERVER mssql_fdw_2 OPTIONS ( table_name 'dbo.t2'); CREATE FOREIGN TABLE test_middle=# select from t1; DEBUG: tds_fdw: Using remote estimate DEBUG: tds_fdw: Getting query DEBUG: tds_fdw: Value of query is SELECT [i] FROM dbo.t2 DEBUG: tds_fdw: Initiating DB-Library DEBUG: tds_fdw: Getting login structure DEBUG: tds_fdw: Setting login user to nidhi DEBUG: tds_fdw: Setting login password to DEBUG: tds_fdw: Setting login tds version to 7.1 DEBUG: tds_fdw: Setting login database to Target DEBUG: tds_fdw: Connection string is sqlmi-common-databases-d-eu2.f106ca4afa6a.database.windows.net:1433 DEBUG: tds_fdw: Connecting to server NOTICE: DB-Library notice: Msg #: 40532, Msg state: 1, Msg: Cannot open server "1433" requested by the login. The login failed., Server: 1433, Process: , Line: 0, Level: 20 DEBUG: Failed to connect using connection string sqlmi-common-databases-d-eu2.f106ca4afa6a.database.windows.net:1433 with user nidhi ERROR: DB-Library error: DB #: 20002, DB Msg: Adaptive Server connection failed (sqlmi-common-databases-d-eu2.f106ca4afa6a.database.windows.net:1433), OS #: 0, OS Msg: Success, Level: 9 test_middle=#

[juliogonzalez]

While Cannot open server "1433" requested by the login. The login failed is not 100% right because the server is not 1433, the part The login failed is 100% true.

I had to review our code for testing, and I noticed: https://github.com/tds-fdw/tds_fdw/blob/master/tests/postgresql-tests.py#L76

And sure enough, if you read See https://learn.microsoft.com/en-us/azure/mysql/single-server/how-to-connection-string, then you will see:

{your_user}@{servername} = userID format for authentication correctly. If you only use the userID, the authentication will fail.

I can reproduce your problem if I use only the user without adding @ and the server name. As long as I change the user mapping including @ and the server name, things work.

You told:

Also , when i trying to connect the same server using tsql, it works fine.

root@a2bb186db7b1:/etc/freetds# tsql -H servername -U username -P password -p 1433 locale is "en_US.utf8" locale charset is "UTF-8" using default charset "UTF-8" 1>

But either you specified the correct username here, either tsql is doing some strange magic to automatically add what's missing if it detects you are using Azure.

[juliogonzalez]

Reviewing tds_fdw code I don't see it contains the string for the error, so I'd say Cannot open server "1433" requested by the login does not come from tds_fdw.

And as I see this same error at pymssql: https://github.com/pymssql/pymssql/issues/330, I think this confirm it does not come from tds_fdw, but from the outside (FreeTDS?)

[juliogonzalez]

And sure enough, if you read See https://learn.microsoft.com/en-us/azure/mysql/single-server/how-to-connection-string, then you will see:

Sorry, the link I gave you is for MySQL, not MSSQL, but still the fix is the same :-)

I will create a PR to clarify this in our doc.

[NidhiGoyal1403]

Hi Julio González Gil, Thanks for your input, i have tried your suggestion.........but the issue is still not resolved. PFB Logs and have a look.

test_middle=# select * from t1; DEBUG: tds_fdw: Using remote estimate DEBUG: tds_fdw: Getting query DEBUG: tds_fdw: Value of query is SELECT [i] FROM dbo.t2 DEBUG: tds_fdw: Initiating DB-Library DEBUG: tds_fdw: Getting login structure DEBUG: tds_fdw: Setting login user to nidhi@sqlmi-common-databases-d-eu2.f106ca4afa6a.database.windows.net DEBUG: tds_fdw: Setting login password to DEBUG: tds_fdw: Setting login tds version to 7.1 DEBUG: tds_fdw: Setting login database to Target DEBUG: tds_fdw: Connection string is sqlmi-common-databases-d-eu2.f106ca4afa6a.database.windows.net:1433 DEBUG: tds_fdw: Connecting to server NOTICE: DB-Library notice: Msg #: 18456, Msg state: 1, Msg: Login failed for user 'nidhi@sqlmi-common-databases-d-eu2.f106ca4afa6a.database.windows.net'., Server: sqlmi-common-databases-d-eu2.f106ca4afa6a.database.windows.net, Process: , Line: 1, Level: 14 DEBUG: Failed to connect using connection string sqlmi-common-databases-d-eu2.f106ca4afa6a.database.windows.net:1433 with user nidhi@sqlmi-common-databases-d-eu2.f106ca4afa6a.database.windows.net ERROR: DB-Library error: DB #: 20002, DB Msg: Adaptive Server connection failed (sqlmi-common-databases-d-eu2.f106ca4afa6a.database.windows.net:1433), OS #: 0, OS Msg: Success, Level: 9 test_middle=# \q postgres@85d2048143b6:~$ pexit -bash: pexit: command not found postgres@85d2048143b6:~$ exit logout root@85d2048143b6:/etc/freetds# tsql -I /etc/freetds/freetds.conf -S sqlmi-common-databases-d-eu2.f106ca4afa6a.database.windows.net -p 3342 -U nidhi -P locale is "en_US.utf8" locale charset is "UTF-8" using default charset "UTF-8" 1> ^C

[jcarnu]

IMHO -p 3342 is the right port you should use, does it ?

On one hand (foreign server) you use 1433 and on the other (tsql call) 3342. Does this solve the problem?

Le mer. 12 avr. 2023 à 15:55, NidhiGoyal1403 @.***> a écrit :

Hi Julio González Gil, Thanks for your input, i have tried your suggestion.........but the issue is still not resolved. PFB Logs and have a look.

test_middle=# select * from t1; DEBUG: tds_fdw: Using remote estimate DEBUG: tds_fdw: Getting query DEBUG: tds_fdw: Value of query is SELECT [i] FROM dbo.t2 DEBUG: tds_fdw: Initiating DB-Library DEBUG: tds_fdw: Getting login structure DEBUG: tds_fdw: Setting login user to @.* DEBUG: tds_fdw: Setting login password to *** DEBUG: tds_fdw: Setting login tds version to 7.1 DEBUG: tds_fdw: Setting login database to Target DEBUG: tds_fdw: Connection string is sqlmi-common-databases-d-eu2.f106ca4afa6a.database.windows.net:1433 DEBUG: tds_fdw: Connecting to server NOTICE: DB-Library notice: Msg #: 18456, Msg state: 1, Msg: Login failed for user ' @.'., Server: sqlmi-common-databases-d-eu2.f106ca4afa6a.database.windows.net, Process: , Line: 1, Level: 14 DEBUG: Failed to connect using connection string sqlmi-common-databases-d-eu2.f106ca4afa6a.database.windows.net:1433 with user @. ERROR: DB-Library error: DB #: 20002, DB Msg: Adaptive Server connection failed ( sqlmi-common-databases-d-eu2.f106ca4afa6a.database.windows.net:1433), OS

: 0, OS Msg: Success, Level: 9

test_middle=# \q @.:$ pexit -bash: pexit: command not found @.:$ exit logout @.*:/etc/freetds# tsql -I /etc/freetds/freetds.conf -S sqlmi-common-databases-d-eu2.f106ca4afa6a.database.windows.net -p 3342 -U nidhi -P *** locale is "en_US.utf8" locale charset is "UTF-8" using default charset "UTF-8" 1> ^C

— Reply to this email directly, view it on GitHub https://github.com/tds-fdw/tds_fdw/issues/331#issuecomment-1505322762, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAA5UHY5LAD66DQQMBIFAPLXA2X6NANCNFSM6AAAAAAWKKEA6Q . You are receiving this because you are subscribed to this thread.Message ID: @.***>

-- Jean-Christophe Arnu

[juliogonzalez]

IMHO -p 3342 is the right port you should use, does it ? On one hand (foreign server) you use 1433 and on the other (tsql call) 3342. Does this solve the problem?

True, but at the initial bug report 1433 was used for both tds_fdw and tsql.

In any case it's, at least, the default port for Azure SQL, and worked fine for me reusing the sentences from the initial report, as soon as I added server hostname to the user.

So I'd say in this case the problem is on the Azulre SQL server.

In fact the calls to tsql from the initial report or the last comment do not try to query the table, and doesn't even connect to a specific database. While the CREATE SERVER is using the database Target

Personally, I'd start having a look at the MSSQL logs to check what's going on exactly. It could be the user does not access to the database, or something similar. As already demonstrated, seems those error messages are not 100% correct, but it doesn't seem they come from tds_fdw but rather from FreeTDS.

I guess https://learn.microsoft.com/en-us/azure/azure-sql/database/auditing-overview?view=azuresql should be used (I managed Microsoft SQL in the past, but never Azure SQL)

[juliogonzalez]

@NidhiGoyal1403 in this case should call tsql with -D Target as well, to replicate exactly what he's doing with tds_fdw.

This smells more and more like a problem with rights to access the database called Target, or that database just not existing at all.

[NidhiGoyal1403]

Tried connection to the database Target and it works fine: root@85d2048143b6:/etc/freetds# tsql -S sqlmi-common-databases-d-eu2.f106ca4afa6a.database.windows.net -U nidhi -P **** -D Target locale is "en_US.utf8" locale charset is "UTF-8" using default charset "UTF-8" Setting Target as default database in login packet 1> select from dbo.t2; 2> go i 1 (1 row affected) 1>

[NidhiGoyal1403]

Although when i try to pass these parameter to freetds.conf file and try to access from there, tsql is unable to connect :

root@85d2048143b6:/etc/freetds# cat freetds.conf |tail -10

[Myserver] host = sqlmi-common-databases-d-eu2.f106ca4afa6a.database.windows.net port = 1433 tds_version = 7.4

root@85d2048143b6:/etc/freetds# tsql -S Myserver -U nidhi -P nidhi locale is "en_US.utf8" locale charset is "UTF-8" using default charset "UTF-8" 5Error 20002 (severity 9): Adaptive Server connection failed OS error 110, "Connection timed out" There was a problem connecting to the server root@85d2048143b6:/etc/freetds#

Below are the logs from freetds:

tls.c:199:GNUTLS: level 3: ASSERT: ../../lib/record.c[_gnutls_send_tlen_int]:466 login.c:534:login packet rejected query.c:3797:tds_disconnect() util.c:165:Changed query state from IDLE to DEAD util.c:322:tdserror(0x55de77f70680, 0x55de789f61b0, 20002, 0) util.c:358:tdserror: client library not called because either tds_ctx (0x55de77f70680) or tds_ctx->err_handler is NULL util.c:375:tdserror: returning TDS_INT_CANCEL(2) util.c:322:tdserror(0x55de789f6170, 0x55de789f61b0, 20002, 110) util.c:352:tdserror: client library returned TDS_INT_CANCEL(2) util.c:375:tdserror: returning TDS_INT_CANCEL(2) mem.c:653:tds_free_all_results() tls.c:199:GNUTLS: level 5: REC[0x55de78a0e960]: Start of epoch cleanup tls.c:199:GNUTLS: level 5: REC[0x55de78a0e960]: End of epoch cleanup tls.c:199:GNUTLS: level 5: REC[0x55de78a0e960]: Epoch #1 freed root@85d2048143b6:/tmp#

[juliogonzalez]

root@85d2048143b6:/etc/freetds# tsql -S Myserver -U nidhi -P nidhi locale is "en_US.utf8" locale charset is "UTF-8" using default charset "UTF-8" 5Error 20002 (severity 9): Adaptive Server connection failed OS error 110, "Connection timed out" There was a problem connecting to the server

Oddly enough, this can also mean an authentication problem. Saw it yesterday.

Below are the logs from freetds:

tls.c:199:GNUTLS: level 3: ASSERT: ../../lib/record.c[_gnutls_send_tlen_int]:466 login.c:534:login packet rejected query.c:3797:tds_disconnect() util.c:165:Changed query state from IDLE to DEAD util.c:322:tdserror(0x55de77f70680, 0x55de789f61b0, 20002, 0) util.c:358:tdserror: client library not called because either tds_ctx (0x55de77f70680) or tds_ctx->err_handler is NULL util.c:375:tdserror: returning TDS_INT_CANCEL(2) util.c:322:tdserror(0x55de789f6170, 0x55de789f61b0, 20002, 110) util.c:352:tdserror: client library returned TDS_INT_CANCEL(2) util.c:375:tdserror: returning TDS_INT_CANCEL(2) mem.c:653:tds_free_all_results() tls.c:199:GNUTLS: level 5: REC[0x55de78a0e960]: Start of epoch cleanup tls.c:199:GNUTLS: level 5: REC[0x55de78a0e960]: End of epoch cleanup tls.c:199:GNUTLS: level 5: REC[0x55de78a0e960]: Epoch #1 freed

And here you see it:

login.c:534:login packet rejected

But again, with tsql -S Myserver -U nidhi -P nidhi you are not specifying a database either. You should always connect to Target.

Not only that, but in some places you use 7.1 and in others 7.4 as tds_version (or in other cases not specifying a version). I know that 7.1 works, at least for our Azure SQL instance, as that's what we use. Stick to that.

Then run all your tests again, using the same credentials, hostname, version, database, etc.

If at that point things are still no working, then I'd say you need to check the Azure SQL logs to understand why the login is failing exactly.

At least in my case, I am unable to reproduce the issue after I started adding @ourserver to the username for the USER MAPPING.