linked server issues

Putoch
Putoch used Ask the Experts™
on
Hello,
I created a linked server which had been running fine. Something has changed since yesterday and for some reason it is throwing me an error.

So i created it;
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'AUXXX'',@useself=N'False',@locallogin=NULL,@rmtuser=N'zxxx',@rmtpassword='########'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'AUXXX',@useself=N'True',@locallogin=N'zxxx',@rmtuser=NULL,@rmtpassword=NULL

I then wrote a query to test it ; Select * from AUXXX.database.tablename
This works fine and returns expected results.

i then tried to create a SP using the same query
Create Procedure NEWProcedure as Select * from AUXXX.database.tablename  and i got this error:

Msg 7416, Level 16, State 1, Procedure NEWProcedure, Line 211 [Batch Start Line 9]
Access to the remote server is denied because no login-mapping exists.

Not sure what to do would love some advice please if you have any?
Thank you,
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Developer
Commented:
Well,  the creation makes no sense. Either you specify a user (first addlinkedsrvlogin ) or you don't (second addlinkedsrvlogin .

The first option is for using SQL Server authentication (@useself=N'False'), whereas the second is for using integrated Windows authentication (@useself=N'True').

So remove the linked server.  And add it using one authentication mode:

USE [master];
GO

-- Add server.
EXEC master.sys.sp_addlinkedserver @server = N'AUXXX' ,
                                   @srvproduct = N'SQL Server';

-- Choose either SQL Server Authentication or Windows Authentication!

-- SQL Server Authentication
EXEC master.sys.sp_addlinkedsrvlogin @rmtsrvname = N'AUXXX' ,
                                     @useself = N'False' ,
                                     @locallogin = NULL ,
                                     @rmtuser = N'zxxx' ,
                                     @rmtpassword = '########';

-- Windows Authentication
EXEC master.sys.sp_addlinkedsrvlogin @rmtsrvname = N'AUXXX' ,
                                     @useself = N'True' ,
                                     @locallogin = NULL ,
                                     @rmtuser = NULL ,
                                     @rmtpassword = NULL;
GO

Open in new window

Author

Commented:
Hi there,
Thank you for your response.
It seems that i can have both of those user levels added to the properties. That wasn't causing the issue.
Now perhaps that is not best practice, so i am taking your advise and just using the SQL server auth

However, the SP that i was running, was referencing a view that was built based on a linked server that no longer existed.
That error wasn't thrown up until i restarted the server completely and then it showed the old linked server name in the error and named the view.

So the solution to my problem was to check the views and insuring they were no longer referencing the old linked server.

Thank you for your help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial