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:

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

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.

Answer 1

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

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.

Answer 2

Old post, but might be useful still. In my case it was that only Windows Authentication was set. Setting authentication for both Windows and SQL Server on the linked server fixed it.

Answer 3

The issue for me was this: Since I was trying to connect to instance via servername\instancename - ALL my instances were running on port 1433 so the "Add Linked Server" was actually connecting to the default instance - and the login was failing.

1. Go to SQL Configuration Manager
2. Click on Protocols for [instancename]
3. Open TCP/IP properties and be sure it's enabled AND flip to the "IP Addresses" Tab and change the port on ALL IPs that you are using for your linked server IP is (a) active and (b) using a unique port - like 14333 (this was important as my VPN IP was not "active".
4. You may have to do this for both 32 and 64 if your machine is running both
5. DONT FORGET TO STOP and START THE INSTANCE

This is the fix