SQL Security Account
Friday, October 21, 2016 6:27 AM


This lesson details the steps that need to be taken in both the SQL & FocalScope installations, to successfully use a SQL security account for FocalScope to authenticate with the SQL server. To see steps on the recommended way to get FocalScope to authenticate with an SQL server (using a Windows account).

During SQL Installation


During the SQL installation, you will be presented with the screen to select between using a Windows account or mixed mode (SQL Server authentication & Windows authentication). By selecting the [Mixed Mode] option, you can enter the password for th SQL "sa" account (system administrator).

WARNING! This is not recommended as SQL account names and passwords are stored in clear text and a security risk. You are strongly advised to use a windows account as its credentials are not stored as clear text. See the lesson Windows Account to accurately configure the Windows account.

Click [Next] to continue.Finish the installation by viewing the chapter, SQL Installation.


Creating a Dedicated Account in SQL

You may decide not to use the "sa" account in SQL and prefer to create a dedicated account in your SQL server, for FocalScope ir you are installing FocalScope to connect to a remote SQL server (this requires a dedicated account for FocalScope).

It is critical that this account be part of the "db_owner" database role in SQL, as FocalScope needs to create and modify tables & procedures, especially during its installation.



 

Firstly, log into the your SQL server instance with the SQL Server Management Studio.



 

Expand the [Security] node, then the [Logins] node. Right click on the [Logins] node and select [New Login...].




The "Login - New" configuration page will open. Here we will configure the account we want FocalScope to use to log into the SQL server instance.

  1. In the [Login name:] field enter the name of the account. During FocalScope's setup, we will specify this as the login name for FocalScope to connect to the SQL server instance.
  2. Enter a password of your choosing in the [Password:] field. This password will also be specified during FocalScope's setup to connect to the SQL server instance.
  3. Select the database you created in SQL, for FocalScope, in the dropdown list for the [Default database:] field.




  1. Now click on the [User Mapping] entry and tick the flag of the database you created for FocalScope.
  2. In the [Databsase role membership for:] field, tick the flag of the [db_owner] entry.

Click [OK] when finished. You have now successfully created a login account for FocalScope in your SQL server instance.



 

You will see your newly created SQL login account for FocalScope.


During FocalScope Installation

 

During the FocalScope installation, you will be presented with a choice to use either a Windows security account or SQL security account to authenticate with the SQL server. It is recommended to use a Windows account, as the username and password don't get stored in clear text (as is the case with an SQL account username and password). However, should you choose an SQL account over a Windows account, simply...

  1. Click the dropdown list for the [Authentication mode:] field and select [SQL Server (Using SQL server login)].
  2. Enter either the "sa" account name configured during the SQL installation or enter a username for an account you created in SQL.
  3. Enter the password for the account. Please note: this password must match the one specified in SQL.

Continue the FocalScope installation as detailed in the lesson Configure FocalScope Instance.