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

user_3303776 0
Viewed 1393

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)

prisma sql-server
3 Answers

I've managed to make Prisma work with SQL Server with the free developer license on localhost following the steps shown in 2 places on the prisma documentation:

To build the DATABASE_URL string just follow this pattern:

// The schema is optional
DATABASE_URL=sqlserver://${MSSQL_HOST}:${MSSQL_PORT};database=${MSSQL_DATABASE};user=${MSSQL_USER};password=${MSSQL_PASSWORD};schema=${MSSQL_SCHEMA};trustServerCertificate=true;encrypt=true

Then set this options in the "SQL Server Configuration Manager"

1 Enable TCP/IP

Prisma Client requires TCP/IP to be enabled. To enable TCP/IP:

Open SQL Server Configuration Manager. (Search for "SQL Server Configuration Manager" in the Start Menu, or open the Start Menu and type "SQL Server Configuration Manager".)

In the left-hand panel, click SQL Server Network Configuration > Protocols for MSSQLSERVER

Right-click TCP/IP and choose Enable.

2 Enable authentication with SQL logins

If you want to use a username and password in your connection URL rather than integrated security, enable mixed authentication mode as follows:

Right-click on your database engine in the Object Explorer and click Properties.

In the Server Properties window, click Security in the left-hand list and tick the SQL Server and Windows Authentication Mode option, then click OK.

Right-click on your database engine in the Object Explorer and click Restart.

3. (Optional) Enable the sa login

To enable the default sa (administrator) SQL Server login:

In SQL Server Management Studio, in the Object Explorer, expand Security > Logins and double-click sa.

On the General page, choose a password for the sa account (untick Enforce password policy if you do not want to enforce a policy).

On the Status page, under Settings > Login, tick Enabled, then click OK.

You can now use the sa account in a connection URL and when you log in to SQL Server Management Studio.

user_11361580

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;
user_57513410

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.

user_148223160
Related