4. Skip to content

4. External Database Connections

By default, Specification Compliance Manager (SCM) is configured for connection to a default SQL Express edition database. You can configure SCM to connect with an external SQL Server database to avoid the limitations of SQL Express.

You must complete the following tasks to set up an external database connection.

  • IT admin enables remote connection in the SQL Server using the server machine.

  • IT admin creates a database on the server machine using the recommended collation.

  • IT admin creates user login accounts with access permissions for the new SQL Server and database.

Enabling Remote Connections in an SQL Server

Complete the following steps to enable remote connections in an SQL Server.

  1. Log in to the SQL Server instance using an account with sysadmin privileges.

  2. Configure remote access on the SQL Server instance.

    1. If SQL Server Management Studio (SSMS) tool is installed, right-click on the server instance, click Server Properties, open the Connections page, enable Allow remote connections to this server, and click OK.

    2. If SSMS tool is not installed, connect to the database through “SQLCMD” and execute the following query:
      EXEC sp_configure 'remote access', 1;
      RECONFIGURE;

  3. Enable SQL Server authentication.

    Check the “SQL Server and Windows Authentication mode” under “Security” section in server instance properties.

  4. Enable TCP/IP protocols for the SQL Server.

    1. Open the “SQL Server Configuration Manager” tool from the Start menu.

    2. Expand the “SQL Server Network Configuration” menu and select “Protocols for \<ServerInstanceName>”.

    3. Right-click on “TCP/IP” and click “Properties”.

    4. Open the IP Address tab and enter the TCP port for the SQL Server in the “IPAll” section.

    5. Click “Apply”, then click “OK”.

    6. Right-click “TCP/IP” and click “Enable”.

  5. Restart the SQL Server Instance in SSMS or Windows SQL Server service.

  1. Configure a Windows Firewall rule to allow access to the port that hosts the SQL Server for connections from required machines.
    Reference: https://knowledgebase.apexsql.com/configure-remote-access-connect-remote-sql-server-instance-apexsql-tools/

4.0.1 Creating a New Database

NI recommends that you create the database with recommended collation. This collation will be updated at a later point to migrate the database to the latest state that is supported by Specification Compliance Manager (SCM).

Complete the following steps to create a new database with the recommended collation.

  1. Open the SQL Server Management Studio (SSMS) tool.

  2. Expand the SQL Server instance item in the Object Explorer.

  3. Right-click on the database folder and select “New Database”.

  1. Enter a name for the database in the Database name field on the General page.

  1. Open the Options page.

  2. Set the collation as SQL_Latin1_General_CP1_CS_AS.

  1. Click “OK” to create the new database with the recommended collation.

4.0.2 Creating a User Login Account for External Database Connections

NI recommends that you create a user login account with the following levels of access permissions.

  • Server Level Access – public

  • Database Level Access – db_owner.

  • The db_owner role is necessary to support any change in database level properties

Complete the following steps to create a user account with public server access and db_owner database access.

  1. Open the SQL Server Management Studio (SSMS) tool.

  2. Expand the SQL Server instance item in the Object Explorer.

  3. Expand the contents of the Security folder, right-click “Logins”, and select “New Login…”.

  4. Enter the following information and settings on the General page of the Login – New window.

    1. Enter a Login name.

    2. Select SQL Server Authentication.

    3. Set a Password for the user login that meets the following requirements.

      • The password does not contain the account name of the user.

      • The password is at least eight characters long.

      • The password contains characters from three of the following four categories:

      • Uppercase English letters (A through Z)

      • Lowercase English letters (a through z)

      • Base 10 digits (0 through 9)

      • Non-alphanumeric characters such as: exclamation point (!), dollar sign (\$), number sign (#), or percent (%).

      • Passwords can be up to 128 characters long. Use passwords that are as long and complex as possible.

    4. Disable the Enforce password expiration option to avoid password expiration.

  5. Open the Server Roles page and select the public server role option.

  1. Open the User Mapping page to associate the user login account with the Specification Compliance Manager (SCM) database.

    1. Select the Map option for the SCM database that is relevant to the user login account.

    2. Select the following Database role memberships for the user login account: db_owner and public

  1. Click OK to complete the user login account creation process.

Note: You can also use the following query to create a user login account for an SCM database, where…

  • \<login_name> represents the user account login name.

  • \<user_name> represents the required username. This can match the user account login name.

  • \<database_name> represents the name of the SCM database.

Replace these variables with the values for your user account and SCM database.

USE [master]
CREATE LOGIN [<login_name>]
WITH PASSWORD = '<password>', CHECK_POLICY = ON;
USE [<database_name>]
CREATE USER [<user_name>] FOR LOGIN [<login_name>];
USE [<database_name>]
GO
ALTER ROLE [db_owner] ADD MEMBER [<user_name>]
GO

4.0.3 Configuring an External Database Connection String for Specification Compliance Manager

Complete the following steps to set up an external database connection string for Specification Compliance Manager (SCM).

  1. Open File Explorer and navigate to “%PROGRAMDATA%\National Instruments”.

  2. Create a folder named “SCM”.

  1. Open the SCM folder and create a new folder inside named “DatabaseConnectionConfiguration”.

  2. Open the DatabaseConnectionConfiguration folder and create a JSON file named “ConnectionConfiguration.json”.

  1. Add the following information to ConnectionConfiguration.json.

    1. DatabaseConnectionString: Defines credentials for external database connections including the server instance, database name and user credentials. Refer to the following example for DatabaseConnectionString formatting.

    "DatabaseConnectionString": "Server=\<DB_Server_Instance>;Database=\<DB_Name>;UserID=\<Login_User_Name>;password=\<Login_Password>;"

  2. TempDataFileName: Optional parameter that defines the unique file name for an additional file group to include in the database. If you do not enter this parameter, SCM will use the default value: “TEMP_DATA_\<DB_NAME>.ndf”.

"TempDataFileName": "TEMP_DATA\_ \<DB_NAME\>.ndf"

Refer to the following for an example of a complete database connection string. 

{
"DatabaseConnectionString":
"Server=SCMDemoServerPC;Database=Demo_DB;User
ID=DemoUser;password=test;",
"TempDataFileName": "TEMP_DATA_Demo.ndf"
}

Note: If you have not yet installed SCM, the SCM installation process will automatically complete the following steps. If SCM is already installed, complete the following steps to migrate the database to the latest SCM database state.

  1. Open File Explorer and navigate to “%PROGRAMFILES%\National Instruments\Shared\SCM\SqlServerConfiguration”.

  2. Run SqlServerConfiguration.exe to migrate the new database to the latest SCM database state.

4.1 Restarting a Specification Compliance Manager Server

If you configure an external database connection string after you install Specification Compliance Manager (SCM), you can use SystemLink to restart the SCM server to apply the required configuration for the external database.

  1. Open “NI SystemLink Server Configuration” from the Start menu.

  2. Select the NI SystemLink Server Manager tab.

  3. Click Restart in the Actions section on the page.

After the restart is complete, the SCM server will be configured correctly for the external database.

Last update: October 10, 2022