none
SQL Server Linked Server Windows Authentication Login RRS feed

  • Question

  • Trying to create a linked server connections from SQL2017 (server A) to SQL2017(server B).DB1.dbo.vwXYZ. Was looking at this article, but, the Windows Authentication Login did not work.

    Previously, connections were successfully made from SQL2008 to SQL2008 using SQL Server Authenticated Logins. However, recent security audit requires Windows authentication logins only.

    Getting errors when connecting in SQL2017 using Windows

    Also reviewed this article, and confirmed the domain account does not have "Account is sensitive and cannot be delegated" checked.

    Any suggestions / help?

    • Edited by Ami2013 Friday, August 9, 2019 8:36 PM
    Friday, August 9, 2019 8:30 PM

Answers

  • In this case, Windows or SQL logins do not matter, because you are not even making it to the server.

    That is the address you specified for the server was not correct, or there is a network problem.

    If you only specified the server name, try using a fully qualified domain name. Also make sure that you don't leave out any instance name.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, August 9, 2019 9:38 PM
    Moderator
  • Hi Ami2013,

    You SQL server service running under Network Service account, could you please change it to Local System account? For a domain administrator account, the account need has permissions to register an SPN. The permissions required for this are the "Read servicePrincipalName" and "Write servicePrincipalName" access control settings in the Active Directory service.

    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

    Friday, August 16, 2019 9:55 AM
  • Issue resolved by implementing constrained delegation on AD.
    • Marked as answer by Ami2013 Thursday, September 12, 2019 3:45 PM
    Thursday, September 12, 2019 3:45 PM

All replies

  • In this case, Windows or SQL logins do not matter, because you are not even making it to the server.

    That is the address you specified for the server was not correct, or there is a network problem.

    If you only specified the server name, try using a fully qualified domain name. Also make sure that you don't leave out any instance name.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, August 9, 2019 9:38 PM
    Moderator
  • Hi Ami2013,

    According to your error message, there are some possible reasons behind the Microsoft SQL Server error 53 in below points.
    1.Wrong name specified in the Linked server text box 
    2.Test server connectivity with PING from the client machine
    3.SQL Server Instance is not accessible due to firewall or any reason.
    4.Telnet port 1433 or port no on which SQL Server is running. These ports might be blocked.
    5.TCP/IP or Named Pipes protocol is disabled in SQL Server Configuration Manager.
    6.Remote Connection is disabled for this SQL Server Instance.
    7.SQL Server Browser service is stopped.

    Please refer to Fix SQL Server Error 53 and Linked Server error: Named Pipes Provider: Could not open a connection to SQL Server [53] to get more information.

    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

    Monday, August 12, 2019 6:56 AM
  • I tried pinging and it was successful. Ran the following tsql, completed successfully.

    USE [master]
    GO
    EXEC master.dbo.sp_addlinkedserver @server = N'<servername>', @srvproduct=N'SQL Server'
    
    GO
    EXEC master.dbo.sp_serveroption @server=N'<servername>', @optname=N'collation compatible', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'<servername>', @optname=N'data access', @optvalue=N'true'
    GO
    EXEC master.dbo.sp_serveroption @server=N'<servername>', @optname=N'dist', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'<servername>', @optname=N'pub', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'<servername>', @optname=N'rpc', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'<servername>', @optname=N'rpc out', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'<servername>', @optname=N'sub', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'<servername>', @optname=N'connect timeout', @optvalue=N'0'
    GO
    EXEC master.dbo.sp_serveroption @server=N'<servername>', @optname=N'collation name', @optvalue=null
    GO
    EXEC master.dbo.sp_serveroption @server=N'<servername>', @optname=N'lazy schema validation', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'<servername>', @optname=N'query timeout', @optvalue=N'0'
    GO
    EXEC master.dbo.sp_serveroption @server=N'<servername>', @optname=N'use remote collation', @optvalue=N'true'
    GO
    EXEC master.dbo.sp_serveroption @server=N'<servername>', @optname=N'remote proc transaction promotion', @optvalue=N'true'
    GO
    USE [master]
    GO
    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'<servername>', @locallogin = N'DOMAIN\USERNAME', @useself = N'True'
    GO

    I tried select * from <servername>.<dbname>.dbo.<viewname> and got following error:

    Msg 18456, Level 14, State 1, Line 101
    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

    The DOMAIN\USERNAME under @locallogin has sysadmin privileges on that server. That username also does not have "Account is sensitive and cannot be delegated" in AD.

    I don't understand the need for Named Pipes to be enabled. Is it because it's using Windows authentication? The current SQL authentication works without enabling Named Pipes.


    • Edited by Ami2013 Monday, August 12, 2019 1:56 PM
    Monday, August 12, 2019 1:53 PM
  • Hi Ami2013,

    You SQL server service running under Network Service account, could you please change it to Local System account? For a domain administrator account, the account need has permissions to register an SPN. The permissions required for this are the "Read servicePrincipalName" and "Write servicePrincipalName" access control settings in the Active Directory service.

    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

    Friday, August 16, 2019 9:55 AM
  • Thank you Cathy for responding.

    SQL Server Service account is running under a network service account. I also had the Domain Admin ran the setspn command.

    When I run setspn -L DOMAIN\SERVICEACCOUNTNAME , the result shows that both servers and instance are listed as Registered ServicePrincipalNames.

    Are you saying the SQL Server Service account needs to be Local System Account to create the Linked Server? If so, which servers will need that? Target server or originating server or both?

    Thank you


    • Edited by Ami2013 Friday, August 16, 2019 9:02 PM
    Friday, August 16, 2019 8:59 PM
  • Issue resolved by implementing constrained delegation on AD.
    • Marked as answer by Ami2013 Thursday, September 12, 2019 3:45 PM
    Thursday, September 12, 2019 3:45 PM