Absent Member.. Absent Member..
Absent Member..
2084 views

SmartConnector for Microsoft SQL Server Multiple Instance Audit DB

Jump to solution

Hi Everyone,

Kindly I am little confused about SQL multiple instance DB, I have several questions about it and hoping find their answers:

1- the difference between this smart connector and SmartConnector for Microsoft SQL Server Audit DB (Legacy) is that we can monitor multiple instances of the DB but are these instances must be located on the same DB or can be located on different DBs ?

2- If we can monitor multiple instances from different/separate DBs, should they all be located in the same domain controller or not?

3-Before installing the Smart Connector, perform the following configuration steps:

  • Create an ODBC Data Source (not necessary when using a JDBC driver).
  • Download a SQL Server JDBC driver (not necessary when using an ODBC data source).
  • Mount a drive on Linux platforms.
  • For SQL Server 2008, ensure TCP/IP connection is enabled.
  • Create a Local SQL Server User.
  • Share permissions for the database log folder.
  • Create a Domain SQL Server User. This step is required for all authentication modes and operating system environments. System     administrator privilege is required for SQL Server database access and for granting folder permissions.
  • Enable Auditing.

" according to Smart Connector for Microsoft SQL Server Multiple Instance Audit DB configuration guide"

If we created a local SQL server user and granted it the required permissions, are we still needing to share the folder which has the trace files or not ?

what I had understood that we create a Domain user and share the trace file folder only in the case of using windows authentication with SQL server , did I understand it right ?

Finally when using C2 auditing what parameters should I provide for the connector :

  • User
  • Password
  • Trace File Local Folder
  • Connector Data Folder

Thanks,

BR,

Labels (3)
0 Likes
1 Solution

Accepted Solutions
Fleet Admiral
Fleet Admiral

1) No. This SmartConnector can read from separate databases.

2) They don't need to be from the same domain, but if using a domain user then each config will need it's own set of credentials.

3) The main thing to understand is that the connector needs BOTH file level access as well as SQL query access. The guide does walk you through different ways of accomplishing this, (e.g. using Windows User or SQL user for query access, etc), however you should also check network layer stuff like ports being open (445 and 1433) as well as the SQL Server listening on external interface on 1433.

4) The paramaters you provide for C2 are the same as for non-C2, except that you have to share the path to where the server writes its C2 logs. This default path cannot be changed.

C2 auditing comes with certain restrictions that you should be aware of before enabling. See these links for more info:

Dinesh Asanka - C2 Level Auditing with SQL Server - Dinesh Asanka

You need to be very aware that C2 auditing isn't for everybody, and should only be enabled if you know what you are doing:

<excerpt 1>

  1. Performance. This is very obvious since all your actions needs to be written to the trace files. To minimize the performance issue, it is better to put the trace file into a separate hard disk (not separate partition).
  2. Server shutdown. In case system cannot do auditing, (hardware failure etc) server will shut down. This is a rule of C2. That is auditing should get the precedence over all the other operations. I have a personal experience where C2 level hard disk was full and SQL Server was shut down. So it is very important to get your alerts correct about the hard disk space on these drivers since these files grows rapidly as your logging each and every details of SQL Server.
  3. If you are looking for much simpler auditing or if you are looking at some specific events for auditing, it will be better to use SQL Server 2008 in-built auditing feature which is came with SQL Server 2008.

<excerpt 2>

C2 Audit is less flexible:

  • Audits all activity on database
  • Trace file size cannot be modified
  • Trace file location is configured at SQL Server installation and cannot be modified


View solution in original post

0 Likes
4 Replies
Fleet Admiral
Fleet Admiral

Hi Tarek,

Refer this to get some idea and will share you the remaining with ur mail id I Know it doesn't answer ur questions. Lets c who has hands on exp can provide his feed on the same dude

0 Likes
Lieutenant Commander
Lieutenant Commander

Hello ,

I have installed MultiDB connector but I am facing challenges below are the issue and process followed.

 

Enviroment

-------------------------------------------

Two Host both are in different domain (x-domain ) & (y-domain)

- The X domain have the MYSQL server 2014 where username<smc.logger> I have created and followed the guide & followed the process .

- The Y domain have the smart connector installed and jdbc driver v8 for smartconnector v 13.

 

Error :

The trace file dose not exsist & there is no event as authentication is done by local account and same account created over X domain & SQL server with same password with necessary right and the Trace folder is shared and mapped in Y domain and is been mention in the Connector Data Folder

 

what will be the possible reason I am not able to go thought it , serious help required.

0 Likes
Fleet Admiral
Fleet Admiral

1) No. This SmartConnector can read from separate databases.

2) They don't need to be from the same domain, but if using a domain user then each config will need it's own set of credentials.

3) The main thing to understand is that the connector needs BOTH file level access as well as SQL query access. The guide does walk you through different ways of accomplishing this, (e.g. using Windows User or SQL user for query access, etc), however you should also check network layer stuff like ports being open (445 and 1433) as well as the SQL Server listening on external interface on 1433.

4) The paramaters you provide for C2 are the same as for non-C2, except that you have to share the path to where the server writes its C2 logs. This default path cannot be changed.

C2 auditing comes with certain restrictions that you should be aware of before enabling. See these links for more info:

Dinesh Asanka - C2 Level Auditing with SQL Server - Dinesh Asanka

You need to be very aware that C2 auditing isn't for everybody, and should only be enabled if you know what you are doing:

<excerpt 1>

  1. Performance. This is very obvious since all your actions needs to be written to the trace files. To minimize the performance issue, it is better to put the trace file into a separate hard disk (not separate partition).
  2. Server shutdown. In case system cannot do auditing, (hardware failure etc) server will shut down. This is a rule of C2. That is auditing should get the precedence over all the other operations. I have a personal experience where C2 level hard disk was full and SQL Server was shut down. So it is very important to get your alerts correct about the hard disk space on these drivers since these files grows rapidly as your logging each and every details of SQL Server.
  3. If you are looking for much simpler auditing or if you are looking at some specific events for auditing, it will be better to use SQL Server 2008 in-built auditing feature which is came with SQL Server 2008.

<excerpt 2>

C2 Audit is less flexible:

  • Audits all activity on database
  • Trace file size cannot be modified
  • Trace file location is configured at SQL Server installation and cannot be modified


View solution in original post

0 Likes
Absent Member.. Absent Member..
Absent Member..

Dear Richard,

Thanks for your helpful information , So the local SQL user created is used when configuring smart connector to be able to access DB but windows/domain user created is used to make Smart connector service "on the agent side" can access the shared folders which contains the log files and this user must have read/write permission on these folder, Am I right ?

Thanks,

BR,

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.