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.
4,631 questions
0 comments
Accepted answer
  1. KranthiPakala-MSFT 46,437 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
Most helpful Newest Oldest
  1. David Browne - msft 3,771 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
    1 person found this answer helpful.