Connecting ITCAM SQL Server agent when SSLv3 and TLS1.0 are disabled

Body

You noticed for sure that the amount of infrastructures where SSLv3 and older versions of TLS are disabled grow constantly and often you are requested to deal with software that stops working or needs to be somehow re-configured to cope with this change.

When SSLv3 and TLSv1.0 are disabled for Microsoft SQL Server, ITCAM SQL Server agent could get some problems connecting with it.
You can find in collector logs some error messages like:

CNTOSVRE (2017-05-12 09:18:32) (15632)Failed to connect to SQL Server:  SQLSRVA03\SQLDBU00 User:ITMONIT.  
DBCONCTT (2017-05-12 09:18:32) KOQSQLD(2812) (15632)could not connect to SQL serve::1  
MSS0510W (2017-05-12 09:18:32) KOQSQLD(12261) (15632)Could not connect to MS SQL Server
CLNDBX0T (2017-05-12 09:18:32) KOQUTIL(2583) (15632)Stopped cleaning of dbx structure, dbx pointer is NULL  
MSU0500I (2017-05-12 09:18:32) (14688)SQLSTATE: 08001, Native error: 18, Message: [Microsoft][ODBC SQL Server Driver][DBMSLPCN]SSL Security error  
MSU0500I (2017-05-12 09:18:32) (14688)SQLSTATE: 01000, Native error:  772, Message: [Microsoft][ODBC SQL Server Driver][DBMSLPCN]  ConnectionOpen (SECDoClientHandshake()).

The problem occurs because of the drivers used by the agent to connect to the SQL Server.
Using the default drivers, the same occurs if you try to manually connect to the server, while using most recent drivers, like "ODBC Driver 11 for SQL Server", you can successfully connect to the server.

So in order to get ITCAM agent correctly connecting to the server without using SSLv3 and TLSv1.0, we need to force it using the newest drivers instead of the default ones.

The driver used by agent can be changed by using one of the environmental variable , 'KOQ_ODBC_DRIVER'' .
So, you can perform below steps to fix the problem:
   
1. Open MTEMS window  
2. Right click on 'Monitoring Agent for Microsoft SQL Server' and click  Stop.  
3. Right click on 'Monitoring Agent for Microsoft SQL Server' ->  Advanced -> Edit Variables  
4. Click on 'Add' button on the newly opened window.  
5. Give variable as 'KOQ_ODBC_DRIVER'  
6. Give value as 'SQL Server Native Client 11.0'  
7. Click ok  
8. Start the agent.  
9. Check if agent is able to connect to SQL server by looking at TEP workspaces and/or connector logs.

Hope it helps.

Best Regards

Subscribe and follow us for all the latest information directly on your social feeds: