Chapter 2 User Authentication, Authorization, and Security (4): restrict the management permissions of the SA account, authentication

Chapter 2 User Authentication, Authorization, and Security (4): restrict the management permissions of the SA account, 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/38756693

 

Preface:

 

The SA account is the system administrator of SQL Server. It can be enabled during installation. Before SQL Server 2005, this account cannot be modified, but it starts from 2005, you can rename or disable sa to reduce the risk of being attacked. In later versions, Sa is only backward compatible. SA is a well-known account and is not recommended.

 

Implementation:

 

1. Disable sa:

ALTER LOGIN [sa] DISABLE;

2. Rename sa:

Alter login [sa] with name = [replace WITH a NAME that is not easily guessed];

3. After renaming the sa, you can use the following query to find the current account of the original sa:

 

SELECT * FROM sys.sql_logins WHERE principal_id = 1;

The principal_id is 1, and the SID is 0x01, which is the original sa.

 

Principle:

 

Sa is disabled by default if mixed authentication is not selected during installation. If you have a fixed server role of sysadmin or securityadmin, you can change the sa password. When you decide to rename the sa, check whether other applications are using this account. renaming may cause these applications to fail.

 

How to manage an account without administrator privilege

 

I have seen many scenarios. Because I intentionally or unintentionally deleted all Windows and SQL accounts with administrator permissions, and sa was disabled or did not know the password, many operations were not allowed, the solution to this problem can be to rebuild the master database, as shown in: http://msdn.microsoft.com/zh-cn/library/dd207003.aspx (rebuild the system database ). However, all logon information will be lost unless you have backed up the latest master database. And you need to stop the SQL Server service.

There is another method that I have practiced:

Enable local account:
1. First see if the account of this machine has administrator rights, if not added.
2.Enter cmd in the search box of the start menu, right-click and select Run as administrator
3. Enter NET STOP MSSQLSERVRE at the command prompt to stop MSSQLSERVER (if it is already stopped, you can not use this method)
4. If there is a problem in 3, an error message is displayed, you can add --- m
5. If there are no problems above, then switch to the installation path, that is, the path of sqlservr.exe under Binn
For example: cd C: \ Program Files \ Microsoft SQL Server \ MSSQL10.MSSQLSERVER \ MSSQL \ Binn
6.Execute sqlservr.exe, that is, single-user mode has entered
7. Login again with administrator account to open a window, enter SQLCMD -A
8. Enter the operation command you want to change. What I need here is to add the local account.
  Such as: 
USE master
GO
CREATE LOGIN [Account to be added, the general format is machine name \ login account name] FROM WINDOWS WITH DEFAULT_DATABASE = [Master]
GO
EXEC sp_addsrvrolemember @ loginame = N'machine name \ login account name ', @ rolename = N'sysadmin'
GO
In order to avoid errors, you can add a SQL account for emergency needs, or you can enable the sa command
9. After the above operations are completed, restart the SQL SERVER service and log in.

 

You can also read the following blog:

Http://www.cnblogs.com/lyhabc/p/3513560.html

Http://sqlserver-help.com/2012/02/08/help-i-lost-sa-password-and-no-one-has-system-administrator-sysadmin-permission-what-should-i-do/

Why is it troublesome to log on with SQL Server and Windows Authentication mode?

Your problem has ruined me, And I have encountered it. The solution is as follows:
"An error occurred while establishing a connection with the server. When you connect to SQLServer 2005, remote connection is not allowed in the default settings, which may cause this failure. (Provider: named pipeline provider, error: 40-unable to open the connection to SQL Server )"

I hate this sentence too much. I thought it would be a very simple database connection, and it bothered me for several days. I asked a lot of people around me and found a lot of information that was fruitless. Today I finally found the correct answer on the Internet and solved it successfully. The root cause is the server parameter settings.

The following is a complete solution:
1: the most important thing is to enable the servers service.

1. install an SQLServer management tool (VS2005 only has configuration tools). It is said that Microsoft has an official website.

2. Enable the sql2005 remote connection function. The method is as follows:
Configuration tool-> sqlserver peripheral application configurator-> service and connected peripheral application configurator-> open the DatabaseEngine node under the MSSQLSERVER node, select "remote connection ", next, we recommend that you select "use TCP/IP and namedpipes at the same time". After you confirm, restart the database service.
3. The Logon Setting is changed to "SQL server and windowsAuthentication". The specific settings are as follows:
Manage Manager-> windowsAuthentication (used in windows for the first time),-> select your data server in object Resource Manager-right-click> Properties> security> Sqlserver and windows Authentication.
4. Set a user name and password for SQL server as follows:
After entering the manage manager, find Security-> Logins-> sa under the server, right-click the attributes, and set the password to sa (of course, you can also set other user names and passwords) in this way, a user with the username sa and password sa is set. the user with the username sa and password sa can be used to enter the database in the SQL server mode during next login.
5: After completing the above four steps, you can write the connection string to enter the database smoothly,
(Server =. \ sqlexpress; uid = sa; pwd = sa; database = master ");

Note! The correct Server name is like this. The machine name \ SQLEXPRESS is not LOCALHOST, 127.0.0.1, and MJZG (machine name) is like this Server: TDS \ SQLEXPRESS.

Terms: Network-related

The Internet Gopher Protocol

(RFC-1436) Internet Gopher protocol

Note: This is a protocol for obtaining data from a remote server before the Internet has developed. Currently, the Gopher protocol is rarely used. It is almost completely replaced by the HTTP protocol.

IETF
1. Overview

The Internet Engineering Task Force (IETF) is a loose, self-disciplined, and voluntary private academic organization established at The end of 1985, its main task is to develop and develop Internet-related technical specifications.

IETF is an international private institution that is independently participated and managed by experts who have contributed to Internet Technology Engineering and Development. It brings together network designers, operators, and researchers related to Internet Architecture Evolution and stable operation of the Internet, and is open to anyone interested in the industry. Anyone can register for an IETF meeting. The IETF conference is held three times a year, with more than people.
A large amount of technical work of IETF is done by various internal working groups. These working groups are created based on different categories, such as routing, transmission, security, and other special topics. The exchange of IETF is mainly carried out in the contact groups set up by various working groups, which is also the main way IETF works.

Currently, IETF has become the most authoritative large-scale technology research organization in the Internet industry. But it is different from standard-setting organizations in the traditional sense like ITU (ITU-International Telecommunication Union. IETF participants are volunteers. Most of them fulfill the following mission of the Organization through the three annual meetings of IETF:

1. Identify Internet operation and technical problems and propose solutions;
2. describe in detail the development or use of Internet protocols to solve relevant problems;
3. provide suggestions to IESG on Internet protocol standards and uses;
4. Promote the technical research achievements of the Internet research task group (IRTF) to the Internet community;
5. Provides information exchange forums for Internet users, researchers, marketers, manufacturers, and managers.

2. IETF-related organizations

(1) Internet Association (ISCO-Internet Society)
ISCO is an international, non-profit membership organization. Its role is to promote the global application of the Internet. One of the implementation methods is to provide financial and legal support to various Internet organizations, especially to IETF managed by IAB.

(2) Internet Architecture Board)
IAB is a Technical Consulting Group of ISOC and acts as an ISCO technical consultant group. IAB defines the entire Internet architecture and long-term development plan, IESG provides guidance to IETF and coordinates the activities of IETF working groups. Before the establishment of the new IETF Working Group, IAB is responsible for reviewing the working group's articles, so as to ensure the rationality of its settings, therefore, IAB is the highest technical decision-making institution of IETF.
In addition, IAB is the organization and manager of IRTF and is responsible for convening a special working group to conduct in-depth discussions on Internet structure issues.

(3) Internet Engineering Steering Group (IESG-Internet Engineering Steering Group)
The IETF Working Group is divided into eight important research fields, each of which has 1-3 domain managers (Ads-Area Directors). ADs is a member of IESG.
IESG is responsible for the technical management of IETF activities and standard-setting procedures, approves or corrects the findings of IETF working groups, and has the right to terminate the establishment of the working groups, ensure the accuracy of the non-working group draft when it becomes a request annotation file (RFC.
As part of the ISOC (Internet Association), it is based on the ISOC Council ...... the remaining full text>