Connect to AZURE SQL PAAS DB is getting failed

Elastic Stack Logstash
1

Hi I am using the below script to connect to AZURE SQL PAAS DB from the logstash script. Service principal is created and added the RBAC role for both Service principal and SQL PAAS sever as a reader role. Please help on this

#file:
input {
jdbc {
jdbc_connection_string => "jdbc:sqlserver://.database.windows.net:1433;database=workfusion;authentication=ActiveDirectoryPassword;driver=com.microsoft.sqlserver.jdbc.SQLServerDriver;clientid=;tenantid=;clientsecret="
jdbc_user => ""
jdbc_password => ""
jdbc_driver_library => "C:/Softwares/logstash-8.14.2/logstash-core/lib/jars/sqljdbc4-4.0.jar"
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
statement_filepath => "C:/Softwares/scriptscog/apac/uat/.sql"
tracking_column => "epochtimestamp"
use_column_value => true
tracking_column_type => "numeric"
schedule => "* * * * *"
}
}

filter {
mutate {
remove_field => [ "epochtimestamp" ]
}
}

output {
# stdout {codec => json_lines }
kafka {
codec => json
topic_id => ""
bootstrap_servers => ""
message_key => ""
security_protocol => ""
ssl_truststore_location => "C:/Softwares/scriptscog/cert/aip-kafka-ts.jks"
ssl_truststore_password => "*"
ssl_keystore_location => "C:/Softwares/scriptscog/cert/ipa-dashboard.jks"
ssl_keystore_password => ""

    }

}

But i am getting the below error.

    at org.jruby.RubyProc.call(org/jruby/RubyProc.java:354) ~[jruby.jar:?]
    at java.lang.Thread.run(java/lang/Thread.java:840) [?:?]

