I am having trouble user data synchronization where I am using SQL Server 2008 SR2 (64bit) on Windows 2008 SR2 (64bit) as a source. I believe I have configure the data source correctly, because the wizard is able to view the data in the table (i.e. only a few rows of test data). Unfortunately, when the job runs, I get the following error:
0 success rows, 0 error rows. RR_4036 Error connecting to database [ [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed Database driver error... Function Name : Connect Database driver error... Function Name : Connect Database Error: Failed to connect to database using user [informatica] and connection string [odbc://dbtype=SqlServer;host=127.0.0.1;port=1433;database=Test].].
Any clues to how I might debug and resolve this issue. I have tried combinations of settings, including the actual server name. I have confirmed that all of the TCP interfaces are enabled and on the correct port (i.e. if they weren't, I am assuming the wizard wouldn't be able to display the data).
I have exactly the same problem. Same Server Version, connection is green, I can see the tables and the preview data but if I run the task I get the same error as Gavin.
Can anyone help? Is this problem related to our 64bit versions?
the solution was in my case to start the "SQL Server Browser" service with the Sql Server Configuration Manager. The connection did then work with the correct instance name!
I think for connecting SQL server you need to update the ODBC.ini config file in the OS. Hope this may help you a bit