We want to use JDBC to connect Sql server none default instance database, but always got below error: Login failed for user 'XXX'. ClientConnectionId:92d1a0fb-49c0-45c2-968f-ded3089d6ac9 We make sure the password is correct. If we try to connect same sqlserver's default instantce database, it's ok. The Sqlserver's version is 2017, please give us some help, thanks.

I am not familiar with DBeaver, but your configuration looks wrong. I think Host should be KSSC-DATA\KSPIG. Database should probably be zkaccess only. It would be very odd to specify the instance name with the database rather than with the host. Also, try to leave the port blank. If you can't, you will need to look up the port for the KSPIG instance in SQL Server Configuration Manager. What is happening now is that you are connecting to the default instance on KSSC-DATA, and apparently, there is no login for zktime on this instance. On another note: you have replied twice by posting Answers. Answers are for answers to the questions, and you would only post an Answer if you find the solution yourself. Else you should use comments, and I converted you Answers to Comments.

use jdbc to connect sql server none default instatnce database fail

China Ryerson 20

We want to use JDBC to connect Sql server none default instance database, but always got below error:

We make sure the password is correct.

If we try to connect same sqlserver's default instantce database, it's ok.

The Sqlserver's version is 2017, please give us some help, thanks.

Erland Sommarskog 115.8K Reputation points MVP

2024-12-12T22:44:40.2966667+00:00

Look in the SQL Server errorlog. You will find more details about the login error there.
LiHongMSFT-4306 30,056 Reputation points

2024-12-13T02:41:44.5+00:00

Hi @China Ryerson

Not enough information right now, please check the SQL Server logs to see if there’s any additional information about the login failure.
China Ryerson 20 Reputation points

2024-12-13T08:08:12.05+00:00

We checked sqlserver log and found below error:

Login failed for user ‘xxx'. Reason: Could not find a login matching the name provided.[Client: xxx.xxx.xxx.xxx]

But we can find the user in Security ->Logins.

If we try to use the account "sa", we got another error:

Login failed for user "sa". Reaseon: Password did not match that for the login provided.
Erland Sommarskog 115.8K Reputation points MVP

2024-12-13T22:05:31.3533333+00:00
The error messages speak for themselves. You need to provide the correct user name. You need to provide the correct password. You need to connect to the correct instance (which you seem to do, since you see the error messages in the error log.)

But when you look at the logins, you need to make sure that you are connected to the same instance as you trying to log on to. Also make sure that the user name is really what it looks like. Run

SELECT * FROM sys.server_principals WHERE name = 'username'

A row should come back. Keep in mind that if the instance has a case-sensitive server collation, MyUser will not match myuser.

Also, try to connect with SQLCMD or SSMS with the user. Hypothetically, something is clobbered in the connection string, so the data arrives molested to SQL Server. Then again, that should appear from the username in the error message. Anyway, connecting in a different way helps to narrow down the problem.

Do I understand it correctly that you are using SQL authentication?
China Ryerson 20 Reputation points

2024-12-16T03:49:58.1033333+00:00

We double checked the user name and didn't find any problem. We can use sqlcmd to connect, please see the attachment for detail.

SQLCMD.png DBeaver(JDBC).png

Accepted answer

Erland Sommarskog 115.8K Reputation points MVP

2024-12-16T22:33:51.1733333+00:00

I am not familiar with DBeaver, but your configuration looks wrong. I think Host should be KSSC-DATA\KSPIG. Database should probably be zkaccess only. It would be very odd to specify the instance name with the database rather than with the host.

Also, try to leave the port blank. If you can't, you will need to look up the port for the KSPIG instance in SQL Server Configuration Manager.

What is happening now is that you are connecting to the default instance on KSSC-DATA, and apparently, there is no login for zktime on this instance.

On another note: you have replied twice by posting Answers. Answers are for answers to the questions, and you would only post an Answer if you find the solution yourself. Else you should use comments, and I converted you Answers to Comments.
Please sign in to rate this answer.

1 person found this answer helpful.
China Ryerson 20 Reputation points

2024-12-17T03:54:19.5566667+00:00

When I corrected the host and database, it works. Thanks for your help.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

use jdbc to connect sql server none default instatnce database fail

0 additional answers

Your answer