Sqlstate 28000 ошибка входа пользователя - Решение и исправление самых разных ошибок на TopOshibok.ru

Server errors are annoying, especially when they are cryptic like “sql error 1045 sqlstate 28000″.

The message contains some error codes.

But, what’s the real problem here?

At Bobcares, we help website owners resolve complex errors like “sql error 1045 sqlstate 28000“ as part of our Outsourced Hosting Support services.

Today, let’s discuss the top 5 reasons for this error and we fix them.

‘SQL error 1045 sqlstate 28000’ – What this means?

Before we move on to the reasons for this error, let’s first get an idea of this error.

Website owners face this error when querying data from the SQL server.

For instance, the complete error message looks like this:

SQLSTATE[28000] [1045] Access denied for user 'user'@'localhost' (using password: YES)

This error shows that the MySQL server disallows the user to connect to it from localhost or 127.0.0.1.

‘SQL error 1045 sqlstate 28000’ – Causes & Fixes

In our experience managing servers, we’ll see the major causes of this error and how our Dedicated Support Engineers fix it.

1) Typo in username and password

This is the most common reason for the error “sql error 1045 sqlstate 28000″.

Users may type wrong username and password while connecting to the database.

Therefore, SQL server can’t identify the authenticity of the account.

Solution

In such cases, we help website owners reset the database user password.

For example, in cPanel servers, we reset the database user password from Databases > Mysql databases > Current Users.

Mysql databases option in cPanel

Also, for database driven websites like WordPress, Magento, etc., we update the new database username and password in the website configuration files.

For example, in the Magento application, we update the database name, username and password in the “app/etc/local.xml” file.

In some cases, website owners get errors like this:

SQLSTATE[28000] [1045] Access denied for user 'root'@'localhost' (using password: YES)

This is because, the root session don’t know the password of mysql root user.

And, it can be probably a mis-typed password during the initial setup.

Here, our Hosting Engineers reset the admin/root password after starting the MySQL in safe mode.

For example, we use the below command to reset the root password in safe mode.

update user set password=PASSWORD("YOURPASSWORDHERE") where User='root';

2) Accessing from wrong host

MySQL uses host based restrictions for user access to enhance security.

In other words, MySQL allows user access only from hosts defined in the MySQL user table.

So, any access from remote machines whose hostnames are not defined in this user table will bounce with the error “sql error 1045 sqlstate 28000”

Solution

First, our Hosting Engineers check whether the remote host is allowed in the MySQL user table.

If not, we add the hostname of the remote machine in the MySQL user table.

For instance, we use the below command to add a host in the MySQL user table.

update user set host='hostname' where user='username';

Here, hostname is the hostname of the remote machine, and username is the MySQL user.

We’ve seen cases where server owners use wildcards(%) in host field which gives universal access to this user.

But, this is not a good practice as there is a security risk that user can access the database from any hosts.

In addition to that, due to security concerns, we always disable accessing root user from remote machines.

[You don’t have to be a MySQL expert to keep your websites online. We have experienced MySQL admins available 24/7.]

3) User doesn’t exist

Similarly, this error “sql error 1045 sqlstate 28000” occurs when the user trying to access the database doesn’t exist on the MySQL server.

For example, if you access MySQL using a testuser that doesn’t exist, you can see the following error.

# mysql -u testuser -p 
Enter password:  
ERROR 1045 (28000): Access denied for user 'testuser'@'localhost' (using password: YES)

Solution

In such cases, our Support Engineers first check whether the user exists in the MySQL user table.

If not, we check the user’s requirement to access the database and if it is valid, we create a user with that username.

4) Existence of Anonymous users

Website owners face this error when there are anonymous MySQL users like ”@localhost or ”@127.0.0.1.

That is, when a client tries to connect to the database, the MySQL server looks through the rows in the user table in a sorted order.

The server uses the first row that matches the most specific username and hostname.

So, here the anonymous user (‘ ‘@localhost) precedes any other users like ‘user’@localhost when connecting from localhost.

And, use the anonymous user password to connect to the server.

Finally, the result is “sql error 1045 sqlstate 28000“.

Solution

Our Hosting Engineers check the MySQL user table and remove the anonymous user account.

For example, we use the below command to remove the anonymous user from MySQL.

delete from user where User = ' ';

5) Insufficient privileges

Likewise, insufficient privileges for the user to access the database can also result in this error.

Solution

In such cases, we assign proper access rights for the user to access the database.

