December 11, 2008 at 3:52 am
Got a strange linked server issue, wondered if anyone can help.
I get this error message when trying to run queries against a linked server:
Msg 18452, Level 14, State 1, Line 1
Login failed for user ''. The user is not associated with a trusted SQL Server connection.
Now the linked server is set up correctly on my machine:
--Drop linked server
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'DEV')EXEC master.dbo.sp_dropserver @server=N'DEV', @droplogins='droplogins'
-- Add linked server - DEV
EXEC sp_addlinkedserver
@server='DEV',
@srvproduct='',
@provider='SQLNCLI',
@datasrc='XXXX\XXXX'
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'DEV',
@useself = 'false',
@locallogin = 'sa',
@rmtuser = 'sa',
@rmtpassword = 'XXXXX'
Then my TSQL is simple enough: SELECT * FROM DEV.[DBname].dbo.[tablename]
I know it's all configured correctly because I sent the code to another developer here, he ran it "as is" and it all worked perfectly.
So it must be something to do with my local setup.
I can connect to the SQL server in Management Studio using the credentials above.
I can also run the sql via SQLCMD using the same credentials.
So I'm guessing it's a network, domain kinda issue, but am stumped as to what.
Anyone got any ideas or had similar experiences?
TIA 🙂
December 11, 2008 at 7:41 am
daft (12/11/2008)
Got a strange linked server issue, wondered if anyone can help.{...}
Anyone got any ideas or had similar experiences?
I've seen this error when the account used for the linked server connection is not sysadmin level. I'm guessing that although you've posted the account as "sa" you're not really going to use that account for the linked server connection. At least I hope not. 😉
The way I've found around it is to specify the provider string complete with username and password (drop the datasrc entry)
December 12, 2008 at 12:05 pm
How are you executing your simple TSQL to get the error? In an SSMS query window? I've seen this error when executing from a SQL Agent job because the SQL Agent credentials don't work on the remote server or not added as a remote login.
December 15, 2008 at 3:40 am
FNS (12/11/2008)
daft (12/11/2008)
Got a strange linked server issue, wondered if anyone can help.{...}
Anyone got any ideas or had similar experiences?
I've seen this error when the account used for the linked server connection is not sysadmin level. I'm guessing that although you've posted the account as "sa" you're not really going to use that account for the linked server connection. At least I hope not. 😉
The way I've found around it is to specify the provider string complete with username and password (drop the datasrc entry)
I am planning using the sa account for the linked server, but don't worry about that, it's not your average setup here - security isn't an issue and we're moving data between dev servers with the linked server being dropped at the end of each script.
I'll give the provider string approach a shot but would that make any difference if the account is sysadmin level? I'll try it anyway
December 15, 2008 at 3:41 am
dongadoy (12/12/2008)
How are you executing your simple TSQL to get the error? In an SSMS query window? I've seen this error when executing from a SQL Agent job because the SQL Agent credentials don't work on the remote server or not added as a remote login.
Yeah, I am trying to execute through SSMS so i think the sql agent credentials should be ok?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply