Server Authentication and user logon in SQL Server

During installation, the SQL Server database engine is set to either Windows Authentication mode or SQL Server and Windows Authentication mode. After the installation is complete, you can change the authentication mode at any time.

If Windows authentication Modeis selected during Setup, the sa login is disabled and setup assigns a password. If you later change the authentication mode to SQL Server and Windows authentication mode, the SA logon name is still disabled. to use the sa login, use the ALTER login statement to enable the SA login and assign a new password. the sa login can only connect to the server using SQL server Authentication.

Reference: https://msdn.microsoft.com/zh-cn/library/ms188670.aspx Change Server Authentication

Change the security authentication mode

2. In SQL server Management Studio, in Object Explorer, right-click the server, and then click Properties.

4. On the Security page, under Server Authentication , select the new server authentication mode, and then click OK.

6. In the SQL Server Management Studio dialog box, click OK to confirm that SQL Server needs to be restarted.

8. in Object Explorer, right-click your server, and then click Restart. If you run a SQL Server agent, you must also restart the agent.

Enable the SA logon name

Enable the SA logon name

1. < Span id= "mt19" class= "sentence" > In Object Explorer, expand "security" , "login name", Right-click "sa" , then click .

2. page, You may need to create a password for the login and confirm the password.

3. in " state " on the page "Login" section, click "enable" , and then click .

Resolve connection Failures/Add users

When we open "Microsoft SQL Management Studio" and try to connect to the server, we encounter the following error:

CANNPT Connect to * *

Login failed for user ' * * * *. (Microsoft SQL Server, error:18456)

It is possible that the appropriate user is not enabled, or the user name/password is incorrect, or the user is authenticated incorrectly, or the user does not have administrative privileges. You can try to resolve this by following the link.

http://itproguru.com/expert/2014/09/ How-to-fix-login-failed-for-user-microsoft-sql-server-error-18456-step-by-step-add-sql-administrator-to-sql-management-st udio/

1) Log in with the default settings at the time of installation, using Microsoft SQL Management Studio

2) in the Object Explorer, select "Security", "Logins", find the appropriate user name, click the property. If no user is required, select "New Login", enter the user name directly or locate the user via search

3) Select the user authentication method for Windows authentication or SQL Server authentication (enter password)

4) If you want the user to have permission to enter Management Studio, you need to check the sysadmin on "Server Roles"

5) for SQL Server authentication, login requires enabled in the status state

Server authentication and user logon in SQL Server