How to: Configure SQL Server Database Permissions

Introduction

The purpose of this article is to:

  1. Specify the SQL Server database permissions required for Northern Storage Suite (including NSS Console).
  2. Illustrate how and where these permissions can be configured.
  3. Act as a point of reference in scenarios where insufficient database permissions are a likely cause of errors.

About the Northern Storage Suite databases:

A complete use of Northern Storage Suite requires two different databases; one data database (NSSData) and one client database (NSSClient). The database names are optional, although we highly recommend using the names NSSData and NSSClient.

NSSData
The data database stores data gathered by running Data Scans and File Level reports. This is the main database that NSS uses. The NSSData database will be automatically created when you configure the database connection under Database Management in the NSS Config. For more information about this procedure, please see KB-2873.

NSSClient
The client database is used to store user settings for the NSS Console Client. An empty database (preferably with the name NSSClient) needs to be created manually on the database server by a database administrator. The database structure (tables and content) will be created automatically as soon as the connection to this database has been established through the NSS Console.

Permissions
To be able to perform operations on these two databases, the accounts used by NSS must be granted certain permissions. The 'Step-by-step'-section below explains all required actions. A table summarizing the required permissions can be found in the 'Confirm Results'-section at the bottom of this page.

Intended Results

A successfully established database connection that will allow you to use Northern Storage Suite as intended.

Step-by-Step

Authentication types 

Windows Authentication
The most common approach is to use Windows Authentication for the two database connections. This authentication type requires that two different database sever logins are created and assigned specific permissions.

The first database server login is for the NSS Service Account (the account that the NSS services run with). This account needs to be able to read data, run stored procedures, create tables and make changes to the main NSS database.

The second database server login is for the Web Server (IIS) that hosts the NSS Console interface. The web server needs to be able to read the content from the main NSS database and make changes to the NSSClient database in order to maintain the individual NSS Console user views

SQL Authentication
If SQL Authentication is to be used for the database connections, only one database server login needs to be created (the SQL Authentication account). This account needs to have the same rights as the NSS Service Account to the NSSData database and the same rights as the Web Server/Machine-account to the NSSClient database.

The following examples are specifically made for Windows Authentication, but the general principles can easily be translated to SQL Authentication scenarios.

Existing Northern Storage Suite implementation

Customers with an existing NSS implemention are very likely to find that there's already a valid database connection configured to the main NSS database (NSSData). This means that the instructions in KB-2873 were followed at one point. If there's no valid database connection in the NSS Config, see 'New Northern Storage Suite implementation' below.

Follow these steps:

  1. Create an empty database named NSSClient on the Database Server and set the Database Recovery Model to Simple. The database schema will be generated automatically when a connection to this database has been established through the NSS Console.

  2. Modify the database server login for the NSS Service Account and verify that it has db_datareader, db_datawriter, db_ddladmin rights and the explicit 'Execute'-permission on NSSData (the main database). See the section 'Permissions for the Northern Storage Suite Service Account'  below for further instructions.

  3. Create a database server login for the Web Server/Machine Account and grant it db_datareader rights to NSSData and db_datareader, db_datawriter and db_ddladmin to NSSClient. See the section 'Permissions for the Web Server/Machine account' for further instructions.

  4. Establish the connection to the NSSData database and the NSSClient database in the NSS Console admin interface.

New Northern Storage Suite Implementation

New customers are recommended to create both databases as empty databases directly on the database server. The reason for this is that this will allow you to ensure that the account permissions are correctly configured before attempting to establish the connection through the graphical interface.

Follow these steps:

  1. Create an empty database named NSSData on the database server and set the Database Recovery Model to Simple. The database schema will be generated automatically when a connection is established to this database through the NSS Config pages.

  2. Create an empty database named NSSClient on the Database Server and set the Database Recovery Model to Simple. The database schema will be generated automatically when a connection is established to this database through the admin pages of NSS Console.

  3. Create a database server login for the NSS Service Account and grant it db_datareader, db_datawriter, db_ddladmin rights and the explicit 'Execute'-permission on NSSData (the main database).  See the section 'Permissions for the Northern Storage Suite Service Account' for further instructions.

  4. Create a database server login for the Web Server/Machine Account and grant it db_datareader rights to NSSData and db_datareader, db_datawriter and db_ddladmin to NSSClient. See the section 'Permissions for the Web Server/Machine account' for further instructions.

  5. Establish a connection to the NSSData database in the NSS Config pages.

  6. Establish a connection to the NSSData database and the NSSClient database in the NSS Console admin pages.

Permissions for the Northern Storage Suite Service Account

This section explains how to create the database server login for the NSS Service Account in SQL Management Studio.

