How to enable SQL Server authentication

SQL Server Authentication is a default way to connect to MS SQL Server, but there may be many times that you need to use SQL Server Authentication to connect to the server, like when you have to connect to a MS SQL Server Database on a different network or when you have a SSPI problem. In order to use SQL Server Authentication, you need to create a SQL Server Authentication Login ID first. Let’s check this below:

To allow SQL Server Authentication for your instance:

Open SQL Server Management Studio.

Expand the Security item in Object Explorer and right-click Logins and choose New Login.

Enter an account name in the Login name field and choose SQL Server authentication. Then, enter a password for the account.

Uncheck the following options to set the password as you want:

• Enforce password policy
• Enforce password expiration
• User must change password at next login

If the options are checked, you should the password according to security policy in Windows.

Go to the Server Roles page and select sysadmin.

Go to the User Mapping page and choose DATABASE and db_owner.

Go to the Status page and set Permission to connect to database engine to Grant and Login to Enabled.

 Right-click the database server in Object Explorer and choose Properties.

Go to the Security page and choose SQL Server and Windows Authentication mode

Right-click the database server and restart it. When a message box shows up, click YES.