locked
SQL Server Linked server stopped working after moving from Windows 2008 to Windows 2016 RRS feed

  • Question

  • We used to have windows 2008 servers, we got new hardware and installed windows 2016 and moved the sql 2014 enterprise instance over(all databases including master).
    This is a clustered server.
    The local admins to window did not change, the server names and ip addresses are the same (switched names).
    I confirmed SPN's we are using to connect to SQL Server is the same 
    I confirmed (select auth_scheme From sys.dm_exec_connections where session_id = @@spid) we are using kerberos to connect to sql server. The linked server is pointing to another sql server and the security option is "be made using current security context"
    From another server (I confirmed the connection is to first sql server is kerberos) I am not able to browse or connect to the linked server
    From the actual sql server itself I can browse / access the linked server, but I also noticed the authentication method for this is NTLM.
    I tried "Kerberos Configuration Manager", I can get to make it work from the actual sql server, the log file it generates it empty, I tried it from another Windows 2016 machine pointing to the same sql server no luck, I ran it from a Windows 2008 machine against the same sql server and it shows everything fine on all tabs.
    Everything moved over from old server to the new ones, I cannot seem to figoure out what could be different.
    Any ideas?
    Thank you

    Gokhan Varol

    Sunday, October 13, 2019 10:16 PM

All replies

  • Can you post the error message?

    Regards;
    Vivek Janakiraman
    ----------------------------
    My Blog
    My Linkedin Page
    ----------------------------

    Monday, October 14, 2019 12:05 AM
  • This is all you will see when you browse the linked server.


    Gokhan Varol

    Monday, October 14, 2019 1:49 PM
  • We would need more details on a non-working server...like the error message you see, are you able to expand the tables and view them? Is it empty when you expand the linked server? Did you try drop and recreate the linked server?
    Monday, October 14, 2019 2:22 PM
  • What you are seeing in SSMS is normally a symptom of "data access" being set to false on the linked server.  If you right click "SALESFORCE" and select "Test Connection", do you get an error:

    Server 'xxxxx' is not configured for DATA ACCESS.

    If so, right click on "SALESFORCE" and select "Properties".  On the "Server Options" tab, set "data access" to true.

    Monday, October 14, 2019 6:59 PM
  • We moved all databases from old server to the new one including master and other system databases, all settions are the same before and old, ips are the same. Something related to Windows 2016 is blocking this I am not sure what it is.

    Gokhan Varol

    Tuesday, October 15, 2019 12:00 AM
  • if you try test connection what error do you get?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, October 15, 2019 8:23 AM
  • We moved all databases from old server to the new one including master and other system databases, all settions are the same before and old, ips are the same. Something related to Windows 2016 is blocking this I am not sure what it is.

    Gokhan Varol

    Hi GV1973,

    Could you please share us the error message that you got when you test link server connection as others mentioned? 

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, October 15, 2019 9:03 AM
  • -- SQL LOG
    2019-10-13 15:13:55.900 Server       SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.
    2019-10-13 15:13:55.950 Server       The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/XXXX.xxxx.Com ] for the SQL Server service.
    2019-10-13 15:13:55.950 Server       The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/XXXX.xxxx.Com:1433 ] for the SQL Server service.


    -- Confirm spn
    >setspn -l XXX\xxxx
    Registered ServicePrincipalNames for CN=ISCP-SA-XXXX,OU=Service Accounts,OU=Global,DC=xxxx,DC=Com:
            MSSQLSvc/XXXX.xxxx.Com:1433
            MSSQLSvc/XXXX.xxxx.Com

    -- Confirm kerberos
    select auth_scheme From sys.dm_exec_connections where session_id = @@spid
    KERBEROS

    -- Test connection
    The test connection to the linked server failed.
    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456)

    If I test this RDP'ing into SQL Server machine at which linked server is setup at the test is fine. This also used to work before we moved from windows 2008 to 2016 (Ip's the same, server names / service accounts / user rights the same, all databases including system was brought over therefore all settings are the same)


    Gokhan Varol

    Tuesday, October 15, 2019 9:22 AM
  • >>The test connection to the linked server failed.
    >>Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456

    Finally you showed the error.

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

    "Login Failed for user 'NT AuthorityANONYMOUS' LOGON"

    In this scenario, client make tcp connection, and it is most likely running under LocalSystem account, and there is no SPN registered for SQL instance, hence, NTLM is used, however, LocalSystem account inherits from System Context instead of a true user-based context, thus, failed as 'ANONYMOUS LOGON'. See http://support.microsoft.com/kb/132679.

    The workaround here is

    a. ask your domain administrator to manually register SPN if your SQL Server running under a domain user account.

    b. use NP connection.

    c. change your sql server to run under either localsystem account or networkservice account.

    Here, a is recommended.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, October 16, 2019 5:50 AM
  • Also read this article

    https://www.sqlservercentral.com/forums/topic/linked-server-setup-error-login-failed-for-user-nt-authorityanonymous-logon


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, October 16, 2019 5:52 AM