Problems getting SQL connection

Our IT dept and I are working on this proposed project and are having trouble getting PMI to attach to our MicroSoft SQL server. We have tried it using the //name and also with //IP. Also with and without username and password. But no matter how much we have attemped to connect it still fails to connect. Any assistance appreciated.

Setting your database connection is a one time thing done in the Gateway configuration web page under datasource connections. Add a new one with type “Microsoft SQL Server”. The important setting is the connect URL, which should be in this form: jdbc:jtds:sqlserver://host:1433/database

Leave that string the same. The only values that you should have to change are host and database, which should be the name/IP/URL of the computer and the database name, respectively. If you changed the port that the database runs on, change the 1433 to match.

Also type in the username and password.

If you are trying to use the local windows authentication (non-standard for a server setup), meaning having the currently logged on windows user be the authentication, the driver supports it but the connect URL changes a bit as described here

So if I want to try out the windows auth method if I understand the forum you redirected me to I need to put this file NTLMAUTH.DLL into my systems directory of windows. Then it will use the SSO of windows to log me into the SQL Server.

Is that correct so Far???

Now the next question you said it changes the connection string, I could not find documentation on that. Could you be more clear and just write it out in the proper order if using the SSO.

Thank you.

[quote=“nathan”]Setting your database connection is a one time thing done in the Gateway configuration web page under datasource connections. Add a new one with type “Microsoft SQL Server”. The important setting is the connect URL, which should be in this form: jdbc:jtds:sqlserver://host:1433/database

Leave that string the same. The only values that you should have to change are host and database, which should be the name/IP/URL of the computer and the database name, respectively. If you changed the port that the database runs on, change the 1433 to match.

Also type in the username and password.

If you are trying to use the local windows authentication (non-standard for a server setup), meaning having the currently logged on windows user be the authentication, the driver supports it but the connect URL changes a bit as described here[/quote]

The simplest way to connect is with the connect string in the following format:
jdbc:jtds:sqlserver://host:1433/database
This is documented right below where you type the string, and much more extensively on the jTDS documentation on the link that I included above under the “What is the URL format used by jTDS?” section.

Let me give you an example. Suppose your IP address is 192.168.1.2 and your database name is fsql_test running on the default port of 1433.

This would be your connect string:
jdbc:jtds:sqlserver://192.168.1.2:1433/fsql_test

I don’t think you need a .DLL in any case. I can’t find where you found it on that link. If you want to go the more complicated route (single sign on) you would probably use the domain and instance parameters of the connect URL. There are many different options that may be necessary depending on your setup. If you follow the previous link to the driver details and look under “What is the URL format used by jTDS?” that gives all possible connection parameters.

The most typical instance is setting up a connection to a MS SQL Server 2005 Express (free) Edition, which uses these named instances instead of standard TCP/IP connections. There are many forum posts addressing that issue including this post. Tweaking the security settings of the SQL Sever is typically the most painful part. I will eventually write a white paper on the subject. It seems like every time I set one up there are different security settings tweaks.

If you have a standard SQL Server setup, it’s very easy to set up a connection as I described above with the connection string.