SqlServer: Login failed for user

Question

I've written a very simple JDBC login test program. And after all kinds of problems I've almost got it working. Almost, just can't seem to get past this problem:

SQLServerException: Login failed for user xxxxx

I created a simple database PersonInfo then I created user user1 password1 (SQL authentication). And after trying everything was unable to connect to the database.

I am using SqlServer2008 on Win 7, I've got the latest JDBC driver from Microsoft.

My code is:

import java.sql.*;

public class hell {
public static void main(String[] args) {
    
    try {
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
Connection conn=  DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databaseName=PersonInfo;user=Sohaib;password=0000;");


System.out.println("connected");
       }

    } catch (Exception e) {
        e.printStackTrace();
    }
}
}

Here's the Exception

Exception: Unable to get connect
com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'Sohaib'.
and all other supporting errors.

There could be a number of things that could cause this - take a look at http://stackoverflow.com/questions/6403636/connecting-sql-server-2008-to-java-login-failed-for-user-error?rq=1 and see if any of the suggestions help you out. — DrewCo, Jun 28 '13 at 20:13
If you created "user1 password1" in the database you should of course use "user1 password1" in your connection string. Does the user1 have enough rights? Another hint: Did you try to connect user1 in the sql server management studio? — gartenabfall, Sep 17 '13 at 19:17

Bryan · Answer 1 · 2021-01-25T10:41:55.830

Is your SQL Server in 'mixed mode authentication' ? This is necessary to login with a SQL server account instead of a Windows login.

You can verify this by checking the properties of the server and then SECURITY, it should be in 'SQL Server and Windows Authentication Mode'

This problem occurs if the user tries to log in with credentials that cannot be validated. This problem can occur in the following scenarios:

Scenario 1: The login may be a SQL Server login but the server only accepts Windows Authentication.

Scenario 2: You are trying to connect by using SQL Server Authentication but the login used does not exist on SQL Server.

Scenario 3: The login may use Windows Authentication but the login is an unrecognized Windows principal. An unrecognized Windows principal means that Windows can't verify the login. This might be because the Windows login is from an untrusted domain.

It's also possible the user put in incorrect information.

http://support.microsoft.com/kb/555332

Don't forget to restart the SQL Server service (not the SSMS) — LoMaPh, Jul 18 '20 at 23:03
For anyone wonder where to find the authentication bit (solution 1): (MS SQL Server Management Studio): `Right-click on Sever (inside Object Explorer Dialog) > Properties > Security > Server Authentication`. Next, restart by `Right-clicking on Server > Restart`. — CybeX, Feb 14 '21 at 01:10

score 103 · Answer 2 · edited Nov 26 '20 at 11:36

103

In my case, I had to activate the option "SQL Server and Windows Authentication mode", follow all steps below:

1 - Right-click on your server

2 - Go to option Security

3 - Check the option "SQL Server and Windows Authentication mode"

4 - Click on the Ok button

5 - Restart your SQL Express Service ("Windows Key" on the keyboard and write "Services", and then Enter key)

After that, I could log in with user and password

edited Nov 26 '20 at 11:36

Federico Navarrete

3,069
5
41
76

answered Oct 10 '19 at 18:40

Junior Grão

1,361
1
8
7

Well understandable – ILIAS M. DOLAPO Feb 11 '20 at 09:13
5

I ALWAYS forget to do this when setting up a new dev machine, must have found at this post 5 times by now :D – Whelkaholism Feb 12 '20 at 15:37
Restarting the server worked for me (i had already done the previous steps before), thanks – shim-kun Nov 24 '20 at 17:12
you can restart mssql from SSSM – CervEd May 13 '21 at 10:17

score 18 · Accepted Answer · edited Nov 26 '20 at 11:36

I ran into the same issue, and I fixed it by adding my windows username to SQL and then to my server, here is how I did:

First, create a new login with your Windows username:

Click Search, then type your name in the box and click check names.

Then add your that user to the server:

Right click on the Server > Permissions > Search > Browse > Select your user (You will notice that now the user you created is available in the list)

I hope it helps ;-)

score 5 · Answer 4 · edited Oct 01 '22 at 07:18

5

We got this error when reusing the ConnectionString from EntityFramework connection. We have Username and Password in the connection string but didn't specify

"Persist Security Info=True".

Thus, the credentials were removed from the connection string after establishing the connection (so we reused an incomplete connection string). Of course, we always have to think twice when using this setting, but in this particular case, it was ok.

edited Oct 01 '22 at 07:18

rene

41,474
78
114
152

answered Aug 19 '19 at 15:13

Alexander

594
3
13

Saved my bacon! I was struggling with this error message and this fixed it for me. Note that this is on asp.net (not asp.net core) connection string in web.config. – Greg Gum Apr 16 '20 at 11:44
1

@GregGum yes, was asp.net for me too. – Alexander Apr 16 '20 at 12:55
helped me when I upgraded from .net core 3.1 to .net 6 – U.P Jan 06 '23 at 15:06

score 2 · Answer 5 · answered Apr 19 '17 at 15:19

I got the same error message when trying to connect to my SQL DB in Azure (using sql-cli). The simple solution was to escape the password with single quotes like this:

mssql -s server.database.windows.net -u user@server -p 'your_password' -d your_db -e

