prisma: [aws rds mysql] Error: P1001 Can't reach database server using ssl connect

Bug description

Running prisma introspect returns the following error:

Error: P1000

Authentication failed against database server at `xxxxx.rds.amazonaws.com`, the provided database credentials for `USER` are not valid.

Please make sure to provide valid database credentials for the database server at `xxxxx.rds.amazonaws.com`.

Debugging Steps Taken

successfully ssl connected (and can query) using DBeaver (a SQL client tool) with the same credentials, host, port, database name, and CA cert.
successfully (non-ssl) connected to and ran prisma introspect against a Docker image of a mysql-employees sample database (found here).

I originally posted similar details in a Q&A Discussion post here.

How to reproduce

Attempt 1

move CA cert to ./prisma dir
add DATABASE_URL="mysql://USER:PASSWORD@xxxxx.rds.amazonaws.com:3306/DATABASE&sslcert=server-ca-cert.pem" to .env file.
prisma introspect

Attempt 2

create a pkcs12 file following instructions here
move pkcs12 file to ./prisma dir
add DATABASE_URL="mysql://USER:PASSWORD@xxxxx.rds.amazonaws.com:3306/DATABASE&sslidentity=keyStore.p12&sslpassword=SSLPASS&sslcert=server-ca-cert.pem" to .env file.
prisma introspect

Attempt 3

create a pkcs12 file using the exact steps listed in Issue #2676
add DATABASE_URL="mysql://USER:PASS@xxx.rds.amazonaws.com:3306/DATABASE&sslidentity=client-identity.p12&sslpassword=SSLPASS&sslcert=server-ca-cert.pem" to .env file.
prisma introspect

Attempt 4

add DATABASE_URL="mysql://USER:PASS@xxx.rds.amazonaws.com:3306/DATABASE to .env file.
prisma introspect

They all result in the same Error: P1000 Authentication failed error.

Expected behavior

A successful prisma introspect run.

❯ prisma introspect
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma

Introspecting based on datasource defined in prisma/schema.prisma …

✔ Introspected x models and wrote them into prisma/schema.prisma in xxxms
      
Run prisma generate to generate Prisma Client.

Prisma information

/prisma/schema.prisma:

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

.env:

# Environment variables declared in this file are automatically made available to Prisma.
# See the documentation for more detail: https://pris.ly/d/prisma-schema#using-environment-variables

# Prisma supports the native connection string format for PostgreSQL, MySQL and SQLite.
# See the documentation for all the connection string options: https://pris.ly/d/connection-strings

DATABASE_URL="mysql://USER:PASSWORD@xxxxx.rds.amazonaws.com:3306/DATABASE&sslcert=server-ca-cert.pem

Environment & setup

OS: Mac OS Catalina 10.15.6
Database: MySQL (Amazon RDS)
Node.js version: 14.15.4
Nestjs version: 7.5.4
Prisma version:

@prisma/cli          : 2.13.1
@prisma/client       : 2.14.0
Current platform     : darwin
Query Engine         : query-engine fcbc4bb2d306c86c28014f596b1e8c7980af8bd4 (at ../../../../../.nvm/versions/node/v14.15.4/lib/node_modules/@prisma/cli/node_modules/@prisma/engines/query-engine-darwin)
Migration Engine     : migration-engine-cli fcbc4bb2d306c86c28014f596b1e8c7980af8bd4 (at ../../../../../.nvm/versions/node/v14.15.4/lib/node_modules/@prisma/cli/node_modules/@prisma/engines/migration-engine-darwin)
Introspection Engine : introspection-core fcbc4bb2d306c86c28014f596b1e8c7980af8bd4 (at ../../../../../.nvm/versions/node/v14.15.4/lib/node_modules/@prisma/cli/node_modules/@prisma/engines/introspection-engine-darwin)
Format Binary        : prisma-fmt fcbc4bb2d306c86c28014f596b1e8c7980af8bd4 (at ../../../../../.nvm/versions/node/v14.15.4/lib/node_modules/@prisma/cli/node_modules/@prisma/engines/prisma-fmt-darwin)
Studio               : 0.329.0

About this issue

Original URL
State: closed
Created 3 years ago
Reactions: 1
Comments: 30 (7 by maintainers)

Most upvoted comments

@gevuong The default was wrong in the docs and has been changed in the version 2.17.0. What happened here is that most of the cloud database providers will not give you a valid and signed certificate due to you not having a public hostname for the server. Now, originally we took an approach to be explicitly secure, and not allow certificates that are not valid. Because, well, it’s kind of not that secure to use those certs, you’re never sure are you actually talking to the right database!

But then again, the only cloud database that actually has valid certificates is Microsoft with their Azure SQL offering, everybody else just uses self-signed certs. Therefore our PostgreSQL and MySQL connectors both now by default accept self-signed certificates from version 2.17.0 forwards. If you’re using an older version, you might want to set sslaccept=accept_invalid_certs in your connection string.

+13

