Pulse 5.6 Connect to SQL Server using Windows Authentication


#1

Hi there,

I’ve successfully connected Pulse to SQL Server using JDBCURL using SLQ Server authentication user in a test environment. Wesley Mission Security Model does not support SLQ Server authenticated user. Is it possible for Pulse to connect to SQL Server using Windows Authentication? Upon configuring Windows Authentication, following error message appeared in the Pulse log file. Please assist. Many thanks, Nainoor Thaker

Settings on the Config file
[SQLServer]
JDBCURL = localhost:1433;databaseName=TM1Pulse_database
UserName = domain_name\server_name
Password = #@CXLyWpk7aoIPbK4IkbhJ4d6mvu4xqD3xtEQ3vEaHuNfmvdK25RC8svEWP2tXwLAf
----------------------------------------

Error
2017-10-27 15:11:24 ERROR SecurityUtil Unable to connect to the database to retrieve public group
com.cubewise.db.AppConnectionException: Could not find a connection to the docs database
at com.cubewise.db.AppConnection.(SourceFile:40)
at com.cubewise.security.SecurityUtil.addPublicGroup(SourceFile:510)
at com.cubewise.security.SecurityUtil.getGuestUser(SourceFile:487)
at com.cubewise.filters.UserRoleFilter.getUser(SourceFile:227)
at com.cubewise.filters.UserRoleFilter.doFilter(SourceFile:61)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:108)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:505)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:423)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1079)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:620)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:745)


#2

Hi @nainoor.thaker,

Yes Windows Authentication should work.

In your post you are using in Pulse.cfg:
UserName = domain_name\server_name
It should be
UserName = domain_name\user_name

In the Pulse for TM1\logs\pulse.log, you can check which database and user name Pulse is using, if the connexion fails you should see these messages:

2017-10-27 17:43:22 INFO InitContextListener Using SQL Server database localhost:1433;databaseName=TM1Pulse

2017-10-27 17:43:22 FATAL InitContextListener Unable to connect to SQL Server
com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user ‘CUBEWISE\VViau’.


#3

Hi @Vincent,

Thank you for further info. In our case server name and user name are same so during writing my post I mixed up but it should not be a problem.

Yes, I’m able to see message containing SQL Server database
2017-10-30 09:28:08 INFO InitContextListener Using SQL Server database localhost:1433;databaseName=TM1Pulse

Yes, I’m getting Login failed message
2017-10-30 09:28:08 FATAL InitContextListener Unable to connect to SQL Server
com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user ‘wesleyd1\BITstApp01’. ClientConnectionId:77f862d2-3f29-4da5-9e60-ec0512f90cbd
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(tdsparser.java:279)
at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:99)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:4346)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:3160)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$100(SQLServerConnection.java:43)
at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:3123)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2445)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1981)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1628)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1459)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:773)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1168)
at java.sql.DriverManager.getConnection(DriverManager.java:664)
at java.sql.DriverManager.getConnection(DriverManager.java:247)
at com.cubewise.servlets.InitContextListener.contextInitialized(SourceFile:357)
at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:5016)
at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5528)
at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:901)
at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:877)
at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:652)
at org.apache.catalina.startup.HostConfig.deployDirectory(HostConfig.java:1263)
at org.apache.catalina.startup.HostConfig$DeployDirectory.run(HostConfig.java:1948)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)

I’ve also tried with and without Password in the config file. And I was able to see if I’ve provided a password, application has encrypted it and in case of blank password it was not encrypted in the config file.

Please assist,
Many thanks, Nainoor


#4

Hi @nainoor.thaker,

To Configure Pulse to set up Windows Authentication with SQL Server, there are 2 ways to do it:

Use Native Authentication

  1. Stop Pulse services
  2. Pulse for TM1 services need to run with the user which has access to the database:
  3. Update the JDBCURL setting in Pulse.cfg
    JDBCURL = localhost:1433;databaseName=TM1Pulse;integratedSecurity=true
  4. Download the sqljdbc_auth.dll
  5. Add the file sqljdbc_auth.dll to C:\Windows\System32 folder
  6. Start Pulse Services

Use Kerberos Authentication

  1. Stop Pulse services
  2. Pulse for TM1 services need to run with the user which has access to the database:
  3. Update the JDBCURL setting in Pulse.cfg
    JDBCURL = localhost:1433;databaseName=TM1Pulse;integratedSecurity=true;authenticationScheme=JavaKerberos
  4. Start Pulse Services

More information in this article from Microsoft.

When you use integrated login, you don’t need to setup username and password in the Pulse.cfg:
[SQLServer]
JDBCURL = localhost:1433;databaseName=TM1Pulse;integratedSecurity=true
UserName =
Password =


#5

Thank you. Problem solved. I’ve used Native Authentication. :slight_smile: