Ошибка sql net sqlclient data provider

By default login failed error message is nothing but a client user connection has been refused by the server due to mismatch of login credentials. First task you might check is to see whether that user has relevant privileges on that SQL Server instance and relevant database too, thats good. Obviously if the necessary prvileges are not been set then you need to fix that issue by granting relevant privileges for that user login.

Althought if that user has relevant grants on database & server if the Server encounters any credential issues for that login then it will prevent in granting the authentication back to SQL Server, the client will get the following error message:

Msg 18456, Level 14, State 1, Server <ServerName>, Line 1
Login failed for user '<Name>'

Ok now what, by looking at the error message you feel like this is non-descriptive to understand the Level & state. By default the Operating System error will show ‘State’ as 1 regardless of nature of the issues in authenticating the login. So to investigate further you need to look at relevant SQL Server instance error log too for more information on Severity & state of this error. You might look into a corresponding entry in log as:

2007-05-17 00:12:00.34 Logon     Error: 18456, Severity: 14, State: 8.
or

2007-05-17 00:12:00.34 Logon     Login failed for user '<user name>'.

As defined above the Severity & State columns on the error are key to find the accurate reflection for the source of the problem. On the above error number 8 for state indicates authentication failure due to password mismatch. Books online refers: By default, user-defined messages of severity lower than 19 are not sent to the Microsoft Windows application log when they occur. User-defined messages of severity lower than 19 therefore do not trigger SQL Server Agent alerts.

Sung Lee, Program Manager in SQL Server Protocols (Dev.team) has outlined further information on Error state description:The common error states and their descriptions are provided in the following table:

ERROR STATE       ERROR DESCRIPTION
------------------------------------------------------------------------------
2 and 5           Invalid userid
6                 Attempt to use a Windows login name with SQL Authentication
7                 Login disabled and password mismatch
8                 Password mismatch
9                 Invalid password
11 and 12         Valid login but server access failure
13                SQL Server service paused
18                Change password required


Well I'm not finished yet, what would you do in case of error:

2007-05-17 00:12:00.34 Logon     Login failed for user '<user name>'.

You can see there is no severity or state level defined from that SQL Server instance’s error log. So the next troubleshooting option is to look at the Event Viewer’s security log [edit because screen shot is missing but you get the

idea, look in the event log for interesting events].

Здравствуйте, хотел запустить sql сервер от имени пользователя sa, и наткнулся на ошибку, прошу помощи, кто сталкивался?

Не удается подключиться к SQL1.

===================================

Ошибка входа пользователя «sa». (.Net SqlClient Data Provider)

——————————
Чтобы получить справку, щелкните: go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%2…

——————————
Имя сервера: SQL1
Номер ошибки: 18456
Серьезность: 14
Состояние: 1
Номер строки: 65536

When trying to connect to the SQL Server, you may encounter the SQL Server database error 18456. The complete error message reads as follows.

"Login failed for user 'Username'. (.Net SqlClient Data Provider)"
"Server Name: yourServer"
"Error Number: 18456"
"Severity: 14"
"State: 1"
"Line Number: 65536"

In the above message, “State 1” means that you cannot see more details about the error due to permission restrictions. “Severity 14” is associated with security errors, like permissions.

Causes behind the SQL Server Database Error 18456

This error usually occurs when you try to log in to SQL Server with SQL Server Management Studio (SSMS) or another tool and the login fails. The most common reasons behind this problem are:

1. The login is wrong.
2. The password is wrong.
3. SQL Authentication is not enabled.

Solutions to Resolve the SQL Server Database Error 18456

Here are some solutions to fix the error 18456 in SQL Server

Verify the Login

To check if the login is correct, type the login name again correctly and make sure there are no special characters.

To verify that the login is correct, go to Security > Logins in SSMS and verify that your login name exists.

If you cannot see the login, ask the database administrator for help. Another way to verify the login is using the following query:

select name from sys.syslogins

Alternatively, you can use the stored procedure

sp_helplogins

If you need to modify the existing login name in SSMS, right-click the login and select Rename.

Alternatively, you can change the login using the ALTER LOGIN statements.

ALTER LOGIN User1 WITH NAME = tester;

The above statement will change the login name ‘User1’ to tester.

If you have a Windows login and not a SQL Server login, you need to coordinate with the system administrator to modify the Windows Account.

SSMS can detect the current Windows user automatically. However, other tools may require you to enter the login manually. If that is the scenario, there are chances that you can write an incorrect login. Make sure to check that the domain is correct and the user name is correct.

If you are not sure about your login name, you can go to the command-line tool and write whoami and the command prompt will return your Windows login. 

Check the Password to fix SQL Server Database Error 18456.

If you’ve entered a wrong password, you only need to enter it again correctly. If not, you will need to coordinate with the system administrator to modify the password if you do not have enough privileges.

If it is a SQL Server login, you can modify the password by right-clicking the login and selecting properties.

In the General page, you can modify the password.

If your login is an Active Directory account, you need to contact the system administrator in charge of the Active Directory accounts.

Another way to change the SQL Server login password is using the T-SQL. The following code illustrates how to do it.

ALTER LOGIN testuser WITH PASSWORD = 'Write$#”paSsw23”ord';

Note: If you need to recover and reset multiple passwords in SQL Server, you can use Stellar Password Recovery for MS SQL which is a tool included in Stellar Repair for MS SQL Technician

Enable SQL Authentication

By default, SQL Server is installed with Windows Authentication only. This means that you can only log in with Windows users. However, there is an option to create SQL Server database logins. If this option is not enabled, you may receive the 18456 error.

The following steps show you how to enable SQL and Windows Authentication.

• Right-click the server and select properties in the Object Explorer in SSMS.

• In Properties, go to the Security page and select the SQL Server and Windows Authentication mode.

• SSMS will ask you to restart the service. Confirm to restart the service.

Enable Contained Databases

Sometimes, the login is lost because the database was migrated as a contained database. There is an option in SQL Server to have a database user without login. By default, when you create a login, you also have a user per database associated to that login.

When you migrate a database to a different server, you need to migrate the login as well. That is why Microsoft included the contained databases. This option allows to have an isolated database easier to migrate.

In order to enable a contained database,

• Go to SSMS.
• Right-click the SQL Server and select Properties.
• Go to the Advanced page and set the Enable Contained Databases to True.

Alternatively, you can use the T-SQL commands.

EXEC sys.sp_configure N'contained database authentication', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

Conclusion

The SQL database error 18456 usually occurs due to a security login problem. Above, we’ve mentioned the solutions to resolve the issue, such as verifying and modify the login, check the password, and change the authentication mode. In addition, we mentioned a third-party SQL password recovery tool that can help recover and reset lost passwords of SQL Server database.