Add Database Manager - SQL Server

Pro Cloud Server connections to SQL Server based databases requires the use of an OLE/DB driver, currently Sparx Systems Pro Cloud Server supports both the historical "Microsoft OLE DB Provider for SQL Server" (that has been included with MDAC for many years) and the newly released "Microsoft OLE DB Driver for SQL Server".  With the latter supporting TLS v1.2 and other new features released with the later versions of SQL Server.

Authentication

The SQL Server OLE/DB drivers support two forms or database authentication, both of which are supported by  Sparx Systems Pro Cloud Server:

1. Windows Authentication / Windows NT Integrated security (or SSPI) and

2. SQL Server Authentication / "specific user name and password"

Many Sparx Systems customers expect the 'Windows Authentication' option to use the authentication details of the end user logging into Enterprise Architect, however this is NOT the case.  When this option is selected it is actually the credentials of the owning user of the Sparx Systems Professional Cloud  Windows Service that are used to authenticate against the database.  Which by default is 'Local System'. 

Credentials are needed to establish a database connection, however it is a relatively slow process, if the Pro Cloud Server was required to establish a connection to the database using the end user's details for every query, performance would be severely impacted.  Instead the Pro Cloud Server creates a 'pool' of database connection when it starts.  Database connections from the pool are allocated to action each requests for data as they arrive, and since they have already been established execution is almost instantaneous.

Therefore when defining database connections to SQL Server based repositories special consideration should be made to determine the best/most appropriate database credentials for your environment.  Typically we recommend the use of SQL Server authentication with the use of a predefine SQL Server user, however for many customers the SQL Server authentication method goes against their company's security policy, so therefore they need to either change the owning user of the Sparx Systems Professional Cloud Windows Service to one that has the necessary permissions in SQL Server or grant the Local System account permissions to the SQL Server database.

Roles / Permissions

In order for end users of Enterprise Architect/WebEA/Prolaborate etc to be able to read and update information within the repository, the database users requires the relevant permissions, the easiest way to achieve this is to grant the following roles to each database user:

db_datareader

db_datawriter

Add SQL Server database using SQL Server authentication

Prior to defining the Pro Cloud Server connection, define a SQL Server user with and roles to the database that will act as the Enterprise Architect repository.

You can define a connection to a SQL Server Database by specifying the connection string to the database. If you are running the Configuration Client on the same machine as the server you can:

1. Click on the button to open the 'Data Link Properties' dialog to build the connection string.
2. Select 'Microsoft OLE DB Provider for SQL Server' or 'Microsoft OLE DB Driver for SQL Server' and click on the Next >> button.
3. Select or type in the instance of SQL Server.
4. Select the 'Use a specific user name and password' checkbox and fill in the user name and password, then select the 'Allow saving password' checkbox.
5. Select the database that is the Enterprise Architect repository in 'Select the database on the server'.

Add SQL Server database using Windows authentication

You can define a connection to a SQL Server Database by specifying the connection string to the database. If you are running the Configuration Client on the same machine as the server you can:

1. Click on the button to open the 'Data Link Properties' dialog to build the connection string.
2. Select the provider 'Microsoft OLE DB Provider for SQL Server' or 'Microsoft OLE DB Driver for SQL Server' and click on the Next >> button.
3. Select or type in the instance of SQL Server.
4. Select the 'Use Windows NT Integrated security' checkbox.
5. Select the database that is the Enterprise Architect repository in 'Select the database on the server'.

Note:

• Because the Sparx Systems Professional Cloud Windows service is run under a single account, all database access will be under that account, so it will not be possible to keep track of what records are updated in the database by individual end users, however internally Enterprise Architect can store audit history if required.