How to use Windows Authentication with database sensor collectors

Products

• Security Information Manager

Issue/Introduction

How to use Windows Authentication with database sensor collectors.

 

Resolution

Previously Windows Authentication could not be used because previous versions of MS SQL Server JDBC drivers do not support it.

Windows Authentication is only supported with Microsoft SQL Server 2005 JDBC driver version 1.1 or newer.

To setup Windows Authentication with Symantec database sensor collectors:

1. You must use off-box collection, you cannot use this setup with on-box collection.
2. You must use Microsoft SQL Server 2005 JDBC driver version 1.2 (which is the oldest version supported by our collectors) or newer.
3. Run the MS SQL Server 2005 JDBC driver installer.
4. Copy the sqljdbc_auth.dll file from the <installation directory>\sqljdbc_<version>\<language>\auth\ location to the <drive>\WINDOWS directory on the computer where the JDBC driver is installed.
   • For the 4.7.x Event Agent, only use the sqljdbc_auth.dll file in the x86 folder regardless of whether the operating system is 32bit or 64bit.
   • For the 4.8 Event Agent, on 32bit operating system, use the sqljdbc_auth.dll file in the x86 folder and on a 64-bit processor, use the sqljdbc_auth.dll file in the x64 folder. 
5. Setup the Symantec Event Agent service with the login credentials of the account whose Windows credentials is used to access the MS SQL Server.
   1. On the Start menu click Run.
   2. In the Open text box, type services.msc, and click OK.
   3. Right click on Symantec Event Agent and click Properties.
   4. On the Log On tab, select This account and enter the users credentials.
   5. Click OK and restart the service.
6. In the Collector configuration, for the database sensor setting, add the string ;integratedSecurity=true to the end of the Database URL.
7. Remove any values entered in the username and password fields in the collector's database sensor setting.

In some situations you might need to copy the sqljdbc_auth.dll in the .\jre\lib folder where the SSIM Agent is installed.


Technical Information
You must setup the Event Agent with the users credentials because the Windows Authentication feature of the JDBC driver does not allow entering the Windows username/password explicitly. It instead searches the local computer credential cache for credentials that have already been provided at the computer or network logon.

If you want to create a user in your DB, please consult your DBA first. Example of script you can use to create and add the Windows user inside the DB permission for the read only user.

create login [Windows\sql_user] from windows
EXEC sp_addrolemember 'db_datareader', [Windows\sql_user];
use sep_big
....

grant select on sem_computer to [Windows\sql_user]
....
 

Example above is for SEP collector. Bascially you add the user to the DBreader group of the collector DB.