For example, in cPanel servers, we manage user privileges from:

cPanel > Mysql databases > Current databases > Privileged users > Click on the database user.

Granting user privileges in cPanel

[Struggling with database user permissions and privileges? Our MySQL Experts are here for your help.]

Conclusion

In short, “sql error 1045 sqlstate 28000” may occur due to insufficient database privileges, wrong username or password, etc. Today we’ve discussed the top 5 reasons for this error and how our Dedicated Support Engineers fix it.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

SEE SERVER ADMIN PLANS

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

Источник

Details of Error:

Executed as user: Domain\LoginName. Login failed for user 'Domain\LoginName'. [SQLSTATE 28000] (Error 18456). The step failed.

or below ODBC Data Source SQL Server Connection – Login Failed For User

Connection failed: SQLState: '28000' SQL Server Error: 18456 [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'UserName'.

Cause of Error:

This error is encountered when SQL Agent Service does not have permissions required to run the job. Or When we try ODBC Data Source SQL Server Connection Login Failed message when Login used for connection does not have permission needed.

Resolution:

First if this message is caused by SQL Agent Job connection then we will need to check the source SQL server agent service account name from Services MMC or Server manager or SQL Server Cofiguration Manager in Services-> Go to the SQL Server Agent Service –> properties –> Check the logon tab where we can find the account name.

Now check and add the login we found above or if the its caused by ODBC Data Source SQL Server Connection then add the Login used in ODBC to the SQL server instance by checking in Security tab –>logins with necessary permission on your target machine where its trying to connect or run the job on. This can be same instance or different instance on same or some other server.

We use cookies on our website to give you the most relevant experience by remembering your preferences and repeat visits. By clicking “Accept All”, you consent to the use of ALL the cookies. However, you may visit «Cookie Settings» to provide a controlled consent.

Источник

I am trying to use Python to connect to a SQL database by using Window authentication. I looked at some of the posts here (e.g., here), but the suggested methods didn’t seem to work.

For example, I used the following code:

cnxn = pyodbc.connect(driver='{SQL Server Native Client 11.0}',
                      server='SERVERNAME', 
                      database='DATABASENAME',               
                      trusted_connection='yes')

But I got the following error:

Error: ('28000', "[28000] [Microsoft][SQL Server Native Client 11.0][SQL Server]
Login failed for user 'DOMAIN\\username'. (18456) (SQLDriverConnect); [28000] [Microsoft]
[SQL Server Native Client 11.0][SQL Server]Login failed for user 'DOMAIN\\username'. 
(18456)")

(Note that I replaced the actual domain name and user name with DOMAIN and username respectively, in the error message above.)

I also tried using my UID and PWD, which led to the same error.

Lastly, I tried to change the service account by following the suggestion from the link above, but on my computer, there was no Log On tab when I went to the Properties of services.msc.

I wonder what I did wrong and how I can fix the problem.

Источник

Remove From My Forums

Question
The following bcp command using xp_cmdshell doesn’t work returning SQL Connection error. Looks like bcp command doesn’t take Windows-Authenticated ID.
exec master.dbo.xp_cmdshell ‘bcp [DBTest].[dbo].TableTest1 IN «\\server12\d$\test.csv» -S»Server12\isnt12″ -U»Domain1\test» -P»Test$12″ -c -t»,»‘

SQLState = 28000, NativeError = 18456
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user ‘Domain1\test’.

However, the following works, which uses SQL-Authentication Login ID.
exec master.dbo.xp_cmdshell ‘bcp [DBTest].[dbo].TableTest1 IN «\\server12\d$\test.csv» -S»Server12\isnt12″ -U»test» -P»test» -c -t»,»‘

Is there anyway to use Windows-Authenticated ID for bcp command? Please advise.

Answers

a) If using SQL Server authentication in which case you have to specify your SQL Server login and password, say your SQL Server login ID is user and password is pass then you should specify -Uuser -Ppass.

b) For using Windows authentication, then just specify -T and it will used trusted connection (do not use –U it will force BCP to sql authentication )

does the following work?

exec master.dbo.xp_cmdshell ‘bcp [DBTest].[dbo].TableTest1 IN «\\server12\d$\test.csv» -S»Server12\isnt12″ -T -c«,»‘
- Marked as answer by
  
  Wednesday, November 18, 2009 10:15 PM

Источник

SQL Ошибка входа пользователя «sa». ☑ 0

Devastator

08.12.13

✎