Microsoft SQL Server database connections

Use the New Database Connection wizard or the Database Connection page to create a JDBC connection to Microsoft SQL Server.

The following JDBC drivers are supported for Microsoft SQL Server. The available drivers vary depending on the version that you are connecting to.

DataDirect SequeLink Driver

The DataDirect SequeLink server must be installed on the remote Microsoft SQL Server server. The DataDirect SequeLink server is licensed software that you must purchase from DataDirect Technologies.

You can either use the DataDirect SequeLink driver that is included with this product, or you can download this JDBC driver from www.datadirect-technologies.com.

Restriction: If you connect using the DataDirect SequeLink 5.3 JDBC driver, you cannot retrieve tables from Microsoft SQL Server Version 7 or Microsoft SQL Server 2000 if you are not the owner of these tables. For example, if you own Table1 and Table2 on DatabaseA, and you specify in the Database Connection wizard to connect to DatabaseB, then only Table1 and Table2 will be shown even though these tables do not belong to DatabaseB. They are retrieved because the user ID used to connect is the owner of the tables.

Microsoft DataDirect Connect JDBC Driver

The Microsoft DataDirect Connect JDBC Driver is included with MS SQL Server 2000. This is licensed software that purchase from Microsoft.

You can download the JDBC driver Microsoft DataDirect Connect JDBC Driver from www.microsoft.com/sql.

WebSphere Connect JDBC Driver

The WebSphere Connect JDBC Driver is the DataDirect Connect JDBC driver that is included with this product. This driver consists of three JAR files: base.jar, sqlserver.jar, and util.jar (or names similar to these).

You can either use the WebSphere Connect JDBC Driver that is included with this product, or you can download this JDBC driver from www.ibm.com/support/docview.wss?rs=180&org=SW&doc=4001312.

Restriction: Using the WebSphere Connect JDBC driver, you cannot retrieve tables from Microsoft SQL Server Version 7 if you are not the owner of these tables. For example, if you own Table1 and Table2 on DatabaseA, and you specify in the Database Connection wizard to connect to DatabaseB, then only Table1 and Table2 will be shown even though these tables do not belong to DatabaseB. They are retrieved because the user ID used to connect is the owner of the tables.

To retrieve tables that you do not own, have database role membership in the roles 'public' and 'db_datareader'.

To change the database role membership:

  1. Start the Microsoft SQL Server Enterprise Manager.

  2. Under the Console Root, open the Users folder under the database that you are working with. A list of users for this database is shown in the right hand pane.

  3. Right-click on the user that you want to change, and click Properties.

  4. Select the appropriate database role memberships in the list:

    db_datareader

    Read access

    db_datawriter

    Insert, update, and delete access

    db_ddladmin

    Table and view delete access

  5. Click OK.

 

Related tasks

Troubleshooting database connection problems

Related reference
Supported database types