title: Login failed when querying linked server
body: <p>I am trying to create a linked server in SQL Server:</p>
  
  <pre><code>--Create the link to server "uranium"
  EXEC master.dbo.sp_addlinkedserver 
        @server = N'uranium', 
        @srvproduct=N'', 
        @provider=N'SQLNCLI'
  
  --Add the catch-all login with SQL Server authentication
  EXEC master.dbo.sp_addlinkedsrvlogin 
        @rmtsrvname=N'uranium',
        @useself=N'False',
        @locallogin=NULL,
        @rmtuser=N'BatteryStaple',
        @rmtpassword='Horsecorrect'
  </code></pre>
  
  <p>And it creates fine. But any attempt to query the linked server, e.g.:</p>
  
  <pre><code>SELECT * FROM uranium.Periodic.dbo.Users
  </code></pre>
  
  <p>results in</p>
  
  <pre><code>Msg 18456, Level 14, State 1, Line 1
  Login failed for user 'BatteryStaple'.
  </code></pre>
  
  <p>Except i know the credentials are correct:</p>
  
  <ul>
  <li><strong>Login:</strong> <code>BatteryStaple</code></li>
  <li><strong>Password:</strong> <code>Horsecorrect</code></li>
  </ul>
  
  <p>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.</p>
  
  <p><a href="https://i.stack.imgur.com/a05aK.png" rel="noreferrer"><img src="https://i.stack.imgur.com/a05aK.png" alt="enter image description here"></a></p>
  
  <h2>Bonus Reading</h2>
  
  <ul>
  <li><a href="https://stackoverflow.com/questions/10569936/login-failed-for-linked-server">Login Failed for linked server</a> <em>(he forgot to call <code>sp_addlinkedsrvlogin</code>)</em></li>
  <li><a href="https://dba.stackexchange.com/questions/43350/why-am-i-getting-a-login-failed-when-creating-this-linked-server">Why am I getting a “login failed” when creating this linked server?</a> <em>(he's trying to use integrated authentication)</em></li>
  <li>MSDN Blogs: <a href="https://blogs.msdn.microsoft.com/sql_protocols/2006/08/10/sql-linked-server-query-failed-with-login-failed-for-user/" rel="noreferrer">SQL Linked Server Query failed with “Login failed for user …”</a> <em>(he's trying to make integrated authentication work)</em></li>
  </ul>
  
  <p><strong>Note</strong>: New SQL Server 2014 install. Every existing SQL 2000, 2005, 2008, 2008 R2 can communicate to their <code>uranium</code> linked server. I'm certain it is related to Microsoft's frustrating <a href="https://stackoverflow.com/questions/28474432/is-put-broken-by-default-in-asp-net-mvc"><em>broken by default</em> policy</a>.</p>