Connecting to Microsoft SQL Server

Selecting the Driver

To connect to SQL Server, use the Microsoft SQLServer JDBC Driver. This is the official Microsoft JDBC driver, and works with SQL Server 2000 (sp3) and above.

Selecting the Database

Unlike some other drivers, the name of the database that the connection will target is defined in the extra connection properties by the databaseName parameter.

Connecting to SQL Server 2000, or a server with a well-known port

To connect to a server using a well known port, use the following Connect URL style:

jdbc:sqlserver://hostname:port

Hostname can be an IP address or the server name, if local on the network. The port, by default, is 1433.

Connecting to a SQL Server named instance

To connect to a named instance (standard in SQL Server 2005 and later), use the following Connect URL syntax:

jdbc:sqlserver://hostname\instancename

The default instance name for SQL Server Express is SQLExpress. Therefore, an common connection URL for connecting to the local database is:

jdbc:sqlserver://localhost\SQLExpress

Using Windows Authentication

To use Windows Authentication, where the connection is authenticated using the identity of the user running the Ignition gateway, you must first install the Microsoft JDBC driver package. The driver package can be found at: http://msdn.microsoft.com/en-us/sqlserver/aa937724.aspx

In the install directory of that package, you will find a sqljdbc_auth.dll file. Copy the correct file for your architecture into the following two directories:

<installation directory>\Ignition\tomcat\webapps\ctx0\WEB-INF\lib

and

<Program Files directory>\Java\<jre folder>\bin

(ie: C:\Program Files\Java\jre6\bin)

Then restart the Ignition service.

In your database connection, add the following Extra Connection Parameter:

integratedSecurity=true;

Common Problems

TCP/IP Communication Not Enabled

SQL Server requires that you explicitly turn on TCP connectivity. To do this, use the SQL Server Configuration Manager, located in the Start menu under "Microsoft SQL Server>Configuration Tools". Under "SQL Server Network Configuration", select your instance, and then enable TCP/IP in the panel to the right. You will need to restart the server for the change to take affect.

Window Firewall

When connecting remotely, make sure that Windows Firewall is disabled, or set up to allow the necessary ports. Normally ports 1434 and 1433 must be open for TCP traffic, but other ports may be required based on configuration.

SQL Server Browser Process Not Running

To connect to a named instance, the "SQL Server Browser" service must be running. It is occasionally disabled by default, so you should verify that the service is not only running, but set to start automatically on bootup. The service can be found in the Windows Service Manager (Control Panel>Administrative Tools>Services).

Mixed Mode Authentication Not Enabled

Unless selected during setup, "mixed mode" or "SQL authentication" is not enabled by default. This mode of authentication is the "username/password" scheme that most users are used to. When not enabled, SQL Server only allows connections using Windows Authentication. Due to the ease of using SQL Authentication over Windows Authentication, we recommend enabling this option and defining a user account for Ignition.

To enable this, open the SQL Server Management Studio and connect to the server. Right click on the instance and select "Properties". Under "Security", select "SQL Sever and Windows Authentication mode".