Installing and configuring Microsoft SQL Server

Install the SQL Server software if you haven’t already.

Gather information about your database server that will be required during Security Fabric installation .

Optional: Create a dedicated database, user, and schema .

Alternatively, you can allow Bravura Security Fabric setup to do this for you, as described in Configure a dedicated database user .

Optional: Create a dedicated report database user .

You can allow Bravura Security Fabric setup to do this for you, as described in SSRS settings .

Optional: Remove public/guest permissions .

If you will be using multiple Bravura Security Fabric instances or servers (replication), read Working with multiple installations for additional considerations.

Follow Microsoft’s best practice guide when setting up your SQL server.

Install Microsoft SQL Server with the following settings:

If you chose to install reporting services , click Install and configure on the Reporting Services Configuration page.

This removes the need for the SSRS post installation steps.

Verify the features to be installed on the Ready to install page.

Click Install.

Consult Microsoft’s documentation for detailed installation instructions.

Obtain a certificate.

Install the certificate with its private key on the SQL Server.

Install the certificate (without private key) at the appropriate place on your instance server, if necessary.

Under the instance's SQL Server registry key, create a DWORD called FORCEENCRYPT and set its value to 1.

On SQL Server, in the certificate's snap-in of mmc.exe, right click the appropriate certificate > all tasks > manage private keys … and add read access to the account that runs the SQL Server service (by default, NT Authority\MSSQLSERVER)

In the SQL Server Configuration Manager, expand SQL Server Network Configuration on the left-hand pane, then right click Protocols for <mssql instance name> and click Properties. Choose the appropriate certificate under the Certificates tab

Restart the SQL Server service.

Restart iddb .

Launch Reporting Services Configuration Manager.

Click the Web Service URL button on the left. Change settings if required.

Click Apply (whether you change the settings or not).

Take note of the Report Server Web Service URL. You will need this when you install Bravura Security Fabric .

Click the Database button on the left.

If you do not have a database:

1. Click Change Database.

2. Select Create a new report server database.

3. Click Next.

4. Follow the prompts to create a database.

This initial database will not be used; however, SSRS requires an initial database to connect to as part of the install process.

Start Microsoft SQL Server Management Studio.

Connect to the server as a system administrator (sysadmin role).

You can do this using SQL Server authentication and the sa account, or using Windows authentication if the Windows user has the sysadmin role.

For example, to connect to the server using the sa account, set:

Server type: Database Engine

Server name: <host name or IP address> \ <instance>

Authentication: SQL authentication

Login: sa

Password <password for sa>

Click Connect.

Create a new database for Bravura Security Fabric :

1. In the Object Explorer (left) pane, right-click Databases, then click New Database….

2. Type the Database name .

3. Click Options .

4. Select Recovery model and choose Simple.

   Note

   Ensure that an appropriate database backup policy is in place. See http://technet.microsoft.com/en-us/library/ms189275.aspx for more information.

5. Select Compatibility level and ensure that this is set to a minimum value ofSQL Server 2016 (130).

   The compatibility level for the installed version of Microsoft SQL Server is suitable.

6. Select Auto Create Statistics and choose True.

7. Select Auto Update Statistics and choose True.

8. Select Auto Update Statistics asynchronously and choose False.

9. Click OK.

Create a new login:

1. In the Object Explorer pane, expand Security.

2. Right-click Logins, then click New Login….

3. On the General page, type the Login name.

4. Select:

   • SQL Server Authentication

     Type and confirm the password for the new login. Deselect the User must change password at next login and Enforce password expiration checkboxes.

     Or

   • Windows authentication

     Pick a local or domain account or group.

5. Set Default database to the database that you created in Step 3.

6. Click OK.

Create a new schema in the database:

1. In the Object Explorer pane expand Databases > < Newdatabase > > Security.

   Where <New database> is the database that you created in Step 3.

2. Right-click Schemas, then click New Schema….

3. Type the Schema name.

4. Click OK.

Set the user in the database:

1. In the Object Explorer pane, expand Databases > <Newdatabase> > Security.

   Where <New database> is the database that you created in Step 3.

2. Right-click Users, then click New User…

3. Type the User name.

4. Set the Login name to the user you created in Step 4.

5. Set the Default schema to the schema you created in Step 5.

6. In the Database role membership area, enable:

   • db_datareader

   • db_datawriter

   • db_ddladmin

   • db_owner

7. Click OK.

Close the connection to the schema by collapsing the database tree and highlighting the root of the SQL Server management interface.

This ensures that the database can be locked to perform the following operation.

Alter the database collation:

1. In the toolbar, click New Query.

2. In the new query window, type the following:

   ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
   ALTER DATABASE <database name> COLLATE Latin1_General_BIN 
   ALTER DATABASE <database name> SET ALLOW_SNAPSHOT_ISOLATION ON 
   ALTER DATABASE <database name> SET READ_COMMITTED_SNAPSHOT ON 
   ALTER DATABASE <database name> SET MULTI_USER

   If the database name is ”default”, enclose it in square brackets: [default].

