none
SQL 2017+ Error ID - 4221 - Cannot connect to the target Sql Server instance. RRS feed

  • Question

  • Hi,

    OpsMgr 2016 UR5.

    SQL 2017+ MP v7.0.7.0.

    I'm getting the below error (plus others similar for all 2017+ discoveries) both in SCOM and in the SQL error log.  The SQL instance is configured for "Windows Authentication".  Only using one service account for discovery/monitoring SQL … "<domain>/<ServiceAcct>".   The service account is distributed to the machine just like for the SQL 2012/2014/2016 databases which do not have the same issue. This error occurs on all our SQL 2017 machines.  The "<domain>/<ServiceAcct>" is sysadmin for the instance and in the Local Administrators group on the box. It is also in the "Allow Log on Locally" group.

    It looks to me like the MP discovery scripts are dropping the <domain> portion of the service account and trying to logon with just "<ServiceAcct>" .

    Also, all the 2017+ instance are using TCP port 14331.  All others (2012/2014/2016 instances) use TCP port 1433.  Windows firewall is turned off on the box.  Agent proxy has been enabled on this machine as well.

    Any help appreciated.

    Noah

    ====================

    Date  18/01/2019 1:44:01 PM
    Log  Windows NT (Operations Manager)

    Source  SQL Server Discovery MP Windows
    Category  (0)
    Event  4221
    Computer  <ServerName>

    Message
    Management Group: "OpsMgr2012"
    Module: Microsoft.SQLServer.Windows.Module.Discovery.Discoveries.FilegroupDiscoveryCD
    Version: 7.0.7.0

    Error(s) was(were) occurred:
    State:
    The configuration properties are:
    ManagementGroupName = OpsMgr
    Publisher = SQLDiscoveryWindows
    ConnectionString = <ServerName>.application.enet\SQL01
    InstanceName = SQL01
    MachineName = <ServerName>
    MonitoringType = Local
    Login = <ServiceAcct>                           <===== I would expect "<domain>/<ServiceAcct>" here...?
    ConnectionString = <ServerName>.application.enet\SQL01
    SqlTimeoutSeconds = 15
    TimeoutSeconds = 300
    Password = ********

    Error(s):

    ---------- Exception: ----------
    Cannot connect to the target Sql Server instance.
    Connection log:
    Failed to connect to data source 'lpc:<ServerName>\SQL01': Login failed for user '<ServiceAcct>'.
    Error number:18456

    Failed to connect to data source '<ServerName>.application.enet\SQL01': A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol)
    Error number:-1

    Failed to connect to data source '<IPAddress>,14331': Login failed for user '<ServiceAcct>'.
    Error number:18456

    Failed to connect to data source '<ServerName>.application.enet,14331': Login failed for user '<ServiceAcct>'.


       at Microsoft.SQLServer.Core.Module.Helper.Sql.SqlConnectivityHelper.SmartConnect(String connectionDataSource, String databaseName, String computerName, String instanceName, SqlCredential sqlCredential, String applicationName, String inputWmiPath, Int32 timeout, Boolean useSqlErrorStopList, IEnumerable`1 stopSqlErrorCodes, Boolean stopOnNetworkRelatedExceptions, String debugUser, String debugPassword)
       at Microsoft.SQLServer.Core.Module.Helper.TransientErrorHandling.SqlRetryClient.<GetNewOpenedConnectionInternalAsync>d__32.MoveNext()
    --- End of stack trace from previous location where exception was thrown ---
       at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
       at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
       at Microsoft.SQLServer.Core.Module.Helper.TransientErrorHandling.SqlRetryClient.<InitConnectionAsync>d__33.MoveNext()
    --- End of stack trace from previous location where exception was thrown ---
       at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
       at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
       at Microsoft.SQLServer.Core.Module.Helper.TransientErrorHandling.SqlRetryClient.<ExecuteCommandDataReaderInternalAsync>d__31.MoveNext()



    • Edited by Noah Deah Friday, January 18, 2019 4:08 AM
    Friday, January 18, 2019 3:18 AM

All replies

  • Please go through to below article. It will be helpful for you.

    https://kevinholman.com/2016/08/25/sql-mp-run-as-accounts-no-longer-required/

    Thanks & Best Regards,

    Abhishek Sachdeva

    Please mark this answer if this post is helpful.

    Friday, January 18, 2019 3:52 AM
  • Thanks for the quick reply, Abhishek.  I had previously gone through that article and tried the recommendations.  But that just gave me other errors.  I have seen other posts that recommend this and I do see it mentioned in the guide, but we have all our other sql servers (hundreds) already mapped in the old way.  I am just trying to get the configuration as described in the guide to work, as it has for our previous SQL versions.

    Thanks again.


    • Edited by Noah Deah Friday, January 18, 2019 4:19 AM
    Friday, January 18, 2019 4:07 AM
  • Hi,

    For SQL Server 2017+, we may also consider the agentless monitoring. With the agentless monitoring mode, we can use SQL credentials for authentication that simplifies security configuration.

    Regards,

    Alex Zhu
    -----------------------------------------------
    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.
    Monday, January 21, 2019 5:27 AM
  • More information:

    When I use the SQLCMD utility from the command line (cmd.exe) to remote connect to one of our SQL 2017 instances using the SQL Server 2017+ Discovery/Monitoring account,  if I specify the -U<Username> -P<password> parameters to connect ('<domain>/<ServiceAcct>' is SysAdmin in the SQL 2017+ instance I'm trying to connect to) , the SQLCMD utility uses ODBC and SQL Authentication to try to connect.  Then I get the same error as above: "Login failed for user '<domain>/<ServiceAcct>'. Reason: An attempt to login using SQL authentication failed. Server is configured for Integrated authentication only."

    If I run  "cmd.exe" as  '<domain>/<ServiceAcct>', then run the SQLCMD utility to connect to the remote 2017 SQL instance and do not specify a username/password, SQLCMD connects using Windows Authentication and the connection is successful.

    I wonder if the 2017+ MP does something different connection-wise than the previous versions...?

    Wednesday, January 23, 2019 2:03 AM
  • Did anyone ever figure a fix for this?  I'm running into the exact same issue and I've verified all the accounts and distribution.
    Tuesday, April 16, 2019 4:40 PM
  • Same issue here. :(
    Thursday, April 18, 2019 9:19 AM