pimeys on Mar 1, 2021

Hi @pantharshit00,

Per #2676, i tried sslaccept=accept_invalid_certs instead of sslmode=require and it worked!

Although the prisma docs state the sslaccept is not required and that the default is accept_invalid_certs, I had to explicitly add the argument to get it to work. Otherwise I will get Can't reach database server at ....

@dhanish2k maybe this will address the error you see in postgres?

Anyhow, thanks for your help and for everyone’s contribution to this thread!

+10

gevuong on Mar 1, 2021

Hello,

Thanks for reporting this. I tried tried and I think it is a usage issue. I was able to connect to a AWS RDS instance via SSL.

Here is how I did it.

Launch and new RDS instance.
Expose your instance to your preferred IP range( I exposed it to everywhere).
Disable non ssl authentication for you user using SQL
Download the AWS root ca from https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.SSL.html and place it inside prisma folder.
Use the following URL format: mysql://<user>:<pass>@<db>.<id>.<region>.rds.amazonaws.com:3306/testdb?ssl_mode=require&sslcert=rootca.pem
Run the commands.

You do not need to make a client cert as AWS doesn’t support x509 client certificate authentication. You need to use their certificate which they provide so need for the openssl step.

Let me know if this helps.

pantharshit00 on Jan 29, 2021

ic…I appreciate your feedback.

Yes, this is interesting, so you did not need a PKCS12 client file or password to SSL connect, even though the prisma doc states that it’s needed to create a SSL connection.

Earlier, I reached out on the Prisma slack channel and was suggested to create a PKCS12 file and password and add those parameters to the connection string. I did just that, following the steps provided here, and used this connection string, DATABASE_URL="mysql://admin:PASS@xxx.rds.amazonaws.com:3306/prisma?sslaccept=strict&sslidentity=client-identity.p12&sslpassword=SSLPASS&sslcert=rds-ca-2019-root.pem", where client-identity.p12 and the server cert are in ./prisma directory.

It returned the same error:

Error: Error in connector: Error querying the database: Error querying the database: Error querying the database: TLS error: `One or more parameters passed to a function were not valid.'

The docs aren’t very clear about what path to provide to the PKCS12 file, so as a sanity check, I tried moving the client-identity.p12 file to the root directory of where prisma introspect would be called. And tried adding the fully qualified path to sslidentity param (ie. sslidentity=/Users/xxx/Projects/nestjs/prisma/client-identity.p12).

But they all return the same error 😞…

Note: The results were from running prisma cli and client v2.8.0.

gevuong on Jan 26, 2021

@vincenujib were you able to fix this. I’m have also been facing this issue for days now

albertdugba on Oct 28, 2022

i am facing the same issue

In my case, I was using a vpn which was the very reason I had the error. If you’re using a vpn, then you will have to turn it off

albertdugba on Nov 12, 2022

Hi @vincenujib

Could you open a new issue?

Something that you could try is connecting with another tool and see if only Prisma errors or if it’s a general problem. For example, you could try with psql
psql -Atx postgresql://xxx:xxx@name.cluster.region.rds.amazonaws.com:5432/postgres?ssl_mode=require&ssl_cert=region-bundle.pem
Or any other tool, like DBeaver or TablePlus.

I can connect through MySQL Workbench. Will open new issue for this.

vincenujib on Aug 29, 2022

Hi @vincenujib

Could you open a new issue?

Something that you could try is connecting with another tool and see if only Prisma errors or if it’s a general problem. For example, you could try with psql

psql -Atx postgresql://xxx:xxx@name.cluster.region.rds.amazonaws.com:5432/postgres?ssl_mode=require&ssl_cert=region-bundle.pem

Or any other tool, like DBeaver or TablePlus.

Jolg42 on Aug 29, 2022

I just went through the same issue. I just found that using ssl_mode and ssl_cert rather than the same parameters without underscores is working for me (to connect to the RDS via prisma from my local machine. Going to confirm in a deploy but I suspect it will work as well.

Example:

DATABASE_URL = "postgresql://xxx:xxx@name.cluster.region.rds.amazonaws.com:5432/postgres?ssl_mode=require&ssl_cert=region-bundle.pem"

benmkramer on Aug 28, 2022

Other people in the thread, please open separate issues describing your problem as mixing providers in a single issue makes it hard for us to triage the issue.

pantharshit00 on Jan 29, 2021

@gevuong “postgresql://admin:xxx@xxx.rds.amazonaws.com:5432/****” that’s what I have. I put the cert in the prisma folder but I don’t refer it in the connection string. And also the check if the @prisma/client is of the same version.

dhanish2k on Jan 22, 2021

I have the same issue as well.

I upgraded my cli version to 2.15.0 and I get

Error: P1001

Can't reach database server at xxx.rds.amazonaws.com:5432`

Please make sure your database server is running at xxx.rds.amazonaws.com:5432.

But if I go back to 2.8.0, I’m able to connect so something has definitely changed.

dhanish2k on Jan 22, 2021