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 .
4条答案
按热度按时间d6kp6zgx1#
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:
The fix is to create the linked server as 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.
6ioyuze22#
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.
wribegjk3#
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.
This is the fix
klsxnrf14#
The following helped me.