Connecting with Integrated Login using VBA

Post Reply
schavarr
Posts: 4
Joined: Tue Jul 18, 2017 5:22 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2016

Connecting with Integrated Login using VBA

Post by schavarr »

Hi all - I've been tasked with creating a excel report that connects to our TM1 production server that uses Integrated Login security. On development I was able to use the N_CONNECT function because we have a lower security. However for production I'm using the following code which runs but it doesn't connect to the server.

Code: Select all

Declare Function TM1SystemServerConnectIntegratedLogin Lib "tm1api.dll" (ByVal hPool As Long, ByVal vServerName As Long) As Long
Declare Function TM1SystemOpen Lib "tm1api.dll" () As Long
Declare Function TM1ValPoolCreate Lib "tm1api.dll" (ByVal hUser As Long) As Long
Declare Function TM1ValIndex Lib "tm1api.dll" (ByVal hPool As Long, ByVal InitIndex As Long) As Long
Declare Function TM1ValString Lib "tm1api.dll" (ByVal hPool As Long, ByVal InitString As String, ByVal MaxSize As Long) As Long
Declare Sub TM1SystemAdminHostSet Lib "tm1api.dll" (ByVal hUser As Long, ByVal AdminHosts As String)
Declare Sub TM1APIInitialize Lib "tm1api.dll" ()

Sub TM1_Connect()

    Dim vServer As Long
    Dim sServerName As String
    Dim pPoolHandle As Long
    Dim vServerName, vStringLength As Long
    Dim hUser As Long
        
    sServerName = "TDPlan_Prod"
    hUser = TM1SystemOpen()
    
    pPoolHandle = TM1ValPoolCreate(hUser)
    
    vStringLength = TM1ValIndex(pPoolHandle, 11)
    
    vServerName = TM1ValString(pPoolHandle, sServerName, vStringLength)
        
    vServer = TM1SystemServerConnectIntegratedLogin(pPoolHandle, vServerName)
    
End Sub
Does anyone have any ideas why my code isn't connecting?

Thanks,

Steve
Paul Segal
Community Contributor
Posts: 306
Joined: Mon May 12, 2008 8:11 am
OLAP Product: TM1
Version: TM1 11 and up
Excel Version: Too many to count

Re: Connecting with Integrated Login using VBA

Post by Paul Segal »

Are you on IntegratedSecurityMode = 2? If so, this will work

Code: Select all

Application.Run("N_CONNECT", "YourServer", "","")
Otherwise look at this thread http://www.tm1forum.com/viewtopic.php?t=765
Paul
upali
Posts: 38
Joined: Thu Oct 11, 2012 6:15 am
OLAP Product: TM1
Version: 10.2.2.4
Excel Version: 2010
Location: Melbourne, Australia

Re: Connecting with Integrated Login using VBA

Post by upali »

Try this:

Code: Select all

	Dim hUser As Long
	Dim hPool As Long
	Dim vServerName As Long
	Dim vStringLength As Long
	Dim hServer As Long

	TM1APIInitialize
	hUser = TM1_API2HAN

	TM1SystemAdminHostSet hUser, "YourAdminHost"
	hPool = TM1ValPoolCreate(hUser)

	vStringLength = TM1ValIndex(hPool, 10)
	
	vServerName = TM1ValString(hPool, "YourTM1InstanceName", vStringLength) 
	hServer = TM1SystemServerConnectIntegratedLogin(hPool, vServerName)
It works for me, provided you run the script from an account in TM1.
schavarr
Posts: 4
Joined: Tue Jul 18, 2017 5:22 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2016

Re: Connecting with Integrated Login using VBA

Post by schavarr »

Paul - Our production server uses IntegratedSecurityMode = 3 so unfortunately the N_CONNECT function won't work.

upali - I tried using the code you linked, changing the "YourAdminHost" and the "YourTM1instanceName" but that also did not connect.

I'm trying to run this code from TM1 Perspectives through Citrix. Does this code need to be run from the workbook on the actual server TM1 is located?

Thank you both for your help!
upali
Posts: 38
Joined: Thu Oct 11, 2012 6:15 am
OLAP Product: TM1
Version: 10.2.2.4
Excel Version: 2010
Location: Melbourne, Australia

Re: Connecting with Integrated Login using VBA

Post by upali »

I had a chance to try it in Citrix and saw that I missed one line in the declares. It worked fine then.

Code: Select all

'ENABLE THE CODE TO USE THE EXCEL TM1 HANDLE - SAVES LOGGING IN AGAIN
'(This function does not appear in the standard tm1api module supplied
' by Applix / Cognos / IBM. Added by Alan Kirk, 15-Jan-10.)
Declare Function TM1_API2HAN Lib "tm1.xll" () As Long
I attached the code to a Button and after clicking it, checked the server connections in Server Explorer and confirmed it logged me in.
schavarr
Posts: 4
Joined: Tue Jul 18, 2017 5:22 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2016

Re: Connecting with Integrated Login using VBA

Post by schavarr »

I had added the declare statement before the subroutine that is supposed to be establishing the connection to TM1 but it still didn't work. I believe my problem might be with these two lines:

Code: Select all

TM1SystemAdminHostSet hUser, "YourAdminHost"
vServerName = TM1ValString(hPool, "YourTM1InstanceName", vStringLength)
I've been using my username as "YourAdminHost" and the TM1 server I'm wanting to connect to as the "YourTM1InstanceName". Is this incorrect? I've just recently started learning and using TM1 so my knowledge is not as advanced as I wish it was.
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Connecting with Integrated Login using VBA

Post by tomok »

schavarr wrote: Fri Jul 21, 2017 2:19 pm I had added the declare statement before the subroutine that is supposed to be establishing the connection to TM1 but it still didn't work. I believe my problem might be with these two lines:

Code: Select all

TM1SystemAdminHostSet hUser, "YourAdminHost"
vServerName = TM1ValString(hPool, "YourTM1InstanceName", vStringLength)
I've been using my username as "YourAdminHost" and the TM1 server I'm wanting to connect to as the "YourTM1InstanceName". Is this incorrect? I've just recently started learning and using TM1 so my knowledge is not as advanced as I wish it was.
That's a pretty big stretch to interpret AdminHost as meaning a user name. AdminHost in TM1 is an application that acts as a traffic cop for communications between users and the TM1 Server service. By default this service is installed on the same machine that the TM1 Server service is on. So, you would put the IP address, or FQDN, of that server in this parameter.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
schavarr
Posts: 4
Joined: Tue Jul 18, 2017 5:22 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2016

Re: Connecting with Integrated Login using VBA

Post by schavarr »

Yes that was the part that was causing my issue. I switched out what was in that parameter and it connected. Rookie mistake! Thank you all for your help!!
Post Reply