Bentley Communities
Bentley Communities
  • Site
  • User
  • Site
  • Search
  • User
OpenUtilities
  • Product Communities
OpenUtilities
OpenUtilities Wiki SQL Server Login
    • 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
    • Replace Family - Find_Replace - Promis.e
    • SQL Server Login

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

    SQL Server Login

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

    Background

    Promis.e and Bentley Substation can be configured to use either Windows Authentication or SQL Server Authentication to connect to the SQL Server hosting the project database.  To use SQL Server Authentication, a valid SQL Server Login must exist on the server and its name and password must be known and entered into the Setup dialog of promis.e or Bentley Substation.

    After creating the project database with the Project Database Utility and using Windows Authentication, the SQL Server may not have a SQL Server Login that users can specify on the Setup dialog, depending on the application and version.  Similarly, the SqlServerProject.sql script for some versions does not include lines for creating an SQL login.

    After restoring a backup of a project database, the SQL Server may already have a SQL Server Login but it will not be properly associated with the restored database, preventing the login from being used to connect to the database.

    To address the above issues, an SQL Server Login can be created and/or associated with database.

    Steps to Accomplish

    Create the login

    The following script adds the ecaduser login with a "Ecad1pass" password for SQL Server Authentication access to the SQL Server and adds the ecaduser user to the promiseX database and links it to the login.

    It can be copied into a text document and saved with a .sql extension then opened and executed using SQL Server Management Studio (SSMS), or pasted directly into a new query in SSMS.

    Substitute the correct database name for all of the instances of “promiseX” in the script and make other changes appropriate to the situation, such as the desired login/user name and password, before executing the script.

    use master
    GO
    if not exists (select * from syslogins WHERE loginname=N'ecaduser')
    begin
       EXEC sp_addlogin 'ecaduser','Ecad1pass','promiseX';
       ALTER LOGIN [ecaduser] WITH DEFAULT_DATABASE=[promiseX], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
    end
    GO
    use promiseX
    GO
    if not exists(select * from sysusers WHERE name=N'ecaduser')
       EXEC sp_adduser 'ecaduser','ecaduser','db_owner'
    GO
    ALTER USER ecaduser WITH LOGIN = ecaduser;
    GO
    ALTER USER ecaduser WITH DEFAULT_SCHEMA = dbo;
    GO

    Change the password

    The script above creates the login and specifies a password. If the password needs to be changed at some later time, a script like the one below can be used. Change "ecaduser" to the actual login name and "myNewPassword" to the desired password before executing the script.

    ALTER LOGIN ecaduser WITH PASSWORD = ‘myNewPassword’;

    Note: The Windows password policy on the machine hosting the SQL Server may be such that the password in the above script does not meet the policy requirements. For example, the "Ecad1pass" password specified in the script above may not meet the minimum character length, in which case executing the script will result in a "Password validation failed. The password does not meet Windows policy requirements because it is too short." message. To resolve that message, add characters to the password specified in the script and execute it again. Make note of whatever password you use.

    See Also

    Creating and Updating the Project Database

    Troubleshooting Project Database Connection Problems

    Moving Projects

    External Links

    How to: Create a SQL Server Login

    Passwords must meet complexity requirements

    Minimum password length

      Original Author: Matt_P
    • Database Platform Support
    • promis.e
    • Administration
    • SQL Server
    • how to
    • Bentley Substation
    • en
    • Share
    • History
    • More
    • Cancel
    • Matt_P Created by Communities MVP Matt_P
    • When: Fri, Sep 9 2011 4:25 PM
    • Matt_P Last revision by Communities MVP Matt_P
    • When: Tue, Oct 17 2017 5:28 PM
    • Revisions: 19
    • 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