Follow these steps:

  1. Access the database server through SQL Management Studio and create new a login for your NSS Service Account under Security\Logins. The service account is named DQ\NSS-Service in this example.

    SQL Login: NSS Service Account

  2.  Click the checkbox for NSSData and grant the service account db_datareader, db_datawriter and db_ddladmin rights to this database. The db_ddladmin rights are needed in order to generate the database schema on the initially empty database. See the screenshot below for an illustration of the required permissions mapping.  

    NSS Data: NSS Service Account

  3. Right click on the NSSData database and click on Properties and select 'Permissions' in the left-sided menu. Select the NSS Service Account in the list named Users or Roles and assign the Execute permission to it. This will allow the NSS Service Account to execute Stored Procedures on the NSSData database.  

    NSS Data: Execute Permission

  4. Save the changes by clicking OK.

Note: If allowed, the permission db_owner can be assigned to the NSS Service Account for the database instead. As the db_owner, the permissions mentioned in the previous steps are automatically assigned to the account.

Permissions for the Web Server/Machine account

The NSS Console runs under the IIS Application Pool called NSSConsole by default. This application pool is configured to run as LocalSystem, which means that when the NSS Console connects to the SQL database it will use the LocalSystem account. Over the network, the LocalSystem account is presented as DOMAIN\IIScomputername$ and locally it is presented as SYSTEM.

Follow these steps:

  1. If a database server login does not already exist for the webserver LocalSystem account, one must be created.
  2. Access the database server through SQL Management Studio and create new a login for the machine account under Security\Logins. The characteristics of this SQL Login may differ depending on where the SQL Server is located and how the IIS is configured. See the different scenarios below:

    • If the SQL Server is located on another machine over the network, create an SQL Login for the server running NSS Console and IIS (DOMAIN\IIScomputername$). Specify the domain name, followed by the name of the NSS-server and a dollar sign. The dollar sign informs SQL that the account is not a user account per se, but a machine account. This is the most common approach.

    • If the SQL Server is located on the same server as the NSS Console and the Web Server (IIS), use the already existing SQL Server login named NT AUTHORITY\SYSTEM. This is the SQL Server account that corresponds to LocalSystem.

    • In very rare cases, the Application Pool identity in IIS has been changed from LocalSystem to something else. If this is the case a database login must be created for the account specified in the IIS Application Pool Advanced settings.

    In this example, a database login has been created based on the first approach, where NSS and SQL are located on separate servers, as it is the most common scenario. DOMAIN = DQ, NSS_SERVER = TWV-RODA-NSS. Final product: DQ\TWV-RODA-NSS$.

     SQL Login: NSS Server

    As you may have noticed, the screenshots show that NSS and SQL are running on the same server, so the account used should actually be NT AUTHORITY\SYSTEM in this case. However, we have chosen to illustrate how the DOMAIN\IIScomputername$ account is constructed.

  3. Right click the Web Server account and select Properties. Go to the section called User Mapping. Click the checkbox for NSSData and grant the Web Server account db_datareader rights to this database.  

    NSS Data: Machine account

  4. Click the checkbox for NSSClient and grant the Web Server account db_datareader, db_datawriter and db_ddladmin rights to this database.

    NSS Client: Machine account

  5. Save the changes by clicking OK.

Confirm Results

  1. Verify that the permissions are mapped according to the table below:

    Account NSSData NSSClient
    NSS Service Account

    db_datareader

    db_datawriter

    db_ddladmin

    Execute permission


    DOMAIN\IIScomputername$

    db_datareader

    db_datareader

    db_datawriter

    db_ddladmin

    SQL Authentication (if used)

    db_datareader

    db_datawriter

    db_ddladmin

    Execute permission

    db_datareader

    db_datawriter

    db_ddladmin


    Reminder: If the SQL Server is running on the same machine as the IIS server, NT AUTHORITY\SYSTEM should be used instead of DOMAIN\IIScomputername$.

  2. Verify that you can connect to the database from the NSS Config and the NSS Console. It should look like the screenshots below.

    NSS Config:

    NSS Classic DB Connection

    NSS Console:

    NSS Console DB Connection

  3. Run a Data Scan or a File Level report from Storage Reporter to verify that data can be stored to the NSSData database.

  4. Make some minor customizations to the NSS Console Dashboards to verify that the changes can be stored to the NSSClient database.


For advanced troubleshooting, please contact the Technical Support team at Northern (support@northern.net).

ADDITIONAL RESOURCES

  • KB2873 How to: Configuring the NSS Database Connection
  • KB1745 What is/are: NSS Deployment Pre-requisites
  • KB1791 What is/are: System Requirements
  • KB Article: 3090

    Updated: 12/7/2016

    • Category
      • Usage
    • Affected versions
      • NSS 9.6
      • NSS 9.7
      • NSS 9.8

    North America HQ

    NORTHERN Parklife, Inc.
    301Edgewater Place, Suite 100
    Wakefield, MA 01880
    USA

    Voice: 781.968.5424
    Fax: 781.968.5301

    salesUS@northern.net

     

    Additional Contact Information

    EMEA & APAC HQ

    NORTHERN Parklife AB
    St. Göransgatan 66
    112 33 Stockholm
    Sweden

    Voice: +46 8 457 50 00

    salesHQ@northern.net

    Northern Parklife



    ©2018 northern parklife

    privacy statement 
    terms of use