Skip to content

SQL Server Connector Overview

The SQL connector enables connection to an MS SQL resource. The connector supports crawling, permissions collection and activity monitoring.

Capabilities

This connector enables you to use File Access Manager to access and analyze data stored in SQL Server and do the following:

  • Analyze the structure of your stored data.
  • Monitor user activity in the resources.
  • Classify the data being stored.
  • Verify user permissions on the resources, and compare them against requirements.
  • Manage access fulfillment - automated granting and revoking of access - according to rules set in File Access Manager.
  • Identity collector – collect IAM users, groups and roles and the connections between them.

See the File Access Manager documentation for a full description.

SQL Server Installation Flow Overview

To install the SQL Server connector:

  1. Configure all the prerequisites.
  2. Add a new SQL Server application in the Business Website.

  3. Install the relevant services:

    • Activity Monitor - This is the activity collection engine, used by all connectors that support activity monitoring.
    • Permissions CollectorIf you are using EC2 login, the collector should be installed on the EC2 instance.
    • Data Classification Collector

Important

Installing the permissions collector and data classification services is optional and should only be installed by someone with a full understanding of File Access Manager deployment architecture. The File Access Manager Administrator Guide has additional information on the architecture.

Supported Versions

The File Access Manager SQL Connector supports the following versions of MS SQLServer:

  • 2019 (15.0)
  • 2017 (14.0)
  • 2016 (13.0)
  • 2014 (12.0)
  • 2012 (11.0)

Note

File Access Manager supports MS SQL Server versions 2012, 2014, 2016, 2017 for running the application database. This document, on the other hand, describes connecting to an MS SQL Server as an application containing business resources.

Limitations of the SQL Server Connector

The following features are not supported by the SQL Server Connector:

  • Nested Roles– Roles within other roles (Database roles and Server roles)
  • SQL Server Permission Covering. Refer to Chart of SQL Server Permissions.

  • Contained Users - SQL Server Database Contained Users. Refer to Make your database portable by using contained databases.

  • MS SQL Server for Azure.

  • SQL Server Resources:

    • XML Schema Collections
    • Message Types
    • Contracts
    • Services
    • Remote Service Bindings
    • Routes
    • Full-text catalog and stoplists
    • Symmetric Key
    • Asymmetric Key
    • Certificate
    • Endpoints
    • Availability Groups
    • Database scoped credential

File Access Manager features not supported by the SQL connector:

  • What If for local groups
  • Access fulfillment
  • Data Classification
  • Effective Permissions are not calculated. The flag is always set to FALSE.

Activity Monitor Operation Principles

The activity monitor collects events from the SQL server using a query that is defined in the application configuration. Each row returned by the query is an activity, and stored in the File Access Manager database.

Important: Just to clarify the point, File Access Manager does not monitor database activity. It monitors a table supplied by you, analyzing the entries as activities, and entering them into the File Access Manager activity analysis engine.

To configure activity monitoring in File Access Manager:

  1. Identify or create a database activity table that contains the activities.
  2. Create a query defining user activities as you wish to monitor them, that points to this activity table.
  3. Add the query to the configuration panel described below, under Activities Query.
  4. Map the fields in the Activities Query to the File Access Manager activity fields, on the same configuration panel.

Permissions Collection Operation Principles

File Access Manager connects to the SQL Server through Microsoft ODBC driver, gathers local SQL Server principals and analyzes its objects and permissions on all the server’s database instances.

Local Principals Gathering

Identity types

Before collecting all the permission-principal relations, three types of identities are collected:

  • Server Logins – principals that might relate to a Windows user / active directory user or an SQL Server authentication user
  • Server Roles – principals that act as SQL Server groups on the entire server scope
  • Database Roles – principals that act as SQL Server groups on a database scope

Principals Naming

SQL Server Login names stored by the Permission Collection have certain naming patterns, whereas “domain” fields might act as – domain name, special groups such as NT SERVICE, Computer name or the server instance name.

Example: domain1\user2, NT SERVICE\MSSQLSERVER, machine45\user56

SQL Server Database Role names stored as database name\role name.

Example: db1\public, db2\db_owner)

Business Resource Full Path Conventions

Tree Node Types

Resource tree nodes can be divided into two categories:

  • A Real SQL Server object node - A server instance, table, assembly, etc.
  • A Virtual SQL Server node - Tables, Databases, Security, etc.

Characters encoding

As each real object might contain special characters such as a period (.) or back-slash (), the node name is wrapped in brackets ‘[‘ and ‘]’

Example: [TABLE1], [VIEW1], [sp_help]

Note

Virtual node names are not wrapped in brackets, since the name of virtual nodes are fixed and defined by File Access Manager.

Root node

Each resource full path starts with an instance name [SERVER\INSTANCE NAME]

Components

  • Virtual components start with a colon ( ‘:’ )

[SERVER]:Databases

[SERVER]:Security:Users

  • Real SQL Server objects start with a period, to separate them from other components

[Server].[DB1].[Schema2].[Table3]

[Server]:Security:Logins.[sa]