Mixed mode is an SQL Authentication mode that allows users to access the SQL Server instance using either Windows or SQL Server Authentication.
When a user accesses the SQL Server instance through Windows Authentication, SQL Server confirms the account name and password through Windows principal token in the Operating system. It means that Windows confirm the user identity. SQL Server doesn't ask for the password and doesn't perform the identity validation.
In SQL Server Authentication, the username and password are created by SQL Server and stored in SQL Server. When a user accesses the SQL Server instance through SQL Authentication mode, the SQL Server confirms the username and password through the credential, which is stored in the SQL Server during the creation or setup. Also, a user accessing the SQL Server instance through SQL Server Authentication must enter the credential every time they connect.
Advantages of SQL Server Authentication
-> It allows users to connect from old and third-party applications that require SQL Server Authentication.
-> It allows the SQL Server to support environments with a different operating system, where all users are not authenticated from a Windows domain.
-> SQL Authentication mode allows users to connect from unknown or untrusted domains.
->It allows SQL Server to connect Web-based applications where users create their own identities.
-> It allows software developers to distribute their applications using a complex permission hierarchy based on known, preset SQL Server logins.
Disadvantages of SQL Server Authentication
-> If a client is a Windows domain user with a login and password for Windows, he still needs to provide another SQL server username and password to connect. Managing multiple usernames and passwords for various users is painful. Also, Entering the credential every time to connect to the SQL instance is challenging.
-> Windows provides additional password policies which are unavailable in the SQL Authentication mode for SQL Server logins.
-> SQL Server Authentication cannot use Kerberos security protocol.
-> There is a chance of attack in the SQL Server authentication because if you encrypt the SQL Server authentication login password, it must be passed through the network at the time of the connection. A few connected applications will store the password in the client application.
How to enable Mixed-mode or SQL Authentication in the existing SQL Server?
Below are the steps on how to enable Mixed-mode or SQL Authentication in the existing SQL Server.
Step 1: Open SQL Server Management Studio in the Windows VPS or dedicated server.
Step 2: Login into SQL Server with Windows Authentication.
Step 3: Go to the Object Explorer pane. Right-click on the Server name and click on the Properties option. In the screen capture, we have selected Accuwebhosting\SQLExpress (SQL ...) as an example.
Step 4: In the Server Properties window, go to the left side of the window and click on the Security option.
Step 5: Select SQL Server and Windows Authentication mode and click the Okay button in the Server authentication section.
Step 6: Once you click on the Okay button, you will get a pop window informing you that some of your configuration changes will not take effect until the SQL server is restarted. You need to click on the Okay button to close the pop window.
Step 7: Again, go to Object Explorer. Right-click on the Server name and select the restart option to restart the SQL Server.
Step 8: By clicking on the restart option, you will get a confirmation window, where you need to click on the Yes button to confirm the reboot.
Enable sa login
You can enable the sa login with SSMS (SQL Server Management Studio) or T-SQL. Here we will use SSMS method to enable sa user login.
Step 1: Go to the Object Explorer. Click on the plus sign left side of the Security option.
Step 2: Then click on the plus sign left side of the Login option.
Step 3: Right-click on the sa user and click on the Properties option.
Step 4: In the Login Properties -sa window, Click on the General option located on the left side of the window.
Next, enter the password which you want to set for sa user in the Password field. Then, in the confirm password, enter the same password which you have entered in the password field.
Step 5: Click on the status option. Select the Enable option in the login section and click on the Okay button to save the changes.