Contained database users with alwayson

Question

We have SQL server 2016 SP2 with contained database with contained users . so Planing to plug in to AG group . As per the micosoft says benifit of using Contained database which not tie up with logins .
My concerns is this.
Q1. Contained user passwords stored in the database level . In this setup let say aplication user (what ever) is connected to contained database user contaied user and password also need to store somewhrere system files in application layer .if change the passwrod in DB side, then need to change the application system files accordintly .
I am not sure how its works smoothly on single signe on setup .

If we use AD accounts logins smoth ways of authentiction provision .

secondly , if compay wish to progress with on prem / cloud integration great to have AD logins with Azure AD.

There fore what your thoughts of having contained database user is going be not a good idea

any one have idea please

https://learn.microsoft.com/en-us/sql/relational-databases/databases/contained-databases?view=sql-server-ver15

thanks

Answer 1

You can use AD authentication with contained databases. Just add the windows user/group as a user to the database. That's it!

Was that your question?

Answer 2

Hi @Ashwan ,

SQL Server supports contained database users for both Windows and SQL Server authentication. If you want to use AD login.

1. Create AD user
2. ---Create ContainedDB sp_configure 'contained database authentication', 1;
   GO
   RECONFIGURE ;
   GO USE [master]
   GO
   ALTER DATABASE ContainedDB SET CONTAINMENT = PARTIAL
   GO
3. ---Create Contained user (Windows Authentication)
   USE ContainedDB;
   GO
   CREATE USER [CONTOSO\Containeduser];
   GO
4. Run SSMS as AD user
5. Connect to Contained database

Azure SQL database supports Azure Active Directory identities as contained database users.

Please refer to the MS document the differences and benefits of using the contained database model compared to traditional login/user model.

Best regards,
Cathy

---

If the response is helpful, please click "Accept Answer" and upvote it.

Answer 3

As Tibor says, you can have contained AD Users. Should you have it? If you typically grant permissions through AD groups, there may be little point in it, as you could grant these logins access to all servers for the AG. I would guess that this is something you typically do once.

On the other hand, if you grant access to each individual AD user, which I would assume is something that happens about daily, contained users gives you the advantage that you only need to add them once to the database.