TM1 Forum

Posted: **Wed Jul 19, 2017 2:16 pm**

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

Posted: **Thu Jul 20, 2017 7:06 am**

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

Posted: **Thu Jul 20, 2017 7:29 am**

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.

Posted: **Thu Jul 20, 2017 1:08 pm**

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!

Posted: **Fri Jul 21, 2017 12:18 am**

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.

Posted: **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.

Posted: **Fri Jul 21, 2017 2:33 pm**

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.

Posted: **Fri Jul 21, 2017 3:17 pm**

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!!

TM1 Forum

Connecting with Integrated Login using VBA

Connecting with Integrated Login using VBA

Re: Connecting with Integrated Login using VBA

Re: Connecting with Integrated Login using VBA

Re: Connecting with Integrated Login using VBA

Re: Connecting with Integrated Login using VBA

Re: Connecting with Integrated Login using VBA

Re: Connecting with Integrated Login using VBA

Re: Connecting with Integrated Login using VBA