Microsoft SQL Server is one of two database options supported by FCS Express for hosting your security system.

 

This option is ideal if your organization already has an SQL Server installation and has experience with it.

 

To configure FCS Express to use SQL Server, a text file will need to be created to define the connection string.  This can be done through the Windows application, NotePad.

 

Please follow the procedure below to start the configuration process between FCS Express and SQL Server.

 

1. Create a database in the SQL server instance. This will be the database that FCS Express will use.

a.FCS Express does not automatically create a SQL Server database.

b.A database will need to be created prior to use with FCS Express.

c.This will most likely need to be coordinated with your DBA team.

 

2. Create a text file containing the connection information to the database.

a.This text file can either be stored in plain text, or encrypted using the FCSEDBINIFileEditor.exe application noted below in Step C.

b.If you want to store the database details and login credentials in a plain text file, create a text file called FCSEDB.ini and start editing it in notepad.

i.In the first line, type [SecurityMSSQL]

ii.In the second line, type ConnectionString=

1.The connection string itself will be placed immediately following the equal sign (=).  

2.The format of the connection string can be seen in the samples below.

iii. On the next line add: Dialect=MSSQL

iv. On the last line add: Manager=UniDAC

c.If you want to encrypt the database details and login credentials, run the program FCSEDBIniFileEditor.exe. You will find this program in the same folder as FCS Express.exe

i.This will create an encrypted INI file that will end in a .CINI extension.

ii.The encrypted file will only require the ConnectionString line.  

iii.This file will be password protected.  Please record the password of the encrypted INI file.  De Novo Software will not be able to recover any password set for the encrypted INI file.

d.To summarize:

i.if you want to create a plain text file, the INI can be created in NotePad.  Make sure to change the extension from .TXT to .INI.

1.You might need to first save the file as .TXT.

2.Make sure that extensions are revealed in your Windows Settings and then change the extension from .TXT to .INI.

3.Windows should prompt you to confirm the change in extension.

4.The .INI can still be opened in NotePad.  If it does not open in NotePad by default, please right click on the INI file and select Open With > NotePad.

ii.If you need to create an encrypted INI, use FCSEDBIniFileEditor.exe.  Please note that the encrypted file will have a .CINI extension.

iii.The connection string includes information about the type of connection to create, the server where the database is located, the name of the database, and user id / password details.

 

3.The following samples provide examples for how the connection string can be written.

4.FCS Express Version 7.24 or greater contains the option include a UniProvider parameter in the connection string.  This parameter is optional and is only needed for institutions that need more options for their workstation client connections.  See the section below regarding this.

 

oSample connection string if you will be using an ADO connection:

 

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=<database>;Data Source=<database host>\<database instance>

 

•Modify the text above so that it uses your database information.  

o<Database> should be replaced with the name of database you created in step #1 above.

o<Database host> should be replaced with the IP address or path to your SQL Server instance.

o<Database instance> will be the instance on the database host, and may not be required, depending on your database installation.

 

oThe connection string can be created to account for Integrated Security with Windows.

▪Below an example of how the complete ADO connection with Integrated Security can look:

[SecurityMSSQL]

ConnectionString=Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=<database>;Data Source=<database host>\<database instance>

Dialect=MSSQL

Manager=UniDAC

 

oNote that this connection string assumes Integrated Security.  This means that Windows will authenticate the connection to SQL Server with the user's Windows credentials.  

 

oIf using an ADO connection without Integrated Security, a SQL Server User will be needed.  Please have a general SQL Server User created that can be used for all users.

 

▪Below an example of how the complete ADO connection without Integrated Security can look:

[SecurityMSSQL]

ConnectionString=Provider=SQLOLEDB.1;Persist Security Info=False;Initial Catalog=<database>;Data Source=<Database Host>\<Database Instance>;User ID=<user name>;Password=<password>

Dialect=MSSQL

Manager=UniDAC

 

oSample connection string if you will be using an ODBC connection

 

Provider=MSDASQL.1;Persist Security Info=False;User ID=<user name>;Password=<password>;Data Source=<Data Source Name created with ODBC Manager>;Initial Catalog=<database>

•Modify the text above so that it uses your database information.  

o<User name> should be replaced with the SQL Server user name.

o<password> replaced with the password for the SQL Server login.

o<Data Source Name created with ODBC Manager> should be replaced with the ODBC connection name,

o<database> should be replaced with the name of the database you created in step #1 above.

 

4. After you have entered the connection string, save the file.

a.Select the menu item File->Save in either notepad or the FCSEDBIniFileEditor. Then navigate to the same folder where your configuration file is stored, and enter a file name.  

b.If you are using a plain text file, the file name must be FCSEDB.ini. It is possible The .TXT extension of the text file can be replaced with .INI.

c.If you are using the FCSEDBIniFileEditor, the file name must be FCSEDB.cini.

d.The .INI or .CINI file must be saved in the same installation folder as your license configuration file.

e.When using FCSEDBIniFileEditor, only the Provider line is needed.

i. For example, Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=<database>;Data Source=<database host>\<database instance> or Provider=SQLOLEDB.1;Initial Catalog=<database>;Data Source=<Database Host>\<Database Instance>;User ID=<user name>;Password=<password> can be copied and pasted into the Connection String field, filling in the variables with the respective values for your institution.

ii.A password will be needed to create the encrypted CINI file.  

1.Please write down and take note of this password.  De Novo Software will not be able to recover the password for your encrypted connection string.

 

Please note:  when creating the connection string in the .INI file, the "<>" are not needed and should not be included.  They are being used in this text to denote a placeholder for a custom value that needs to be entered by the user or administrator.

 

Starting with FCS Express 7.24 or greater, a new UNIPROVIDER parameter will be possible in the connection string to allows for different connection protocols.

 

The following options will be available:

 

Below is an example of how this parameter can be incorporated into your connection string in FCS Express v7.24 or greater:

 

[SecurityMSSQL]

ConnectionString=Provider=SQLOLEDB.1;Uniprovider=<Option>;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=<database>;Data Source=<database host>\<database instance>

Dialect=MSSQL

Manager=UniDAC

 

When FCS Express starts, it will read the information in the file, connect to your database, and automatically create all the required tables and indices.  

 

At this point, it is extremely important that users have the proper SQL Server permissions to be able to make the database connection.  

 

Please refer to the next section for information regarding SQL Server Database Roles.