Test Connection Exception for SQL Server as repository database during Informatica installation

Update Strategy Transformation
How should the target table be updated if there is no primary and unique key defined?
May 3, 2017
L1 and L2 Support
L1 and L2 Support
May 26, 2017
Show all

Test Connection Exception for SQL Server as repository database during Informatica installation

One of the issues which is faced during the installation of Informatica 9x/10x with Microsoft SQL Server 2014 as repository database. Created the Repository database and granted the user with DBA privileges.

However when we start installing Informatica server and try to connect to SQL Server database at the Create Domain screen, one of the errors which we face is the below.

***************

This log was generated during Informatica 9.6.1 Services installation

Components installing are … : server,ODBC7.1,isp,services,java,tomcat,tools,DataTransformation,jasperreports-server,plugins,externaljdbcjars,connectors,pwxmfplugins

Disk Space Check Enabled : true

Enter the installation directory : C:\Informatica\9.6.1

Required Disk Space: 6,310 MB

Available Disk Space: 382,106 MB

Configuring files
=================
C:\Informatica\9.6.1\services\MetadataManagerService\utilities\mmcmd\mmcmd.bat
C:\Informatica\9.6.1\services\MetadataManagerService\utilities\mmrepocmd\mmrepocmd.bat
*************************************
Creating Shortcuts

Installing VS C++ 2008 RT
**********************************
C:\Informatica\9.6.1\tools\VS2008\vcredist_x64.exe /q

COMMAND_EXITCODE: 0

Generating keystore… 2:35:20 PM
**********************************

Command Executed : C:\Informatica\9.6.1\java\jre\bin\keytool -genkey -alias “infa_dflt” -keyalg “RSA” -keypass ***** -storepass ***** -keystore “C:\Informatica\9.6.1\tomcat\conf\Default.keystore” -dname “CN=ajaved-w7.na.ds.lexmark.com,OU=Informatica,O=Informatica,L=RedwoodCity,S=California,C=US”

Exit Code : 0

OutPut :

Test Connection Exception -java.sql.SQLNonTransientConnectionException: [informatica][SQLServer JDBC Driver]Error establishing socket to host and port: AJAVED-W7:1433. Reason: Connection refused: connect

**************

For resolving this issue, there is something called as READ_COMMITTED_SNAPSHOT which is by default OFF and it should be set to ON. The below SQL command can be used to update the property.

SELECT is_read_committed_snapshot_on from sys.databases where name=’infarep’ — This should be 1, which is equal to ON

ALTER DATABASE infarep

SET READ_COMMITTED_SNAPSHOT ON

Sid
Sid
Business Intelligence Consultant and Trainer with 13+ years of extensive work experience on various client engagements. Delivered many large data warehousing projects and trained numerous professionals on business intelligence technologies. Extensively worked on all facets of data warehousing including requirement gathering, gap analysis, database design, data integration, data modeling, enterprise reporting, data analytics, data quality, data visualization, OLAP. Has worked on broad range of business verticals and hold exceptional expertise on various ETL tools like Informatica Powercenter, SSIS, ODI and IDQ, Data Virtualization, DVO, MDM.

Leave a Reply

Your email address will not be published. Required fields are marked *

Share
+1
Tweet
Pin
Share