Chapter 2 user authentication, authorization, and Security (2): Create a Logon account

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/38657111

 

Preface:

 

 

A Logon account is an account that is defined at the server (Instance) level and can be authorized to manage tasks, connect to databases, and other permissions. The SQL Server security model has two levels: server level and database level. The Logon account must be created at the server level. To access the database, you also need to create a user at the database level to map to an existing login name.

For a database (partially contained databases) introduced from SQL Server 2012, you can access the database without a login name at the server level, which will be described in later sections.

 

Implementation:

 

1. Open SSMs, connect to the corresponding instance, and then right-click Security at the server level, select logon name, and choose new logon ]:

 

 

2. enter the login name, which can be an SQL Server account or a Windows Account (<machine name or Domain Name> \ <Account Name>). If it is a Windows account, you can browse in [Search.

Source: http://blog.csdn.net/dba_huangzj/article/details/38705965

3. If [SQL Server Authentication] is selected, you can enter the password and confirm the password. In some cases, you can use an empty password (of course not recommended). The following describes the options:

• Enforce Password Policy: forces the use of Windows Password policy. If this option is not checked, you can use a blank password. The following two options will also be disabled.
• Enforce Password Expiration: the password expires according to the password policy of windows.
• The user must change the password (user must change password at next login) upon next login: the user must change the password upon the first logon using the GUI.

4. at the bottom of the interface, you can select the default database. If you do not select the database, it is the master database. As the best practice, we recommend that you do not connect to the master database, DBAs and developers may forget to switch databases, causing some DDL/DML statements to run in the master database. I am used to choosing the database or tempdb that the user needs.

5. You can also set the default language, but this may cause errors and date format problems in a multi-language environment.

6. In addition to using SSMs to create a login, you can also use the T-SQL to create a login, the following is an example:

-- Create a Windows Logon account in the domain environment. The domain name is domain, and the account name is Fred create login [domain \ Fred] from windows; -- create an SQL Logon account, login Name: Fred create login Fred with Password = 'strong password' must_change, check_expiration = on, check_policy = on, default_database = adventureworks2012, default_language = us_english;

 

Principle:

 

Password Policy on Windows Server 2008 can be referred to: http://technet.microsoft.com/en-us/library/cc264456.aspx

The policy defines the password complexity, length, password history, minimum and maximum lifecycle, and lock parameters. The default complexity is:

• It cannot contain more than two consecutive characters that appear in the login name.
• The password must contain at least 6 characters.
• The password must contain at least three of the following four types of characters: uppercase, lowercase, numbers, and special characters.

If you use a Windows user group as the login name, you can use the following statement to check whether the current logon belongs to a member of a group:

Select is_member ('domain name \ group name'); -- 1 is a member of the corresponding group of the current user in the domain.

However, note that this function does not reflect the situation where the logon name is connected to the SQL server and the group members are modified.

 

 

More:

Source: http://blog.csdn.net/dba_huangzj/article/details/38705965

When the check_policy option is used for SQL logon, the account may be locked due to some factors. You can use the following command to unlock:

-- Unlock alter login Fred with Password = 'new password' unlock by resetting the password; -- you do not need to reset the password: Alter login Fred with check_policy = off; -- first disable the policy alter login Fred with check_policy = on; -- then enable the policy.

 

Check the Logon account status:

 

You can use the loginproperty function to check the login status: Details accessible: http://msdn.microsoft.com/zh-cn/library/ms345412.aspx

The following are some examples:

DECLARE @login AS SYSNAME = ‘Fred‘; SELECT  LOGINPROPERTY(@login, ‘BadPasswordCount‘) AS [Bad Password Count] ,         LOGINPROPERTY(@login, ‘BadPasswordTime‘) AS [Last Bad Password Time] ,         LOGINPROPERTY(@login, ‘DaysUntilExpiration‘) AS [Nb of days before expiration] ,         LOGINPROPERTY(@login, ‘HistoryLength‘) AS [Nb of passwords in history] ,         LOGINPROPERTY(@login, ‘IsExpired‘) AS [is expired] ,         LOGINPROPERTY(@login, ‘IsLocked‘) AS [is locked] ,         LOGINPROPERTY(@login, ‘PasswordLastSetTime ‘) AS [Password Last Set Time];

 

 

Modify the SQL logon password:

 

You can use alter login Login Name with Password = 'new password'; to modify the SQL logon password. To execute this password, you must have the control server or alter any login permission (for the SysAdmin or securityadmin fixed role members respectively ). If you need to change your password, you need to provide the old password, such:

ALTER LOGIN fred WITH PASSWORD = ‘my new complex password‘ OLD_PASSWORD = ‘my old complex password‘;

 

The password is stored using an irreversible hash. to check whether the password is correct, you can use the undisclosed function pwdcompare:

Use Master Go create login Fred with Password = '[email protected] # 100' -- create a login name go -- check the login name password select pwdcompare (' [email protected] #100 ', cast (loginproperty ('fred ', 'passwordhash') as varbinary (256); -- returns 1 to verify that the password is correct select pwdcompare ('20140901 ', cast (loginproperty ('fred ', 'passwordhash') as varbinary (256); -- if 0 is returned, the password is incorrect.

Source: http://blog.csdn.net/dba_huangzj/article/details/38705965

 

Copy the SQL login name between instances:

 

If you need to migrate the server, you cannot simply create the same account and password on the new server. Some applications need to use Sid for verification, and the SID of each account is different, starting with SQL Server 2005, a set of scripts are provided to achieve migration: http://support.microsoft.com/kb/918992

 

In addition to scripts, you can also use the "transfer login name task" of SSIS to perform the following steps:

1. Open SQL server data tools (renamed after 2012, which was previously bids, but is more powerful after 2012), create a new project, select the Business Intelligence Group, and click integration services project. Enter a project name and click OK ]:

 

2. Find the [transfer login name task ]:

Source: http://blog.csdn.net/dba_huangzj/article/details/38705965

3. Double-click the icon to open [transfer login name task Editor ]:

 

 

4. On the logon name page, select the source link and target link:

 

5. In loginstotransfer, select the login name you want to copy:

• Alllogins: transfer all SQL Server login names on the source server.
• Selectedlogins: transfers only the login name that exists in the loginslist option.
• Allloginsfromselecteddatabases: transfers all user login names mapped to the database defined in databaseslist.

 

6. In the ifobjectexists option, define how to respond to the existing login name of the target server:

• Failtask: stops the task and carries out an error.
• Overwrite: overwrite the login name of the target server. If the SID is not copied, the user will become an isolated account.
• SKIP: Skip and continue with other login names.

7. In the copysids option, you can copy and log on and retain the same Sid. We recommend that you select this step.

 

 

How to Use SSIS tasks can be referred to the http://msdn.microsoft.com/zh-cn/library/ms188664.aspx (using the replication Database Wizard)

 

Manually copy the password:

 

To manually copy the password, run the create LOGIN command:

-- Obtain the hash value of the password. The statement does not need to change select loginproperty ('fred ', 'passwordhash'); -- use the hash value obtained above to create a new login name, you need to manually paste the hash value create login Fred with password 'and paste the obtained hash value 'hashed;