"Authentication failed for user" when running prisma db pull on a SQL Server database

Question

I am trying to import a SQL Server database into prisma using to prisma db pull command.

I created a new DB user with proper rights (that I can log in to SQL Server Management Studio with). However, it does not work through prisma.

The schema.prisma file looks like this:

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

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

I've tried the following connection strings:

DATABASE_URL="sqlserver://localhost:1434;database={master};user={test};password={test123}"

Result:

Error: P1011

Error opening a TLS connection: The TLS settings didn't allow the connection to be established. Please review your connection string. (os error -2146762487))

and

DATABASE_URL="sqlserver://localhost:1434;database={master};user={test};password={test123};trustServerCertificate={true};encrypt={false};sslaccept={accept_invalid_certs};"

Result:

Error: Error in connector: Authentication failed for user 'test'

I also tried with port 1433 and that yields the result that the database server is not found. I also tried various combinations of different security-related flags, but I get stuck at the same error, that authentication failed. I am unable to debug this further because I am unaware of how I can get at the underlying engine behind prisma, and/or try this connection in an isolated environment.

I also tried with integrated security (which I do not know if it is supported), and to connect to a MS Azure DB. All of these kinds of connections work fine from our .NET backend, using EF, but that is in a whole other context I guess.

Thanks in advance.

EDIT / UPDATE:

Thanks to @AlwaysLearning I have tried updating the version of sql server express (LocalDb). I am still hitting the same issue though.

SELECT @@VERSION

Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)   Sep 24 2019 13:48:23   Copyright (C) 2019 Microsoft Corporation  Express Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 19043: ) 

Any way I construct the connection string using port 1433 gives the same error:

Error: Migration engine error:
Error creating a database connection.

Any way I construct the connection string using port 1434 but omitting trustServerCertificate=true; gives the same error:

Error: P1011: Error opening a TLS connection: The TLS settings didn't allow the connection to be established. Please review your connection string.

Addressing these issues with what I think should be a proper connection string

DATABASE_URL="sqlserver://localhost:1434;database=testing;user=admin;password=admin;trustServerCertificate=true;encrypt=false"

gives the error

Performing a TLS handshake
Trusting the server certificate without validation.
TLS handshake successful
Turning TLS off after a login. All traffic from here on is not encrypted.
Login failed for user 'admin'.
Login failed for user 'admin'.

I can create databases fine using SSMS.

Thanks again!

EDIT / UPDATE 2:

Seems like @AlwaysLearning was correct that the port should be 1433. I had to open this port using SQL Server Configuration Manager. Doing this, I am now able to create new databases (using integrated secuirty, which is what I want for local development). Using localhost:1433, I cannot find the existing DB though, which I guess means that localhost:1433 != LocalDB. I will look into this further. Thanks so far.

EDIT / UPDATE 3:

Yes, it seems like the connection string localhost:1433 does not go to SQL Server Express (LocalDB) but to SQL Server (DESKTOP-{SOMETHING}). Not sure if prisma supports SQL Server Express (LocalDB)

Answer 1

You can jsut add integratedSecurity=true;trustServerCertificate=true; to the connection string.

An Example connection string will be:

sqlserver://DB_HOST:DB_PORT;database=DB_NAME;integratedSecurity=true;trustServerCertificate=true;

Answer 2

While it appears not be your case, I would like to emphasize the characters used for the password. I think SQL authentication failed for me due to the password containing invalid characters, like ", { or '.

If this is your case, try changing the password of the admin account, such that it doesn't use potentially invalid characters. If you cannot change the admin password, create another user with read/write access.