VBA code for Integrated Login

Post Reply
Posts: 1
Joined: Tue Feb 24, 2009 1:45 pm
Version: 9.0.3
Excel Version: 2003

VBA code for Integrated Login

Post by eyalfluk » Tue Feb 24, 2009 2:10 pm

I have often used this piece of vb code for connecting to TM1 from an Excel application (using TM1 Dll functions):

Public Function Connect(AdminHost As String, ServerName As String, UserName As String, Password As String) As Long
Dim hUser As Long
Dim hServer As Long

hUser = TM1SystemOpen()
TM1SystemAdminHostSet hUser, Trim(AdminHost)
hServer = TM1SystemServerConnect(hUser, Trim(ServerName), Trim(UserName), Trim(Password))
Connect = hUser
End Function

Now I need to connect to a server configured with:
IntegratedSecurityMode = 3

The Connect function doesn't work for this situation.
Do you have any solution for this?

Thanks in advance,
Eyal Fluk
Biconix Intl.

User avatar
Posts: 25
Joined: Thu May 15, 2008 10:18 am
Location: Paris, France

Re: VBA code for Integrated Login

Post by Renaud MARTIAL » Tue Feb 24, 2009 2:50 pm


the API documentation indicates that it is not possible :(
NOTE: If your server is set up to accept only integrated login
(IntegratedSecurityMode=3), you cannot log in to the TM1 server using the
API. If your server is set up to use integrated security exclusively, set the
IntegratedSecurityMode setting to 2, and establish a user in the TM1 security
system specifically for the API. For additional information on TM1
authentication, see the TM1 Security Guide.


David Usherwood
Site Admin
Posts: 1377
Joined: Wed May 28, 2008 9:09 am

Re: VBA code for Integrated Login

Post by David Usherwood » Tue Feb 24, 2009 4:00 pm

I've been working recently with IL (mainly patching round the unusable, poorly documented ETLDAP stuff). With IL, since you don't need to provide a user and password, do you need this code at all? Try just skipping it and 'assuming' you are already logged in. And let the forum know what happened.

User avatar
George Regateiro
Posts: 326
Joined: Fri May 16, 2008 3:35 pm
OLAP Product: TM1
Version: 10.1.1
Excel Version: 2007 SP3
Location: Tampa FL USA

Re: VBA code for Integrated Login

Post by George Regateiro » Tue Feb 24, 2009 4:56 pm

Though I dont know alot about the VBA piece, but I was told when I inquired about the .NET API that the APIs are still in developement and the Intergrated Login was not included as of this point.

Posts: 7
Joined: Wed Jun 04, 2008 7:37 pm

Re: VBA code for Integrated Login

Post by Bert » Tue Feb 24, 2009 6:48 pm

Have you tried TM1SystemServerConnectIntegratedLogin?

Declare Function TM1SystemServerConnectIntegratedLogin Lib "tm1api.dll" (ByVal hPool As Long, ByVal sServerName As Long) As Long


Gregor Koch
Posts: 263
Joined: Fri Jun 27, 2008 12:15 am
OLAP Product: Cognos TM1, CX
Version: 9.0 and up
Excel Version: 2007 and up

Re: VBA code for Integrated Login

Post by Gregor Koch » Wed Feb 25, 2009 3:08 am


If you have IntegratedSecurityMode = 2

Application.Run "N_CONNECT", TheServer, "", ""

does the trick (at least on 9.0 SP3 and SP4) . Whereby 'TheServer' is the tm1 server name.

Normally this function needs the user and password but with integrated login it works without it.

There are some minor clitches when you do have the Server Explorer open and you are not connected, subsequently open the report which uses this function which will result in a 'blank' (at least that's what you'll get from the Who Am I) user in the Server Explorer.
Other than that it works just fine.

Hope this helps and works on Mode 3



User avatar
Posts: 718
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016

Re: VBA code for Integrated Login

Post by paulsimon » Wed Feb 25, 2009 10:58 pm


I think that the IntegratedLogin bit should only be necessary if you are connecting via the API outside of Excel.

In Excel you can piggy back the connection from Excel to TM1 via a much older API call. I can't remember it off hand, but you will find it if you search the forum, and it is built in to my TM1EasyAPI.

If you are still in the pre-Cognos license model where you need to be concerned about multiple connections from users, then this method has the advantage of not creating another connection, ie it uses the same connection as the one that the user has already established via Excel, and just gets a handle to it.


Paul Simon

User avatar
Community Contributor
Posts: 310
Joined: Tue Jul 20, 2010 5:01 pm
OLAP Product: Cognos TM1
Version: Planning Analytics Local 2.0.x
Excel Version: 2013 2016
Location: Istanbul, Turkey

Re: VBA code for Integrated Login

Post by mce » Wed Mar 09, 2011 4:42 pm

What if I use IntegratedSecurityMode = 5 ? How do I login using n_connect in this case?
How do I specify the namespace?

User avatar
Alan Kirk
Site Admin
Posts: 6003
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2 Classic (PAW-free zone)
Excel Version: 2010 and 2016
Location: Sydney, Australia

Re: VBA code for Integrated Login

Post by Alan Kirk » Wed Mar 09, 2011 10:28 pm

PaulSimon wrote: In Excel you can piggy back the connection from Excel to TM1 via a much older API call. I can't remember it off hand,

Code: Select all

Declare Function TM1_API2HAN Lib "tm1.xll" () As Long
It's used in all of TM1Tools' API calls. It's also undocumented, though I have been lobbying, cajoling, pushing and screaming at Iboglix Support to do so for years now. :evil:

They seem to be of the opinion that all API code will involve connecting and disconnecting each time you need to do something, despite the fact that in Excel-based solutions this view is somewhat disconnected from reality.

The biggest potential "gotchas" when using that function are that you must not call either TM1SystemClose or TM1APIFinalize, or you'll destabilise your Excel session and send it crashing into the ground in a flaming heap.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

Post Reply