Login failed when querying linked server

I am trying to create a linked server in SQL Server:

--Create the link to server "uranium"
EXEC master.dbo.sp_addlinkedserver 
      @server = N'uranium', 

--Add the catch-all login with SQL Server authentication
EXEC master.dbo.sp_addlinkedsrvlogin 

And it creates fine. But any attempt to query the linked server, e.g.:

  • SQL Query to group items by time, but only if near each other?
  • SqlBulkCopy cannot access table
  • How to display Date in DD/MM/YYYY H:MM AM/PM format in SQL Server
  • Passing irregular xml file to the stored procedure
  • MVC create SQL database using SMOLite
  • SELECT * FROM uranium.Periodic.dbo.Users

    results in

    Msg 18456, Level 14, State 1, Line 1
    Login failed for user 'BatteryStaple'.

    Except i know the credentials are correct:

    • Login: BatteryStaple
    • Password: Horsecorrect

    because i can login when i connect directly using SQL Server Management Studio, or any other technology that is able to connect to a database.

    enter image description here

    Bonus Reading

    • Login Failed for linked server (he forgot to call sp_addlinkedsrvlogin)
    • Why am I getting a “login failed” when creating this linked server? (he’s trying to use integrated authentication)
    • MSDN Blogs: SQL Linked Server Query failed with “Login failed for user …” (he’s trying to make integrated authentication work)

    Note: New SQL Server 2014 install. Every existing SQL 2000, 2005, 2008, 2008 R2 can communicate to their uranium linked server. I’m certain it is related to Microsoft’s frustrating broken by default policy.

  • Cannot implicitly convert type 'System.Collections.Generic.IEnumerable<dynamic>' to 'int'
  • SQL Server : Columns to Rows
  • How to swap value in SQL Server?
  • How to setup SQL Azure Reporting?
  • SSRS multi axis chart issue
  • How can I add a display order within a parent/child query?
  • One Solution collect form web for “Login failed when querying linked server”

    The issue is that the SQL Server Management Studio interface creates the linked server using the OLEDB Provider:

    enter image description here

    This is equivalent to the original T-SQL:

    --Create the link to server "uranium"
    EXEC master.dbo.sp_addlinkedserver 
          @server = N'uranium', 
          @srvproduct=N'', @provider=N'SQLNCLI'

    The fix is to create the linked server as SQL Server:

    --Create the link to SQL Server "uranium"
    EXEC master.dbo.sp_addlinkedserver 
          @server = N'uranium', 
          @srvproduct=N'SQL Server'

    Shouldn’t matter. Probably a regression in Microsoft SQL Server 2014 12.0.4213.0. Might be fixed in a service pack – if there is one.

    But there it is; solved.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.