Bentley Communities
Bentley Communities
  • Site
  • User
  • Site
  • Search
  • User
OpenUtilities
  • Product Communities
OpenUtilities
OpenUtilities Wiki Troubleshooting Project Database Connection Problems
    • Sign In
    • +OpenUtilities
    • Access Service Request Manager
    • +Bentley sisHYD Multi Utility
    • Design Features - promis.e Functionality - Promis.e Standard
    • +OpenUtilities sisNET - English
    • +OpenUtilities Substation
    • Promis.e Paths
    • -Promis.e Wiki
      • +CONNECT Edition - Promis.e
      • About Promis.e
      • +Administration Features - Promis.e
      • +APIs_VBA - Promis.e
      • +Automation Features - Promis.e
      • +Component Features - Promis.e
      • +Component Management - Promis.e
      • +CONNECT Services - Promis.e
      • +Data Manager - Promis.e
      • -Database Platform Support - Promis.e
        • Backing Up Promis.e Data Automatically
        • Converting to an SQL Parts Database
        • +Creating and Updating the Project Database
        • Desired SQL server Not Listed in the available Servers List
        • Disable Auto Shrink to avoid performance degradation
        • Error - Could not find SQL Server instance named BENTLEYECAD
        • Error - Could not find stored procedure
        • Error - Database version does not match the application
        • Error - Index was outside the bounds of the array (Microsoft.SqlServer.smo)
        • Error - Invalid database name - on Project Database Utility
        • Error - Invalid object name
        • Error - Login failed for Ecaduser: Reason: The Password of the account Expired
        • Error - No version resource could be located in database
        • Error - OraOLEDB.Oracle provider is not registered
        • Error - T-SQL execution command line utility stopped working
        • Error - The database is for product... you cannot use it
        • Error at Startup - Error 0 No connection could be made because the target machine actively refused it.
        • Error at Startup - Error 40 Could Not Open a Connection
        • Error at startup - The Type initializer for ECT.ECAD.API.SystemSetup threw an exception
        • Error at Startup - Unrecognized database format, catalog.mdb
        • Error deleting project - transaction log full
        • Error messages encountered due to case sensitive collation of SQL Server
        • Error on backup - Invalid database object reference
        • Error on Project Database Utility - Cannot find the file specified
        • Error running SQL scripts - Incorrect syntax
        • Exception thrown when creating a project due to SQL permissions
        • Exception thrown when opening a page, transaction log is full
        • Finding the SQL Data folder
        • Installing SQL Server
        • Minimum SQL user permissions
        • Project Database Version - Promis.e
        • Rebuild indexes in the SQL project database
        • Rules for Regular Identifiers
        • SQL Server FAQ
        • Troubleshooting Project Database Connection Problems
      • +Display - Promis.e
      • +Drawing Management - Promis.e
      • +Engineering Design Considerations - Promis.e
      • Error - Object reference...General
      • +Find_Replace - Promis.e
      • Finding the Tutorial
      • +Installation_Configuration - Promis.e
      • +Interoperability - Promis.e
      • +Licensing - Promis.e
      • +Maintenance Features - Promis.e
      • +Migration - Promis.e
      • +OS Support - Promis.e
      • +Panel Layout - Promis.e
      • +Performance - Promis.e
      • +PowerPlatform Support - Promis.e
      • +Print_Publish Features - Promis.e
      • +ProjectWise Integration - Promis.e
      • Promis.e Help file
      • +Reports_Output Features - Promis.e
      • +Template Management - Promis.e
      • +Text Tools - Promis.e
      • Training FAQs
      • +Wire Numbering - Promis.e
      • +Wiring Features - Promis.e
      • +Project Management - Promis.e
      • Promis.e Title
      • Working from home with Promise.e
    • Replace Family - Find_Replace - Promis.e
    • SQL Server Login

     
     Questions about this article, topic, or product? Click here. 

    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:

    Login name: ecaduser
    Password: Ecad1pass

    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:

    1. Stop the SQL Service using SQL Server Configuration Manager
    2. Select the files in Windows Explorer
    3. Right-click the selection and select Properties
    4. Click the Advanced button
    5. Disable the “Compress contents to save disk space” check box, click OK
    6. Click OK to exit the Properties dialog
    7. 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.

    See Also

    Install SQL Server Management Studio

    SQL Server Login

    Creating and Updating the Project Database

    Compatibility Chart - Promis.e V8i

    Compatibility Chart - Bentley Substation V8i

      Original Author: Matt_P
    • Database Platform Support
    • promis.e
    • Administration
    • SQL Server
    • error message
    • Installation/Configuration
    • how to
    • Bentley Substation
    • en
    • SELECTsupport
    • Problem Solving
    • Share
    • History
    • More
    • Cancel
    • Matt_P Created by Communities MVP Matt_P
    • When: Tue, Oct 26 2010 1:14 PM
    • Jesse Dringoli Last revision by Bentley Colleague Communities MVP Jesse Dringoli
    • When: Sun, Mar 29 2020 9:28 PM
    • Revisions: 36
    • Comments: 0
    Recommended
    Related
    Communities
    • Home
    • Getting Started
    • Community Central
    • Products
    • Support
    • Secure File Upload
    • Feedback
    Support and Services
    • Home
    • Product Support
    • Downloads
    • Subscription Services Portal
    Training and Learning
    • Home
    • About Bentley Institute
    • My Learning History
    • Reference Books
    Social Media
    •    LinkedIn
    •    Facebook
    •    Twitter
    •    YouTube
    •    RSS Feed
    •    Email

    © 2023 Bentley Systems, Incorporated  |  Contact Us  |  Privacy |  Terms of Use  |  Cookies