Introduction
For the security of user’s database, SQL server provides some authentication modes by which an unauthorized person cannot access user’s database. In this section we will know about the types of authentication modes provided in SQL server and learn how to deal with login failure issues on SQL server.
At first we will discuss how to configure the authentication mode in SQL server.
Authentication Mode Configuration
The SQL server provides two types of Authentication modes during the setup. One is Windows Authentication Mode and second is SQL Server Authentication Mode. It is important to choose one of these modes for the database engine. The Windows Authentication Mode allows you to connect through Windows NT 4.0 or Windows 2000 user account and disable you for SQL Server Authentication while the mixed mode allows you for both Windows Authentication and SQL Server Authentication. The Windows Authentication is always accessible for the user.
Let us know what happens when we use Windows Authentication Mode.
Windows Authentication Mode
When you use Windows authentication mode, the SQL server confirms the user name and password through the windows principal token in the operating System, which means that the windows confirms the user identity. The SQL server doesn’t inquire for any identity validation. The Windows authentication mode is more secure than SQL Server Authentication mode because it is a default authentication mode.
Security Features of Windows Authentication:
- Uses Kerberos security protocol
- Provides validation complexity for a strong password
- Supports account lockout after attempting multiple invalid logins
- Manages password expiration
A Windows authentication connection is more secure than a SQL server authentication. Because the maintenance of users and groups are only managed by Windows 2000 or Windows NT 4.0
NOTE: Windows Authentication Mode is useful when SQL Server runs only on Windows 2000 or Windows NT 4.0.
SQL Server Authentication Mode
In SQL server authentication mode logins are created on the basis of SQL server. The user name and password are built in SQL server and stored in SQL server. When using SQL server authentication mode, you need to create strong passwords for all the SQL server accounts. The SQL server authentication mode permits SQL server to support applications, which is provided by third parties and expects SQL server authentication. It also permits the SQL server to use web based applications by which users create their own identities. But it cannot use Kerberos security protocol. We can use the SQL server authentication mode when we run SQL server on Windows 98 because Windows 98 doesn’t support Windows Authentication mode.
NOTE: In SQL server authentication mode the username and passwords are saved in a SQL server’s master database and when we use SQL server logins, the username and passwords are passed through a network, which makes them unprotected.
When you install SQL server, it installs with a login named SA (System administrator). You need to create a strong password for the SA.
To enable SA login on SQL server follow the simple steps given below:
- Click on Object Explorer>> click on Security>> click on Logins
- Right click on SA>> click on Properties
- You need to create and confirm a password for the login on the General page
- On the Status page>> in the Login section>> click Enabled>> then click Ok
What If I Can’t Access SQL Server & Keep Getting Error 18456?
There are a number of reasons, from which the login failed error 18456 can occur. Sometimes the error 18456 displayed that the server name is correct, but the connection is not established due to a number of reasons. The error is same for all other versions of SQL server. The error message 18456 does not show all the information regarding to the error, rather it hides all the information from unauthorized users in case someone tries to misuse the SQL server. But you can check the detail of this error 18456 if you are the administrator of SQL server.
Causes and Troubleshooting of Error 18456
1. Unrecognized User: The main reason of this error is that the mixed mode authentication is enabled and you are trying to login with Windows Authentication with the user who is not recognized by SQL server. This error may occur while you are using user account control on Windows 7 or Windows Vista. You should use “Run as Admin Option” if you are using Windows 7 or Windows Vista.
2. Incorrect Password: The second reason could be that you are using the SQL server authentication and the password may be incorrect or maybe expired.
3. Disabled Authentications: One other cause of this error is that the SQL server authentication or Windows Authentication is not enabled by SQL server itself.
Sometimes, it happens when the sysadmin had changed the password of username SA and now he is not able to recall the password. In such case you need to reset SA password.
How to Reset SA Password?
SQL server provides you some facilities to reset your SA password.
You can reset your SQL server password by using the other SQL server account who is the member of the sysadmin role
Follow the below steps to reset SA password:
- Go to Object Explorer >> open Security Folder >> open Login folder
- Then right click on SA Account >> go to Properties
- Change SA password >> then click OK
NOTE: To check the new password, restart the SQL server and all its other services then test the new password.
NOTE: It is important to turn off the single user mode in SQL Server before attempting above procedure.
Conclusion:
In this article we learned that how many authentication modes we are using in SQL server and how we use them. Also, did we learn about error 18456 and how do we reset the SA password because the SA password in SQL server provides much security. But unfortunately, if we forget the SA password or if we are facing any other issue related to SA password, because of reasons whatsoever, from which the login failed error 18456 can occur there is no way out. So in such case you need to have an SQL SA Password Recovery Tool with which you can regain access to your database and also secure it.
