Microsoft SQL Server 2008 connection string for QB #1598

leo7654321 · 1 decade ago

Hi,

I have a commercial licensed QB 4.0 running OK on the embedded database, and now would like to get it running on an instance of SQL Server on another physical server in our network. Hasn't worked so far (keep getting an error when it tries connecting -- I'm not at work now, so can't remember the exact message, but something to the effect of "connection refused" or "user domain\user can't connect"), so I have a few questions:

1. Does the SQL QuickBuild database have to reside in an instance that just has QuickBuild databases, or can the database be created in an instance that's already being used for, say, an installation of TFS?
2. In the example connection URL string in conf/hibernate.properties, there doesn't seem to be any defining of the database name to be connected to, just the server\instance. Once QuickBuild successfully connects to that instance of SQL Server when the QuickBuild server is first started, how does it know which database in the instance it's supposed to use?
3. What SQL database permissions/rights have to be granted to QuickBuild's domain user account for it to be able to successfully connect to its database and read/write as necessary?

Thanks in advance for any hints or tips to get this to work. I've already read through other posts on this forum, and tomorrow will try the telnet trick to see if I can connect to that SQL Server port that way.

Leo

replies 9
views 5989
stars 0

leo7654321 · 1 decade ago

Here's some more details.
My client server is QBUILD. My database server is TFS2010. My service account is QBProcess.
From QBUILD, a command of "telnet TFS2010 1433" works, I get the blank screen telling me that the connection occured without errors.
From QBUILD, a command of "telnet TFS2010 8810" doesn't work, I get a connection error.
From TFS2010, a command of "telnet QBUILD 8810" works.
From TFS2010, a command of "telnet QBUILD 1433" does not work.
Correct me if I'm wrong, but all of this is as it should be, no?

I have created a brand new database called qbuild. This was created in the default SQL instance on TFS2010 (all the other dbs in this instance are TFS-related -- is it OK to put a QB db here alongside these TFS dbs? Or does the QB DB require its own seperate instance?).

Everything in my conf\hibernate.properties file is commented out save for the following lines:

hibernate.dialect=org.hibernate.dialect.SQLServerDialect
hibernate.connection.driver_class=net.sourceforge.jtds.jdbc.Driver
hibernate.connection.url=jdbc:jtds:sqlserver://tfs2010:1433;databaseName=qbuild
hibernate.connection.username=QBProcess
hibernate.connection.password=[password]

When I run server.bat console from a command prompt with these settings, I get the following feedback:

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.

C:\Tools\quickbuild-4.0.0\bin>server.bat console
wrapper | --> Wrapper Started as Console
wrapper | Java Service Wrapper Standard Edition 64-bit 3.5.12
wrapper | Copyright (C) 1999-2011 Tanuki Software, Ltd. All Rights Reserved.
wrapper | http://wrapper.tanukisoftware.com
wrapper | Licensed to PMEase Inc. for QuickBuild
wrapper |
wrapper | Launching a JVM...
jvm 1 | WrapperManager: Initializing...
jvm 1 | 2011-10-02 14:33:53,845 INFO - Cleaning temp directory...
jvm 1 | 2011-10-02 14:33:54,173 INFO - Benchmarking...
jvm 1 | 2011-10-02 14:33:58,861 INFO - Booting QuickBuild from 'C:\Tools\quickbuild-4.0.0'...
jvm 1 | 2011-10-02 14:33:58,876 INFO - Starting framework...
jvm 1 | 2011-10-02 14:34:04,830 INFO - Starting QuickBuild server...
jvm 1 | 2011-10-02 14:34:04,830 INFO - Starting IOC container...
jvm 1 | 2011-10-02 14:34:05,564 INFO - Initializing velocity...
jvm 1 | 2011-10-02 14:34:06,549 INFO - Checking data version...
jvm 1 | 2011-10-02 14:34:06,736 ERROR - Error starting QuickBuild.
jvm 1 | java.lang.RuntimeException: java.sql.SQLException: Login failed for user 'QBProcess'.
jvm 1 | at com.pmease.quickbuild.bootstrap.BootstrapUtils.wrapAsUnchecked(BootstrapUtils.java:58)
jvm 1 | at com.pmease.quickbuild.util.ExceptionUtils.wrapAsUnchecked(ExceptionUtils.java:82)
jvm 1 | at com.pmease.quickbuild.util.DbUtils.getConnection(DbUtils.java:79)
jvm 1 | at com.pmease.quickbuild.util.DbUtils.getDataVersion(DbUtils.java:88)
jvm 1 | at com.pmease.quickbuild.Quickbuild.startServer(Quickbuild.java:283)
jvm 1 | at com.pmease.quickbuild.Quickbuild.access$3(Quickbuild.java:226)
jvm 1 | at com.pmease.quickbuild.Quickbuild$2.run(Quickbuild.java:161)
jvm 1 | at java.lang.Thread.run(Thread.java:662)
jvm 1 | Caused by: java.sql.SQLException: Login failed for user 'QBProcess'.
jvm 1 | at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
jvm 1 | at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
jvm 1 | at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
jvm 1 | at net.sourceforge.jtds.jdbc.TdsCore.login(TdsCore.java:603)
jvm 1 | at net.sourceforge.jtds.jdbc.ConnectionJDBC2.<init>(ConnectionJDBC2.java:345)
jvm 1 | at net.sourceforge.jtds.jdbc.ConnectionJDBC3.<init>(ConnectionJDBC3.java:50)
jvm 1 | at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:184)
jvm 1 | at java.sql.DriverManager.getConnection(DriverManager.java:582)
jvm 1 | at java.sql.DriverManager.getConnection(DriverManager.java:185)
jvm 1 | at com.pmease.quickbuild.util.DbUtils.getConnection(DbUtils.java:74)
jvm 1 | ... 5 more
jvm 1 | 2011-10-02 14:34:06,736 INFO - Stopping QuickBuild server...
jvm 1 | 2011-10-02 14:34:07,736 INFO - Stopping task scheduler...
jvm 1 | 2011-10-02 14:34:07,736 INFO - QuickBuild server stopped.
jvm 1 | 2011-10-02 14:34:08,736 INFO - Stopping QuickBuild server...
jvm 1 | 2011-10-02 14:34:08,736 INFO - Stopping task scheduler...
jvm 1 | 2011-10-02 14:34:08,736 INFO - QuickBuild server stopped.
wrapper | <-- Wrapper Stopped

C:\Tools\quickbuild-4.0.0\bin>

Any ideas? Could it be some sort of firewall issue, even though telnet works? A rights issue in SQL?

robinshen ADMIN · 1 decade ago

The instance name can be specified via below url:

jdbc:jtds:sqlserver://tfs2010:1433/qbuild;instance=<instance_name>

If instance property is not specified, QB will connect to the default instance. The user specified in hibernate.properties ought to have write permission on the database, for instance, be able to create tables, drop tables, insert/delete/update rows.

For your problem, I suggest to install sqlcmd on server QBUILD, and test connectivity to sql server by running:

sqlcmd -U qbuild -P <password> -S tfs2010,1433
1> use qbuild;
2> go

leo7654321 · 1 decade ago

The connection URL you post has server and instance, but again, how does QB know which database to use within that instance? My fear is that QB is going to start mucking with my TFS databases in the same instance.

I ran sqlcmd on server QBUILD, and got the same error as when I try starting the QB server via the console:

C:\quickbuild-4.0.0\bin>sqlcmd -U QBProcess -P [password] -S tfs2010,1433
Msg 18456, Level 14, State 1, Server TFS2010DB, Line 1
Login failed for user 'QBProcess'.

robinshen ADMIN · 1 decade ago

qbuild is the database to be used in below connection string:
jdbc:jtds:sqlserver://tfs2010:1433/qbuild;instance=<instance_name>

If sqlcmd can not succeed, most probably the database "qbuild" is not created in the default instance. You may try to append the instance name to see if it works:

sqlcmd -U qbuild -P <password> -S tfs2010/<instance name>,1433
1> use qbuild;
2> go

leo7654321 · 1 decade ago

Well, after some reading and experimentation, I was able to get a successful SQLCMD connection by changing your recommeded line from this:

sqlcmd -U QBProcess -P <password> -S tfs2010/<instance name>,1433

to this:

sqlcmd -S tfs2010
1> use qbuild;
2> go

I think this is because using -U equals SQL authentication, whereas leaving it out assumes -E, which is Windows authentication.

Looking through the online documentation for the jTDS JDBC driver which QuickBuild uses, it looks as if I will need to use a DOMAIN= property in my conf\hibernate.properties URL in order for it to use Windows authentication instead of SQL authentication. In that situation, do I still fill out the user and pass in the next two lines of conf\hibernate.properties? Do you have a standard connection URL for Windows authentication as opposed to SQL authentication? Thanks.

robinshen ADMIN · 1 decade ago

Sorry I am not a SQL server expert. You may test with domain property to see if it works. The user name and password can be left empty in this case.

leo7654321 · 1 decade ago

OK, we finally got this to work. You might want to add this to your official documentation for those users who want to use a SQL Server database with Windows SSO (Single Sign-On) authentication.

First, here's what our conf\hibernate.properties file looks like:

hibernate.dialect=org.hibernate.dialect.SQLServerDialect
hibernate.connection.driver_class=net.sourceforge.jtds.jdbc.Driver
hibernate.connection.url=jdbc:jtds:sqlserver://[server name];databaseName=[database name];domain=[Windows domain name]
hibernate.connection.username=
hibernate.connection.password=

Then, you need a copy of the SSO Library, a file called ntlmauth.dll, which is available in the free jTDS driver download available online here: http://sourceforge.net/projects/jtds/files/ (I suppose you could even start including this file in your QuickBuild installation packages). Put this DLL into any directory that is in your server's PATH variable. In Windows Server 2008, you can access your PATH by going to START--> right-click on COMPUTER --> Properties --> Advanced System Settings --> Environment Variables --> System Variables --> Path.

Once you do these steps, QuickBuild can connect to a SQL database using SSO Windows Authentication (I guess it's using the same usercode that is setup to run the QuickBuild service in Computer Management--> Services).

Anyway, I just restored our Quickbuild backup to the SQL database, and it looks like it is working. Thanks so much for all of the suggestions and tests, they were invaluable when attempting to figure this out. (I'm no SQL expert either, alas). All of your help was very much appreciated.

robinshen ADMIN · 1 decade ago

Thanks a lot for the info. We will add this info to our wiki to help others.

#10

leo7654321 · 1 decade ago

One more bit of information: here are the errors that QuickBuild generated during startup (/bin/server.bat console) and what they meant for a user trying to use Windows Authentication to connect to their SQL Server database:

Login failed for user '[your domain usercode]'.

This means you failed to put the DOMAIN= property into your conf/hibernate.properties login string.

I/O Error: SSO Failed: Native SSPI library not loaded. Check the java.library.path system property.

This means you either do not have the ntlmauth.dll file at all, or do not have it located in a directory that is covered by your server's PATH variable.

Thanks.