Create a Microsoft SQL Server Database for Autoform DM

This page describes how to create a Microsoft SQL Server database for Autoform DM to use.

In summary, the process has the following stages:

1. Create the database.

2. Create the database user (that Autoform DM will connect to the database as).

3. Assign database roles to the user.

You can create the database and user manually (through various windows in SQL Server Management Studio). Or, if you prefer, you can run a SQL script.

Before You Begin

Ensure that the Microsoft SQL Server instance that you want to use has been created and is running. Then, start SQL Server Management Studio and connect to the instance as a user that is able to create databases and users.

You can now follow the manual method or SQL command method of creating the Autoform DM database and user.

Manual Method

The following sections describe how to manually create the database and user.

Manually Create the Database

To create the database, follow these steps:

1. Start Microsoft SQL Server Management Studio and connect to the relevant instance.

2. In the Object Explorer pane, right-click Databases, then select New Database. The New Database window opens.

   

3. On the General page, enter a Database name for the new database. The Autoform DM installer assumes that the database name is AFPDM, so enter AFPDM (unless you want to give the database a different name). If you do use a different name, ensure that you enter that same name when you later install Autoform DM.

4. Click OK.

Manually Create the Database User for Autoform DM

To create the database user, follow these steps:

1. In the Object Explorer pane, expand Security.

2. Right-click Logins, then select New Login. The Login - New window opens.

   

3. Select the General page, then configure the new user:

   1. Enter a Login name. The default name used by the Autoform DM installer is pdm. Unless you want to use a different name (see warning below), enter pdm.

   2. Click SQL Server authentication.

   3. Enter a password into Password and Confirm Password. The default password used by the Autoform DM installer is pdm. Unless you want to use a different password (see warning below), enter pdm.

      Warning

      If you enter a Login name or password other than the default pdm (for example, to mitigate any security risks around using the default credentials), ensure that you enter those same credentials later in the Autoform DM installer.
      
      It is strongly advised that you use a strong password (instead of the default pdm). Password policy features in SQL Server are also available to you in this window. However, you must not select User must change password at next login.

   4. In the Default database list, select the database that you created. For example, AFPDM.

      

4. Select the Server Roles page, then select the public checkbox.

   

5. Select the User Mappings page, then configure the new user:

   1. In the upper table, click the AFPDM row.

   2. In that row, select the Map checkbox.

   3. Ensure that the AFPDM row is still selected. Then, select the following checkboxes in the lower part of the window:

      • db_ddladmin

      • db_datareader

      • db_datawriter

      

6. Click OK to create the user.

SQL Command Method

To use SQL commands to create the database and user (instead of the manual method described above), run the following SQL script in SQL Server Management Studio.

Warning

In the SQL commands, you must make the following substitutions:

Substitute <databasename> for the database name that you want to use. For example:

• SET @DatabaseName = '<databasename>'; becomes SET @DatabaseName = 'AFPDM';

Substitute <username> for the user name that you want to use. For example:

• SET @Username = '<username>'; becomes SET @Username = 'pdm';

Substitute <password> for the password that you want to use. For example:

• SET @Password = '<password>'; becomes SET @Password = 'YourSecurePassword';

Warning

Your system might be configured with a password complexity policy that causes SQL Server to reject your chosen password. If you run the script but the Messages pane contains an error related to password validation, you must change the password in the script so that it complies with SQL Server password complexity rules, then run the updated script.

SQL script:

DECLARE @DatabaseName VARCHAR(50);
DECLARE @Username NVARCHAR(50);
DECLARE @Password NVARCHAR(50);
 
SET @DatabaseName = '<databasename>';
SET @Username = '<username>';
SET @Password = '<password>';  -- Ensure you set a secure password here
 
DECLARE @SQL NVARCHAR(MAX);
 
-- Create database if it does not exist
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = @DatabaseName)
BEGIN
    SET @SQL = 'CREATE DATABASE [' + @DatabaseName + ']';
    EXEC sp_executesql @SQL;
END
 
-- Create login if it does not exist
IF NOT EXISTS (SELECT name FROM sys.server_principals WHERE name = @Username)
BEGIN
    SET @SQL = 'CREATE LOGIN [' + @Username + '] WITH PASSWORD = ''' + @Password + ''', DEFAULT_DATABASE = [' + @DatabaseName + ']';
    EXEC sp_executesql @SQL;
END
 
-- Create user mapped onto database and assign appropriate roles if user does not exist
SET @SQL = 'USE [' + @DatabaseName + ']; ' +
           'IF NOT EXISTS (SELECT name FROM sys.database_principals WHERE name = ''' + @Username + ''') ' +
           'BEGIN ' +
           'CREATE USER [' + @Username + '] FOR LOGIN [' + @Username + ']; ' +
           'EXEC sp_addrolemember ''db_ddladmin'', ''' + @Username + '''; ' +
           'EXEC sp_addrolemember ''db_datareader'', ''' + @Username + '''; ' +
           'EXEC sp_addrolemember ''db_datawriter'', ''' + @Username + '''; ' +
           'END';
EXEC sp_executesql @SQL;

Troubleshooting

If user roles have been incorrectly assigned, Autoform DM will write failure messages to its logs in particular situations:

• When Autoform DM starts for the first time (as a new installation or after an upgrade)

  • Failure messages related to logging into the database.

  • Failure messages related to creating tables and database objects.

• During administration operations such as adding or removing key definitions

  • Failure messages related to modifying the tblDocuments or tblDocumentHistory tables.

If you see these messages in the logs, check that users have been created and that they have the correct role assignments:

1. In the Object Explorer pane, expand Databases, then expand the new database.

2. Expand Security > Users, right-click the new user (pdm), then select Properties from the menu. The Database User - pdm window opens.

   

3. On the General page, check that Default schema is dbo. This is the correct setting for a standard setup of Autoform DM.

4. On the Membership page, check that the following role checkboxes are selected:

   • db_ddladmin

   • db_datareader

   • db_datawriter