SQL Server

The SQL Server adapter imports device information from arbitrary SQL servers: Microsoft SQL Server, MySQL, Oracle and PostgreSQL.

Types of Assets Fetched

This adapter fetches the following types of assets:

• Devices
• Users

Parameters

1. SQL Server Host (required) - The hostname / domain of the SQL server.
   For MSSQL:
   • To use a specific named instance, the value supplied should be in the following format: {server_host}\{instance_name}.
   • If no instance is supplied, the default instance will be used.
2. SQL Server Port (required) - The port of the SQL server.
   For MSSQL the required ports are:
   • Microsoft SQL Server discovery port - 1433.
   • The specific port for the supplied named instance, if relevant.
3. User Name (required) - The credentials for a user account that has the required permissions to fetch assets.

4. Password (required) - The user's password. The password must not include ";".
5. SQL Server Database Name (required) - The database to connect to.
6. SQL Server Table Name (required) - The name of the table to fetch information from. Axonius runs a 'SELECT * FROM [[specified value]]' statement.
7. Database Type (required) - MSSQL, MySQL, Oracle, PostgreSQL or HyperSQL. MySQL also supports the MariaDB.
8. Is Users Table (required, default: False) - Select whether the SQL Server table is a devices or a users table.
   • If enabled, Axonius will consider the data fetched from the specified table as user data.
   • If disabled, Axonius will consider the data fetched from the specified table as device data.
9. Server Tag (optional, default: empty) - Specify a custom tag for the SQL Server adapter connection that you have configured.
   • If supplied, Axonius will set the Server Tag field with the defined value.
   • If not supplied, Axonius will not populate the Server Tag field.
10. MySQL Character Set (optional, default: empty) - Specify a custom character set (encoding) for connections to MySQL databases. Example values: 'utf8', 'latin1'. When no character set is specified, 'utf8' is used by default.
    Note: This option is only valid when MySQL is selected from the Database Type dropdown.
11. Ignore entities in current connection that have not been seen in the last X hours - Specify the number of hours, so that the system will not fetch entities that were not seen in the last x hours. When Is Users Table is configured, this refers to users; otherwise, this refers to devices.
12. Delete entities in current connection that have not been returned in the last X hours - Specify the number of hours, so that the system will delete entities that were not fetched in the last x hours. When Is Users Table is configured, this refers to users; otherwise this refers to devices.
13. MSSQL Connection Timeout (optional, default: 30)- Specify a custom timeout in seconds for MSSQL connections.
14. For details on the common adapter connection parameters and buttons, see Adding a New Adapter Connection.

Advanced Settings

1. SQL pagination (required, default: 1000) - Set the number of results per page received for a given SQL query, to gain better control of the performance of all connections for this adapter.
2. Add dynamic date raw field (required, default: false) - Select whether to duplicate each date field appearing in the MSSQL database as a text value. The name of the new field is appended with the ‘_raw’ suffix.

Creating a Local Read-Only User for Microsoft SQL Server

To connect to the Microsoft SQL Server to create a Local Read-Only User, you can use Microsoft SQL Server Management Studio. If you don't have it on your local machine, you can probably find it on the machine the Microsoft SQL Server is installed on.

After connecting to the server, you should do the following:

1. If you don't have the name of your database, expand the Databases folder which shows all the databases in this server. Your database should appear here, starting with "CM_".

2. Navigate to the Security folder and expand it. Right-click the Logins folder and click New Login.

   

3. Create a user using the "SQL Server Authentication" option. Fill in the details and select your database from the Default Database List.

   

4. Navigate to the User Mapping page, and check the check box for the database that your login can access. In the Database role membership list, leave the default option public selected, and select the db_datareader check box.

   

5. Click OK and create the user.

6. Reconnect with the new user to validate that it was indeed created (File -> Connect Object Explorer).

   

Troubleshooting

• "Login failed" - If you are using a domain user, in the User Name field, specify the domain and the user name in the following format: domain\username.