Login failed when querying linked server
<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>