You can store Certificate Enrollment Gateway data in an external Microsoft SQL Server database. This topic describes how to deploy a Microsoft SQL Server database for Certificate Enrollment Gateway.

To deploy a Microsoft SQL Server database for Certificate Enrollment Gateway using the Microsoft SQL Server Management Studio

1. Download the Microsoft SQL Server software from https://www.microsoft.com.
2. Install Microsoft SQL Server according to the instructions in the Microsoft documentation.
3. Open the Microsoft SQL Server Management Studio. Select Start > Microsoft SQL Server Tools > Microsoft SQL Server Management Studio.
   The Microsoft SQL Server Management Studio dialog box appears.
4. Log in to the Microsoft SQL Server Management Studio.
   1. Select File > Connect Object Explorer.
      The Connect to Server dialog box appears.
   2. Log in to the Database Engine as a Windows user account (Windows Authentication) or database administrator (SQL Server Authentication) with permission to administer SQL Server.
5. Create a new database instance for Certificate Enrollment Gateway:
   1. In the Object Explorer pane, expand the server, then right-click Databases > New Database.
      The New Database dialog box appears.
   2. Under the General page:
      • In the Database name field, enter a name for the database. For example, ceg.
      • Keep Owner set to <default>.
   3. Click OK.
6. Create a database user for Certificate Enrollment Gateway:
   1. The login account can use Windows authentication or SQL Server authentication.
      • If the login account will use Windows authentication, use your Windows tools to create the Windows user account.
      • If the login account will use SQL Server authentication, the user will exist only in the database. SQL Server authentication is only available if you selected mixed mode authentication for your SQL Server instance.
   2. In the Object Explorer pane, expand the server > Security > Logins, then right-click Logins > New Login.
      The Login - New dialog box appears.
   3. Under the General page:
      • In the Login name field, enter a name for the database user.
        If the login account will use Windows authentication, enter the Windows user account name in the format <DOMAIN>\<user>. For example, DOMAIN\ceguser.
      • Select the authentication method for the new login account.
        To use Windows authentication, select Windows authentication. The login account will be created as a Windows user account.
        To use SQL Server authentication, select SQL Server authentication. The login account will be created in the database only. SQL Server authentication is only available if you selected mixed mode authentication
        for your SQL Server instance.
      • In the Default database drop-down list, select the database you created earlier.
   4. If you selected SQL Server authentication, configure the following additional options under the General page:
      • Enter a password for the login account into the Password and Confirm password fields.
      • (Optional.) To enforce the SQL Server password policy, select Enforce password policy. By default, this option is already selected.
        Deselecting Enforce password policy will also deselect the Enforce password expiration and User must change password at next login options.
      • (Optional.) To never have the password expire, deselect Enforce password expiration. By default, this option is selected (the password expires).
      • Deselect User must change password at next login.
   5. Under the User Mapping page:
      • For Users mapped to this login, select the database instance you created earlier.
        When you select the database, the User field is filled with the login name you provided earlier.
      • For Database role membership, select the following roles:
        
        • db_accessadmin
        • db_backupoperator
        • db_datareader
        • db_datawriter
        • db_ddladmin
        • db_owner
        • db_securityadmin
        • public
   6. Click OK.

To deploy a Microsoft SQL Server database for Certificate Enrollment Gateway using the sqlcmd utility

1. Download the Microsoft SQL Server software from https://www.microsoft.com.
2. Install Microsoft SQL Server according to the instructions in the Microsoft documentation.
3. Start the sqlcmd utility:
   
   1. Open a Command Prompt. Select Start > Windows System > Command Prompt.
   2. Enter the following command:
      
      sqlcmd -E -S <server>
      Where <server> is the server name. For example:
      
      sqlcmd -E -S MSSQLSERVER
4. Create a new database instance for Certificate Enrollment Gateway:
   1. Enter the following commands to switch to the master database context:
      
      USE MASTER;

      GO
   2. Enter the following commands to create a new database for Certificate Enrollment Gateway:
      
      CREATE DATABASE <database>

      GO
      Where <database> is a name for the new database. For example:
      CREATE DATABASE ceg

      GO
5. Create a database user for Certificate Enrollment Gateway:
   1. Enter the following commands to create a new login for Certificate Enrollment Gateway:
      
      CREATE LOGIN <user> WITH PASSWORD = '<password>', DEFAULT_DATABASE = <database>;

      GO
      Where:
      
      • <user> is a name for the login account.
      • <password> is a password for the login account.
      • <database> is the database instance you created for Certificate Enrollment Gateway.
   2. For example:
      
      CREATE LOGIN ceguser WITH PASSWORD = 'EXAMPLE_password1234', DEFAULT_DATABASE = ceg;

      GO
   3. Enter the following commands to create a user in the Certificate Enrollment Gateway database for the login account:
      
      CREATE USER <user> FOR LOGIN <user>;

      GO
      Where <user> is the name of the login account you created in the previous step. The user name must be the same as the login name. For example:
      
      CREATE USER ceguser FOR LOGIN ceguser;

      GO
6. Enter the following commands to switch to the database you created for Certificate Enrollment Gateway:
   
   USE <database>;

   GO
   Where <database> is the database instance you created for Certificate Enrollment Gateway. For example:
   
   USE ceg;

   GO
7. Enter the following commands to assign the correct database roles to the account:
   
   ALTER ROLE db_accessadmin ADD MEMBER <user>;

   ALTER ROLE db_backupoperator ADD MEMBER <user>;

   ALTER ROLE db_datareader ADD MEMBER <user>;

   ALTER ROLE db_datawriter ADD MEMBER <user>;

   ALTER ROLE db_ddladmin ADD MEMBER <user>;

   ALTER ROLE db_owner ADD MEMBER <user>;

   ALTER ROLE db_securityadmin ADD MEMBER <user>;

   GO
   Where <user> is the name of the login account you just created. For example:
   
   ALTER ROLE db_accessadmin ADD MEMBER ceguser;

   ALTER ROLE db_backupoperator ADD MEMBER ceguser;

   ALTER ROLE db_datareader ADD MEMBER ceguser;

   ALTER ROLE db_datawriter ADD MEMBER ceguser;

   ALTER ROLE db_ddladmin ADD MEMBER ceguser;

   ALTER ROLE db_owner ADD MEMBER ceguser;

   ALTER ROLE db_securityadmin ADD MEMBER ceguser;

   GO