3. Click Execute.

Exit SQL Server Management Studio.

Note the database name, and the name and password of the login that you create. You will need these values, as well as the information you gathered earlier, when you install Bravura Security Fabric .

Start Microsoft SQL Server Management Studio.

Connect to the server as a system administrator (sysadmin role).

You can do this using SQL Server authentication and the sa account, or using Windows authentication if the Windows user has the sysadmin role.

For example, to connect to the server using the sa account, set:

Server type: Database Engine

Server name: <host name or IP address> \ <instance>

Authentication: SQL authentication

Login: sa

Password <password for sa>

Click Connect.

Create a new schema in the database:

1. In the Object Explorer pane expand Databases > <instancedatabase> > Security.

2. Right-click Schemas, then click New Schema….

3. Type the Schema name.

4. Set the Schema owner to dbo.

   

5. Click OK.

Create a new login:

1. In the Object Explorer pane, expand Security.

2. Right-click Logins, then click New Login…

3. On the General page, type the Login name.

4. Select SQL Server Authentication.

5. Type and confirm the password for the new login.

6. Deselect the User must change password at next login and Enforce password expiration checkboxes.

7. Set Default database to the instance database created either in a previous install or in Step 3.

   

8. Click User Mapping on the left.

9. Map the <instance database> to this new user and set the default schema to the schema created in the previous step.

   

10. Click OK.

Set the user in the database:

1. In the Object Explorer pane, expand Databases > <instancedatabase> > Security > Users.

2. Right-click the user created in Step 4 and click Properties.

3. Click General on the left.

4. Set the Default schema to the schema you created in Step 3.

   

5. In the Membership area, enable:

   • db_datareader

   

6. Click Securables on the left.

7. Search and select Schema object types.

   

8. Select the instance database's schema.

9. Deny this user access to the instance database's schema.

   

10. Search and select the report schema you created in Step 3.

11. Grant Execute and Select permissions.

    

12. Click OK.

Start Microsoft SQL Server Management Studio.

Connect to the server as a system administrator (sysadmin role).

For example, to connect to the server using the sa account, set:

Server type: Database Engine

Server name: <host name or IP address> \ <instance>

Authentication: SQL authentication

Login: sa

Password <password for sa>

Click Connect.

Create a new user:

1. In the Object Explorer (left) pane, expand Databases > SystemDatabases > master > Security , right-click Users, then select New User….

2. Type the User name.

3. Select Login name created in Creating a dedicated database, user, and schema .

4. Select Default schema (for example, sys).

5. Select Securables to search and grant select permission on schemas sys and INFORMATION_SCHEMA.

6. Click OK.

Create a new database role for sp_describe_first_result_set:

1. In the Object Explorer (left) pane, expand Databases > System Databases > master > Security > Roles, right-click Database Roles, then select New Database Role….

2. Type the Role name.

3. Add user created in previous step to Role Members.

4. Select Securables page, and click Search.

5. Select sp_describe_first_result_set (Extended Stored Procedures).

6. Grant Execute permission, and click OK.

Repeat last step to create a new database role for sp_executesql.

For upgrade or migration, repeat to create a new database role for sp_rename.

For upgrade or migration, ensure your login user can connect:

1. In the Object Explorer (left) pane, expand Security > Logins, and select your login user.

2. Select Securables page, and click Search.

3. Select your server.

4. Under the Permissions for <server> check the following permissions:

   • Connect SQL

   • Control server

   • Create any database

   • Create availability group

   • Create DDL event notification

   • Create endpoint

   • Create server role

   • Create trace event notification

   • External access assembly

   • View any definition

   • View server state

5. Click OK.

Start the SQL Server Configuration Manager (Start > All Programs > Microsoft SQL Server 2008 > Configuration Tools)

Select SQL Server Network Configuration > Protocols for < SQLServer instance > and enable Shared Memory, TCP/IP, and Named Pipes protocols.

Select SQL Native Client Configuration > Client Protocols and enable protocols with the following order:

Restart the server to apply your settings.

Click Server > Security > Logins.

Right-click the DBUser then select Properties> User mapping.

Ensure that the correct Login is mapped to the database schema and user, and has correct privileges granted, as in Step 6.

Before you begin, ensure the database is available.

Log on to the affected server.

On the Start menu, click Run , type services.msc, Click OK.

In the results pane, find the Bravura Security Logging Service.

Right-click the service, then select Restart.

In the results pane, find the Bravura Security Database Service .

Right-click the Service, then select Start.

Repeat steps 6 and 7 for all Bravura Security services that did not start.

Log on to the affected server.

On the Start menu, click Run, type services.msc, Click OK.

In the results pane, find the Bravura Security services.

Right-click the service, then select Properties….

Change the Startup Type to Automatic (Delayed Start).

Click Apply , then click OK.

Repeat steps 4-6 for all Bravura Security services that are installed.

Restart the server.