"User_name" is not a babelfish user

Question

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;

Answer 1

The (sparse) documentation says:

You can use CREATE LOGIN to create a new Babelfish login with access to all databases. Babelfish logins are implemented as PostgreSQL login roles of the same name.

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.

Answer 2

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.