Problem:
Error Message and Blank list of tables to choose from when configuring CDC Service

Scenario:
When using SQL Server CDC for SSIS, I create a new CDC Service using the ‘CDC Service Configuration Wizard’. The source SQL Server database resides on a different machine than the one I am developing on. In the first screen, I use ‘Windows Authentication’ to connect to the source SQL Server, and successfully pass the ‘Test Environment’ check.


The CDC Service is created successfully in the ‘CDC Service’ step, but after clicking ‘Next’ on the ‘CDC Service’ step, I get a screen with an error message and NO tables listed.

This is when you would expect to see a list of tables from the source database to choose from to create the CDC solution. Instead, you get an error message similar to:
“Error while retrieving tables list:[ExA018] server.internalError:HY000 General Error
SDK9051E: FETCH ERROR
SDK9051X: Failed to bind to Datasource ‘BULK’
SQS9028E:[Microsoft][ODBC SQL Server Driver][SQL Serever] Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’. (SQL State:00000; SQL Code: 18456) [A016 GetRows on RowCursor failed on table name ‘SP_TABLES’.”


CAUSE:
This is most often caused when the account associated Windows Service that has been created by the wizard does not have access to the SQL Server used as a source. By default, the wizard creates the service (which will be named AttunitySQLServerCDC_PROJECTNAME) to use the LocalSystem account.

SOLUTION:
Click OK on the error message, but DO NOT cancel out of the CDC Service Configuration Wizard.

Leave the ‘Select Tables’ Screen open, and open the Windows Services MMC.
You should see the newly created
‘AttunitySQLServerCDC_PROJECTNAME
service has been created and started. By default it uses the ‘LocalSystem’ account to run under. You will need to change it to run with the Windows account you want.

After supplying the valid Windows user for the CDC service, re-start the service, go back to BIDS, and click ‘Refresh’ in the ‘Select Tables’ screen.


You should now see the list of tables from your source database to select from.

Complete the CDC Service Wizard and deploy the CDC Service. When that is done, re-open the Windows Services MMC – you should now see the associated ‘Logger’ service having been created. The naming convention for this service is:
‘AttunitySQLServerCDC_PROJECTNAME_Logger’


This service is part of the Transaction Log Protector, and is also set to use the ‘LocalSystem’ account by default. You will need to go through the same steps to change the user account on this service as was done with the AttunitySQLServerCDC_PROJECTNAME service.