Creating a

[SalD]

Hi,

When I try to create a SQLServer connection for a local SQL Express install, CData Sync is throwing the following error: System error: UnknownHostException - No such host is known (DATA-DEV2\SQLEXPRESS)

I am able to connect to the server with SSMS but for some reason CData cannot find it. CData and SQL Express are on the same server.

What am I missing? Thanks.

[DougN_Nouria]

This post has some useful things to check (firewall, configuring SQL express to allow TCP connections, and so on).

Cannot connect to SQL Server Express - Microsoft Q&A

Try changing the server name from “DATA-DEV2” to “localhost” in your connection string, if they are on the same machine.

[SalD]

Thank you for the response. I have tried localhost to no avail. I have also tried the local IP address with similar results. The thing is though I can connect to the SQLExpress both locally and remotely using SSMS so I know the database instance is up and running. I just can’t connect to it with CData Sync. Please see the error log posted below.

Here is the error log:

2024-05-24T08:28:00.354-0500    2    [56|Q-Id]    [INFO|Connec] Connection Property: port = 1433
2024-05-24T08:28:00.355-0500    2    [56|Q-Id]    [INFO|Connec] Connection Property: customizedlog = sync.connection.CustomizedLogger@7ce74ca8
2024-05-24T08:28:00.355-0500    2    [56|Q-Id]    [INFO|Connec] Connection Property: authscheme = Password
2024-05-24T08:28:00.355-0500    2    [56|Q-Id]    [INFO|Connec] Connection Property: server = localhost\SQLEXPRESS
2024-05-24T08:28:00.355-0500    2    [56|Q-Id]    [INFO|Connec] Connection Property: useconnectionpooling = False
2024-05-24T08:28:00.355-0500    2    [56|Q-Id]    [INFO|Connec] Connection Property: user = qbuser
2024-05-24T08:28:00.355-0500    2    [56|Q-Id]    [INFO|Connec] Connection Property: verbosity = 5
2024-05-24T08:28:00.355-0500    2    [56|Q-Id]    [INFO|Connec] Connection Property: other = cdataapp=DataSync
2024-05-24T08:28:00.355-0500    2    [56|Q-Id]    [INFO|Connec] Connection Property: oemkey = *****
2024-05-24T08:28:00.355-0500    2    [56|Q-Id]    [INFO|Connec] Connection Property: password = *****
2024-05-24T08:28:00.355-0500    2    [56|Q-Id]    [INFO|Connec] Connection Property: database = QBMay24th
2024-05-24T08:28:00.356-0500    1    [56|Q-Id]    [INFO|Connec] Executed connect: Error: System error: UnknownHostException - No such host is known (localhost\SQLEXPRESS)
2024-05-24T08:28:00.356-0500    2    [56|Q-Id]    System error: UnknownHostException - No such host is known (localhost\SQLEXPRESS)
XcoreXsqlX230X8839.rge
    at java.base/java.net.Inet6AddressImpl.lookupAllHostAddr(Native Method)
    at java.base/java.net.InetAddress$PlatformNameService.lookupAllHostAddr(Unknown Source)
    at java.base/java.net.InetAddress.getAddressesFromNameService(Unknown Source)
    at java.base/java.net.InetAddress$NameServiceAddresses.get(Unknown Source)
    at java.base/java.net.InetAddress.getAllByName0(Unknown Source)
    at java.base/java.net.InetAddress.getAllByName(Unknown Source)
    at java.base/java.net.InetAddress.getAllByName(Unknown Source)
    at java.base/java.net.InetAddress.getByName(Unknown Source)
    at XcoreXsqlX230X8839.to$a.f(Unknown Source)
    at XcoreXsqlX230X8839.to$a.run(Unknown Source)
2024-05-24T08:28:00.356-0500    1    [56|Q-Id]    [INFO|Connec] Closed SQL connection
2024-05-24T08:28:00.358-0500    2    [57|Q-Id]    [INFO|Connec] Connection Property: port = 1433
2024-05-24T08:28:00.358-0500    2    [57|Q-Id]    [INFO|Connec] Connection Property: customizedlog = sync.connection.CustomizedLogger@7ce74ca8
2024-05-24T08:28:00.358-0500    2    [57|Q-Id]    [INFO|Connec] Connection Property: authscheme = Password
2024-05-24T08:28:00.358-0500    2    [57|Q-Id]    [INFO|Connec] Connection Property: server = localhost\SQLEXPRESS
2024-05-24T08:28:00.358-0500    2    [57|Q-Id]    [INFO|Connec] Connection Property: useconnectionpooling = False
2024-05-24T08:28:00.358-0500    2    [57|Q-Id]    [INFO|Connec] Connection Property: user = qbuser
2024-05-24T08:28:00.358-0500    2    [57|Q-Id]    [INFO|Connec] Connection Property: verbosity = 5
2024-05-24T08:28:00.358-0500    2    [57|Q-Id]    [INFO|Connec] Connection Property: other = cdataapp=DataSync
2024-05-24T08:28:00.358-0500    2    [57|Q-Id]    [INFO|Connec] Connection Property: oemkey = *****
2024-05-24T08:28:00.358-0500    2    [57|Q-Id]    [INFO|Connec] Connection Property: password = *****
2024-05-24T08:28:00.358-0500    2    [57|Q-Id]    [INFO|Connec] Connection Property: database = QBMay24th
2024-05-24T08:28:00.359-0500    1    [57|Q-Id]    [INFO|Connec] Executed connect: Error: System error: UnknownHostException - localhost\SQLEXPRESS
2024-05-24T08:28:00.359-0500    2    [57|Q-Id]    System error: UnknownHostException - localhost\SQLEXPRESS
XcoreXsqlX230X8839.rge
    at java.base/java.net.InetAddress$CachedAddresses.get(Unknown Source)
    at java.base/java.net.InetAddress.getAllByName0(Unknown Source)
    at java.base/java.net.InetAddress.getAllByName(Unknown Source)
    at java.base/java.net.InetAddress.getAllByName(Unknown Source)
    at java.base/java.net.InetAddress.getByName(Unknown Source)
    at XcoreXsqlX230X8839.to$a.f(Unknown Source)
    at XcoreXsqlX230X8839.to$a.run(Unknown Source)
2024-05-24T08:28:00.359-0500    1    [57|Q-Id]    [INFO|Connec] Closed SQL connection

[SalD]

I was able to make it work with a full SQL install. The SQL Express did not work for me. If anyone finds a way to make SQL Express work please let me know. For now I can move on.

[Ankit_Singh]

Hey @SalD

CData Sync connects to SQL Server using SQL Server Authentication (Auth Scheme=Password). Normally, with SQL Server Express, SQL Authentication is disabled on the server level.
The error you are getting is documented here: MSSQLSERVER_18456 - SQL Server | Microsoft Learn

You can check this by following the steps on your SSMS:

1. In SQL Server Management Studio (SSMS) Object Explorer, right-click the server and then select Properties.
2. On the Security page, under Server authentication, select SQL Server and Windows Authentication Mode.
3. In the SQL Server Management Studio dialog box, select OK to acknowledge the requirement to restart the SQL Server.
4. In Object Explorer, right-click your server, and then select Restart. If SQL Server Agent is running, it must also be restarted.

If you have already tried this, and it still doesn’t work, it’d be best to check the network access by checking the IP Port configurations in SQL Server Configuration Manager and ensure all the IPs are active.

I hope this helps!