Create a Functional Account for a SQL Server Database

When you are adding SQL Server as a managed system, you must first create a security login in SQL Server to use for the functional account.

Permissions and Roles in SQL Server

The following roles and permissions are required for the functional account:

  • Server roles – public
  • ALTER ANY LOGIN
  • CONNECT SQL

Apply Permissions to a Functional Account:

The following code samples show you how to apply the required permissions to the functional account.

GRANT CONNECT SQL TO [FunctionalAccountName];
GRANT ALTER ANY LOGIN TO [FunctionalAccountName];

Create the Account in SQL Server

  1. Connect to a database as the SQL Server sa on the asset you manage.
  2. Expand Security and expand Logins.
  3. Right-click Logins and select New login.

    Create a new login on the SQL Server for the funtional account.

  4. Enter a Login name and select SQL Server Authorization.
  5. Enter and confirm a password.
  6. Configure the user as desired and click OK.

     

  7. To configure the user, right-click the user and select Properties.

    Create Server Roles for the Functional Account Login on the SQL Server.

  8. Select Server Roles and ensure the public roles is selected.

     

  9. Select Securables and click Search.
  10. Select the server instance and click OK.

    Set permissions in SQL Server for the login that was created for the functional account.

  11. From the list of permissions, ensure the Alter any login and Connect SQL are selected for Grantor sa.
  12. Click OK.

Retrieve SQL Server Instance Port Number

To configure a SQL Server database for Password Safe, you must retrieve the port number on the managed database instance using a query. The below query is required for database instances only. You do not need to provide a port number for the default instance.

  1. Create an instance on SQL Server.
  2. Once the instance is running, open the database and then select New Query.
  3. Execute the following query as shown on separate lines:
    GO
    xp_readerrorlog 0, 1, N'Server is listening on'
    GO
  4. Within BeyondInsight on the Assets page, find the asset where the SQL Server database is installed.
  5. Within the asset's menu actions, select Go to advance details.
  6. Select the Database tab.

    SQL Server port retrieval from the BeyondInsight Asset Wizard - Database Tab

  7. Click Add Database. Leave the default port or manually add the correct database port.
  8. Click Save Database.

     

Add Port number for the SQL Server database instance.

  1. In the Database grid, select the newly created database from above.
  2. From the Database menu actions, select Add to Password Safe.
  3. Fill out the details required for the managed system.
  4. Create the Create Managed System button.

 

Add a PostgreSQL Database Instance

A PostgreSQL database instance must be added manually.

Before adding the instance to Password Safe management, you must create an account in PostgreSQL to use as the functional account in Password Safe.

Create Accounts in PostgreSQL

The following instructions are for guidance only. For details on how to create an account, refer to the PostgreSQL documentation.

To create the account with appropriate level permissions:

  1. Run pgadmin from the icon on the tray.
  2. Right-click Login/Group roles, and then click Create.
  3. Enter a name. This is the functional account.
  4. On the Privileges tab, ensure the following permissions are in place for the functional account: Login, Create role, and Inherit rights from parent roles.
  5. Right-click Login/Group roles, and then select Create.
  6. Enter a name. This is the managed account.
  7. On the Privileges tab, ensure the following permissions are in place for the managed account: Login, and Inherit rights from parent roles.

You must also know the database instance name and the port number. In pgadmin, click Object , select Properties, and then click the Connection tab.

Add the PostgreSQL Instance to Password Safe

  1. Scan the asset where the PostgreSQL instance resides.
  2. Go to the Assets page.
  3. Select the desired asset, click the More Option button, and then select Go to advanced details.
  4. Under General Data, select Databases.
  5. For the desired instance, click the More Options icon, and then select Add to Password Safe.
  6. Set the following:
    • Instance Name: Enter the instance name.
    • Platform: Select PostgreSQL.
    • Version: Enter the PostgreSQL version number. This is optional.
    • Port: The default port value is 5432.
  7. Click Create Managed System.