Error 7416
Contents |
(Русский)ישראל (עברית)المملكة العربية السعودية (العربية)ไทย (ไทย)대한민국 (한국어)中华人民共和国 (中文)台灣 (中文)日本 (日本語) HomeLibraryLearnDownloadsTroubleshootingCommunityForums Ask a question Quick access Forums home Browse forums users FAQ Search related threads Remove From My Forums Asked by: Error 7416 -
Error 7416 Access To The Remote Server
Access to the remote server is denied because no login-mapping error 7399 exists SQL Server > SQL Server Database Engine Question 0 Sign in to vote I am trying
Sql Server Error 7416
to use a linked server and it works as long as I do not specify the sp_addlinkedserver @provstr parameter. If I specify that parameter I always sql linked server permissions get a 7416 "Access to the remote server is denied because no login-mapping exists" error. I have tried adding the logins various ways but it's very specific to the @provstr parameter, and it doesn't even matter what I put in that parameter. As soon as I put something in there whether it is valid sql server linked server security best practices or invalid, I get the error. Anyone else seen this? There is an amazing lack of any discussion about the error when I search for it. If I do this it works fine, EXEC sp_addlinkedserver @server= 'linkedname', @srvproduct='', @provider='SQLNCLI', @datasrc='servername', @catalog='mydatabase'EXEC sp_addlinkedsrvlogin 'linkedname', 'true', 'AppUser' But as soon as I add the @provstr parameter, then I get the error if I try to use linkedserver, EXEC sp_addlinkedserver @server= 'linkedname', @srvproduct='', @provider='SQLNCLI', @datasrc='servername', @catalog='mydatabase', @provstr='Failover Partner=otherservername'EXEC sp_addlinkedsrvlogin @rmtsrvname='linkedname', @useself='true', @locallogin='AppUser' It doesn't even make any difference what I put in the @provstr parameter - the sp_addlinkedserver statement always executes without an error, but running a query that uses the linked server generates the error. Friday, October 26, 2007 2:28 PM Reply | Quote All replies 0 Sign in to vote I don't know you have seen BOL, however I wanted to share the following info with you. From BOL: When the linked server is created again
Recent PostsRecent Posts Popular TopicsPopular Topics Home Search Members Calendar Who's On Home » SQL Server 2008
Sql Server Linked Server Security Context
» SQL Server 2008 - General » Linked Server Security 11 posts,Page linked server security risk 1 of 212»» Linked Server Security Rate Topic Display Mode Topic Options Author Message Brian CarlsonBrian Carlson Posted Thursday,
Sql Server Linked Server Windows Authentication
January 7, 2010 11:45 AM Old Hand Group: General Forum Members Last Login: Tuesday, August 2, 2016 6:05 AM Points: 308, Visits: 455 I do not really understand linked server security https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6543310d-1b44-4b28-8bb6-caf959c1249b/error-7416-access-to-the-remote-server-is-denied-because-no-loginmapping-exists?forum=sqldatabaseengine in sql server 2008. I have a SQL Server logon for the remote server. I do not want everyone to be able to access this though. I can get the linked server to work by setting "Be made using this security context" under "For a login not defined in the list above, connections will" and providing the login and password. What I http://www.sqlservercentral.com/Forums/Topic843774-391-1.aspx would like to do is create the linked server with permissions only for a windows account with sysadmin priveledges. Then a stored procedure would access the data, owned by the same user, with the execute as owner permission set. That is the theory. I have not been able to create the linked server. Would anyone be able to guide me to instructions on how to do this? I have checked BOL and it has not been helpful. Thanks,Brian. Post #843774 Ray KRay K Posted Thursday, January 7, 2010 11:57 AM SSCrazy Group: General Forum Members Last Login: Friday, October 7, 2016 4:05 PM Points: 2,471, Visits: 4,407 The security issue is a good question. I wouldn't mind learning more about this myself.As for how to link servers, I've had good luck with the sp_addlinkedserver SP. Did you come across this in BOL? +--------------------------------------------------------------------------------------+Check out my blog at https://pianorayk.wordpress.com/ Post #843784 Brian CarlsonBrian Carlson Posted Thursday, January 7, 2010 12:17 PM Old Hand Group: General Forum Members Last Login: Tuesday, August 2, 2016 6:05 AM Points: 308, Visits: 455 Yes, I have looked at sp_
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies http://stackoverflow.com/questions/32084453/sql-linked-server-yields-error-no-login-mapping-exists-if-non-admin-account-is of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=90915 Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: linked server Sign up SQL Linked Server yields error “no login-mapping exists” if non-admin account is used up vote 1 down vote favorite I have a local SQL Server 2008R2. I have configured Linked Server to a remote database. The Linked Server works great when I login to the local server using a SQL-login account with sysadmin server role. I can query against the sql server linked remote server, so I know the Linked Server setting is correct. However, I would get the error below if I use an account that does not have the sysadmin server role. Msg 7416, Level 16, State 2, Line 2 Access to the remote server is denied because no login-mapping exists. For both local and remote servers, SQL login is used (Windows authentication is not used) What kind of security I need to configure for a regular SQL-login account to use Linked Server? sql sql-server security linked-server share|improve this question edited Aug 19 '15 at 20:42 asked Aug 19 '15 at 0:00 Tony 614711 1 If you right click . properties on the linked server definition and go to the Security tab, how is it set up? Alternatively script the linked server as Create and paste the sp_addlinkedsrvlogin part(s) (sanitising passwords etc.) –Nick.McDermaid Aug 19 '15 at 0:31 What @Nick.McDermaid said. If it says Not be made, you need to add a mapping to the list OR provide one of the 3 alternate options below in the radio buttons. –Brad D A
SQL Server experts to answer whatever question you can come up with. Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums. SQL Server Forums Profile | ActiveTopics | Members | Search | ForumFAQ Register Now and get your question answered! Username: Password: Save Password Forgot your Password? All Forums SQL Server 2000 Forums SQL Server Administration (2000) Error 7416 - Help needed Reply to Topic Printer Friendly Author Topic OldHippy Starting Member 5 Posts Posted-10/12/2007: 13:19:53 Hi,I'm attempting to set up a linked server from a customer's sharepoint MSDE (SP4) to a SQL Server 2000 box at my web host. I keep getting: "Error 7416: Access to the remote server is denied because no-login mapping exists." no matter what I do.I have written scripts at my office that work at the office, run successfully at the customer site , but fail to link to the remote DB. I always get the same error (above) when I try to see the tables. I have tried multiple drivers including SQL Server, SQLOLEDB and MSDASQL. They all work at my office but fail at the customer. Here's a script that works at my office but not at the client:sp_addlinkedserver 'WebDB','SQL Server' Gosp_SetNetName 'WebDB', 'WebDB'Gosp_addlinkedsrvlogin 'WebDB', 'false', NULL, 'DB_User', 'DB_Pwd'GOsp_addlinkedsrvlogin 'WebDB', 'false', 'LocalDomain\Administrator', 'DB_User', 'DB_Pwd'GO('DB_User' is SQL authenticated) I have set up a DNS entry for WebDB to map it to the remote DB server IP. The web DB is a default instance, not a named one.sp_helplinkedsrvlogin shows: Linked Server Local Login Is Self Mapping Remote LoginMAINSERVER\SHAREPOINT NULL 1 NULLWebDB NULL 0 DB_UserWebDB LocalDomain\Administrator 0 DB_UserDoes anyone have any clue as to what causes Error 7416? Any ideas on how to troubleshoot this?Mike rmiao Flowing Fount of Yak Knowl