Search code examples
azurepermissionsconnectionazure-sql-database

Cannot connect to Azure SQL Database with specific user(s)

I want to connect to an Azure SQL Database with a specific user in SSMS and pyodbc, respectively. I added this user without a problem using my sysadmin user (login and connecting with sysadmin is no problem in SMSS & pyodbc). I have multiple users, none of them can connect to the database.

To add the users I used following steps as described on azure's blog post:

1. create login
2. create user

The login is visible on server level and the user on the database level. They have the necessary roles etc. The engineeruser is in fact db_owner.

When I check SELECT name, is_disabled FROM sys.sql_logins; All users are enabled.

enter image description here

I try to login in SSMS explicity stating the database. enter image description here

enter image description here

But I receive the following error:

Login failed for user '_engineeruser'. (Microsoft SQL Server, Error: 18456)

Solution

  • To connect to Azure SQL Database with specific user(s)

    Using SSMS as an admin in master, establish a connection to your Azure SQL Database server. Construct a database user mapped to the login, then create a SQL authentication login with the password

    CREATE LOGIN demologin1 WITH PASSWORD=N'qwertyuiop@29'
GO

    Open another query window and choose your database in the dropdown. enter image description here

    then create user in that database with login created previously with same name as login.

    CREATE USER demologin1 FOR LOGIN demologin1 WITH DEFAULT_SCHEMA = dbo
GO
EXEC sp_addrolemember N'db_owner', N'demologin1'
GO

    Execution:

    enter image description here