Chapter 2 User Authentication, Authorization, and Security (10): Create a database that contains, authentication

Chapter 2 User Authentication, Authorization, and Security (10): Create a database that contains, authentication
Source: Workshop

Without the consent of the author, no one shall be published in the form of "original" or used for commercial purposes. I am not responsible for any legal liability.

Previous Article: http://blog.csdn.net/dba_huangzj/article/details/39003679

 

Preface:

 

In SQL Server, security is divided into two levels: Server level and database level. The login name of the server is mapped to a user in the database. Authentication occurs when the login name is connected to the server. If the database has the user corresponding to this login name, This login name can access the database. The ing between a login name and a user is based on an internal SID. When a database is copied from a server to another server, the connection relationship is planned even if the user name is the same and the SID is different. The Contained database concept was introduced from 2012 to solve this problem.

A database that contains data does not depend on any external definition. It can be freely moved between servers without any additional configuration. SQL Server has the following three concepts:

• Non-contained: the database is server-based.
• Partially contained (Partially-contained): the user is defined inside the database, the database is independent, but can still access external resources of the database.
• Full-contained: the database is independent and users cannot access external resources.

SQL Server 2012 only supports partial inclusion. This function mainly solves two problems: Login Name and user ing, and temporary table sorting rules. When a local temporary table is created within the scope of the database, the sorting rules of the CHAR/VARCHAR column are determined by the ordering rules of the called database, rather than tempdb.

 

Preparations:

 

Before creating a contained database, you must enable the containing database authentication at the instance level. Right-click an instance → properties → advanced]

You can also use T-SQL implementation:

EXEC sp_configure 'show advanced options', 1; RECONFIGURE; GO EXEC sp_configure 'contained database authentication', 1; RECONFIGURE; GO sp_configure 'show advanced options', 0; RECONFIGURE; GO

This configuration should also be enabled on the server to be restored (assuming that your database needs to be moved to another server ).

 

 

Implementation:

 

Follow these steps to create a database:

1. Right-click the database node in SSMS and select new database]

2. on the options page, select [Part] in the [include type] Option]

Into T-SQL is:

CREATE DATABASE containedDb CONTAINMENT = PARTIAL;

 

3. Then, you can create a user in the database:

USE containedDb; create user Fred with password = N 'strong password',-SQL authentication and Password DEFAULT_SCHEMA = dbo; CREATE USER [DOMAIN \ Fred];-Windows Authentication

 

4. To view included users in a database, use the following statement:

SELECT name, type_desc, authentication_type_desc FROM sys.database_principals WHERE authentication_type = 2;

 

5. the identity authentication used is displayed in DMV sys. dm_exec_sessions:

SELECT  session_id ,         login_time ,         login_name ,         DB_NAME(database_id) AS db ,     IIF(         authenticating_database_id = 1,         'server login',         QUOTENAME(DB_NAME(authenticating_database_id)) + ' user '         + QUOTENAME(original_login_name) )     AS authentication_type FROM    sys.dm_exec_sessions WHERE   is_user_process = 1;

 

 

Principle:

 

Including databases have brought about some changes to the traditional SQL Server security mode. In the past, only authorized logins were allowed to connect to SQL Server. Now, users can be directly authorized independently of any logon. The password is stored at the database level and can be modified at the server level. If the account that contains the database is a Windows Account, the entire account is only valid within the database because it does not have a login name ing at the server level.

A user with no default database exists, so if the database is not explicitly defined during connection, the connection cannot be created, you can use the SSMS connection attribute or define it in the connection string of the application. For example, you can use the SQL Server Native Client ODBC driver:

Driver={SQL Server Native Client 11.0};Server=SERVER\SQL2012;Database=ContainedDB;Uid=Fred;Pwd=iamaweakpassword;

When creating a contained database, the target server may already have the same login name, which may cause some security risks or permission conflicts. In this case, you can use the DDL trigger to modify the existing database inclusion type. For details, refer to the following blog:

Http://blogs.msdn.com/ B /sqlsecurity/archive/2010/12/06/contained-database-authentication-how-to-control-which-databases-are-allowed-to-authenticate-users-using-logon-triggers.aspx

 

