April 12, 2012 at 12:08 pm
Steve Jones - SSC Editor (4/12/2012)
Cliff Jones (4/12/2012)
Steve Jones - SSC Editor (4/12/2012)
The AD Credential thing is interesting. If there are matching AD credentials at the instance level, the authentication takes place there first, which makes sense. So if you move a partially contained database to a server that has a matching login, the login isn't contained.Not a big deal, but interesting.
That is interesting. I was confused by Contained versus Partially Contained and assumed your question pertained to the former. Thanks for the interesting question.
Contained Databases are only partially contained in SQL Server 2012. Hopefully SQL Server 2014/2015 will be completely contained.
Sounds like a good place to keep SQL Server managment data and isolated from my client data. Is that an intended use for Contained Database?
April 12, 2012 at 12:42 pm
Cliff Jones (4/12/2012)
Sounds like a good place to keep SQL Server managment data and isolated from my client data. Is that an intended use for Contained Database?
You can already do that with a non-contained database - just give nobody else access to the database.
As I understand it, the main beneift of contained databases is that they have no dependency on annything outside the database, so it's very easy to move a database to a different server. With an uncontained database, that is hell - users will be orphaned, if the new server has a different collation, you'll get all kinds of problems in your code, and lots more. A fully contained database should not have any of those problems.
April 12, 2012 at 12:50 pm
Hugo Kornelis (4/12/2012)
Cliff Jones (4/12/2012)
Sounds like a good place to keep SQL Server managment data and isolated from my client data. Is that an intended use for Contained Database?You can already do that with a non-contained database - just give nobody else access to the database.
As I understand it, the main beneift of contained databases is that they have no dependency on annything outside the database, so it's very easy to move a database to a different server. With an uncontained database, that is hell - users will be orphaned, if the new server has a different collation, you'll get all kinds of problems in your code, and lots more. A fully contained database should not have any of those problems.
Yes, I am familiar with that kind of purgatory :-). Thanks for the response.
April 12, 2012 at 1:27 pm
Hugo took the words out of my mouth. It's mainly for HA stuff, or potentially in the future, movement of dbs to new hosts without the clients knowing. Kind of like we move VMs not with LiveMotion or Live Migration.
April 12, 2012 at 3:11 pm
Steve Jones - SSC Editor (4/12/2012)
Hugo took the words out of my mouth. It's mainly for HA stuff, or potentially in the future, movement of dbs to new hosts without the clients knowing. Kind of like we move VMs not with LiveMotion or Live Migration.
It didn't occur to me that this would be an HA feature but I get it now. We have to move a few TB's of databases around this week-end so I am in that space right now.
April 12, 2012 at 6:16 pm
Nice interesting question.
Some of the discussion makes me want to comment, because it seems ill-advised - but I'll do a bit more reading first, as my knowledge of this stuff is from reading docs not from playing with it so I may well have it wrong.
Tom
April 13, 2012 at 5:39 am
Steve Jones - SSC Editor (4/12/2012)
The AD Credential thing is interesting. If there are matching AD credentials at the instance level, the authentication takes place there first, which makes sense. So if you move a partially contained database to a server that has a matching login, the login isn't contained.Not a big deal, but interesting.
At first I thought "Not just not a big deal, a very small deal indeed, according to BoL no deal at all" reasoning something like as follows (which I now think is utterly wrong):
If the contained database which gives access to a windows principal (not via any login in master) is moved to another SQL server instance there are just two possibilities:
1) the windows OS supporting the new instance doesn't support a principal with the same identifiation string in which case that string can't be used to gain access.
2) the windows OS does support a principal with the same identification; in that case the principle has access if it goes direct to the contained database (exactly as before the move). According to the BoL CREATE USER page, the user associated in the contained database with this principal is not related to any login in the master database. Although the same principal may have access to databases other than the contained database via such a login that can only happen when CREATE USER has been executed in the contained database with FOR LOGIN for that master login, so either that specific action has to be taken after the move to cause that difference or it was already done before the move in which case it isn't a difference.
Where that reasoning goes wrong is that the existence of a login in master, which will not have access to the contained database after the move (unless an identical login in master with such access existed before the move) means that if master gets to authenticate first even if the contained database is specified in the login parameters (which would, in my view, be very silly: but as you tell me that it happens I have to believe that MS did it that way) the principal gets mapped to the login in master and doesn't therefor get access to the contained database.
Anyone think it would be worth raising a connect item on this?
Tom
April 13, 2012 at 5:59 am
L' Eomot Inversé (4/13/2012)
Anyone think it would be worth raising a connect item on this?
I think you're misunderstanding the process (or I am, obviously).
Let's say I have a partially contained database Cdb1 on server Srv1. I create a contained user for Dom\Us (Windows user Us on domain Dom), who has no login on that server.
Now, I move Cdb1 to server Srv2. There are two possibilities:
1. Dom\Us has no login on Srv2. A login attempt at Cdb1 by that user will still succeed, as a contained user. Login attempts by the same user at other databases on Srv2 will fail, as Dom\Us is not a valid login on that server or a valid contained user on those databases.
2. Dom\Us does have a login on Srv2. A login attempt at Cdb1 by that user will succeed, not as a contained user, but as a "normal" user (linked to login in master). The user can now switch database context to other databases where the login is linked to a user, which was not possible on Srv1. He can also login to another database where he has access and then switch database context to Cdb1. Other than that, nothing should change.
Note that the above is my understanding of the documentation; I have not tried the feature yet. If you have tested and found that it behaves differently, then I think you should indeed raise a bug on Connect.
April 13, 2012 at 6:00 am
Hugo Kornelis (4/12/2012)
Cliff Jones (4/12/2012)
Sounds like a good place to keep SQL Server managment data and isolated from my client data. Is that an intended use for Contained Database?You can already do that with a non-contained database - just give nobody else access to the database.
As I understand it, the main beneift of contained databases is that they have no dependency on annything outside the database, so it's very easy to move a database to a different server. With an uncontained database, that is hell - users will be orphaned, if the new server has a different collation, you'll get all kinds of problems in your code, and lots more. A fully contained database should not have any of those problems.
If Steve is right about login checking for a login in master first even when the contained database is specified in the connection parameters we certainly can get orphaned users (of a completely new kind, of course) when we move a contained database to a different instance of SQL Server, so in this case "should not" isn't the same as "will not".
I must upgrade to Windows 7 so that I can start playing with SQL 2012 and get a better handle on this stuff!
Tom
April 13, 2012 at 6:06 am
L' Eomot Inversé (4/13/2012)
If Steve is right about login checking for a login in master first even when the contained database is specified in the connection parameters we certainly can get orphaned users (of a completely new kind, of course) when we move a contained database to a different instance of SQL Server, so in this case "should not" isn't the same as "will not".
I think you missed one important part from Steve's message: "So if you move a partially contained database to a server that has a matching login, the login isn't contained". If that works as advertised, it means that SQL Server notices the match between the name of the contained user in the contained database and the name of the login in master, and automatically connects the two (changing the contained user into a normal user). I can only assume that the reverse will happen if you attack a partially contained database that has "normal" users to a server that has no logins for those users.
April 13, 2012 at 6:25 am
Hugo Kornelis (4/13/2012)
L' Eomot Inversé (4/13/2012)
If Steve is right about login checking for a login in master first even when the contained database is specified in the connection parameters we certainly can get orphaned users (of a completely new kind, of course) when we move a contained database to a different instance of SQL Server, so in this case "should not" isn't the same as "will not".
I think you missed one important part from Steve's message: "So if you move a partially contained database to a server that has a matching login, the login isn't contained". If that works as advertised, it means that SQL Server notices the match between the name of the contained user in the contained database and the name of the login in master, and automatically connects the two (changing the contained user into a normal user). I can only assume that the reverse will happen if you attack a partially contained database that has "normal" users to a server that has no logins for those users.
One thing I do see clearly in the documentation (at http://msdn.microsoft.com/en-us/library/ff929055.aspx) is that the order for sql-server authentication with target database specified is the opposite to what Steve says it is for connection to SQL Server using Windows authentication with target database specified, which seems very peculiar - why not check in the target database first when it is partially contained and specified in the connection parameters for windows authentication as well as for sql authentication?
I'm not sure why "the login isn't contained" should be interpreted in the way you suggest - although clearly if that interpretation is correct there isn't the problem I thought there was. But then what happens if the database is subsequently moved back to the original server, where the login doesn't exist in master? Does it magically become contained again - if so, that would be good. Not as good, perhaps, as keeping it contained throughout (so that a connection couldn't access both the contained database and other databases unless the user was explicity selt up as a login in master which had access to the contained database as well as others, instead of letting that situation happen by accident when the contained database is moved), but better than losing access.
Tom
April 13, 2012 at 7:11 am
thank you for the question
Iulian
April 13, 2012 at 7:14 am
L' Eomot Inversé (4/13/2012)
I think you missed one important part from Steve's message: "So if you move a partially contained database to a server that has a matching login, the login isn't contained". If that works as advertised, it means that SQL Server notices the match between the name of the contained user in the contained database and the name of the login in master
Not name matching, principal matching. The principal from the domain and user is checked.
You're mis-interpreting what I wrote and making an assumption about a situation that doesn't occur.
In SQL Auth logins, you check the contained db when specified, if it's a contained user, it's authenticated. If not, then it's not a contained user and it checks master. Doesn't matter if you move this db, this is how it works.
In Windows Auth, if I don't specify the db, master is checked and you are authenticated or not.
If I specify a contained db, property turned on for that database, then it checks master first, because if the user has a principal in master, it's a login. If it doesn't have one in master, then the contained database is checked.
The "contained or not" has to do with the session, not the principal stored in the database. I think that's where you are making assumptions about what I wrote.
April 13, 2012 at 8:13 am
Hugo Kornelis (4/13/2012)
Note that the above is my understanding of the documentation; I have not tried the feature yet.
So I decided to test it. Here are my observations.
First, I updated my SQL 2012 instance to allow SQL authorization and to allow contained databases, and I restarted the instance (necessary for the first change to take effect). I then created a partially contained database (CdbTest).
I then created a contained user (Test1) with password (Test1) in CdbTest. I was only able to login when I specified CdbTest as my initial database. After that, I was able to switch to system databases (I tried tempdb and master) and back to CdbTest, but not to other databases.
Next, I created a login with the same name and password, and gave it access to another database (FC). Now, I could login with this user and initial database CdbTest, to get access there but not to FC; or I could login with user Test1 and initial database not specified and get access to system databases and FC, but not to CdbTest. So apparently, the login Test1 and the contained user Test1 are completely seperate entities that only happen to have the same name. If I try to map the login Test1 to a user in DcbTest, I get an error message, unless I specify a different name for the user ("CREATE USER Test1b FOR LOGIN Test1;").
Next, I created a new local user (Test) on my computer. I then created a contained user for Windows user perFact\Test (my computer name is perFact). I used "runas /user:perFact\Test cmd" to run under the credentials of this user and then used "osql -S perFact\SQL2012 -E" to try to connect to SQL Server. This only worked when I also specified -d CdbTest to log in to the contained database; just as before, I was able to switch to master and tempdb, but not to other user databases.
When I then created a login for this windows user and mapped it to a user in FC, it immediately also mapped to CdbTest, even though I never specified this. Now, I could connect to SQL Server both with and without the -d CdbTest parameter, and I could freely switch database contest between FC, CdbTest, and the system databases.
I then dropped the login again, and I was back to the old behaviour - the login in CdbTest started behaving like a contained login again.
I did not test attach/detach or backup/drop/restore, let alone actually moving to another computer (since that would require my purchase of a new computer), but I assume that the behaviour will be similar.
April 13, 2012 at 11:05 am
Hugo Kornelis (4/13/2012)
Hugo Kornelis (4/13/2012)
Note that the above is my understanding of the documentation; I have not tried the feature yet.So I decided to test it. Here are my observations.
(.....)
Good, that's nice and clear. Thanks, Hugo.
Tom
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply