0

I want to allow a non-admin account the ability to query a linked server.

If I create a linked server with the following under a sysadmin account like below.

EXEC master.dbo.sp_addlinkedserver @server = N'LINKEDSERVER', @srvproduct=N'', @provider=N'IhOLEDB.iHistorian', @datasrc=N'linkedservername'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LINKEDSERVER',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

When I run SELECT * FROM OPENQUERY(LINKEDSERVER, 'SELECT * FROM ihTags') from a sysadmin account (no matter what account it is), it works.

If I run the query from a non-sysadmin account I get the following error.

Msg 7416, Level 16, State 2, Line 2
Access to the remote server is denied because no login-mapping exists.

Improve this question
10
  • 2
    Wrap those commands in a stored procedure and sign it. See this and this tutorial. This may also be useful.
    – Aaron Bertrand
    Oct 9, 2018 at 21:39
  • @AaronBertrand same issue "Access to the remote server is denied because no login-mapping exists." My SP creates the LinkedServer, however when I run a query like above it gives the error message.
    – Kevin Vasko
    Oct 10, 2018 at 14:09
  • You need to create a stored procedure that runs the select command(s), sign it, and have your users call that. It doesn't matter who created the linked server, it matters who is trying to query from it...
    – Aaron Bertrand
    Oct 10, 2018 at 14:16
  • @AaronBertrand But people need to be able to run any query they want, not just a single static query. The only option I can think of is allowing the user to pass in an entire query to the SP such as the entire string "SELECT * FROM OPENQUERY(LINKEDSERVER, 'SELECT * FROM ihTags')" but that seems extremely hacky. Is there not another way?
    – Kevin Vasko
    Oct 10, 2018 at 14:22
  • If you want them to run any arbitrary query against a linked server, then yes either you need to execute dynamic SQL from within your signed procedure (they only need to pass the SELECT * FROM ihTags bit if you know this will always be run against LINKEDSERVER), or you'll have to give them appropriate permissions against the linked server. This doesn't mean sysadmin but it's obviously more than they've got now, and I'm not sure if you're using Windows credentials or not but you'll need to add additional logins with the right permissions using sp_addlinkedsrvlogin.
    – Aaron Bertrand
    Oct 10, 2018 at 14:50

1 Answer 1

Reset to default
1

According to Article: Access to the remote server is denied because no login-mapping exists.

Need to specify User ID in the provider string if Non-SYSAdmin accounts are used.

Example: 

EXEC master.dbo.sp_addlinkedserver 
    @server = N'MyLinkServerName',
    @provider = N'SQLNCLI',
    @srvproduct = 'SQLNCLI',
    @provstr = N'SERVER=MyServerName\MyInstanceName;User ID=myUser'
Improve this answer
1
  • Thanks. So providing a username EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LINKEDSERVER',@useself=N'True',@locallogin=N'MyLocalAccountRO',@rmtuser=NULL,@rmtpassword=NULL seems to have gotten past that error message. However, now I'm getting a different error message. Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "IhOLEDB.iHistorian" for linked server "LINKEDSERVER". But this seems to be a different problem and I'll create a different question about it.
    – Kevin Vasko
    Oct 11, 2018 at 16:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.