0

I am trying to select a view using a SQL authenticated account. This view is actually reading a spreadsheet from a local drive (through a LinkedServer). I am getting below error:

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

Currently, the linked server is set to For a login not defined in the list above, connection will be made using the login's current security context. The spreadsheet is on the same server.

How may I resolve this error and select the view using the same account? The account is added as db_owner.

P.S If I run the view using a windows authenticated account, it is working fine

  • When you check the LinkedServer properties do you see a mapping to that SQL Server login under the security menu allowing it to use the linked server? – Ronaldo Oct 16 at 13:43
  • Currently it is set to - For a login not defined in the list above ,connection will be made using the login's current security context. spreadsheet is on the same server. What change do i need to make? – Andleeb Hasan Oct 16 at 13:47
  • @Ronaldo - please suggest me into this. – Andleeb Hasan Oct 16 at 13:59
2

If you check the How to Create a Linked Server doc, you'll understand that your current configuration works like this:

Be made using the login's current security context
Specify that a connection will be made using the current security context of the login for logins not defined in the list. If connected to the local server using Windows Authentication, your windows credentials will be used to connect to the remote server. If connected to the local server using SQL Server Authentication, login name and password will be used to connect to the remote server. In this case a login with the exact same name and password must exist on the remote server.

Since your SQL Server login doesn't exist on windows you can't access the file.
You should create a mapping to that specific login defining a remote user and password with permissions on that server to read the file.

Just beware that anyone logged in SQL Server with that SQL Server user will have the privileges of the Windows login you defined on the mapping.

| improve this answer | |
  • Thanks and One last thing @Ronaldo- If I use the first method, should I keep a windows user as Local user and SQL User (with password ) as Remote user? is this how we do this? – Andleeb Hasan Oct 16 at 14:27
  • @AndleebHasan, I've edited the question and there's no second method (it seems using that second option the specified remote login should be another SQL Server login and you'd have the same problem). About the first method, it should be the other way around: Your SQL Server is the Local login and the Windows account is the Remote user and Remote password. – Ronaldo Oct 16 at 14:35
  • Thanks a lot @ronaldo This worked perfectly for me... I can run the view now – Andleeb Hasan Oct 16 at 15:16
  • Glad I could help, @AndleebHasan. One more thing: if that view isn't the only use of the linked server for that user, by changing the mapping you could break something else, by making the SQL Server login lose permissions on some remote server. If you're not sure of the existence of other remote servers being accessed through that linked server, I advise you to create another local user for that task and keep the previous mapping as it was before. – Ronaldo Oct 16 at 16:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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