azure-sql-database

Login failed for user '<token-identified principal>' Error 18456 On Deploy Database

From SSMS 2017 I right click the database, then select Tasks->Deploy Database to Microsoft Azure SQL database.

In the Wizard Deployment Settings screen, I click "Connect" and log in to my Azure SQL Server:

Login

Then I click "Next" and get this error:

This error

This question explains the need to grant the user access to the database. But we are creating a new database here.

Update I am connecting as the admin Admin Name

Under Access control (IAM) -> Check access -> View my access I can see that I have Owner as a current role assignment

However when I click Eligible assignments I see

Activate a role to perform operations that require elevated permissions. The role will remain active for a limited time.

and

The tenant needs to have Microsoft Entra ID P2 or Microsoft Entra ID Governance license.

Solution

  • >ConnectionError: Login failed for user 'token-identified-principal'.

    According to the MS document

    CREATE DATABASE permissions are necessary. To create a database a login must be either the server admin login (created when the Azure SQL Database logical server was provisioned), the Microsoft Entra admin of the server, a member of the dbmanager database role in master

    The user who you provided is not having the necessary permissions to create database. That may be the reason to get above error. If you are using Entra user to login to SQL server, make sure your Entra user is set as server admin, otherwise set server admin as shown below in SQL server:

    enter image description here

    If the user is from external provider, add dbmanager role to the user in master db using below command:

    ALTER ROLE dbmanager ADD MEMBER <user>;

    Then you will be able to deploy the database successfully as shown below:

    enter image description here

    Deployed database:

    enter image description here

    Note: Use latest version of SSMS i.e. SSMS v20.2, it will work.