SQL Server Authentication is a default mechanism to connect to MS SQL Server, but there are times that you need to use SQL Server Authentication to connect to the server, like when you have to connect to an MS SQL Server Database on a different network or when you have an SSPI problem. In order to use SQL Server Authentication, you need to create a SQL Server Authentication Login ID first.
we will create a new SQL Server account.
Run Microsoft SQL Server Management Studio.
with local windows Authentication
Expand the Security folder, and right-click on the logins folder, and then click New Login.
- Enter a login name and choose an authentication method.
- use SQL Server authentication and enter a password (and confirm it).
Click the Server Roles page and check the SysAdmin Role, or a the combination of DbCreator, DiskAdmin, ProcessAdmin, and SecurityAdmin Roles.
Go to the User Mapping page and choose BigDataShrink.com.
Go to the Status page and set Permission to connect to the database engine to Grant and Login to Enabled.
then Click Ok
Server Authentication Mode
In the Object Explorer, right-click on the server node and select Properties.
Click on Security in the left menu.
Server authentication heading, select SQL Server and Windows Authentication mode.
In the Object Explorer, right-click on the server and select Restart. Once the server has been restarted.
You should be able to log in using SQL Server Authentication.