Return
-
Title
Cannot add or connect with SQL Server agent due to "Integrated authentication failed" -
Description
When attempting to connect to a SQL Server host using the SQL Server agent, you may encounter an error like:
Failed to establish a SQL-Server JDBC connection protocol to Host [DOMAIN\HOSTNAME], user account [DOMAIN\USERNAME]. Reason: [Integrated authentication failed. ClientConnectionId:... Profile: MSSQLProfile{...}]This article explains common causes of this error and provides step-by-step resolutions.
-
Cause
Cause ID Description 1 Domain user account issues: account may not exist, password incorrect, locked, disabled, expired, or lacks permissions to log into SQL Server or master database. SQL Server may not allow mixed mode authentication. 2 FglAM service running as Local System cannot impersonate users when using WINDOWS_DEFAULT authentication. 3 Network/firewall blocking connection, TCP/IP disabled on SQL Server, wrong port, or DNS cannot resolve hostname. 4 Missing or incorrect Service Principal Name (SPN) registrations causing Kerberos authentication to fail. 5 Domain trust issues when FglAM and SQL Server are in different domains. 6 Secure connections (SSL) required but not configured. 7 Kerberos authentication is enforced for the Active Directory account. -
Resolution
Resolution ID Action 1 Verify and validate domain account:
- Confirm account exists in AD.
- Userunas /user:DOMAIN\USER cmdon FglAM server.
- Test with SSMS orsqlcmd -S FQDN -E.
- Ensure account hasCONNECT SQLandVIEW SERVER STATEpermissions.
- Verify SQL Server allows Windows authentication.
- Check account access tomasterdatabase:SELECT name, type_desc, is_disabled FROM sys.server_principals WHERE name = 'DOMAIN\USERNAME';2 Run FglAM service under domain account:
- Openservices.msc.
- Locate Foglight Agent Manager service.
- Change Log On to a domain account.
- Restart service.
Note: Issue may occur after upgrade due to Kerberos changes (FOG-9730).3 Verify network connectivity and SQL Server settings:
- Enable TCP/IP in SQL Server Configuration Manager.
- Confirm listening port.
- Usetelnet HOSTNAME portorTest-NetConnectionin PowerShell.
- Check firewall rules.
- Confirm DNS resolution of SQL Server hostname.4 Check and register SPNs:
- On SQL Server host, run:setspn -L <SQL_SERVICE_ACCOUNT>.
- Verify presence ofMSSQLSvc/SQLServerFQDN:portandMSSQLSvc/SQLServerFQDN.
- Register missing SPNs with:setspn -A MSSQLSvc/SQLServerFQDN:port <SQL_SERVICE_ACCOUNT>setspn -A MSSQLSvc/SQLServerFQDN <SQL_SERVICE_ACCOUNT>
(Requires domain admin or delegated rights)5 Resolve domain trust issues:
- Use FglAM server in the same domain as SQL Server host.
- Troubleshoot cross-domain trust relationships.6 Configure secure connections:
- Enable SSL in installer and on FglAM.
- Ensure SQL Server supports secure connections.7 Address Kerberos authentication requirements:
- Confirm Kerberos settings for AD account.
- Adjust database agent authentication type if needed (Windows_Default vs Windows_Custom).
Additional Information
If the above resolutions do not resolve the issue:
-
Check SQL Server error logs via Management Studio or file system for login failures referencing the domain user or ClientConnectionId.
-
Review any related Windows Event Logs on both SQL Server and FglAM hosts for authentication errors.
-
-
Additional Information
If the steps listed above do not resolve the issue, On the SQL Server, check the error logs (via SQL Server Management Studio or the log files in the SQL Server directory) for login failures or related errors. Look for entries related to the DOMAIN\USERNAME or the ClientConnectionID listed in the error message.