Win a copy of Programming with Types this week in the Angular and TypeScript forum
or The Design of Web APIs in the Web Services forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Bear Bibeault
  • Paul Clapham
  • Jeanne Boyarsky
Sheriffs:
  • Junilu Lacar
  • Knute Snortum
  • Henry Wong
Saloon Keepers:
  • Ron McLeod
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Frits Walraven
  • Joe Ess
  • salvin francis

Connecting to Microsoft SQL Server with SQL Authentication - Login failed for user

 
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Greetings,

I am trying to connect to our SQL Server from my program, but I'm encountering an error:
com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'myUser'. ClientConnectionId:xxxxxx

We have a SQL server running with a user account for reading from the database. I am able to connect using SQL authentication from my computer via SQL Server Management Studio. This is working just fine.
We also have an application server on a separate box that runs our ticketing software. From within that application, I am also able to connect to the database using the same login and basic information (db server name, instance, db name, port).

Since I'm able to connect using both methods and from different boxes with the same account, I'm inclined to think it's not an issue with the account, although I'm a noob with SQL server administration.

Most of the examples that came up in searches had to do with people mixing authentication modes with the wrong connection strings. I don't think I've done that here as my scenario is very simple, but I must be missing something.

Also, the attempts to connect from my application do not show up in the SQL server's log.

Can you please take a look and let me know what you think?



 
Wayne Woodrow
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Quick update:
Not sure if I'm headed in the right direction, but I think I needed to specify the authentication type as 'setAuthentication("SqlPassword")' according to the link below:
https://docs.microsoft.com/en-us/sql/connect/jdbc/setting-the-connection-properties?view=sql-server-ver15

Once I did this, I got a new error:


I also tried this using 'setAuthentication("ActiveDirectoryIntegrated")' to login using my own AD credentials. The result was the same SSL error above. So, that's the trail I'm on now and hoping it's the correct one.
 
Bartender
Posts: 1215
38
IBM DB2 Netbeans IDE Spring Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I would try to pass the credentials as properties in the proper connection method.Also, please check if SSL is required to establish a connection or not.
 
Wayne Woodrow
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yup so apparently these two values can work around SSL issue according to the article: https://docs.microsoft.com/en-us/sql/connect/jdbc/connecting-with-ssl-encryption?view=sql-server-ver15
setEncrypt(true);
setTrustServerCertificate(true);

I set these two values and also changed the code to use a data source, and then set the properties using methods. This got me back to where I was with the 'Login failed for user...'

Another note, the SQL server is not making any log entries for failed logins. However, if I use SQL Server Management Studio and attempt a login with bad credentials, I do see an entry for it in the log.
So it seems like it's not even making a login attempt. I also tested the code using an incorrect URL and got a different error as expected.

 
Claude Moore
Bartender
Posts: 1215
38
IBM DB2 Netbeans IDE Spring Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Did you check if you are using the correct msqlserver driver? I've just remembered that you need to use the correct driver version - with respect to sql server version And with respect to Java version you are using. I've got some problem using jdk 8 with a driver targeted to jdk 7.
 
Wayne Woodrow
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes I think it's correct as far as the version. In the POM I'm using the following dependency, and for the shade plugin, I am specifying version 11 for the source and target. On the computer, I am running java version 11.0.2.



 
Claude Moore
Bartender
Posts: 1215
38
IBM DB2 Netbeans IDE Spring Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Have you tried to just copy and paste a simple sample provided by Microsoft in it's JDBC and SQL server web page ? Check both the URL and some details (like a property called integratedsecurity I haven't seen in your example).
Generally speaking, you should try to verify that a minimal example runs without issues, before attempting more complex code.
 
Sheriff
Posts: 6551
176
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

the SQL server is not making any log entries for failed logins. However, if I use SQL Server Management Studio and attempt a login with bad credentials, I do see an entry for it in the log.


Maybe that means that you are not even getting to the server when using the code.  Have you double checked things like the port?  Is the stack trace the same as the one you posted?
 
Wayne Woodrow
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Knute Snortum wrote:

the SQL server is not making any log entries for failed logins. However, if I use SQL Server Management Studio and attempt a login with bad credentials, I do see an entry for it in the log.


Maybe that means that you are not even getting to the server when using the code.  Have you double checked things like the port?  Is the stack trace the same as the one you posted?



Yes it kind of seems that way. As a test, when I intentionally put the wrong server name or port, I get a different error as expected. However, it doesn't seem to matter what instance name or database name that I use, I still get the same login failed for user error.

To avoid any typing errors, I copy pasted from my code into SQL Server Management Studio and I was able to login just fine. I'm not sure how to dig deeper on this. I simplified the code again and included the stack trace below:

Just another quick update... I did a test using the JTDS driver and it worked fine. I may just switch to this if I can't get the Microsoft driver working soon.



 
Marshal
Posts: 24820
60
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Wayne Woodrow wrote:Just another quick update... I did a test using the JTDS driver and it worked fine. I may just switch to this if I can't get the Microsoft driver working soon.



When I was working I had to connect my Java app to an SQL Server instance and I couldn't get the Microsoft driver working. My problem at the time was that my Java app, or its environment, wouldn't use SSL while logging in. I know that SSL was mentioned earlier in this thread but on the other hand this was a decade ago and your environment now is quite likely different from my environment then. So I don't really want to point the finger at an SSL issue (not least because you'd think the Microsoft driver could deal with that).

Anyway the JTDS driver worked fine for me and solved my problem.
 
Wayne Woodrow
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you all for your assistance. Yes, I'm not too sure about that SSL aspect of this either. I messed with it for a while longer, but I'm giving up on the Microsoft driver now and will just go with the JTDS.
 
Claude Moore
Bartender
Posts: 1215
38
IBM DB2 Netbeans IDE Spring Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Glad you solved your problem with another JDBC driver, anyway it's really weird that Microsoft driver doesn't work, while another vendor's does not.
If you can, could provide exactly which SQL server you are connecting to, from which java runtime, and from which OS?
I think it would be interesting to keep track here, on the ranch, of a similar issue.Who knows, maybe it may be useful for someone else.
 
Wayne Woodrow
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sure thing, I believe it's

SQL Server 12.0.5223.6
Connecting from: Windows 10 64-bit
Java Versions: 1.8.0231, 11.0.2, 13.0.1
Microsoft JDBC Driver Versions: Multiple versions of 7 and 8 in the maven repo
 
I am going down to the lab. Do NOT let anyone in. Not even this tiny ad:
Java file APIs (DOC, XLS, PDF, and many more)
https://products.aspose.com/total/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!