Closed khanna43 closed 2 years ago
How was babelfish_user created? If created in PG with CREATE USER, it is not possible to use this to connect to the TDS port. You can only connect to the TDS port with a login that was created in T-SQL with CREATE LOGIN. It is true that that will be visible in the PG catalogs as a PG user, but that is not the same as when that user was created through PG directly. Babelfish internally maintains additional metadata required to connect to TDS, and that metadata is not existing when the user was created directly through PG.
Thanks Rob for the update. Yes I created the babelfish_user using psql create user command. I did the below steps as mentioned initial setup section ( as mentioned in Single vs. multiple database setups - Babelfish for PostgreSQL (babelfishpg.org) https://babelfishpg.org/docs/installation/single-multiple/)
But How can I create TSQL Login User when I am unable to connect to postgreSQL instance using sqlcmd. Please advise.
I removed the bablefish user that I created using pgsql and now tried to connect with an already existing user present in bablefish postgreSQL db like master_dbo but sill I am getting the same error.
@. ~]# sqlcmd -S 192.168.56.11,1434 -U master_dbo Password: Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Client unable to establish connection because an error was encountered during handshakes before login. Common causes include client attempting to connect to an unsupported version of SQL Server, server too busy to accept new connections or a resource limitation (memory or maximum allowed connections) on the server.. Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: Error code 0x2746. Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Client unable to establish connection. Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Client unable to establish connection due to prelogin failure. @. ~]#
postgres=# \du List of roles Role name | Attributes | Member of -----------------+------------------------------------------------------------+------------------------- master_db_owner | Cannot login | {master_guest} master_dbo | Cannot login | {master_db_owner} master_guest | Cannot login | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} sysadmin | Create role, Create DB, Cannot login | {master_dbo,tempdb_dbo} tempdb_db_owner | Cannot login | {tempdb_guest} tempdb_dbo | Cannot login | {tempdb_db_owner} tempdb_guest | Cannot login | {}
########### Step performed in initial setup
CREATE USER babelfish_user WITH CREATEDB
CREATEROLE PASSWORD '
Thanks and Regards Sachin Khanna
On Mon, Jan 31, 2022 at 5:17 PM Rob Verschoor @.***> wrote:
How was babelfish_user created? If created in PG with CREATE USER, it is not possible to use this to connect to the TDS port. You can only connect to the TDS port with a login that was created in T-SQL with CREATE LOGIN. It is true that that will be visible in the PG catalogs as a PG user, but that is not the same as when that user was created through PG directly. Babelfish internally maintains additional metadata required to connect to TDS, and that metadata is not existing when the user was created directly through PG.
— Reply to this email directly, view it on GitHub https://github.com/babelfish-for-postgresql/babelfish_extensions/issues/65#issuecomment-1025655597, or unsubscribe https://github.com/notifications/unsubscribe-auth/AHZ5SVJJPFXH65POKDG6CILUYZZGDANCNFSM5NARST4Q . Triage notifications on the go with GitHub Mobile for iOS https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Android https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.
You are receiving this because you authored the thread.Message ID: @.*** .com>
Once the babelfish database is created, connect to the TDS port with babelfish_user and run CREATE LOGIN mylogin WITH PASSWORD='myS3cret'. You will need to give this login sysadmin role as well: ALTER SERVER ROLE sysadmin ADD MEMBER mylogin
Thanks Rob for the quick response.
Understood you point and now I tried the below steps now but I am still getting 2 issue
1) when calling SYS.INITIALIZE_BABELFISH('babelfish_user'); it is faling 2) also although the "ALTER SYSTEM SET babelfishpg_tsql.database_name = 'demo';" was successful but I getting error "could not find default database for user "babelfish_user""
CREATE USER babelfish_user WITH CREATEDB CREATEROLE PASSWORD 'test123' INHERIT;
DROP DATABASE IF EXISTS demo;
t (1 row) ALTER DATABASE demo SET babelfishpg_tsql.migration_mode = 'single-db'; ALTER DATABASE
postgres=# \c demo You are now connected to database "demo" as user "postgres". demo=# demo=# demo=# CREATE EXTENSION IF NOT EXISTS "babelfishpg_tds" CASCADE; psql:setup.sql:7: NOTICE: installing required extension "babelfishpg_tsql" psql:setup.sql:7: NOTICE: installing required extension "uuid-ossp" psql:setup.sql:7: NOTICE: installing required extension "babelfishpg_common" CREATE EXTENSION
demo=# CALL SYS.INITIALIZE_BABELFISH('babelfish_user'); ERROR: Could not initialize babelfish in current database: Reserved role sysadmin exists. Please rename or drop existing role and try again CONTEXT: PL/pgSQL function sys.initialize_babelfish(character varying) line 18 at RAISE demo=#
tsql -S bable -U babelfish_user Password: locale is "en_IN.UTF-8" locale charset is "UTF-8" using default charset "UTF-8" Msg 33557097 (severity 16, state 1) from BABELFISH Line 1:
Thanks and Regards Sachin Khanna
On Tue, Feb 1, 2022 at 3:30 PM Rob Verschoor @.***> wrote:
Once the babelfish database is created, connect to the TDS port with babelfish_user and run CREATE LOGIN mylogin WITH PASSWORD='myS3cret'. You will need to give this login sysadmin role as well: ALTER SERVER ROLE sysadmin ADD MEMBER mylogin
— Reply to this email directly, view it on GitHub https://github.com/babelfish-for-postgresql/babelfish_extensions/issues/65#issuecomment-1026662434, or unsubscribe https://github.com/notifications/unsubscribe-auth/AHZ5SVNOWR4Y7S7U4JGDYOLUY6VKTANCNFSM5NARST4Q . Triage notifications on the go with GitHub Mobile for iOS https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Android https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.
You are receiving this because you authored the thread.Message ID: @.*** .com>
Further when I am specifying the default database name in freetds.conf file.
[bable] host = 192.168.56.11 port = 1433 database = demo tds version = 7.3
I am getting this error .
tsql -S bable -U babelfish_user Password: locale is "en_IN.UTF-8" locale charset is "UTF-8" using default charset "UTF-8" Msg 3701 (severity 11, state 1) from BABELFISH Line 1:
Thanks and Regards Sachin Khanna
On Wed, Feb 2, 2022 at 2:03 PM Sachin Khanna @.***> wrote:
Thanks Rob for the quick response.
Understood you point and now I tried the below steps now but I am still getting 2 issue
1) when calling SYS.INITIALIZE_BABELFISH('babelfish_user'); it is faling 2) also although the "ALTER SYSTEM SET babelfishpg_tsql.database_name = 'demo';" was successful but I getting error "could not find default database for user "babelfish_user""
CREATE USER babelfish_user WITH CREATEDB CREATEROLE PASSWORD 'test123' INHERIT;
DROP DATABASE IF EXISTS demo;
postgres=# ALTER SYSTEM SET babelfishpg_tsql.database_name = 'demo'; ALTER SYSTEM postgres=# SELECT pg_reload_conf(); pg_reload_conf
t (1 row) ALTER DATABASE demo SET babelfishpg_tsql.migration_mode = 'single-db'; ALTER DATABASE
postgres=# \c demo You are now connected to database "demo" as user "postgres". demo=# demo=# demo=# CREATE EXTENSION IF NOT EXISTS "babelfishpg_tds" CASCADE; psql:setup.sql:7: NOTICE: installing required extension "babelfishpg_tsql" psql:setup.sql:7: NOTICE: installing required extension "uuid-ossp" psql:setup.sql:7: NOTICE: installing required extension "babelfishpg_common" CREATE EXTENSION
demo=# CALL SYS.INITIALIZE_BABELFISH('babelfish_user'); ERROR: Could not initialize babelfish in current database: Reserved role sysadmin exists. Please rename or drop existing role and try again CONTEXT: PL/pgSQL function sys.initialize_babelfish(character varying) line 18 at RAISE demo=#
tsql -S bable -U babelfish_user Password: locale is "en_IN.UTF-8" locale charset is "UTF-8" using default charset "UTF-8" Msg 33557097 (severity 16, state 1) from BABELFISH Line 1:
- "could not find default database for user "babelfish_user""* Error 20002 (severity 9): Adaptive Server connection failed There was a problem connecting to the server @.*** ~]#
Thanks and Regards Sachin Khanna
91 9049522511
On Tue, Feb 1, 2022 at 3:30 PM Rob Verschoor @.***> wrote:
Once the babelfish database is created, connect to the TDS port with babelfish_user and run CREATE LOGIN mylogin WITH PASSWORD='myS3cret'. You will need to give this login sysadmin role as well: ALTER SERVER ROLE sysadmin ADD MEMBER mylogin
— Reply to this email directly, view it on GitHub https://github.com/babelfish-for-postgresql/babelfish_extensions/issues/65#issuecomment-1026662434, or unsubscribe https://github.com/notifications/unsubscribe-auth/AHZ5SVNOWR4Y7S7U4JGDYOLUY6VKTANCNFSM5NARST4Q . Triage notifications on the go with GitHub Mobile for iOS https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Android https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.
You are receiving this because you authored the thread.Message ID: <babelfish-for-postgresql/babelfish_extensions/issues/65/1026662434@ github.com>
Relevant logs:
2022-01-28 14:13:23.804 IST [6268] LOG: could not bind IPv4 address "0.0.0.0": Address already in use 2022-01-28 14:13:23.804 IST [6268] HINT: Is another postmaster already running on port 1433? If not, wait a few seconds and retry. 2022-01-28 14:13:23.804 IST [6268] WARNING: could not create listen socket for "0.0.0.0"
It looks like something is already listening to port 1433. That's why PG is not able to create the listening socket on 1433. Is it possible that the you are running another Babel server or SQL Server on the same instance which is also listening to 1433?
Thanks Kuntal and Rob. Server is correctly listening on port 1433 and 5432 after starting we can verify the logs below
Also now I am able to connect the bablefish postgresql using sqlcmd after the function CALL SYS.INITIALIZE_BABELFISH('babelfish_user'); successfully completed. I was able to do this after giving GRANT ALL ON SCHEMA sys to babelfish_user; This step is missing in the bablefish documentation.
Now my question is no usage of this postgreSQL bablefish flavour. Although in this news blog published on Oct 28, 2021., says that Announcing Open Source Babelfish for PostgreSQL: An Accelerator for SQL Server Migration · Babelfish (babelfishpg.org) https://babelfishpg.org/blog/releases/2021/10/babelfish-launch/
"Babelfish is available under the open source Apache 2.0 and PostgreSQL licenses and builds on top of existing PostgreSQL. The licensing allows you to freely use, modify, distribute, and sell your own products that include Apache 2.0 licensed or PostgreSQL licensed software."
But the select version on postgreSQL is showing Copyright (c) Amazon Web Service
Is it a free to use / modify version or is it an AWS copy right. ?
select @@VERSION 3> GO version
Babelfish for PostgreSQL with SQL Server Compatibility - 12.0.2000.8 Jan 28 2022 09:49:34 Copyright (c) Amazon Web Services PostgreSQL 13.4 Babelfish for PostgreSQL on x86_64-pc-linux-gnu
#####################################################
t (1 row)
babelfish_db=# CALL SYS.INITIALIZE_BABELFISH('babelfish_user'); CALL babelfish_db=# \q
2022-02-07 13:08:10.874 IST [29001] LOG: listening on IPv4 address "0.0.0.0", port 5432 2022-02-07 13:08:10.874 IST [29001] LOG: listening on IPv6 address "::", port 5432 2022-02-07 13:08:10.885 IST [29001] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2022-02-07 13:08:10.893 IST [29001] LOG: listening on IPv4 address "0.0.0.0", port 1433 2022-02-07 13:08:10.895 IST [29001] LOG: listening on IPv6 address "::", port 1433 2022-02-07 13:08:10.912 IST [29002] LOG: database system was shut down at 2022-02-07 13:05:04 IST 2022-02-07 13:08:10.916 IST [29001] LOG: database system is ready to accept connections
Thanks and Regards Sachin Khanna
On Thu, Feb 3, 2022 at 8:03 PM Kuntal Ghosh @.***> wrote:
Relevant logs:
2022-01-28 14:13:23.804 IST [6268] LOG: could not bind IPv4 address "0.0.0.0": Address already in use 2022-01-28 14:13:23.804 IST [6268] HINT: Is another postmaster already running on port 1433? If not, wait a few seconds and retry. 2022-01-28 14:13:23.804 IST [6268] WARNING: could not create listen socket for "0.0.0.0"
It looks like something is already listening to port 1433. That's why PG is not able to create the listening socket on 1433. Is it possible that the you are running another Babel server or SQL Server on the same instance which is also listening to 1433?
— Reply to this email directly, view it on GitHub https://github.com/babelfish-for-postgresql/babelfish_extensions/issues/65#issuecomment-1029050647, or unsubscribe https://github.com/notifications/unsubscribe-auth/AHZ5SVPTH2M4RENTY2R4LE3UZKG5FANCNFSM5NARST4Q . Triage notifications on the go with GitHub Mobile for iOS https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Android https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.
You are receiving this because you authored the thread.Message ID: @.*** .com>
On the "missing step": it is actually in the documentation. Please see https://babelfishpg.org/docs/installation/single-multiple/ and https://github.com/babelfish-for-postgresql/babelfish_extensions/tree/BABEL_1_X_DEV/contrib
Also now I am able to connect the bablefish postgresql using sqlcmd after the function CALL SYS.INITIALIZE_BABELFISH('babelfish_user'); successfully completed. I was able to do this after giving GRANT ALL ON SCHEMA sys to babelfish_user; @khanna43 Hi~ I meet the same problem when I tried the function [CALL SYS.INITIALIZE_BABELFISH('babelfish_user');] could you please share with me how did you fix that?
@khanna43: Is this still an issue?
For my issue, it was fixed by ensuring the Db port numbers 5432 and 1433 were not used by any other db instance.
Rest all config were the same.
Thanks and Regards Sachin Khanna
On Fri, Jun 17, 2022 at 3:17 PM buck1593 @.***> wrote:
Also now I am able to connect the bablefish postgresql using sqlcmd after the function CALL SYS.INITIALIZE_BABELFISH('babelfish_user'); successfully completed. I was able to do this after giving GRANT ALL ON SCHEMA sys to babelfish_user; @khanna43 https://github.com/khanna43 Hi~ I meet the same problem when I tried the function [CALL SYS.INITIALIZE_BABELFISH('babelfish_user');] could you please share with me how did you fix that?
— Reply to this email directly, view it on GitHub https://github.com/babelfish-for-postgresql/babelfish_extensions/issues/65#issuecomment-1158703070, or unsubscribe https://github.com/notifications/unsubscribe-auth/AHZ5SVP6CIA5UUYNPO5FUMTVPRCUTANCNFSM5NARST4Q . You are receiving this because you were mentioned.Message ID: @.*** .com>
Contact Details
khanna.43@gmail.com
What's the question?
I am getting this below error while connecting to Babelfish postgreSQL database using sqlcmd. I am able to make connection with the database using pgsql. I tried connecting from SSMS also but is failing with error ( A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) (.Net SqlClient Data Provider))
Not sure if there is any mistake in Setup Any help is really appreciated in this regard.
sqlcmd -S tcp:192.168.56.11,1433 -U babelfish_user Password: Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Client unable to establish connection because an error was encountered during handshakes before login. Common causes include client attempting to connect to an unsupported version of SQL Server, server too busy to accept new connections or a resource limitation (memory or maximum allowed connections) on the server.. Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: Error code 0x2746. Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Client unable to establish connection. Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Client unable to establish connection due to prelogin failure. [root@Rhel7VM1 ~]# [root@Rhel7VM1 ~]#
/postgres/bin/psql -h 192.168.56.11 -p 1433 psql (11.2, server 13.4 Babelfish for PostgreSQL) WARNING: psql major version 11, server major version 13. Some psql features might not work. Type "help" for help.
postgres=#
Also SQLCMD is working fine when making connection to MS SQL Server databsae running on Docker
sqlcmd -S tcp:192.168.29.88,1433 -U SA Password: 1> 2> 3>
Checked the telnet to the Db server and is showing the expected behavior as per documentation https://babelfishpg.org/docs/faq/troubleshooting/
telnet 192.168.56.11 1433 Trying 192.168.56.11... Connected to 192.168.56.11. Escape character is '^]'.
Connection closed by foreign host. [root@Rhel7VM1 ~]#
Below is Bablefish postgreSQL database is setup on Ubuntu Host
postgres@sachin-VirtualBox1:~$ /usr/local/pgsql/bin/psql -p 1433 psql (13.4 Babelfish for PostgreSQL) Type "help" for help.
postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------------+----------+---------+-------+----------------------------------- demo | babelfish_user | UTF8 | en_IN | en_IN | postgres | babelfish_user | UTF8 | en_IN | en_IN | =Tc/babelfish_user + | | | | | babelfish_user=CTc/babelfish_user+ | | | | | sysadmin=CTc*/babelfish_user + | | | | | master_dbo=CTc/sysadmin + | | | | | tempdb_dbo=CTc/sysadmin template0 | postgres | UTF8 | en_IN | en_IN | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_IN | en_IN | =c/postgres + | | | | | postgres=CTc/postgres (4 rows)
postgres=# select from pg_extensions; ERROR: relation "pg_extensions" does not exist LINE 1: select from pg_extensions; ^ postgres=# select * from pg_extension; oid | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition -------+--------------------+----------+--------------+----------------+------------+---------------------------+--------------- 13434 | plpgsql | 10 | 11 | f | 1.0 | | 16386 | uuid-ossp | 10 | 2200 | t | 1.1 | | 16397 | babelfishpg_common | 10 | 2200 | t | 1.0.0 | | 17608 | babelfishpg_tsql | 10 | 2200 | t | 1.0.0 | {18146,18178,18156,18187} | {"","","",""} 18274 | babelfishpg_tds | 10 | 2200 | t | 1.0.0 | | (5 rows)
On every attempt to try to connect to the database we are getting this message in the database log. LOG: invalid length of startup packet
Relevant log output or information
Code of Conduct