Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

cannot connect to azure postresql - Username should be in <username@hostname> format #6371

Closed
terchris opened this issue Mar 27, 2018 — with docs.microsoft.com · 13 comments

Comments

Copy link

@terchris terchris commented Mar 27, 2018 — with docs.microsoft.com

I'm trying to use your PostgreSQL for CKAN (ckan.org)
CKAN is using http://www.sqlalchemy.org/ for database connection.

A connection string looks like this:
engine = create_engine('postgresql://scott:tiger@myhost.postgres.database.azure.com:5432/mydatabase')
syntax is: postgresql://username:password@hostname:5432/databasename
When trying to connect to azure postgresql I get this error:

FATAL: Invalid Username specified. Please check the Username and retry connection. The Username should be in username@hostname format.

If I try to use the username format like this:
postgresql://scott@myhost:tiger@myhost.postgres.database.azure.com:5432/mydatabase

Then I get:
invalid port number: "tiger@myhost.postgres.database.azure.com:5432"

Is there a solution to this ?

Regards
Terje


Document Details

Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

@BryanTrach-MSFT

This comment has been minimized.

Copy link
Member

@BryanTrach-MSFT BryanTrach-MSFT commented Mar 28, 2018

@terchris Thanks for the feedback! We are currently investigating and will update you shortly.

@rachel-msft

This comment has been minimized.

Copy link
Contributor

@rachel-msft rachel-msft commented Mar 28, 2018

Hi Terje,

Since that tool requests a url syntax as follows: postgresql://username:password@hostname:5432/databasename

You would need to escape the '@' in our username@hostname format, because that url syntax expects @ to signify the start of the full hostname.

Please try this format: postgresql://scott%40myhost:tiger@myhost.postgres.database.azure.com:5432/mydatabase
Here I'm using the %40 escape for @.

@Mike-Ubezzi-MSFT

This comment has been minimized.

Copy link
Contributor

@Mike-Ubezzi-MSFT Mike-Ubezzi-MSFT commented Mar 29, 2018

@terchris Can you review the comment by @rachel-msft and let us know if this issue is resolved.
Thank you,
Mike

@Mike-Ubezzi-MSFT

This comment has been minimized.

Copy link
Contributor

@Mike-Ubezzi-MSFT Mike-Ubezzi-MSFT commented Apr 3, 2018

@terchris When you have a chance to test, please let us know if this resolves your connectivity issue.

@Mike-Ubezzi-MSFT

This comment has been minimized.

Copy link
Contributor

@Mike-Ubezzi-MSFT Mike-Ubezzi-MSFT commented Apr 6, 2018

@terchris We will now proceed to close this thread. If there are further questions regarding this matter, please reopen it and we will gladly continue the discussion.

@sgandon

This comment has been minimized.

Copy link

@sgandon sgandon commented Nov 21, 2018

We have tried to escape with %40 and \ and the jdbc driver fails to connect, any idea to solve this please ?

@rachel-msft

This comment has been minimized.

Copy link
Contributor

@rachel-msft rachel-msft commented Nov 27, 2018

Hi @sgandon, what is the error message you see?

You said you are using %40 and . Where are you using \ ?

@sgandon

This comment has been minimized.

Copy link

@sgandon sgandon commented Nov 28, 2018

Hi Rachel, thanks for the quick answer, in fact @ made it work.

@rachel-msft

This comment has been minimized.

Copy link
Contributor

@rachel-msft rachel-msft commented Nov 28, 2018

Thanks for letting us know

@etiennecelery

This comment has been minimized.

Copy link

@etiennecelery etiennecelery commented Aug 13, 2019

Hi. I tried everything I could think off, including escaping the @ character with %40. I am using sqlalchemy to connect to the database, and I am not able to connect, as the error says "The Username should be in username@hostname format."
My password has "/" characters and my hostname is similar to "sql-rs-rd423", so it has the "-" character.
If I try to connect with the following URL I get the format error (I edited the password and hostname to a similar-styled one):

postgresql://username:sDsO/43J/TU2%40sql-rs-rd423@sql-rs-rd423.database.windows.net:5432/sandbox

Any help will be very much appreciated

@PRMerger19 PRMerger19 added the Pri2 label Aug 13, 2019
@rachel-msft

This comment has been minimized.

Copy link
Contributor

@rachel-msft rachel-msft commented Aug 13, 2019

Hi @etiennecelery. There a couple things here.

  1. It looks like you moved the %40servername lower down in the uri. It is part of the username, before the password. Could you try postgresql://username%40sql-rs-rd423:sDsO/43J/TU2@sql-rs-rd423.database.windows.net:5432/sandbox

  2. Your password has "/". Can you change to a password that doesn't use that character? The uri format will think that you are referring to a port number.
    If you don't want to change it, you can try doing a uri encoding on the password. Some coding languages have functions that will do this for you.

@etiennecelery

This comment has been minimized.

Copy link

@etiennecelery etiennecelery commented Aug 13, 2019

Hi Rachel and thank you for your reply.
I tried your suggestion unsuccessfully postgresql://username%40sql-rs-rd423:{uri_password}@sql-rs-rd423.database.windows.net:5432/sandbox, with the following error: (psycopg2.OperationalError) FATAL: The server name you tried cannot be found. Please use the correct name and retry. Please check your server name sql-rs-rd423

It is weird because I am able to connect using pyodbc module, so I can confirm that the database exists and that the login credentials are ok. However, when trying to connect via sqlalchemy or psycopg2, I do either get a format error in the username (username@hostname) or I get this "check your server name" error.

I also tried uri formating the password, but the error messages prevail. It has been very frustrating to connect to the database, so any suggestions you have would be greatly appreciated.
Thank you!

@rachel-msft

This comment has been minimized.

Copy link
Contributor

@rachel-msft rachel-msft commented Aug 19, 2019

@etiennecelery, can you confirm that you are connecting to a Postgres server? In my previous response, I only edited the url you had sent to arrange the components correctly. But now I notice you are currently using .database.windows.net which would be for Azure SQL DB. If this is a Postgres database it should be .postgres.database.azure.com

To be sure, I recommend going to the Azure Portal and selecting the Postgres server. On the Overview page you will see your server's fully defined host name.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
7 participants
You can’t perform that action at this time.