Password for Contained User in Synapse Analytics

Neil Pereira 81 Reputation points
2020-09-11T17:46:16.797+00:00

Team,

I'm unable to create password for a contained user in Azure Synapse Analytics (SQL DW), seems like this feature isn't available.
Please confirm on this!

The available option is to create a contained user without password.
But after creating this, when this user would like to login using SQL authentication, the SSMS portal requires a password to be entered along with login (user name for contained user) .
What password should be entered in that case for this contained user ??

Thanks,
Neil.

Azure SQL Database
Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
2,625 questions
No comments
{count} votes

Accepted answer
  1. KranthiPakala-MSFT 33,136 Reputation points Microsoft Employee
    2020-09-11T22:05:40.153+00:00

    Thank you anonymous usere-msft for sharing the workaround.

    Hi @Neil Pereira ,

    Thanks for your query and using this forum.

    Yes, you are correct, Contained database user with password is not available in Azure Synapse Analytics (SQL Data Warehouse). Ref: Create User

    24098-image.png

    As per the contained database user model documentation, SQL Database supports contained database users using SQL Server authentication, but Azure Synapse Analytics (SQL Data Warehouse) does not.

    24136-image.png

    In order to workaround this please try the suggestion provided by anonymous usere-msft above.

    First create a login with password and then create a user for the login and use those login details in SQL authentication.

    Hope this helps. Let us know how it goes.

    ----------

    Please do consider to click on "Accept Answer" and "Upvote" on the post that helps you, as it can be beneficial to other community members.


1 additional answer

Sort by: Most helpful
  1. David Browne - msft 3,756 Reputation points
    2020-09-11T20:31:47.55+00:00

    Currently you must create a login with a password before creating a user in Azure Synapse Analytics SQL Pool. eg

    create login fred with password='xxxxxxxxxx'  
    create user fred for login fred  
    alter role reporting_users add member fred