Add Database Manager - SQL Server

Pro Cloud Server connections to SQL Server-based databases require the use of an OLE/DB driver; currently Sparx Systems Pro Cloud Server supports both the historical 'Microsoft OLE DB Provider for SQL Server' (which 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 of 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.  This, 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 connections when it starts. Database connections from the pool are allocated to action each request for data as it arrives 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 given to determine the best/most appropriate database credentials for your environment. Typically we recommend SQL Server authentication with the use of a predefined SQL Server user; however, for many customers the SQL Server authentication method goes against their company's security policy, so 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 to be able to read and update information within the repository, the database users require the relevant permissions; the easiest way to achieve this is to grant these 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 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 Pro Cloud Server is a Windows service and runs under a single account, all database access will be performed using the credentials of the owning 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

Learn More

• Create a Project in a SQL Server Database
• SQL Server Security Permissions