issues creating a foreign catalog

[DaveCh]

I've created a foreign connection to an on prem sql server...

CREATE CONNECTION IF NOT EXISTS `dagrbs-dr01`

  TYPE SQLSERVER

  OPTIONS (

    host = secret('scope-kv-key-vault', 'ip-dagrbs-dr01'),

    port = '1433',

    user = secret('scope-kv-key-vault', 'username-dagrbs-dr01'),

    password = secret('scope-kv-key-vault', 'password-dagrbs-dr01')

next step to create the foreign catalog returns...

summary: [DATA_SOURCE_OPTION_CONTAINS_INVALID_CHARACTERS] Option host must not be empty and should not contain invalid characters, query strings, or parameters. SQLSTATE: 42602

the schema and tables are compliant with databricks naming conventions, but the server name (not specified in the connection) does include a hyphen.

Is this the issue, or am I missing something else?

[balajij8]

@DaveCh 

The hyphen in the server name is valid for hostnames and IP addresses, so that isn't triggering this issue. The underlying issue here is likely the exact value being returned by secret('scope-kv-key-vault', 'ip-dagrbs-dr01'). The `DATA_SOURCE_OPTION_CONTAINS_INVALID_CHARACTERS` error SQLSTATE 42602 specifically calls out the host option. Databricks validates these connection options at the point you attempt to create the foreign catalog, which is why the initial CREATE CONNECTION statement succeeded but the catalog creation is throwing the error.

Typically, this occurs because the secret stored in Azure Key Vault includes extra formatting or contains invalid characters. The host option expects a plain hostname or IP address. If the secret contains a protocol prefix like tcp://, a port suffix like 10.0.0.1:1433, or any query string syntax, validation will typically fail. Since you've already defined the port correctly as a separate option, make sure the host secret doesn't duplicate it. Check if any trailing whitespace or hidden newline characters are pasted into the Key Vault entry.

Run below and check

DESCRIBE CONNECTION `dagrbs-dr01`

If you don't have the permissions to view the option values in it, you want to first temporarily recreate the connection using a hardcoded host string instead of the secret function. If the catalog creation succeeds with the hardcoded host, you can be sure that the Key Vault secret's payload needs to be cleaned up.

[aayush_410]

The hyphen almost certainly isn’t the problem. Hyphens are valid in hostnames, and the connection name dagrbs-dr01 is fine because it’s backticked. The error is about the value that secret() resolves to for host.

Most likely causes, in order:

1. Trailing newline or whitespace in the Key Vault secret. This is the most common cause. If the secret was created with echo, a CLI pipe, or pasted with a line break, the stored value is 10.x.x.x\n. It looks fine in the portal but fails the character validation.
2. The secret contains more than a bare host. Anything like 10.1.2.3,1433, 10.1.2.3:1433, tcp:10.1.2.3, server\instance, or a JDBC-style string with ; or ? parameters will trip this check. host must be just the hostname or IP, with the port in the separate port option.
3. The secret resolves to an empty string. This can happen if the key name or scope is slightly off, depending on how the scope is backed.

[data_pulse]

@DaveCh 

I did a quick validation of this with SQL Server federation in my workspace, and one thing stood out: CREATE CONNECTION and even CREATE FOREIGN CATALOG can succeed with a malformed host value. The stricter validation only showed up when I actually queried the foreign catalog.

For example, these were accepted initially:

host = x.x.x.x:1433
host = x.x.x.x?foo=bar

but SHOW SCHEMAS on the Foreign Catalog then failed with: 

[DATA_SOURCE_OPTION_CONTAINS_INVALID_CHARACTERS] Option host must not be empty and should not contain invalid characters, query strings, or parameters. SQLSTATE: 42602

 Using a clean host/IP with the port supplied separately:

host 'x.x.x.x',
port '1433'

got past that validation. In my test it then failed later with FAILED_JDBC_CONNECTION, but that was a separate networking issue because the SQL Server was running on my laptop and was not reachable from Databricks serverless.

So for the issue you encountered, just inspect the exact value returned by the secret used for host. Make sure it contains only the hostname/IP, with no :1433, query string, protocol prefix, instance syntax, or hidden whitespace/newline. Keep the port in the separate port option.

A simple way to isolate it is to temporarily hardcode the plain hostname/IP. If the error changes from DATA_SOURCE_OPTION_CONTAINS_INVALID_CHARACTERS to a connectivity/login error, that confirms the secret value itself was the first problem.

[Ashwin_DSA]

Hi @DaveCh,

The error message is a strong clue here: "Option host must not be empty and should not contain invalid characters" points to the value being resolved bysecret(), not to the server name itself.

As per this documentation, the recommended pattern uses secret() for credentials (user and password) but a plain string literal for host.

CREATE CONNECTION <connection-name> TYPE sqlserver
OPTIONS (
  host '<hostname>',
  port '<port>',
  user secret('<secret-scope>', '<secret-key-user>'),
  password secret('<secret-scope>', '<secret-key-password>')
)

The most likely cause is that your secret('scope-kv-key-vault', 'ip-dagrbs-dr01') call is resolving to an empty string or a value with unexpected characters. This can happen if the secret key doesn't exist in the scope, if there's a permissions issue reading from the Key Vault, or if the stored value contains trailing whitespace or hidden characters. Because secret() failures in SQL are often silent (returning empty rather than throwing), the connection creation just sees an empty host and gives you that DATA_SOURCE_OPTION_CONTAINS_INVALID_CHARACTERS error.

Can you verify whether the secret is actually returning a value. You can test this in a notebook cell with dbutils.secrets.get(scope='scope-kv-key-vault', key='ip-dagrbs-dr01'). The output will be redacted, but if the key doesn't exist or you lack permissions, you'll get an explicit error there that the SQL secret() function swallows. 

Second, as a quick sanity check, try hardcoding the host IP directly as a plain string to confirm the connection works withoutsecret():

CREATE CONNECTION IF NOT EXISTS `dagrbs-dr01`
  TYPE SQLSERVER
  OPTIONS (
    host '10.x.x.x',
    port '1433',
    user secret('scope-kv-key-vault', 'username-dagrbs-dr01'),
    password secret('scope-kv-key-vault', 'password-dagrbs-dr01')
  )

If that succeeds, you know the issue is specifically with the secret resolution forhost. You can still use secret() for host if you need to keep the IP out of the DDL, but you'll want to make sure the Key Vault secret name is exactly right and that the value stored there is a clean hostname or IP with no extra whitespace or special characters.

The hyphen in the server name itself shouldn't be the problem here since it's not part of the connection options. The error is about the resolved value of the host option.

If this answer resolves your question, could you mark it as “Accept as Solution”? That helps other users quickly find the correct fix.