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.