I am installing new Sitecore9 on our dev server.
Our DBA team is concerned about why Sitecore DBs should have contained users?
From my understanding, it is just for using SIF framework.
We also got "DB has to be Contained" error while restoring Sitecore 9 DBs in another server.
Can we use SQL users after installation? We have our SQL on Amazon RDS. Appreciate if anyone can help me on this.
Thanks
The use of the "Contained Database User model" was introduced for very good reasons, and could be considered useful outside the realm of Sitecore.
You can read the article here and here published by Microsoft on it.
The important principal is that both the login (in the master database) and the user (in the user database) must exist and be related to each other. This means that the connection to the user database has a dependency upon the login in the master database, and this limits the ability of the database to be moved to a different hosting SQL Server or Azure SQL Database server.
In the contained database user model, the login in the master database is not present. Instead, the authentication process occurs at the user database, and the database user in the user database does not have an associated login in the master database....To connect as a contained database user, the connection string must always contain a parameter for the user database so that the Database Engine knows which database is responsible for managing the authentication process.
By reducing the ties to the instance of SQL Server, partially contained databases can be useful during failover when you use Always On availability groups.
Creating contained users enables the user to connect directly to the contained database. This is a very significant feature in high availability and disaster recovery scenarios such as in an Always On solution. If the users are contained users, in case of failover, people would be able to connect to the secondary without creating logins on the instance hosting the secondary. This provides an immediate benefit.
So if I understand this correctly, Sitecore made a good decision to do this since it simplifies the way they distribute the databases for on-premise and Azure instances. Given that this is a feature promoted by Microsoft, I recommend you help provide articles/documentation to the Database Administrators. I don't imagine Sitecore will provide an alternative approach so education will be your best stategy.
I have outlined some ways to NOT use Contained Authentication in this other question on SSE:
Sitecore 9 installation using SQL active directory user
You should note, based on the details from that question, that Contained is an absolute requirement when using Azure. If you plan on going to Azure SQL, you will need to use contained database users. This is one of the reasons it has been introduced so that we can move towards practices that are current and "cloud-ready".
Regarding your restore issue, you likely were restoring databases that already had contained users in them, which is why you would receive that error.
Amazon RDS supports contained databases. You will have to enable it in the parameter group instead of the sp_configure stored proc. See below screenshot:
