ODBC Errors with Data Stores in MSSQL Server

Document ID : KB000004958
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

Either a Policy Store, Key Store, Session Store, Audit Store, or User Store has been configured on a MS SQL Server.  

ODBCAD32.exe:  Error When 'Test Connection' is run: 

[DataDirect][ODBC SQL Server Wite Protocol driver] Cannot load trust store.

 

SMConsole: Error When 'Test Connection' is run: 

Failure. Siteminder can not access the following data sources: <DSN Name> : SM-DBU-00620. Error code -1063 

NOTE: SMConsole error only applies to the Stores defined in the SMCONSOLE (Policy Store, Key Store, Session Store or Audit Store).

Environment:
Policy Server: AnyPolicy Server OS: AnyPolicy Store: MSSQL Server
Cause:

The MSSQL Server instance is configured with 'Force Encryption' and requires an SSL connection with its clients.   The 'Validate Server Certificate is enabled ('ValidateServerCertificate=1'), however the dependant parameters such as 'TrustStore'; 'TrustStorePassword'; and 'HostNameInCertificate' are either not defined, or have don't values populated in them.

Resolution:

Enabling 'ValidateServerCertificate' is an optional step.  If 'ValidateServerCertificate' is enabled, then the 'TrustStore', TrustStorePassword', & 'HostNameInCertificate' will also need to be defined and have values populated in the DSN.

 

Windows Policy Server

1) Logon to the Policy Server

2) Open ODBCad32.exe 

3) Select the System DSN tab 

4) Select the DSN Name, then select CONFIGURE 

5) Within the DSN Properties, select the Security Tab 

6) (OPTIONAL) Configure the Validate Server Certificate settings (e.g Trust Store) 

NOTE: If 'ValidateServerCertificate' is enabled, then the 'TrustStore', TrustStorePassword', & 'HostNameInCertificate' will also need to be defined and have values populated in the DSN.

6b) If there is no Trust Store, then remove the "Validate Server Certificate Flag" 

 

 

UNIX Policy Server

1) Open the ODBC.ini file  [<siteminder_home>/db/system_odbc.ini]

2) Locate the DSN for the Store 

EncryptionMethod=1

The EncryptionMethod parameter is populated with a bitmap value:

0 = Disabled

1 = SSL

6 = Request SSL

7 = LoginSSL

CryptoProtocolVersion=SSLV2,SSLV3,TLSV1  

The CryptoProtocolVersion is a CSV delimited, multi-valued parameter which allows any combination of the following three values:

SSLV2; SSLV3; TLSV1 

ValidateServerCertificate=1 (Optional)

The ValidateServerCertificate parameter is an Optional parameter.  It has a binary value and is either enabled or disabled

1 = Enabled

0 = Disabled

TrustStore=<TrustStoreName>

TrustStorePassword=<TrustStorePassword>

HostNameInCertificate=<FQDN in Certificate>

 

3) (OPTIONAL) Configure the Validate Server Certificate settings (e.g Trust Store) 

NOTE: If 'ValidateServerCertificate' is enabled, then the 'TrustStore', TrustStorePassword', & 'HostNameInCertificate' will also need to be defined and have values populated in the DSN.

6b) If there is no Trust Store, then remove the "Validate Server Certificate Flag" 

'ValidateServerCertificate=0'