score 1 · Answer 6 · answered Aug 16 '17 at 07:55

1

Also make sure that account is not locked out in user properties "Status" tab

answered Aug 16 '17 at 07:55

irfandar

1,690
1
23
24

score 1 · Answer 7 · answered Apr 12 '23 at 04:46

I have also caught the same issue,But don't worry the solution is so simple

We need to understand the basic terms first that this error “Login Failed for User (Microsoft SQL Server, Error: 18456)” means you entered invalid credentials when logging into SQL Server with specific server authentication type.

Where I have Selected the option of Windows Authentication mode in Server Authentication

and In my ConnectionString in appsettings.json file has the following connection string:

Server=DESKTOP-PK4BJF5;Database=lms;Trusted_Connection=false;MultipleActiveResultSets=true;TrustServerCertificate=true

In which the "trusted-connection" is a parameter used in database connection strings to specify the type of authentication to be used when connecting to a database server.

When "trusted-connection" is set to "true" or "SSPI" (Security Support Provider Interface), it means that the connection will use Windows Authentication to authenticate the user. This means that the credentials of the currently logged-in Windows user will be used to connect to the database server. This type of authentication is also sometimes referred to as "integrated security".

When "trusted-connection" is set to "false", it means that the connection will use SQL Server Authentication to authenticate the user. This means that the connection string will contain a user ID and password that are used to connect to the database server.

I am Updating the Connection String line and just write SSPI instead of /false

"Server=DESKTOP-PK4BJF5;Database=lms;Trusted_Connection=SSPI;MultipleActiveResultSets=true;TrustServerCertificate=true",

score 0 · Answer 8 · answered Oct 24 '17 at 19:32

For Can not connect to the SQL Server. The original error is: Login failed for user 'username'. error, port requirements on MSSQL server side need to be fulfilled.

There are other ports beyond default port 1433 needed to be configured on Windows Firewall.

https://stackoverflow.com/a/25147251/1608670

score 0 · Answer 9 · answered Jun 05 '18 at 18:46

0

We solved our Linux/php hook to SQL Server problem by creating a new login account with SQL Server authentication instead of Windows authentication.

answered Jun 05 '18 at 18:46

Charles Kuehn

1

score 0 · Answer 10 · answered Aug 04 '20 at 06:23

Just in case any one else is using creating test users with their automation....

We had this same error and it was because we had recreated the user (as part of the test process). This caused the underlying SID to change which meant that SQL couldn't properly authenticate the user.

We fixed it by adding a drop login command to our testing scripts to ensure that a previously created user (with the same user name) was no longer present on the instance.

score 0 · Answer 11 · answered Oct 31 '20 at 16:38

0

I faced with same problem. I've used Entity Framework and thought that DB will be created automatically. Please make sure your DB exists and has a correct name.

answered Oct 31 '20 at 16:38

Сергей

780
4
13
31

score 0 · Answer 12 · answered Jan 11 '21 at 22:44

In my case I have configured as below in my springboot application.properties file then I am able to connect to the sqlserver database using service account:

url=jdbc:sqlserver://SERVER_NAME:PORT_NUMBER;databaseName=DATABASE_NAME;sendStringParametersAsUnicode=false;multiSubnetFailover=true;integratedSecurity=true
    jdbcUrl=${url}
    username=YourDomain\\$SERVICE-ACCOUNT-USER-NAME
    password=
    hikari.connection-timeout=60000
    hikari.maximum-pool-size=5
    driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver

score 0 · Answer 13 · answered Jan 03 '23 at 16:04

0

try using this connection string

Server=ServerName;Database=DbName;Trusted_Connection=SSPI;MultipleActiveResultSets=true;TrustServerCertificate=true

answered Jan 03 '23 at 16:04

Saeed RayatMoghadam

279
2
7

score 0 · Answer 14 · edited May 03 '23 at 17:08

0

You can try this method: add Trusted_Connection=True; to your connection string.

edited May 03 '23 at 17:08

Brian Tompsett - 汤莱恩

5,753
72
57
129

answered Apr 29 '23 at 10:36

Huseyn Hemidov

11
1

score -1 · Answer 15 · answered Feb 18 '19 at 07:33

-1

If you are using Windows Authentication, make sure to log-in to Windows at least once with that user.

answered Feb 18 '19 at 07:33

Farkhod

196
4
8

Jéssica Machado · Answer 16 · 2021-03-30T09:51:05.773

Previously I was using the Windows Authentication without problems, then occurred me the error below.

"Failed to generate SSPI context."

Witch I resolve by changing my connection string from

Server=127.0.0.1;Database=[DB_NAME];Trusted_Connection=True;

to

Server=127.0.0.1;Database=[DB_NAME];Trusted_Connection=False;

Then the next error occurred me

"Login failed for user ''."

To solve this problem I used the sa user. Access the sa user to update de password if you need (on the SQL server Security > Logins > sa (right click) > Properties > General)) and then update the connection string to..

Server=127.0.0.1;Database=[DB_NAME];User Id=sa;Password=[YOUR_PASSWORD]

You can use another user of your choice.

Disabling Windows Authentication and logging in as a sysadmin is a **very** bad idea. Fix the real error instead of disabling security — Panagiotis Kanavos, May 27 '21 at 10:22

SqlServer: Login failed for user

16 Answers16

Linked

Related