I have created role under postgresql using PgAdmin.
CREATE ROLE user_name LOGIN PASSWORD 'xyz';
I’m able to login to server using pgAdmin using above credentials.
But when I try to connect from MSSQL Server (Babelfish) getting an following error:
But when I query using postgres user login I get the role details in babelfish.
SELECT * FROM pg_user;
2
Answers
The (sparse) documentation says:
That could be more explicit, but you have to create the login in a TDS connection, not while you are connected with the PostgreSQL protocol.
For background: this is by design. You can only connect to the TDS port with a PG user that was created through CREATE LOGIN in T-SQL. A PG user that was created in PG with CREATE ROLE (or USER) cannot connect to the TDS port. This is because additional metadata is stored for T-SQL logins which is not present for a PG-created user.