Error when attempting to use Azure SQL database within an elastic pool as source for CDC based replication

[gavindew]

Enviroment

• Airbyte version: example is 0.29.21-alpha
• OS Version / Instance: example Ubuntu 20.04
• Deployment: Docker
• Source Connector and version: Microsoft SQL Server (MSSQL) 0.3.5
• Severity: High
• Step where error happened: Setup new source

Current Behavior

Fails on save with the error:

Could not connect with provided configuration. Error: com.microsoft.sqlserver.jdbc.SQLServerException: USE statement is not supported to switch between databases. Use a new connection to connect to a different database.

Expected Behavior

Should validate the database and save the source.

The database is configured for CDC and size is > S3 standard so CDC is supported and enabled.

Steps to Reproduce

1. Create a new source
2. Fill out the connection details pointing to an Azure SQL database within an elastic pool and set the replication type to CDC
3. Attempt to save and then error occurs (Note, works if replication type is standard)

[marcosmarxm]

Hi @gavindew we use the USE to select the database with CDC. What version of MSSQL database are you using?
From Microsoft docs the USE is supported by Azure Managed Instance.

airbyte/airbyte-integrations/connectors/source-mssql/src/main/java/io/airbyte/integrations/source/mssql/MssqlSource.java

Lines 175 to 188 in 6d9ba02

	protected void assertCdcSchemaQueryable(JsonNode config, JdbcDatabase database) throws SQLException {
	List<JsonNode> queryResponse = database.query(connection -> {
	final String sql = "USE " + config.get("database").asText() + "; SELECT * FROM cdc.change_tables";
	PreparedStatement ps = connection.prepareStatement(sql);
	LOGGER.info(String.format("Checking user '%s' can query the cdc schema and that we have at least 1 cdc enabled table using the query: '%s'",
	config.get("username").asText(), sql));
	return ps;
	}, sourceOperations::rowToJson).collect(toList());
	// Ensure at least one available CDC table
	if (queryResponse.size() < 1) {
	throw new RuntimeException("No cdc-enabled tables found. Please check the documentation on how to enable CDC on MS SQL Server.");
	}
	}

[gavindew]

Hi @marcosmarxm. The database is an Azure Sql database, not a Azure Sql Managed Instance. Azure Sql databases do not support the USE keyword.

For servers not supporting that USE statement, you need to specify the database before connecting.

You can determine if you are running on Azure SQL vs Azure SQL Managed Instance using the ServerProperty('Edition') or ServerProperty('EngineEdition'). My suggestion would be if you are running on Azure SQL, you know you will always be in the correct database already (as you have no access to master) and so you can skip the USE statement.

[grishick]

This requires further investigation before we decided that MS SQL connector cannot be used for Azure. Could be a simple fix.

[grishick]

@alexandr-shegeda @kimerinn looks like the change which fixed this issue was reverted. Is this issue still fixed or should it be reopened now?

[creativedutchmen]

Even though the changeling mentions the fix, it's not actually fixed.

com.microsoft.sqlserver.jdbc.SQLServerException: USE statement is not supported to switch between databases. Use a new connection to connect to a different database.

[grishick]

Even though the changeling mentions the fix, it's not actually fixed.

com.microsoft.sqlserver.jdbc.SQLServerException: USE statement is not supported to switch between databases. Use a new connection to connect to a different database.

What is the version of MS SQL Source connector that you are using?

[creativedutchmen]

I'm using the latest version, 0.4.13 - it failed on 0.4.12 and 0.4.8 as well. Which is to be expected, as the fix mentioned above was reverted as soon as it was merged.

[creativedutchmen]

@grishick is there a version I can switch to that works?

[grishick]

@grishick is there a version I can switch to that works?

@creativedutchmen today I used MS SQL Source connector version 0.4.16 with Airbyte to run a CDC sync from Azure SQL to BigQuery. I followed this tutorial to enable CDC on Azure SQL: https://www.mssqltips.com/sqlservertip/7237/azure-sql-database-change-data-capture-configuration-query-data/