Microsoft SQL Server Database bridge reference

Prerequisites and parameter information for the Microsoft SQL Server Database bridge.

About this bridge

The Microsoft SQL Server Database bridge imports implemented data resources including database schemas and database tables from Microsoft SQL Server versions 7 to 10.5.

Prerequisites

Meet the following prerequisites before you use the bridge to import metadata.

The bridge establishes a JDBC connection with a physical database to import schemas. You must correctly specify the parameter values to satisfy the local connection on the client workstation that runs the bridge.
The database user name that is specified must have the VIEW DEFINITION and CONNECT permissions.
If you do not specify a database user name to connect to SQL Server, the bridge assumes that integrated security is being used and attempts to connect with the signature jdbc:sqlserver://; integratedSecurity=true instead of jdbc:sqlserver://; user=userid;password=userpassword.

To connect by this method, the file sqljdbc_auth.dll must be available on the PATH environment variable. Also the version of the library must match the version of the sqljdbc4.jar that you are using. For information on integrated connection, see Connecting with Integrated Authentication at http://msdn.microsoft.com.
The bridge does not support logging in by using Windows Authentication. You can provide the user with SQL Server authentication and map it to the necessary DBO database user.
Before you use the bridge for imports, you must copy the SQL Server driver file sqljdbc4.jar from the SQL Server installation folder into the folder \IBM\InformationServer\Clients\MetaBrokersAndBridges\Bridges\java\Jdbc.

Import parameters

The Microsoft SQL Server Database bridge uses the following import configuration parameters.

Host

Required. Type the host name or IP address where the Microsoft SQL Server database server is running, or type the fully qualified JDBC connection string for the Microsoft SQL Server JDBC driver.

An example of a fully qualified JDBC connection string: jdbc:sqlserver://192.168.56.61:1433;databaseName=AdventureWorksDW;integratedSecurity=false

Port

Type the Microsoft SQL Server database engine service port number. The default value is 1433.

For optimal connection performance, set the Port value when you connect to a named instance to avoid a roundtrip to the server to determine the port number.

If you specify values for both the Port and the Instance parameters, the Instance value is ignored.

Instance

If you did not specify a value for Port, type the Microsoft SQL Server instance name. The instance value is used only if a value for Port is not specified.

SQL Server 2000 and SQL Server 2005 allow for the installation of multiple database instances per server. Each instance is identified by a specific name. To connect to a named instance of SQL Server, either specify the port number of the named instance for optimal performance, or specify the instance name.

User name

Type the name of the database user on whose behalf the connection is being made. The user must have at least VIEW DEFINITION permission and CONNECT permission for the database.

If you leave the field blank, the bridge attempts to connect by using integrated authentication. See the bridge prerequisites section for details.

Password

Type the password for the database user on whose behalf the connection is made.

Database

Required. Browse for a single database to import or type the name of the database. The schemas and tables of this database are imported.

Schemas

Browse to select schemas from the specified database, or type a list of schemas separated by semicolons (;). If you leave the field blank, only the tables of the default schema are imported.

Import stored procedures

Select to import stored procedures. By default, stored procedures are not imported.

Metadata consistency check

Perform a consistency check on the selected metadata before it is imported into the metadata repository. It is possible to save metadata in source tools in ways that cause problems when the assets are imported into the metadata repository or used in other tools. For example, a foreign key might have no connection to a primary key or to an alternate key. In some cases, the metadata might be so semantically inconsistent that the bridge cannot import it.

The metadata consistency check returns warnings and errors in the log file.

Basic check: The default. Performs the minimum consistency checks necessary to validate the metadata, including checking for missing relationships and foreign keys that are not connected to primary or alternate keys.; In some cases, the basic check might be more rigorous than necessary and you can ignore certain errors or warnings.
Detailed check: Performs the basic check plus more advanced semantic checks specific to the type of metadata that is imported. This level can be used when the source tool does not have the ability to validate the metadata.
No check: Use with extreme caution. Selecting this option could result in the import of duplicates or invalid identities and might cause serious problems with your use of suite tools and the metadata repository.