[2024-07-12T18:10:47,868][ERROR][logstash.inputs.jdbc ][main] Unable to connect to database. Tried 1 times {:message
=>"Java::ComMicrosoftSqlserverJdbc::SQLServerException: Login failed for user ''. ClientConnectionId:92b7c2c0-6a71-4ab4-
90a2-887091fc4df9", :exception=>Sequel::DatabaseConnectionError, :cause=>#<Java::ComMicrosoftSqlserverJdbc::SQLServerExc
eption: Login failed for user ''. ClientConnectionId:92b7c2c0-6a71-4ab4-90a2-887091fc4df9>, :backtrace=>["com.microsoft.
sqlserver.jdbc.SQLServerException.makeFromDatabaseError(com/microsoft/sqlserver/jdbc/SQLServerException.java:216)", "com
.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(com/microsoft/sqlserver/jdbc/tdsparser.java:254)", "com.microsoft.sqlser
ver.jdbc.TDSParser.parse(com/microsoft/sqlserver/jdbc/tdsparser.java:84)", "com.microsoft.sqlserver.jdbc.SQLServerConnec
tion.sendLogon(com/microsoft/sqlserver/jdbc/SQLServerConnection.java:2908)", "com.microsoft.sqlserver.jdbc.SQLServerConn
ection.logon(com/microsoft/sqlserver/jdbc/SQLServerConnection.java:2234)", "com.microsoft.sqlserver.jdbc.SQLServerConnec
tion.access$000(com/microsoft/sqlserver/jdbc/SQLServerConnection.java:41)", "com.microsoft.sqlserver.jdbc.SQLServerConne
ction$LogonCommand.doExecute(com/microsoft/sqlserver/jdbc/SQLServerConnection.java:2220)", "com.microsoft.sqlserver.jdbc
.TDSCommand.execute(com/microsoft/sqlserver/jdbc/IOBuffer.java:5696)", "com.microsoft.sqlserver.jdbc.SQLServerConnection
.executeCommand(com/microsoft/sqlserver/jdbc/SQLServerConnection.java:1715)", "com.microsoft.sqlserver.jdbc.SQLServerCon
nection.connectHelper(com/microsoft/sqlserver/jdbc/SQLServerConnection.java:1326)", "com.microsoft.sqlserver.jdbc.SQLSer
verConnection.login(com/microsoft/sqlserver/jdbc/SQLServerConnection.java:991)", "com.microsoft.sqlserver.jdbc.SQLServer
Connection.connect(com/microsoft/sqlserver/jdbc/SQLServerConnection.java:827)", "com.microsoft.sqlserver.jdbc.SQLServerD
river.connect(com/microsoft/sqlserver/jdbc/SQLServerDriver.java:1012)", "jdk.internal.reflect.NativeMethodAccessorImpl.i
nvoke0(Native Method)", "jdk.internal.reflect.NativeMethodAccessorImpl.invoke(jdk/internal/reflect/NativeMethodAccessorI
mpl.java:77)", "jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(jdk/internal/reflect/DelegatingMethodAccessorIm
pl.java:43)", "java.lang.reflect.Method.invoke(java/lang/reflect/Method.java:568)", "org.jruby.javasupport.JavaMethod.in
vokeDirectWithExceptionHandling(org/jruby/javasupport/JavaMethod.java:330)", "org.jruby.javasupport.JavaMethod.invokeDir
ect(org/jruby/javasupport/JavaMethod.java:188)", "RUBY.connect(C:/Softwares/logstash-8.14.2/vendor/bundle/jruby/3.1.0/ge
ms/sequel-5.80.0/lib/sequel/adapters/jdbc.rb:237)", "RUBY.new_connection(C:/Softwares/logstash-8.14.2/vendor/bundle/jrub
y/3.1.0/gems/sequel-5.80.0/lib/sequel/database/connecting.rb:248)", "RUBY.make_new(C:/Softwares/logstash-8.14.2/vendor/b
undle/jruby/3.1.0/gems/sequel-5.80.0/lib/sequel/connection_pool.rb:163)", "RUBY.assign_connection(C:/Softwares/logstash-
8.14.2/vendor/bundle/jruby/3.1.0/gems/sequel-5.80.0/lib/sequel/connection_pool/threaded.rb:209)", "RUBY.acquire(C:/Softw
ares/logstash-8.14.2/vendor/bundle/jruby/3.1.0/gems/sequel-5.80.0/lib/sequel/connection_pool/threaded.rb:139)", "RUBY.ho
ld(C:/Softwares/logstash-8.14.2/vendor/bundle/jruby/3.1.0/gems/sequel-5.80.0/lib/sequel/connection_pool/threaded.rb:91)"
, "RUBY.synchronize(C:/Softwares/logstash-8.14.2/vendor/bundle/jruby/3.1.0/gems/sequel-5.80.0/lib/sequel/database/connec
ting.rb:293)", "RUBY.test_connection(C:/Softwares/logstash-8.14.2/vendor/bundle/jruby/3.1.0/gems/sequel-5.80.0/lib/seque
l/database/connecting.rb:302)", "RUBY.initialize(C:/Softwares/logstash-8.14.2/vendor/bundle/jruby/3.1.0/gems/sequel-5.80
.0/lib/sequel/database/misc.rb:189)", "org.jruby.RubyClass.new(org/jruby/RubyClass.java:922)", "org.jruby.RubyClass$INVO
KER$i$newInstance.call(org/jruby/RubyClass$INVOKER$i$newInstance.gen)", "RUBY.connect(C:/Softwares/logstash-8.14.2/vendo
r/bundle/jruby/3.1.0/gems/sequel-5.80.0/lib/sequel/database/connecting.rb:57)", "RUBY.connect(C:/Softwares/logstash-8.14
.2/vendor/bundle/jruby/3.1.0/gems/sequel-5.80.0/lib/sequel/core.rb:124)", "RUBY.jdbc_connect(C:/Softwares/logstash-8.14.
2/vendor/bundle/jruby/3.1.0/gems/logstash-integration-jdbc-5.4.11/lib/logstash/plugin_mixins/jdbc/jdbc.rb:123)", "org.jr
uby.RubyKernel.loop(org/jruby/RubyKernel.java:1722)", "org.jruby.RubyKernel$INVOKER$s$0$0$loop.call(org/jruby/RubyKernel
$INVOKER$s$0$0$loop.gen)", "RUBY.jdbc_connect(C:/Softwares/logstash-8.14.2/vendor/bundle/jruby/3.1.0/gems/logstash-integ
ration-jdbc-5.4.11/lib/logstash/plugin_mixins/jdbc/jdbc.rb:120)", "RUBY.open_jdbc_connection(C:/Softwares/logstash-8.14.
2/vendor/bundle/jruby/3.1.0/gems/logstash-integration-jdbc-5.4.11/lib/logstash/plugin_mixins/jdbc/jdbc.rb:163)", "RUBY.r
egister(C:/Softwares/logstash-8.14.2/vendor/bundle/jruby/3.1.0/gems/logstash-integration-jdbc-5.4.11/lib/logstash/inputs
/jdbc.rb:309)", "RUBY.register(C:/Softwares/logstash-8.14.2/vendor/bundle/jruby/3.1.0/gems/logstash-mixin-ecs_compatibil
ity_support-1.3.0-java/lib/logstash/plugin_mixins/ecs_compatibility_support/target_check.rb:48)", "RUBY.register_plugins
(C:/Softwares/logstash-8.14.2/logstash-core/lib/logstash/java_pipeline.rb:237)", "org.jruby.RubyArray.each(org/jruby/Rub
yArray.java:1981)", "org.jruby.RubyArray$INVOKER$i$0$0$each.call(org/jruby/RubyArray$INVOKER$i$0$0$each.gen)", "RUBY.reg
ister_plugins(C:/Softwares/logstash-8.14.2/logstash-core/lib/logstash/java_pipeline.rb:236)", "RUBY.start_inputs(C:/Soft
wares/logstash-8.14.2/logstash-core/lib/logstash/java_pipeline.rb:395)", "RUBY.start_workers(C:/Softwares/logstash-8.14.
2/logstash-core/lib/logstash/java_pipeline.rb:320)", "RUBY.run(C:/Softwares/logstash-8.14.2/logstash-core/lib/logstash/j
ava_pipeline.rb:194)", "RUBY.start(C:/Softwares/logstash-8.14.2/logstash-core/lib/logstash/java_pipeline.rb:146)", "org.
jruby.RubyProc.call(org/jruby/RubyProc.java:354)", "java.lang.Thread.run(java/lang/Thread.java:840)"]}

2

Hi @krishna58 , welcome to our community.

The credentials such as the client ID, tenant ID, and client secret in your configuration should have the values, shouldn't they?

3

Hi @Alex_Salgado-Elastic

Yes they are correct and i am able to login manually in SSMS.

Would you suggest any changes to the script?

4

Hi @krishna58 ,
Please fill in the credential values in the input section of your Logstash configuration. It seems like they are empty, or did you just remove them before sharing it on the forum?


jdbc {
  jdbc_connection_string => "jdbc:sqlserver://.database.windows.net:1433;database=workfusion;authentication=ActiveDirectoryPassword;driver=com.microsoft.sqlserver.jdbc.SQLServerDriver;clientid=<YOUR_clientid>;tenantid=<YOUR_tenantid>;clientsecret=<YOUR_clientsecret>"
  jdbc_user => "<YOUR_USERNAME_HERE>"  # Replace with your actual username
  jdbc_password => "<YOUR_PASSWORD_HERE>"  # Replace with your actual password
  jdbc_driver_library => "C:/Softwares/logstash-8.14.2/logstash-core/lib/jars/sqljdbc4-4.0.jar"
  jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
  statement_filepath => "C:/Softwares/scriptscog/apac/uat/.sql"
  tracking_column => "epochtimestamp"
  use_column_value => true
  tracking_column_type => "numeric"
  schedule => "* * * * *"
}
5

Hi @Alex_Salgado-Elastic

I have removed those before sharing on the forum.

7

Hi @Alex_Salgado-Elastic

Can you please help to resolve this issues?

Thanks,
Krishna

8

Hello @krishna58

Please refrain from directing questions to just one individual; instead, address the community so that everyone can assist you.

Unless you prefer individual support, which you can hire according to your license, the community here strives voluntarily to help everyone asynchronously and according to their availability.

In my opinion, I still believe there might be an issue with your connection. What I can suggest:

  1. Check Azure SQL Database Settings:

    • Verify Firewall Rules:
    • Verify Authentication Settings:

  2. Check JDBC Driver Dependencies:

    • Verify the JDBC Driver Path:

  3. Create simple connection tests and gradually add the details.

Sources that might help you:

1 Like