Pulse 5.6 Connect to SQL Server using Windows Authentication

nainoor.thaker · October 27, 2017, 5:12am

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)

Vincent · October 27, 2017, 6:53am

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’.

nainoor.thaker · October 29, 2017, 10:49pm

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

Vincent · October 30, 2017, 1:06am

Hi @nainoor.thaker,

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

Use Native Authentication

Stop Pulse services
Pulse for TM1 services need to run with the user which has access to the database:

image.png880×47 4.97 KB
Update the JDBCURL setting in Pulse.cfg
JDBCURL = localhost:1433;databaseName=TM1Pulse;integratedSecurity=true
Download the sqljdbc_auth.dll
Add the file sqljdbc_auth.dll to C:\Windows\System32 folder
Start Pulse Services

Use Kerberos Authentication

Stop Pulse services
Pulse for TM1 services need to run with the user which has access to the database:

image.png880×47 4.97 KB
Update the JDBCURL setting in Pulse.cfg
JDBCURL = localhost:1433;databaseName=TM1Pulse;integratedSecurity=true;authenticationScheme=JavaKerberos
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 =

nainoor.thaker · October 30, 2017, 2:48am

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