Simple setup of TM1 to connect to SQL Server?

[Hippogriff]

I am guessing this question must have been asked before... but I did a search and saw plenty of threads, many about going the other way. I'm wanting to teach myself how to connect TM1 to a SQL Server database and pull something into a Cube via a Process. Seems like a simple request, right?

I have a database in SQL Server and a table. I have a TI Process where the Datasource Type is ODBC. From there I must admit I'm pretty stuck. I assume a Data Source Name will be required - say "myDB" - but I've so far spectacularly failed to properly create an ODBC Data Source via the ODBC Data Source Administrator, at least one that I can use... I started on the User DSN tab and pressed Add, I selected SQL Server Native Client 2011.110.2100.60 (I'm using SQL Server 2012 Express on my laptop, where TM1 is running) and gave it a name - "myDB", description and selected the only option from the which SQL Server to connect to drop-down... I let it use Windows authentication (as I'd gone for that as I installed SQL Server). On the next page I first left Change the default database unchecked and pressed Next, then Finish, but it failed on Test Data Source... saying:

"Attempting connection
[Microsoft][SQL Server Native Client 11.0]Named Pipes Provider: Could not open a connection to SQL Server [2].
[Microsoft][SQL Server Native Client 11.0]Login timeout expired
[Microsoft][SQL Server Native Client 11.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections."

So I went back and tried to check the "change the default database" hoping I could select the database I knew I had, but it would not show me anything.

I thought that the Server is basically my machine's name, right? I can see that in Services - and I can see that the Start menu option in SQL Server Management Studio is greyed-out, implying that it's started. However, I went back and changed it to my machine name followed by "\SQLEXPRESS" and I then saw:

"Microsoft SQL Server Native Client Version 11.00.2100

Running connectivity tests...

Attempting connection
Connection established
Verifying option settings
Disconnecting from server

TESTS COMPLETED SUCCESSFULLY!"

Which is nice. It seems like I have an ODBC data source created! However, Architect still moans at me, saying "unable to open connection to database" when I double-click on the Process, even though the data source name is set... so I figured I would click on Browse and that returned "An error occurred while retrieving datasource names from the server"... so I'm guessing I've missed some important step? I did change the default database to "myDB".

Is there any asset or person out there that could spoon-feed me a little bit? I'm much more interested in getting using things out of SQL Server into TM1 than struggling with the plumbing!

[gtonkin]

Hi Hippo,
The "Named Pipes" issue may be down to surface configuration or firewall on port 1433 - it has been a while since I looked at these in detail but check that Named Pipes is allowed and port 1433 is accessible.

You may also want to create a System DSN (user may be different to the service for TM1) via the 32-bit ODBC as a test otherwise 64-bit failing that.

edit: Also check that you can connect to the SQL server with your credentials via windows authentication, per your config.

[tomok]

Show us a screen shot of the Data Source tab of your TI process.

[Hippogriff]

Morning - I could do that, but it's - like - well, it's blank.

I create a new Process... I'm presented with the Data Source tab, I select a Data Source Type of ODBC and lots of other things appear and I'm then hoping that I can then click the Browse button to select a Data Source. I can't. Architect says "An error occurred while retrieving datasource names from the server."

This is true if I leave UserName and Password blank, or whether I put in my Windows logon credentials (as that's how I set up the Data Source - "With Integrated Windows authentication).

So I'm stuck at that very basic level.

How I created my SQL Server ODBC Data Source:

Tab used was "User DSN"... not "System DSN"... will probably try this next.
Name and Description is "myDB".
SQL Server to connect to is "[machinename]\SQLEXPRESS".
With Integrated Windows Authentication is selected.
Change the default database is checked and set to "myDB" (I can choose this from the drop-down list).
Everything else is default.

Microsoft SQL Server Native Client Version 11.00.2100

Data Source Name: myDB
Data Source Description: myDB
Server: BLAH\SQLEXPRESS
Use Integrated Security: Yes
Database: myDB
Language: (Default)
Data Encryption: No
Trust Server Certificate: No
Multiple Active Result Sets(MARS): No
Mirror Server:
Translate Character Data: Yes
Log Long Running Queries: No
Log Driver Statistics: No
Use Regional Settings: No
Use ANSI Quoted Identifiers: Yes
Use ANSI Null, Paddings and Warnings: Yes

And that tests successfully. Obviously I am missing something very basic and probably obvious.

[Hippogriff]

Created a System DSN and I now get a choice immediately when I press the Browse button.

[Hippogriff]

Um, still cannot connect to the database in Architect, even though I can now select the Data Source name by pressing "Browse".

I am using Windows authentication so I tried either leaving the UserName and Password fields blank or using my Windows logon credentials - Architect says "Unable to Open Connection to Database".

If I create a UDL file and put in the details - server name, NT integrated security (effectively this seems to imply they're blank as I'm not asked to key in my Windows username and password) and select the database from the drop-down list and press Test Connection - it works and tells me "Test connection succeeded".

[Hippogriff]

Nah, I'm stuck.

My simple query works in SQL Server Management Studio, it's about as Noddy as you can get.

SELECT [NAME] FROM [myDB].[dbo].[NAMES]

Returns... Hippo, Lemur, Fred, John, James, Jack, Freddie, Bungle, Zippy and Mary Poppins.

In Architect it just keeps telling me - Unable to Open Connection to Database... but it's not giving me any clues as to why that might be. This happens when I open it or when I press the Preview button.

I've tried blank username and password. My Windows username and password. My Windows username prefixed by [machinename]\ and the password. I can select the myDB data source from within Architect, so something is talking to something, at some level.

[Hippogriff]

Thought it might be a "bitness" thing, so deleted my data source created via Administrative Tools - Data Sources (ODBC) and used the c:\Windows\SysWOW64>.\odbcad32.exe one instead - this is confirmed as a 32-bit bit data source by the free utility ODBC Connect - appears in the 32-bit version, doesn't show in the 64-bit version. Same result, same generic error that isn't helping me get past the issue.

Grumpy now, I'm going to eat something.

Sorry, I forgot... when I used ODBC Connect I did not need a username and password at all, which makes sense... so I think Architect should be the same.

[gtonkin]

Confirm that the TM1 Service is started using a Windows users that does actually have access to SQL and the schema/tables you expect.
May be worthwhile configuring SQl to allow SQL users then give relevant access and try passing the user and password from TI.

[Hippogriff]

Ah!

In Task Manager tm1a.exe has a User Name of my Windows logon, whereas things like tm1admsd.exe and tm1sd.exe (for the Planning Sample Service) have a user name of SYSTEM.

ODBC Connect 32 has the same User Name as Architect, though, and it can connect and retrieve data fine. Curiouser and curiouser. I guess it's not Architect I need to worry about?

[gtonkin]

The service for each TM1 instance i.e. TM1SD.EXE should be started with the relevant user account to access SQL, file shares etc. etc.

[tomok]

whereas things like tm1admsd.exe and tm1sd.exe (for the Planning Sample Service) have a user name of SYSTEM.

Bingo! You can't use integrated login to SQL server if your TM1 service is running under the SYSTEM account. This is a local system ID, which is not part of the Active Directory. Change those services to run under a valid Active Directory account, that has access to the SQL server AND the databases in question, and it should work.

[Hippogriff]

I'm starting to think my angst might be misplaced.

Instead of Windows authentication, I decided to create a login and user of myDBtester in SQL Server Management Studio:

CREATE LOGIN myDBtester
WITH PASSWORD = 'myDBtester';
GO

CREATE USER myDBtester FOR LOGIN myDBtester;
GO

But I cannot even log in to SQL Server Management Studio with this... never mind do anything from Architect or even set up the System DSN with these details.

[Hippogriff]

Change those services to run under a valid Active Directory account, that has access to the SQL server AND the databases in question, and it should work.

Working on this...

[Hippogriff]

OK, at the server level in SQL Server Management Studio - Properties - Security, there was a set of radio buttons where "Windows Authentication Mode" was checked only... I changed this to "SQL Server and Windows Authentication mode" and recreated my data source to adhere to that, now I don't get - "Unable to Open Connection to Database" - and I can preview the data returned from SQL Server.

So that was my issue. Threefold, really... 1 - I was using different accounts for different pieces of software when using Windows authentication, 2 - I couldn't log in using SQL Server authentication because of the above setting blocking me and 3 - I really don't know what I'm doing.

Thanks for taking the time to provide all the pointers.

[Steve Rowe]

Nice Hippo, I think recognising 3 is the first step in being not 3!