azureazure-sql-databaseazure-sql-serverazure-sql

Azure SQL Contained User. Back-end can't connect using connection string


I followed this guide https://www.mssqltips.com/sqlservertip/5242/adding-users-to-azure-sql-databases/ to create a new user for my back-end API with restricted permissions for basic security reasons but can't make the back-end connect to the server. Every time it tries to connect I'm getting

System.Data.SqlClient.SqlException: 'Login failed for user 'xxxx'.'

I'm able to log-in this new user via SSMS by setting the target database in the login window options.

The back-end can connect just fine with the default connection string supplied by the Azure Portal, witch uses the server admin login. Changing the username and password for the new user, keeping the Initial Catalog to my desired database does not work. I would assume the back-end would be able to access it since the Initial Catalog property of the connection string is set to the database the contained user was created on. But nothing is working.

This is my connection string used on my back-end:

Server=tcp:xxx.database.windows.net,1433;Initial Catalog=dbName;Persist Security Info=False;User ID=newUser;Password=newUserPw;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

I tried many guides but none worked before I found this one that seems to be very knowledgeable about creating Azure SQL users, but even so no luck so far.

This are the commands I used to create the user on the DB I need it to connect(ofc with my own values):

-- select your db in dropdown and create a contained user 
CREATE USER [test] 
WITH PASSWORD = 'SuperSecret!', 
DEFAULT_SCHEMA = dbo; 
  
-- add user to role(s) in db 
ALTER ROLE db_datareader ADD MEMBER [test]; 
ALTER ROLE db_datawriter ADD MEMBER [test]; 

Anyone knows whats going on? I don't want to have to use my admin login on my back-end.


Solution

  • Turns out the straight answer is to set

    Persist Security Info=True;

    in the connection string.