Linked Server Restriction

Question

Hi,

When we create a linked server object any user in the instance gets access to it,which is not pleasant for me.

I need to specify which users can have access to the linked server object.

please help.

Answer 1

You need to specify a restricted user when you configure a linked server

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 2

Hi Arash,

I believe you want to hide the linked server for particular user and they should not allowed to run query using the linked server. I believe that is not possible.

Linked server are used to access remote datasources and user who is using the linked server is authenticated on the remote server(where the linked server is pointing to) not on that logged in sql instance.

So i am sure what you want to achieve by just hiding the linked server from a user?

---

Regards Harsh

Answer 3

Thanks for responding,

I am not talking about the receiving server side,

What I mean is that after creating a linked server using administrator(sysadmin) on the sending server,

any other user on this side is able to query and use the linked server by Tsql.

But I want to grant this access only to my desired user and revoke it from others.

Answer 4

A linked server is not a securable, that is, you cannot grant or deny permissions to it.

As Harsh says, users cannot do much with the linked server as such, if they don't have access to the remote server. If you do things like setting up login mappings, you need to be careful and not set up mappings for users that should have access to the remote server.

But I agree that it would be useful to be able to restrict access to a linked server, particularly when then linked server is something with no means of authentication of its own like an Excel file.

I tried to search on https://connect.microsoft.com/SQLServer/feedback/
to see if there are any existing Connect items you could vote for, but surprisingly enough I did not find any. I encourage you to submit an item. When you submit an item do not only say that you want to restrict access to linked servers, but also why. Microsoft puts much more value into your suggestion if you give a good business reasons.

If you post a link to your item here, I will give it my vote.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 5

Erland,

Unfortunately such ability exists in Oracle but not in MSSQL.

I am really thankful for your concern.

Answer 6

It may be the case that linked servers works differently in Oracle, so it is more of a concern there. But nevertheless, if you enter a Connect item, don't fail to mention the point about Oracle. That kind of ticks Microsoft off.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 7

Hello,

As per my understanding, there is no way to "hide" a linked server in SSMS Object Explorer or prevent unauthorized users from trying to use the linked server. But we can control is whether user can have access to linked server by specify log mapping.
When SQL Server connects to a linked server to execute a distributed query or a stored procedure, SQL Server looks for any login mappings for the current login that is executing the query or the procedure. If there is a login mapping, SQL Server sends the corresponding remote login and password when it connects to the linked server. Distributed query or a stored procedure will failed if there is no login mapping for current local login.

Reference:http://technet.microsoft.com/en-us/library/ms175537(v=sql.105).aspx

Regards,
Fanny Liu

---

Fanny Liu
TechNet Community Support

Answer 8

Thanks to you Fanny,

I am sure your suggestion will solve my problem.I tried to create the linked server mapping a local SQL Authenticated user to a Remote SQL Authenticated user as described in the link below

http://www.databasejournal.com/features/mssql/article.php/3691721/Setting-up-a-Linked-Server-for-a-Remote-SQL-Server-Instance.htm

But I keep getting an error.If it is possible show me how to do it.

---

Regards  Arash

Answer 9

This picture shows the concept of what I want to do.

And these are the steps I passed.

Step1

Answer 10

Step2

Step3

Answer 11

EXEC sp_addlinkedserver MyMediaCatalog, '', 'MediaCatalogOLEDB', '10.255.138.39'
EXEC sp_addlinkedsrvlogin MyMediaCatalog, 'false', 'arash', 'arash', 'thepassword'
EXEC sp_testlinkedserver MyMediaCatloag

Notes:
1) Media Catalog is unknown to me, so I don't know the exact name for the OLE DB Provider.
2) The first user name in the call to sp_addlinkedsrvlogin is the login name in SQL Server. The second is the user name in MediaCatalog.
3) The call to sp_testlinkedserver will fail, if you are not logged in as arash in SQL Server.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 12

Since srvproduct is SQL Server provider is not to be mentioned.

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'10.255.138.30', @srvproduct=N'SQL Server'
GO

Answer 13

Finally the problem is solved.

After doing as I described ,I was just supposed to ignore the error at the last step and click on Yes button.

Then to test the link I needed to log in with the local sql server user.

I specially thank

      Fanny Liu for his suggestion

      and Erland Sommarskog for this hint

          "3) The call to sp_testlinkedserver will fail, if you are not logged in as arash in SQL Server."

I really appreciate your help.

Thank to you all.