Sql Server - Authentication

What are the authentication modes in SQL Server? How can it be changed?
Ans: Authentication mode is used for authentication of the user in SQL server, and it can be selected at the time of setup of the database engine.
SQL Server supports two authentication modes: Window authentication mode and mixed mode.

• Window authentication mode: This authentication mode is used to connect through a Microsoft NT 4.0 or window 2000 user account. In Windows authentication server take computer's username and password for authentication purpose. SQL server authentication mode is disabled in this mode.
• Mixed mode: It is used to connect with the instance of SQL Server using window authentication or SQL Server authentication. In SQL server authentication mode a unique username and password are required for a particular database, as it will not depend on windows account.

How do you start single user mode in clustered installations?
In clustered installation, the SQL Server uses DLL available connection, and thus blocks any other connections to the server.
In this state, if you try to bring SQL Server Agent resources online, then it may fail over SQL resources to a different node as it could be configured to a group. So, to start a single user-mode in clustered installation, you can follow the below steps:
1. Go to advanced properties and remove -m startup parameter.
2. Now, put the SQL Server resource offline.
3. Issue the following command from the command prompt, and make sure you are at the current owner node of the group:

4. Next, you have to verify from the cluster administrator or fail over cluster management console that SQL Server resource is still offline or not.
5.Then, connect to the SQL Server by using the following command and perform the required operation: SQLCMD -E -S.
6. Once an operation is complete, you have to close the command prompt and then bring back the SQL and other resources online through cluster administrator.