SQL Server 2012的 Contained Database认证

  • Article

SQL Server将认证和授权分散给了不同的对象来完成。SQL Server的“登录名”用于认证，连接SQL Server的Windows账号或账号所在的组必须在SQL Server中有对应的登录名才能成功登录到SQL Server上。而每个数据库中的“用户”被授予了操作数据库中对象的相应权限。登录名和用户之间通过SID联系起来。每个登录名在master数据库中都记录有该登录名所对应的SID。而在用户数据库中，用户名也会和某个SID对应起来。通过SID的连接，登录名被映射到了数据库用户上，于是登录SQL Server的Windows账号也获得了操作数据库的相应权限。

今天看来这个机制带来了如下两个问题：

1.       提高了高可用解决方案的维护成本。举例来说，对于一个启用了数据库镜像的系统，如果你在主体服务器上添加了新的登录名来访问镜像数据库的话，你必须在镜像服务器上也添加相同的登录名，否则一旦发生了故障转移，数据库就无法使用新的登录名进行访问。另外，在镜像服务器上添加登录名时要确保和主体服务器上的登录名使用相同的SID。否则就会破坏登录名到数据库用户之间的对应关系，成为所谓的孤立用户。

2.       增加了迁移应用的复杂程度。应用程序所需要的数据都保存在用户数据库中，但是当你需要将应用从开发坏境迁移到生产环境，或者从老的服务器迁移到新的服务器上的时候，你不能仅仅简单的迁移用户数据库和程序。因为还有一部分和应用相关的数据遗漏在用户数据库之外，其中包括登录名。在迁移应用的时候，登录名需要被单独的从老的环境中提取出来，再部署到新环境上。

为了解决这样的问题，SQL Server 2012引入了新的特性，叫做Contained Database认证。Contained Database认证主要是由两类对象构成整个认证体系，一个叫做Contained Database。另一个是创建在Contained Database中的用户对象，被称为Contained database user。Contained database，顾名思义，就是将应用所需要的所有信息全部“包含”在用户数据库内部，不遗漏在系统数据库中。当应用程序连接contained database的时候，它不需要通过记录在master数据库中的登录名然后再映射到数据库用户，而是直接在contained database上进行验证，然后获得对应的contained database user被授予的权限进行数据库操作。可以说Contained database user就是一个把传统的登录名和数据库用户名的功能揉在一起的一种新的对象类型。有个Contained Database认证，上面谈到的两个问题就能很简单的得到解决。

Contained Database认证并不是独立于传统的Windows认证和SQL认证之外的新的认证方式，而是建立在这两种认证方式之上的新功能。也就是说，登录到SQL Server的账号依旧需要通过Windows认证或SQL Server认证的机制来验明正身，只是一旦验证通过该账号就会直接被转换成一个数据库用户。因此你根本不需要在实例中为该账户建立任何对应的登录名。

你可以通过以下步骤来启用Contained Database。

1.       使用SQL Server Management Studio，从实例的属性中将Enabled Contained Databases选为True。这样就启用例了Contained database功能。

2.       你可以创建一个新的Contained Database，也可以将一个现有的数据库更改为Contained database。

（1）      要创建一个Contained Database，只需要在创建数据库的对话框中将Containment Type选为Partial。

（2）      要将一个数据库更改为Contained database，首先你需要在数据库的属性页中将Containment Type改为Partial（类似于创建一个Contained Database）。其次你要将数据库用户转换为Contained database user，并且需要将数据库用户对应的登录名都禁用掉。你可以使用一下脚本很方便的禁用这些登录名

DECLARE @username sysname ;

DECLARE user_cursor CURSOR

    FOR

        SELECT dp.name

        FROM sys.database_principals AS dp

        JOIN sys.server_principals AS sp

        ON dp.sid = sp.sid

        WHERE dp.authentication_type = 1 AND sp.is_disabled = 0;

OPEN user_cursor

FETCH NEXT FROM user_cursor INTO @username

    WHILE @@FETCH_STATUS = 0

    BEGIN

        EXECUTE sp_migrate_user_to_contained

        @username = @username,

        @rename = N'keep_name',

        @disablelogin = N'disable_login';

    FETCH NEXT FROM user_cursor INTO @username

    END

CLOSE user_cursor ;

DEALLOCATE user_cursor ;

3.       要创建一个Contained database user，你可以通过SQL Server Management Studio来完成。只需要将User type设置为SQL User this Password或者Windows user即可。

Contained Database除了能够使得认证过程“跳过”登录名这个对象之外，还带来了一些其他的好处。比如说，使用了Contained Database，你就不用担心数据库的collation和系统数据库的collation不一致的问题了。当你对于Contained database进行的SQL操作时，如果产生了临时表的话，临时表的collation全部是和Contained database的collation一致，而不是和tempdb一致。另外，Contained database还引入了一个新的概念叫做 Catalog collation，并且所有的Contained database都使用同一种Catalog Collation - Latin1_General_100_CI_AS_WS_KS_SC。这种catalog collation被用于Contained database中各种对象的元数据，也能带来很多的便利，这里碍于篇幅就不具体展开了。