[QUESTION] 'Login failed for user' when using AD credentials

[rnv812]

Question

We use mssql-jdbc-12.6.4 in Logstash to connect to MSSQL database, but we are facing authentication problem: Login failed for user 'domian\username'.

Here is the traceback:

Configuration with jdbc connection string:

input {
    jdbc {
        jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
        jdbc_driver_library => "/app/logstash/utils/jdbc/mssql-jdbc-12.6.4.jre11.jar"
        jdbc_connection_string => "jdbc:sqlserver://***:1433;databaseName=***;user=***\***;password=***;encrypt=true;trustServerCertificate=true;loginTimeout=30;"
        jdbc_user => "***"
        jdbc_password => "***"
        statement => "SELECT 1"
    }
}

We specify username in format <domain>\<username>.

We tried to connect to DB using sqlcmd util with the same parameters and authentication was successful:

sqlcmd -C -S "***:1433" -d "***" -U "***\***" -Q "SELECT 1"

Password: 

------------
           1
(1 row affected)

Do you have any idea why authentication fails in case of using driver?

Thank you.

[tkyc]

You'll need to use integratedSecurity=true, to login with the Windows account. And so, you'll need to logon to Windows using your domain user. Then you should supply a connection string that looks like the one below without the user/password.

jdbc:sqlserver://***:1433;databaseName=***;encrypt=true;trustServerCertificate=true;loginTimeout=30;integratedSecurity=true;.

OR you can also do the following below in your connection string to use your AD user to login. The connection string below uses NTLM and integratedSecurity=true:

jdbc:sqlserver://my-sql-server.mydomain.ad;integratedSecurity=true;authenticationScheme=NTLM;domain=mydomain.ad;user=domainUser;password=domainUserPassword;encrypt=false;trustServerCertificate=true;

[rnv812]

Thank you for your answer. I tried your examples and in both cases behavior was the same: Message about failed logon is gone, but nothing happened then. After about 10 minutes of hanging I can see that "Connection reset by peer":
I also tried with authenticationScheme=JavaKerberos and result was the same.

I'm on linux and I have ticket granting ticket for user in klist. Domain controller port 88 (used for kerberos) is reachable as well as database port 1433.

Cannot really got why sqlcmd authenticates successfully (just providing to it user and password), but driver does not.

[rnv812]

I also tried all examples from #2406 and #2127 . It hangs for 10 minutes and then "Connection reset by peer" is raised.

[tkyc]

Could you grab me the SQL Server logs?

Also, just curious, does a basic SQL authentication work with the driver?

https://learn.microsoft.com/en-us/sql/relational-databases/performance/view-the-sql-server-error-log-sql-server-management-studio?view=sql-server-ver16

[rnv812]

With basic SQL Authentication I got "Login failed for user '' ". Actually SQL Server belongs to customer, not us, but I'll try to ask them to provide the logs. Thank you for instruction.

[rnv812]

While I'm waiting for the logs, I checked what network connections are established for the process that uses driver to figure out the reason of 10 minutes hanging. Using netstat -tupn | grep <PID> I can see that for the first ~ 1-2 minutes it is connected to database to port 1433 and during all 10 minutes of hanging process has connection to domain controller / dns servers (it periodically changes) to port 53 using udp.

Port 53 of DC and DNS servers are all reachable (checked it with telnet <server> <port>).

What it could be?

Tried with next jdbc string: jdbc:sqlserver://<ip>:1433;integratedSecurity=true;authenticationScheme=NTLM;domain=<mydomain>;user=<domainUser>;password=<domainUserPassword>;encrypt=false;trustServerCertificate=true;

[tkyc]

For your connection attempts with the domain user, are you still setting the user in the format of <domain>\<username> still?

If you are, with the connection string below, try only doing <username> without the prepended domain.

jdbc:sqlserver://<ip>:1433;integratedSecurity=true;authenticationScheme=NTLM;domain=<mydomain>;user=<domainUser>;password=<domainUserPassword>;encrypt=false;trustServerCertificate=true;

[rnv812]

Tried with and without prepended domain. Behavior is the same.

[tkyc]

Any news on the server logs?

[rnv812]

Hello, sorry for long delay. I'll come back with logs tomorrow.

[rnv812]

Here is log entries:

09/25/2024 11:07:02,Logon,Unknown,Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication. [CLIENT: xx.xx.xx.xx]
09/25/2024 11:07:02,Logon,Unknown,SSPI handshake failed with error code 0x80090302<c/> state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The operating system error code indicates the cause of failure. The function requested is not supported   [CLIENT: xx.xx.xx.xx]

[tkyc]

I was expecting a different error, because you were able to auth through sqlcmd and you mentioned you also had a successful klist.

Can you confirm again if your machine is properly domain joined (through some alternative command)? I'm guessing you already doubled checked further for domain problems after that log error message.

[rnv812]

Yeah, checked this using kinit with same credentials and got TGT successfully. Do you have an idea how can I check it especially for NTLM authentication scheme?

Also I tried to grant all related permissions to JVM in security policy:

grant {
    // Permissions to access network resources
    permission java.net.SocketPermission "*", "connect,resolve";
    permission java.net.SocketPermission "*", "connect,resolve";

    // Permissions for NTLM or Kerberos authentication
    permission javax.security.auth.AuthPermission "doAs";
    permission javax.security.auth.AuthPermission "doAsPrivileged";
    permission javax.security.auth.kerberos.ServicePermission "*", "initiate";
};

This didn't help.

[tkyc]

Do you have an idea how can I check it especially for NTLM authentication scheme?

No, I believe there isn't a way to check for that other than making a connection.

I'm wondering if it's a SPN issue. It shouldn't be because you're able to get TGT. Do you see the following in the SQL Server logs?

The SQL Server Network Interface library successfully registered the Service Principal Name (SPN)

OR

The SQL Server Network Interface library could not register the Service Principal Name (SPN)

We want it to say successfully registered the Service Principal Name (SPN).

[rnv812]

Ok. Thank you. I asked customers about this messages.

[rnv812]

Due to long delays in responses from the customer, I suggest to close this issue until new information comes from my side. Thank you for your assistance!

[rnv812]

Hi, here is message from logs:

The SQL Server Network Interface library could not register the Service Principal Name (SPN)
[ MSSQLSvc/SetverName:1433 ] for the SQL Server service. Windows return code: 0x200b, state: 15.
Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This
is an informational message. Further action is only required if Kerberos authentication is required by
authentication policies and if the SPN has not been manually registered.

[lilgreenbird]

hi @rnv812

Based on the error above it looks like @tkyc was right it's an issue with the SPN. Please make sure to register the SPN and verify in the logs that this was done successfully. . Then you will need to specify the SPN using the serverSpn connection property. Please see Setting the connection properties for a description of the property.

I would suggest using a standalone java app to test the connection here is an example of how to do that using NTLM.

[rnv812]

Thank you very much! Customer approved that the error was related to improper SPN configuration.