Troubleshooting Project Database Connection Problems

	Product(s):	Promis.e, Bentley Substation
	Version(s):	through 08.11.13.140
	Environment:	SQL Server
	Area:	Database Platform Support
	Subarea:	N/A

Background

Promis.e and Bentley Substation require a connection to a SQL project database in order to function. If such a connection does not exist, an error message such as the following may be displayed:

Unable to connect to database server
Unable to connect to setup configuration
Unable to connect to setup configuration ANSI-IEEE Default
Unable to connect to setup configuration IEC Default
Login failed for user 'ecaduser'
You have no available database connection
Configuration not saved. A network-related to instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.
- (provider: Shared Memory Provider, error: 40 - Could not open a connection to SQL Server)
- (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
- (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.)
Configuration not saved:A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

The purpose of this document is to serve as a guide for troubleshooting these connection problems when SQL Server is the database server software used. Oracle can be used as the database server software but is not covered in this article.

This document assumes that Microsoft SQL Server has been successfully installed. If there was a problem during installation, please consult the documentation and web sites for SQL Server.

Some questions to keep in mind while troubleshooting connection problems or requesting technical support include:

Was this working at one time? (What might have changed?)
Is this a new installation of Promis.e, Substation, or SQL Server?
Is the SQL Server local (installed on the workstation) or on a network server?
Was the database recently moved or restored?
Is any workstation able to connect to the project database?
Can you connect to the SQL Server using SQL Server Management Studio on the server? When run on the client workstation?

Requirements for a successful connection:

The connection must be enabled on the Setup dialog
SQL Server software must be installed on the machine that is to host the SQL Server
The login must be valid
The Server Name must be correct, including the instance name
The SQL service must be running on the server machine
The database must exist and be attached to the SQL Server, an empty or arbitrarily created database cannot be used, the .mdf and .ldf files for the database must not be compressed.
The server must be configured for remote connections
Other Notes

Click the Apply button on the Setup dialog in Promis.e/Substation after making changes to see if a connection can be made. A successful connection to the project database is indicated by no error message being returned. The connection is confirmed by the ability to create new projects or open existing projects that are in the database specified on the Setup dialog.

For the remainder of this document, "the software" will refer to Promis.e and Bentley Substation

Steps to Resolve

The connection must be enabled on the Setup dialog

If everything was working and Promis.e/Substation projects could recently be opened and created by the workstation now encountering the problem, sometimes all that is necessary is to enable the appropriate radio button on the Project Database area of the Setup dialog. If neither the “SQL Server/MSDE” nor the “Oracle” options are enabled, the software will not have a project database connection even if the remainder of the settings are valid. Unless you know that you are using an Oracle server, enable the “SQL Server/MSDE” option.

The SQL Server software must be installed on the machine that is to host the SQL Server

This means that the Microsoft’s SQL Server installation files must be run on the machine that is to act as the server. Running the installation files on a workstation will not install the software onto a network server machine.

An indication that SQL Server is installed is that a “Microsoft SQL Server...” program group exists in Start > Programs on the machine hosting the database.

The SQL Server version must be compatible with the Bentley software that is trying to connect. Refer to the compatibility chart for the software:

Compatibility Chart - Promis.e V8i

Compatibility Chart - Bentley Substation V8i

The login must be valid

Wrong login or password

Another common cause of connection problems is using the wrong SQL Server Authentication login or password, or the login does not even exist in SQL Server.

If the database was created automatically by the software the SQL login created may use the following credentials:

The SqlServerProject.sql script from pre-SELECTseries 4 era software also created the above login.The SqlServerProject.sql script included with recent releases no longer creates this login.

If the SQL Server was originally installed via a promis.e 2007 v2.0.x installation file or CD, the password may be: ecadpassword

If the SQL Server that is to be used is installed by the user (not by an admin with a different Windows login), Windows Authentication can often be used instead of SQL Server Authentication and requires no password be entered on the Setup dialog.

It is possible that a database administrator (DBA) created another SQL login with a different name and/or password or created Windows logins for each user, allowing users to select Windows Authentication on the Setup dialog. Contact the DBA for help in these situations.

Login does not exist

If the database was moved to a different server, the ecaduser login that may have once worked may need to be created again.

If the database was created using the SqlServerProject.sql for recent versions of the software, the SQL login will not exist or will not be associated with the database.

In these cases, see Creating an SQL Server Login.

SQL Server not configured for SQL Authentication

If the SQL Server is configured for Windows Authentication only, the following message can result when trying to connect with SQL Server Authentication:

"Login failed for user 'ecaduser'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error 18452)"

To be able to connect with SQL Server Authentication, the SQL server must be configured to use "SQL Server and Windows Authentication mode" instead of only "Windows Authentication mode". To view or modify this setting, connect with SQL Server Management Studio (using Windows Authentication), right-click the server name in the Object Explorer, and select Properties > Security. If SQL Server Management Studio is not installed, see Install SQL Server Management Studio.

The Server Name must be correct

In the Project Database area of the Setup dialog, the machine name of the computer hosting the project database should be entered into the Server Name field, followed by a backslash ( \ ) and then the SQL instance name:

SERVERMACHINENAME\INSTANCE

Note that using a forward slash will not work.

Standard and higher (non-Express) editions of SQL Server may not use an instance name. In this case, only the machine name of the server should be entered into the Server Name field, with no backslash or instance name:

SERVERMACHINENAME

There should be no leading slashes before the machine name as if this was a UNC path.

When the SQL Server is located on the workstation, the text “(local)” can be substituted for the actual machine name. In this case, the workstation is also acting as the server machine:

(local)\BENTLEYECAD

It is also possible, but not necessarily recommended, to use the IP address of the server in place of the server machine name:

192.168.1.106\BENTLEYECAD

The name of the SQL instance is most likely either BENTLEYECAD or ECTECAD if the SQL Server was installed via the Promis.e or Bentley Substation installation. However, it could be the default name of SQLEXPRESS or whatever was specified during a standalone installation of SQL Server Express.

Instance names are not case sensitive.

To verify the names of the installed instance(s), on the server machine select Start > All Programs > Microsoft SQL Server xxxx > Configuration Tools > SQL Server Configuration Manager, where xxxx is the version such as "2005" or "2008". In the configuration manager, click on "SQL Server Services" in the left pane and view the instance names in the right pane.

Specifying a Port

If a non-standard static port is used it is possible to specify the port used for connecting to the SQL Server instead of running the SQL Server Browser service on the server. The format for the Server Name field on Setup is the Microsoft standard convention:

SERVERMACHINENAME\INSTANCE,PORT

For example:

ENGRSERVER\BENTLEYECAD,49170

Note there are no spaces between any of the elements and separators in what is specified for the Server Name field.

The SQL service must be running on the server machine

In single-user environments, the SQL Server hosting the project database is often installed on the user's workstation. Since the SQL Server service is set to start Automatically by default, rebooting the workstation often resolves the problem of a stopped service.

To determine if the service is running, select Start > Programs > Microsoft SQL Server xxxx > Configuration Tools > SQL Server Configuration Manager on the server machine. In the configuration manager, click on SQL Server Services in the left pane and view the instance names in the right pane. The SQL Service for the instance that the software uses must be running in order to connect to it. If the service is stopped, right-click it and select Start.

If the service cannot be started (for example "The request failed or the service did not respond in a timely fashion"), open the ERRORLOG file with a text editor and look for an error number. Search the internet for the error number/message.

The ERRORLOG file should be found in a path similar to one of the following:

C:\Program Files\Microsoft SQL Server\MSSQL10.BENTLEYECAD\MSSQL\Log\
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\

If other machines are to connect to the SQL Server, use the SQL Server Configuration Manager to:

Start the SQL Server Browser service and set it to start Automatically.

Expand the SQL Server Network Configuration item in the left pane and right-click the Protocols for the instance name, and select Properties. Enable the TCP/IP protocol.

The database must exist and be attached to the SQL Server

The project database has a required structure. An empty or arbitrarily created database cannot be used. Scripts and front-end tools are provided for creating the project database on existing SQL Servers. See the Creating and Updating the Project Database article.

The names of Promis.e project databases usually begin with "promise".

The names of Bentley Substation project databases usually begin with "Substation".

To determine what databases are attached to the SQL Server, start Microsoft SQL Server Management Studio (SSMS) on the server machine and connect. Look in the Databases “folder” in the Object Explorer to determine the name of the database(s). If SQL Server Management Studio is not installed, see Install SQL Server Management Studio.

If there is more than one instance, connect to the various instances and look for the project databases. Note the name of the database and the instance it was found in and use these on Setup > Project Database.

Alternatively, search for the .mdf and .ldf files that constitute SQL databases on the machine hosting the SQL Server. This method does not indicate if the databases are attached, however.

For SQL Server 2014 the files are often located in a path similar to

C:\Program Files (x86)\Microsoft SQL Server\MSSQL12.BENTLEYECAD\MSSQL\DATA

For SQL Server 2008, a typical path is:

C:\Program Files (x86)\Microsoft SQL Server\MSSQL10.BENTLEYECAD\MSSQL\DATA

For SQL Server 2005, a typical path is:

C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data

For example, files named promise2.mdf and promise2_log.ldf indicate a database named “promise2”.

After determining the database name(s), try specifying it on Setup > Project Database.

If the data files for the project database exist but the database does not appear in the Object Explorer in SSMS, try attaching the database to the server. In SSMS, right-click the Databases folder in Object Explorer and select Attach.

The data files for the database must not be compressed

One indication that a file is compressed is that the text of the file name appears blue in Windows Explorer:

Compressed "promise" database files

Disk clean-up utilities have been known to compress SQL database files. A database with compressed datafiles can appear in the Object Explorer of SSMSE with no "subfolders" within it. To decompress the files:

Stop the SQL Service using SQL Server Configuration Manager
Select the files in Windows Explorer
Right-click the selection and select Properties
Click the Advanced button
Disable the “Compress contents to save disk space” check box, click OK
Click OK to exit the Properties dialog
Start the SQL Service using SQL Server Configuration Manager

The Server Must Be Configured for Remote Connections

If the SQL Server hosting the project database is running on a different machine than the workstation, the server machine must be configured to allow remote connections. This often entails

Starting the SQL Server Browser Service. The browser service is required when using dynamic ports (default behavior) or a non-standard static port without specifying the port along with the server name. See The Server Name must be correct section regarding the specification of a static port.
Enabling the TCP/IP protocol for the SQL service
Adding exceptions to the firewall for the port used by SQL Server or for the SQL Server program.

See the links below for more information

SQL Server 2014

How to enable remote connections in SQL Server 2014?
https://technet.microsoft.com/en-us/library/ms191464(v=sql.120).aspx

Configuring the Windows Firewall to Allow SQL Server Access
https://msdn.microsoft.com/en-us/library/cc646023(v=sql.120).aspx

Configuring a Fixed Port
https://technet.microsoft.com/en-us/library/ms177440(v=sql.120).aspx

SQL Server Browser Service
https://msdn.microsoft.com/en-us/library/ms181087(v=sql.120).aspx

SQL Server 2008

How to enable remote connections in SQL Server 2008?
http://blogs.msdn.com/b/walzenbach/archive/2010/04/14/how-to-enable-remote-connections-in-sql-server-2008.aspx

Configuring the Windows Firewall to Allow SQL Server Access
http://msdn.microsoft.com/en-us/library/cc646023%28v=sql.105%29.aspx

Configuring a Fixed Port
http://msdn.microsoft.com/en-us/library/ms345327%28v=sql.100%29.aspx

SQL Server Browser Service
http://msdn.microsoft.com/en-us/library/ms181087%28v=sql.105%29.aspx

SQL Server 2005 (not compatible with recent versions of Promis.e and Bentley Substation)

How to configure SQL Server 2005 to allow remote connections
http://support.microsoft.com/kb/914277

How to: Configure a Firewall for SQL Server Access
http://msdn.microsoft.com/en-us/library/ms175043%28v=sql.90%29.aspx

Configuring a Fixed Port
http://technet.microsoft.com/en-us/library/ms345327%28v=sql.90%29.aspx

Other Notes

If problems persist, try connecting to the SQL Server using SQL Server Management Studio (SSMS) from the workstation using the same credentials as being used on the Setup dialog. This may provide a more verbose error message that can be then investigated further. Generally, the settings used to successfully connect via SSMS can be used on Setup > Project Database.

SQL Server Management Studio is backward compatible, meaning SSMS 2014 can manage SQL Server 2008, but SSMS 2008 cannot connect to SQL Server 2014.

SSMS is not included with Promis.e and Bentley Substation packages that include SQL Server 2014 Express. See Install SQL Server Management Studio.

Promis.e and Bentley Substation use the .NET provider to connect to the SQL Server project database, so some connection issues can be caused by corruption of the .NET Framework.