More:

 

Because some views and stored procedures may depend on tables in other databases, synonyms, and server-level system objects, it is difficult to truly include databases. Therefore, SQL Server 2012 contains some databases. You can query the DMV sys. dm_db_uncontained_entities:

 SELECT  e.feature_name ,         [object] = COALESCE(QUOTENAME(SCHEMA_NAME(o.[schema_id])) + '.'                             + QUOTENAME(o.[name]),                             QUOTENAME(SCHEMA_NAME(s.[schema_id])) + '.'                             + QUOTENAME(s.[name])) ,         [line] = COALESCE(e.statement_line_number, 0) ,         [statement / synonym target / route / user/login] = COALESCE(s.[base_object_name],                                                               SUBSTRING(m.[definition],                                                               e.statement_offset_begin                                                               / 2,                                                               e.statement_offset_end                                                               / 2                                                               - e.statement_offset_begin                                                               / 2) COLLATE CATALOG_DEFAULT,                                                               r.[name],                                                               'User : '                                                               + p.[name]                                                               + ' / Login : '                                                               + sp.[name]) FROM    sys.dm_db_uncontained_entities AS e         LEFT JOIN sys.objects AS o ON e.major_id = o.object_id                                       AND e.class = 1         LEFT JOIN sys.sql_modules AS m ON e.major_id = m.object_id                                           AND e.class = 1         LEFT JOIN sys.synonyms AS s ON e.major_id = s.object_id                                        AND e.class = 1         LEFT JOIN sys.routes AS r ON e.major_id = r.route_id                                      AND e.class = 19         LEFT JOIN sys.database_principals AS p ON e.major_id = p.principal_id                                                   AND e.class = 4         LEFT JOIN sys.server_principals AS sp ON p.[sid] = sp.[sid];

 

How to convert a database to include a database:

 

You can use the following statement to convert a database to an included database:

USE [master] GO ALTER DATABASE [marketing] SET CONTAINMENT = PARTIAL;

 

If a user maps to SQL login, you can use sp_migrate_user_to_contained system stored procedure conversion to include the database user, If you need automation, you can view Microsoft documentation: http://msdn.microsoft.com/en-us/library/ff929275.aspx, you can also use the following script:

SELECT 'EXEC sp_migrate_user_to_contained @username = N''' + dp.name + ''',   @rename = N''keep_name'',   @disablelogin = N''do_not_disable_login'' ;' 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;

Next article:

When weblogic is started, it is automatically disabled. What is the situation?

The user name and password are incorrect, so it cannot be started. check whether there is a boot in your own domain. properties file, open it to see if the user name and password are set the same as when you set the domain, different to the same and then start. Or is it wrong to enter the user name and password when starting the file.

Benefits of using Windows2000server

Write composition? Refer to the following changes.

Top 10 Reasons Why to Upgrade from Windows 2000 Server to Windows Server 2003 R2
Windows Server 2003 R2 builds upon the increased security, reliability, and performance provided by Windows Server 2003 Service Pack 1 (SP1) to provide a more secure and dependable platform on which to deliver business-critical applications and Web services. at the same time, Windows Server 2003 R2 is easier to manage and integrate into existing environments. this page describes the major new features and improvements encoded in Windows Server 2003 R2.

Centralize user authentication and authorization

Introduced in Windows 2000, the Active Directory directory service simplifies the administration of complex network directories and makes it easy to locate resources on even the largest networks. this enterprise-class service is scalable, is built on Internet-standard technologies, and integrates with the Standard, Enterprise, and Datacenter editions of Windows Server 2003 R2.
Windows Server 2003 R2 provides numerous features-of-use improvements to Active Directory and new features, including cross-forest trusts, the ability to rename domains, and the ability to deactivate attributes and classes in the schema so that their definitions can be changed.

Simplify end user policy management

Administrators can use Group Policy to define the settings and allowed actions for your users and computers. in contrast with local policy, organizations can use Group Policy to set policies Ies that ...... remaining full text>