External log storage not working with SQL Server Express

[GavinHe]

Issue

Receive alarm messages as "Server does not exist or access denied", "08001: [FreeTDS][SQL Server]Unable to connect to data source" or "08S01: [FreeTDS][SQL Server]Unable to connect: Adaptive Server is unavailable or does not exist" when using SQL Server Express for External Log Storage even though login credentials are correct.

08001: [FreeTDS][SQL Server]Unable to connect to data source

08S01: [FreeTDS][SQL Server]Unable to connect: Adaptive Server is unavailable or does not exist"

Product Line

EcoStruxure Building Operation

Environment

• Enterprise Server
• Workstation
• External Log Storage
• SQL Server

Cause

SQL Server Express is using Windows Authentication and/or the TCP/IP network protocol is disabled.

Resolution

To use external log storage with SQL Server Express the User must be able to login with SQL Server Authentication (e.g. a username and password) and the TCP/IP Network protocol must be enabled.  Consult Setup External Log Storage using MSSQL for additional details.

Check SQL external log storage host address:

1. In the host address type the IP address or DNS name of your external log storage, without the SQL instance name. Having the SQL instance name in the host address can cause communication failure if EBO server is connecting to remote SQL server, refer to: Microsoft SQL Server - Basic Tab.

To check the User login credentials:

1. Open SQL Server Management Studio and enter the details from EcoStruxure Building Operation into the SQL Server connection window.  Choose SQL Server Authentication (A), enter the server details (1), user name and password (2) and attempt to login.
2. If an error message appears indicating cannot connect and the credentials are correct it is possibly SQL Server Authentication is not enabled.
3. Login to the SQL Server using Windows Authentication and follow the Microsoft article Change server authentication mode to set to 'SQL Server and Windows Authentication mode'.
4. Attempt to login to SQL Server and the specific database should be visible.

To enable the TCP/IP Network protocol:

1.  Open SQL Server Configuration Manager (1)
2. In the SQL Server Network Configuration menu, select Protocols for SQLExpress (2)
3. Right-click TCP/IP (3) and select Properties (4)
4. In the window that opens, click the IP Addresses tab (5)
5. Either set a different port number for each one of the IP addresses or simply set one for all (6).  The default is 1433 but will need to be a port that is free.   Click OK.
6. Back in the SQL Server Management Studio ensure the SQLExpress service is restarted.

For additional information please consult Default SQL Server Network Protocol Configuration and Enable or disable a server network protocol