Why do we require Contained database for Sitecore 9?

Clash Royale CLAN TAG#URR8PPP

up vote
6
down vote

favorite

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

edited 2 hours ago

Pratik Wasnik

313114

asked 21 hours ago

dev

311

New contributor

1

This answer contains information on workarounds for contained DB authentication. sitecore.stackexchange.com/questions/11047/â€¦
â€“Â Lyle Jordan
21 hours ago

add a commentÂ |Â

up vote
6
down vote

favorite

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

edited 2 hours ago

Pratik Wasnik

313114

asked 21 hours ago

dev

311

New contributor

1

This answer contains information on workarounds for contained DB authentication. sitecore.stackexchange.com/questions/11047/â€¦
â€“Â Lyle Jordan
21 hours ago

add a commentÂ |Â

up vote
6
down vote

favorite

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

edited 2 hours ago

Pratik Wasnik

313114

asked 21 hours ago

dev

311

New contributor

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

sitecore-install-framework contained-database

edited 2 hours ago

Pratik Wasnik

313114

asked 21 hours ago

dev

311

New contributor

edited 2 hours ago

Pratik Wasnik

313114

asked 21 hours ago

dev

311

New contributor

edited 2 hours ago

Pratik Wasnik

313114

edited 2 hours ago

Pratik Wasnik

313114

edited 2 hours ago

Pratik Wasnik

313114

asked 21 hours ago

dev

311

New contributor

asked 21 hours ago

dev

311

asked 21 hours ago

dev

311

New contributor

dev is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

1

This answer contains information on workarounds for contained DB authentication. sitecore.stackexchange.com/questions/11047/â€¦
â€“Â Lyle Jordan
21 hours ago

add a commentÂ |Â

1

This answer contains information on workarounds for contained DB authentication. sitecore.stackexchange.com/questions/11047/â€¦
â€“Â Lyle Jordan
21 hours ago

This answer contains information on workarounds for contained DB authentication. sitecore.stackexchange.com/questions/11047/â€¦
â€“Â Lyle Jordan
21 hours ago

add a commentÂ |Â

2 Answers
2

active

oldest

votes

up vote
11
down vote

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.

Traditional Login and User Model

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.

Contained Database User Model

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.

Conclusion

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.

edited 20 hours ago

answered 20 hours ago

Michael West

7,23721145

add a commentÂ |Â

up vote
0
down vote

Can we use SQL users after installation?

I have outlined some ways to NOT use Contained Authentication in this other question on SSE:

Sitecore 9 installation using SQL active directory user

Why contained database users?

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".

"DB has to be contained" error

Regarding your restore issue, you likely were restoring databases that already had contained users in them, which is why you would receive that error.

answered 1 hour ago

Jay S

4,097728

add a commentÂ |Â

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "664"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

dev is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsitecore.stackexchange.com%2fquestions%2f13859%2fwhy-do-we-require-contained-database-for-sitecore-9%23new-answer', 'question_page');

);

Post as a guest

Name

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

up vote
11
down vote

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.

Traditional Login and User Model

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.

Contained Database User Model

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.

Conclusion

edited 20 hours ago

answered 20 hours ago

Michael West

7,23721145

add a commentÂ |Â

up vote
11
down vote

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.

Traditional Login and User Model

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.

Contained Database User Model

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.

Conclusion

edited 20 hours ago

answered 20 hours ago

Michael West

7,23721145

add a commentÂ |Â

up vote
11
down vote

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.

Traditional Login and User Model

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.

Contained Database User Model

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.

Conclusion

edited 20 hours ago

answered 20 hours ago

Michael West

7,23721145

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.

Traditional Login and User Model

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.

Contained Database User Model

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.

Conclusion

edited 20 hours ago

answered 20 hours ago

Michael West

7,23721145

edited 20 hours ago

answered 20 hours ago

Michael West

7,23721145

answered 20 hours ago

Michael West

7,23721145

answered 20 hours ago

Michael West

7,23721145

add a commentÂ |Â

up vote
0
down vote

Can we use SQL users after installation?

I have outlined some ways to NOT use Contained Authentication in this other question on SSE:

Sitecore 9 installation using SQL active directory user

Why contained database users?

"DB has to be contained" error

Regarding your restore issue, you likely were restoring databases that already had contained users in them, which is why you would receive that error.

answered 1 hour ago

Jay S

4,097728

add a commentÂ |Â

up vote
0
down vote

Can we use SQL users after installation?

I have outlined some ways to NOT use Contained Authentication in this other question on SSE:

Sitecore 9 installation using SQL active directory user

Why contained database users?

"DB has to be contained" error

Regarding your restore issue, you likely were restoring databases that already had contained users in them, which is why you would receive that error.

answered 1 hour ago

Jay S

4,097728

add a commentÂ |Â

up vote
0
down vote

Can we use SQL users after installation?

I have outlined some ways to NOT use Contained Authentication in this other question on SSE:

Sitecore 9 installation using SQL active directory user

Why contained database users?

"DB has to be contained" error

Regarding your restore issue, you likely were restoring databases that already had contained users in them, which is why you would receive that error.

answered 1 hour ago

Jay S

4,097728

Can we use SQL users after installation?

I have outlined some ways to NOT use Contained Authentication in this other question on SSE:

Sitecore 9 installation using SQL active directory user

Why contained database users?

"DB has to be contained" error

Regarding your restore issue, you likely were restoring databases that already had contained users in them, which is why you would receive that error.

answered 1 hour ago

Jay S

4,097728

answered 1 hour ago

Jay S

4,097728

answered 1 hour ago

Jay S

4,097728

answered 1 hour ago

Jay S

4,097728

add a commentÂ |Â

dev is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

dev is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Post as a guest

Name

Category

Random preview