41312

Login failed when querying linked server

Question:

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', @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'

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

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:

<ul><li><strong>Login:</strong> BatteryStaple</li> <li><strong>Password:</strong> Horsecorrect</li> </ul>

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.

<a href="https://i.stack.imgur.com/a05aK.png" rel="nofollow"><img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/a05aK.png" data-original="https://i.stack.imgur.com/a05aK.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /></a>

<h2>Bonus Reading</h2> <ul><li><a href="https://stackoverflow.com/questions/10569936/login-failed-for-linked-server" rel="nofollow">Login Failed for linked server</a> <em>(he forgot to call sp_addlinkedsrvlogin)</em></li> <li><a href="https://dba.stackexchange.com/questions/43350/why-am-i-getting-a-login-failed-when-creating-this-linked-server" rel="nofollow">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="nofollow">SQL Linked Server Query failed with “Login failed for user …”</a> <em>(he's trying to make integrated authentication work)</em></li> </ul>

<strong>Note</strong>: 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 <a href="https://stackoverflow.com/questions/28474432/is-put-broken-by-default-in-asp-net-mvc" rel="nofollow"><em>broken by default</em> policy</a>.

Answer1:

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

<a href="https://i.stack.imgur.com/DDhcv.png" rel="nofollow"><img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/DDhcv.png" data-original="https://i.stack.imgur.com/DDhcv.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /></a>

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 <strong>SQL Server</strong>:

--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.

Recommend

  • Javascript setCustomValidity does not work on Chrome Version 65
  • Efficient User-Agent Regex to find Safari in Python
  • What Makes These Two Array Adds Different?
  • Regarding starting the threads on a condition
  • Unable to send e-mail through Java
  • Bigquery event streaming and table creation
  • NUnit 3.0 TestCase const custom object arguments
  • Undefined references when compiling gSOAP client
  • how does System.Web.HttpRequest::PathInfo work?
  • IE11 throwing “SCRIPT1014: invalid character” where all other browsers work
  • Reading a file into a multidimensional array
  • Cannot upload to OneDrive using the new SDK
  • How to remove a SwiftyJSON element?
  • RxJava debounce by arbitrary value
  • pyodbc doesn't report sql server error
  • NSScanner Loop Question
  • Display java JPanel in a JFrame
  • Can you perform a UNION without a subquery in SQLAlchemy?
  • Typescript - Unable to get 'import' statement to function
  • PostgreSQL Query without WHERE only ORDER BY and LIMIT doesn't use index
  • Change Inet root folder for iis 7
  • how to adjust image in a panel in Java swing?
  • Encrypt data by using a public key in c# and decrypt data by using a private key in php
  • How to make a tree having multiple type of nodes and each node can have multiple child nodes in java
  • Sony Xperia Z Tablet not found by adb
  • How to recover from a Spring Social ExpiredAuthorizationException
  • Running a C# exe file
  • Display Images one by one with next and previous functionality
  • ORA-29908: missing primary invocation for ancillary operator
  • How can I estimate amount of memory left with calling System.gc()?
  • How to get next/previous record number?
  • Linker errors when using intrinsic function via function pointer
  • Windows forms listbox.selecteditem displaying “System.Data.DataRowView” instead of actual value
  • SQL merge duplicate rows and join values that are different
  • Hits per day in Google Big Query
  • How do you join a server to an Active Directory (domain)?
  • How does Linux kernel interrupt the application?
  • LevelDB C iterator
  • Running Map reduces the dimensions of the matrices
  • How can i traverse a binary tree from right to left in java?