connection to mssql server

**tomislav** · 12-15-2005, 07:42 AM

I'm trying to run MDX queries on a mssql 2000 database with very little succes. I'm using tomcat 5.5.9, jtds 1.0 jdbc driver, jdk 1.5.0-06 on linux and I get the following error in the exception trace when I try to run a MDX query:

mondrian.olap.MondrianException: Mondrian Error:Internal error: Error while creating connection from data source
at mondrian.resource.MondrianResource$_Def0.ex(MondrianResource.java:1015)
at mondrian.olap.Util.newInternal(Util.java:839)
...
[snip!]
...
at java.lang.Thread.run(Thread.java:595)
Caused by: java.sql.SQLException: Single-Sign-On is only supported on Windows. Please specify a user name.
at net.sourceforge.jtds.jdbc.TdsCore.sendMSLoginPkt(TdsCore.java:1703)
...

My connection string in the web.xml file is as follows:
jdbc:jtds:sqlserver://192.168.0.3/zs2?user=myusername&password=mypassword

I've tried a number of variations, but it seems that the username and password just get ignored. This is a bit frustrating.

I also have to specify a jdbcUrl in my .jsps and those look like so:
jdbc:jtds:sqlserver://192.168.0.3/zs2?user=myusername&password=mypassword

Can anybody advise me as to the syntax of my connection strings?

On a side note, I was able to connect to a postgresql database, but don't know how I would load the data from the MSSQL database into pgsql if I take that direction...any advice on the subject is very welcome.

TIA,
Tomislav

**mbatchelor** · 12-15-2005, 11:09 PM

Hi there,

I suggest you use the free Microsoft SQL Server JDBC driver, located here:

http://www.microsoft.com/downloads/d...DisplayLang=en

The documentation for the driver is included in the download, including the JDBC URL format.

Hope this helps,

Marc

**jhyde** · 12-16-2005, 10:27 AM

Marc's suggestion -- SQL Server's own JDBC driver -- will work, but I've been hearing good things about jtds too, so don't count it out.

I think the problem is the '&' ampersand separating the password parameter. You need to escape it in your web.xml:

jdbc:jtds:sqlserver://192.168.0.3/zs2?user=myusername&password=mypassword

See also Mondrian's help forum on SourceForge:

http://sourceforge.net/forum/forum.php?forum_id=111376

**tomislav** · 12-16-2005, 11:31 AM

No luck. I set the following connection string in my $WEBAPPS/mondrian/web.xml file:

connectString
Provider=mondrian;Jdbc=jdbc:microsoft:sqlserver://192.168.0.3:1433;DatabaseName=zs2;user=myusername;password=mypassword; Catalog=/WEB-INF/queries/FoodMart.xml;JdbcDrivers=com.microsoft.jdbc.sqlserver.SQLServerDriver;

I tried with ; instead of ;, but the result was the same:

[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

I'm totally at a loss here, can't believe it's this hard to make it work.

**jdixon** · 12-16-2005, 09:07 PM

I think Julian means try using '&' instead of & e.g.

Code:

jdbc:jtdsqlserver://192.168.0.3/zs2?user=myusername&password=mypassword

James

Post edited by: jdixon, at: 12/17/2005 02:07

**tomislav** · 12-17-2005, 09:30 AM

I forgot about *that* kind of escaping.

I tried it just now (with the string as follows):

Code:

jdbc:jtdsqlserver://192.168.0.3/zs2?user=myusername&password=mypassword

The result remains the same:
"Single-Sign-On is only supported on Windows. Please specify a user name."

But escaping the ampersand seems irrelevant to me (at least at this stage): when I try to log on without specifying a password (in other words, without an ampersand), I get the same error message. As far as the official microsoft driver is concerned, there are no ampersands there and I can't get that one to even register a username, letalone a password.

To recapitulate:
* I've placed the mondrian.war in the $TOMCAT/webapps dir and unpacked it into $TOMCAT/webapps/mondrian
* I've placed the jtds and microsoft drivers in $TOMCAT/common/endorsed
* I've updated mondrian.properties as follows:

Code:

mondrian.result.limit=50000
mondrian.foodmart.jdbcURL=jdbc:jtds:Â«Â»sqlserver://192.168.0.3:1433/zs2?user=...&password=...
mondrian.jdbcDrivers=com.microsoft.jdbc.sqlserver.SQLServerDriver,net.sourceforge.jtds.jdbc.Driver
mondrian.trace.level=1
mondrian.debug.out.file=/tmp/mondrian.log
mondrian.rolap.aggregates.Use=true
mondrian.rolap.aggregates.Read=true

* I've updated the web.xml file in $TOMCAT/webapps/mondrian/WEB-INF as follows:

Code:

...

  ...
  
connectString

Provider=mondrian;Jdbc=jdbc:jtds:Â«Â»sqlserver://192.168.0.3:1433/zs2?user=...&password=...; Catalog=/WEB-INF/queries/FoodMart.xml;JdbcDrivers=net.sourceforge.jtds.jdbc.Driver;RoleXX='California manager';
  
...

...

    MDXQueryServlet
    mondrian.web.servlet.MDXQueryServlet
    
      connectString
Provider=mondrian;Jdbc=jdbc:jtds:Â«Â»sqlserver://192.168.0.3:1433/zs2?user=...&password=...; Catalog=/WEB-INF/queries/FoodMart.xml;JdbcDrivers=net.sourceforge.jtds.jdbc.Driver;RoleXX='California manager';
    
  
...

* I start tomcat
* I use the "basic interface for ad hoc queries" link on the http://localhost:8080/mondrian/ page as a test tool: I select any query (I am aware that none of them work with an "empty" database, but I'm solving connectivity problems now so I guess it's as good a way to test as any)
* and last but not least, I analyze the various error messages I've provoked so far.

I feel this is a valid test as I get an expected error (something about none existant tables or schemas or dimensions...) when I use an empty postgresql database instead of mssql.

At this point, I'm curious: has anyone been able to connect mondrian with MSSQL? How?
This connectivity problem seems to have delayed my plans for deployment of a first prototype olap app to (at least?) January 2006 and it's hard to get over the fact that it's over something so trivial as a connection string.

**tomislav** · 12-17-2005, 09:49 AM

I must be overworked or something 'couse I replyed to Julian instead of James as well as forgot to preview my (now obviously) hideous message layout: please have a look at it anyway and I'll take care not to repeat such mistakes.

**tomislav** · 12-18-2005, 09:18 AM

I've found a solution - finally! - that is kind of a way arround the problem, but no so much that it'd bother me.

The answer is so obvious that I can't help but think I got too close to the problem to see the big picture: I just placed the whole string into quotes and voila! Hope this helps someone...
Julian helped with the link to the sourceforge forum: someone there mentioned quoting the connection string...I know, it seem silly now not to have thought of it right away but sometimes it's hard to think simple.

I can now establish a connection with whichever driver I choose (jtds) and can proceed to develop a schema of my own...finally try some queries of my own!

Big round of applause for everyone who took the time to help me on this one, thanks!

**jerics99** · 08-23-2006, 04:22 AM

Hi,

Could you please show us your full sample connection string in web.xml. I have used mondrian a lot with MYSQL but I currently have problems getting it to work with SQL Server.

Cheers!