Tips for finding Knowledge Articles

  • - Enter just a few key words related to your question or problem
  • - Add Key words to refine your search as necessary
  • - Do not use punctuation
  • - Search is not case sensitive
  • - Avoid non-descriptive filler words like "how", "the", "what", etc.
  • - If you do not find what you are looking for the first time,reduce the number of key words you enter and try searching again.
  • - Minimum supported Internet Explorer version is IE9
Home  >
article

KB-1311: Collector unable to connect to SQL database

Centrify DirectAudit ,  

13 April,17 at 10:02 PM

Applies to: All versions of Centrify DirectAudit


Problem:

The Collector is unable to connect to the SQL database and the following error appears in the Collector debug log:

[ <Unnamed thread> | 6/9/2009 1:14:20 PM ] [ Warning ] Centrify DirectAudit collector cannot contact DirectAudit database "Data Source=dacc01.continents.com;Initial Catalog=DirectAudit;Integrated Security=True;Max Pool Size=3000". Please check the network connection. Collector service will be temporarily unavailable until the database can be contacted.


When this error occurs, the corresponding behavior on the DirectAudit agent when you run dainfo is:

dainfo -d
Establishing connection with dad: Success
Getting dad's online status: Offline
Getting dad's current collector:
Getting dad's offline db size: 0.00 Bytes
Getting offline database information:
Size on disk: 8.00 KB
Database filesystem usage: 1.93 GB used, 2.62 GB total, 704.58 MB free
Machine is Joined to continents.com
Pinging adclient: Available
Zone is enabled for auditing
Located collector information from continents.com/UNIX/PROD/Zones/aix-admin-prod: DACC01$@CONTINENTS.COM:dacc01.continents.com:65535
Attempting to connect to collectors:
Host: dacc01.continents.com Port: 65535 - Error: Timed out connecting to server (aix handler)



Cause:

This only happens if an Active Directory service account is being used for starting up the SQL server service instead of the local system account. If the service account does not have any SPN's then the Kerberos authentication request from the Collector (client) to the SQL server fails. Specifically, Kerberos authentication uses the SPN registered for the SQL service account to authenticate a service. When a client wants to connect to a service, it locates an instance of the service, composes an SPN for that instance, connects to the service, and presents the SPN for the service to authenticate.


Solution:

Navigate to C:\Program Files (x86)\Centrify\DirectAudit or download the attached script (at the end of this KB) to a machine which is joined to Active Directory. 

 

Note: This vbscript connects to the specified computer and search for SQL server services. For each of the SQL server services, it will examine the service accounts and make sure the service principal names are set correctly  so that the Kerberos authentication can work properly.  If the SPN is missing, it will try to fix it.  In order to fix the service ' principal name, you need to have write permission on the service account in Active Directory. This script requires RootDSE, Configuration Naming Context and Global Catalog accessible in the forest. If Global Catalog is not  available, the SPN cannot be assured to be unique within the forest.  Multiple user accounts with the same SPN will cause Kerberos authentication failure.

 

This script requires Windows Management Instrumentation service running on the target machine.  Please make sure the service is running.  If you are running this script remotely, please also make sure TCP port 135 is not blocked by any firewall.  

 

Usage: cscript checkspn.vbs [SQL Server] [GC server]

 

where "SQL server" is mandatory and must be in FQDN format. "GC server" is optional.  You can specify either forest name or Global Catalog server name.  Default value is current forest

 

By default, this script runs in report-only mode. It will check if the required SPN's are present on the service account in question and will prompt to fix it. This script registers the SPN in the format:

 

MSSQLSvc/<FQDN>:<tcpport>

 

into the service account servicePrincipalName attribute.


References:

http://technet.microsoft.com/en-us/library/ms191153(SQL.90).aspx

http://blogs.msdn.com/sql_protocols/archive/2006/12/02/understanding-kerberos-and-ntlm-authentication-in-sql-server-connections.aspx

Attachments:

Still have questions? Click here to log a technical support case, or collaborate with your peers in Centrify's Online Community.