JDBC configuration for MS SQL Server with Integrated Security
Article ID: KB0073519
Updated On:
| Products | Versions |
|---|---|
| TIBCO Streaming | 7 |
Description
When Integrated Security is enabled the application fails to typecheck the JDBC tables and associated Query operators.
What extra configuration steps are needed in StreamBase to use Microsoft SQL Server as a JDBC data source when also using the Integrated Security option?
What extra configuration steps are needed in StreamBase to use Microsoft SQL Server as a JDBC data source when also using the Integrated Security option?
Issue/Introduction
JDBC configuration for MS SQL Server with Integrated Security
Resolution
When using Integrated Security, the Microsoft JDBC Driver library, sqljdbc4.jar, and the native library, sqljdbc_auth.dll, require additional configuration to use.
The reason that this extra configuration is needed is that sqljdbc4.jar is only allowed to load sqljdbc_auth.dll once per session because of a one-time connection made by sqljdbc_auth.dll. Normally each time Studio typechecks it disposes of the previous class-loader and reloads the application Java libraries. This would cause sqljdbc4.jar to load again and attempt to load sqljdbc_auth.dll again, which fails. By placing the sqljdbc4.jar in "bootext" it is guaranteed to be loaded only once and initialize the connection using sqljdbc_auth.dll only once, avoiding the problem. Additional steps are needed to ensure it can find the native library however the application may be started.
Steps:
1. Put sqljdbc4.jar in %STREAMBASE_HOME%\lib\bootext\.
2. Place sqljdbc_auth.dll into any local directory without spaces in the path.
3. Create sbd.sbconf with data-source definition (example):
This configures sbd for running outside of Studio from the command-line.
4. In Studio, Open the project Build Path editor in studio and use "Add external JARs" to add sqljdbc4.jar from STREAMBASE_HOME\lib\bootext\. Expand the sqljdbc4.jar entry on the Libraries tab and select the "Native library location:" entry. Click "Edit" and add the path to the directory containing sqljdbc_auth.dll. Click "OK" to exit the editor dialogs. This configures Studio for typechecking and for launching sbd.
The reason that this extra configuration is needed is that sqljdbc4.jar is only allowed to load sqljdbc_auth.dll once per session because of a one-time connection made by sqljdbc_auth.dll. Normally each time Studio typechecks it disposes of the previous class-loader and reloads the application Java libraries. This would cause sqljdbc4.jar to load again and attempt to load sqljdbc_auth.dll again, which fails. By placing the sqljdbc4.jar in "bootext" it is guaranteed to be loaded only once and initialize the connection using sqljdbc_auth.dll only once, avoiding the problem. Additional steps are needed to ensure it can find the native library however the application may be started.
Steps:
1. Put sqljdbc4.jar in %STREAMBASE_HOME%\lib\bootext\.
2. Place sqljdbc_auth.dll into any local directory without spaces in the path.
3. Create sbd.sbconf with data-source definition (example):
<java-vm>
<jar file="${STREAMBASE_HOME}/lib/bootext/sqljdbc41.jar" />
<library path="<path-to-directory-containing-sqljdbc_auth.dll>" />
</java-vm>
<data-source name="MSSQL64" type="jdbc">
<driver value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
<uri value="jdbc:sqlserver://localhost;databasename=mssql_test;user=sa;password=sa;integratedSecurity=true"/>
</data-source>
This configures sbd for running outside of Studio from the command-line.
4. In Studio, Open the project Build Path editor in studio and use "Add external JARs" to add sqljdbc4.jar from STREAMBASE_HOME\lib\bootext\. Expand the sqljdbc4.jar entry on the Libraries tab and select the "Native library location:" entry. Click "Edit" and add the path to the directory containing sqljdbc_auth.dll. Click "OK" to exit the editor dialogs. This configures Studio for typechecking and for launching sbd.
Attachments
Feedback
Yes
No
Powered by
