Sunday, September 28, 2014

0 Microsoft SQL Server Contained Databases

By default, SQL server required the user has login exist at the server level and the user at the database level in order to access the database. From Microsoft SQL Server 2012, the new concept of the database “Contained Database” has been introduced.

Users can connect to the database without authenticating a login at the Database Engine level. Isolating the database from the Database Engine makes it eerier  to move the database to another instance.

Contained Databases does not support replication, change tracking , change data capture.

Configuration

sp_configure 'contained database authentication',1;
reconfigure with override 
 
select name,value,value_in_use , description from sys.configurations
where name ='contained database authentication'

image

USE [master]
GO
Create database containedDB;
GO
ALTER DATABASE containedDB SET CONTAINMENT = PARTIAL WITH NO_WAIT
GO
select name,containment,containment_desc from sys.databases
--- Create user 
USE [containedDB]
GO
CREATE USER [skye1] WITH PASSWORD=N'Pass@word'
GO
ALTER ROLE [db_datareader] ADD MEMBER [skye1]
GO
image

Test connection

When connect to to the SQL server from SSMS, I get below error 18456. This is because, the login only exist in the contained DB, SQL server does not have knowledge for this login. Therefore, we have to specify the database in the connection.

image

image

After successfully connect, the user only can see contained DB.

image[9]

Move Contained database

Use the normal backup restore database procedure . The target instance must also enable the contained database on the server instance level.

Covert Database to contained database

Create the test user

USE [master]
GO
CREATE LOGIN [skye2] 
    WITH PASSWORD=N'Pass@word', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [Northwind]
GO
CREATE USER [skye2]
    FOR LOGIN [skye2]
GO
ALTER ROLE [db_datareader] 
    ADD MEMBER [skye2]
GO
-- Check if the db has objects that can not be modifed 
USE [Northwind]
GO
select * from sys.dm_db_uncontained_entities
--- Check the active login in the target database
use [NorthWind]
select sp.name, dp.name,sp.type_desc,dp.type_desc,dp.authentication_type_desc,sp.is_disabled,dp.create_date,dp.modify_date   from sys.database_principals dp join sys.server_principals sp on dp.sid = sp.sid
where sp.is_disabled = 0
GO
select name,authentication_type_desc from sys.database_principals
Where type = 'S' and authentication_type_desc <> 'NONE' and name <>'dbo'

image

--- Convert the database
Alter database [NorthWind] set containment = PARTIAL
--- Migrate User http://msdn.microsoft.com/en-us/library/ff929275.aspx 
use [NorthWind]
EXEC sp_migrate_user_to_contained 
@username = N'skye2',
@rename = N'keep_name',
@disablelogin = N'do_not_disable_login' ;

After convention, we can se the user login ’skye2’ authentication type has changed from ‘INSTANCE’ to ‘DATABASE’.

image

Error: 18456, Severity: 14, State: 58.

While testing with contained database, I get this error when use the contained user to login.

2014-09-28 18:50:23.95 Logon       Error: 18456, Severity: 14, State: 58.
2014-09-28 18:50:23.95 Logon       Login failed for user 'skye1'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: 192.168.116.1]

The error is pretty self explain. To resolved this error, we have to enable the authentication mode to mixed. See here for more detail.

ERROR Msg 5061, Level 16, State 1, Line 24

While convert the database, to PARTIAL or NONE. We need to get the exclusive lock on the database level. No user can access it. If there are existing user connection to the database, we will get the below error.

Msg 5061, Level 16, State 1, Line 24
ALTER DATABASE failed because a lock could not be placed on database 'NorthWind'. Try again later.
Msg 5069, Level 16, State 1, Line 24
ALTER DATABASE statement failed.

Reference

http://msdn.microsoft.com/en-us/library/ff929071.aspx

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates