Post reply

Linked server/ very restricted access

  • November 20, 2013 at 2:24 am

    #285369

    We have a user who has access to just one view on one database, and he needs to create a linked server from his database to that view. He has asked me to GRANT CONTROL SERVER to his user, which I'm obviously not going to do, but how can I give him just enough access to create a linked server from his server without elevating his permissions?

    Thanks.

  • November 20, 2013 at 5:53 am

    #1667754

    Beatrix Kiddo (11/20/2013)

    We have a user who has access to just one view on one database, and he needs to create a linked server from his database to that view. He has asked me to GRANT CONTROL SERVER to his user, which I'm obviously not going to do, but how can I give him just enough access to create a linked server from his server without elevating his permissions?

    Thanks.

    does his windows login credentials work for the linked server? He doesn't need CONTROL, he just needs a linked server, i think.

    YOU can create the linked server, and he could try to use it, but a portion depends on how you set up security for the linked server.

    so if he were to connect to the remote SQL server directly, does his windows login already limit the necessary permissions there?

    every login can see the list of linked servers if they query sys.servers, but they might not be able to DO anything with it.

    if a different set of credentials needs to be used when connecting to the linked server, you can map out impersonation of the user on this part of the GUI setup for a linked server.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 20, 2013 at 6:26 am

    #1667773

    Thanks for replying.

    Lowell (11/20/2013)

    does his windows login credentials work for the linked server?

    He logs into my server (A) with a local account on that server. (This is because he's not an employee here, so he doesn't have a domain account.) He logs into SSMS with Windows Authentication, and his login gives him access to just one view on one database.

    He wants to create a linked server TO that database (on server A) FROM a database on his server (B). I know nothing about his database or his server, and I don't have an account on either of them so I can't test it myself.

    Does that help at all? I've been told he HAS to be given access which is fine, but given that the database on A holds payroll data, I want it to be as locked-down as possible.

  • November 20, 2013 at 7:03 am

    #1667793

    ok perfect, if he's using a SQL login, it really goes towards the screenshot i provided.

    on the remote server, a database role (in the apropriate database(s)(ie LIMITED_ACCESS), and make sure that role that has the severly limited access to whatever is appropriate on serverB:

    USE [WHATEVER];

    CREATE ROLE [LIMITED_ACCESS]

    GRANT SELECT ON dbo.VW_Whatever TO [LIMITED_ACCESS];

    GRANT EXECUTE ON dbo.pr_GetData TO [LIMITED_ACCESS];

    now, create a login + database user on ServerB that will be used for this limited access, and add that user to the role above...if you use an EXISTING login/user, more priviledsges than you wnat might result.

    Create LOGIN [ClarkKent] WITH PASSWORD='NotTheRealPassword'

    USE [WHATEVER];

    Create USER [ClarkKent] FOR LOGIN [ClarkKent] ;

    EXEC sp_addrolemember N'LIMITED_ACCESS', N'ClarkKent'

    finally, you add the linked server on server A, and make sure you use the mapping idea fromt eh screenshot: login not defined = not be made, and map your ReportUser to [CLARKKENT], and he will use those credentials when accessing the linked server.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 20, 2013 at 8:12 am

    #1667822

    finally, you add the linked server on server A, and make sure you use the mapping idea fromt eh screenshot: login not defined = not be made, and map your ReportUser to [CLARKKENT], and he will use those credentials when accessing the linked server.

    Thanks again for your patience.

    He is a member of a database role that only grants SELECT on the view.

    I've gone into New Linked Server and under Security selected his local login MyServer\jobloggs.

    The 'not be made' radio button is selected, but I don't have anything for Remote User and Remote Password. Does he have to supply me with those? (Sorry if it's a stupid question but since I don't have access and can't troubleshoot it myself, I don't quite follow what is happening. I have set up loads of linked servers before but never to a database I don't have full permissions on!)

    Thanks

  • November 20, 2013 at 8:28 am

    #1667824

    Beatrix Kiddo (11/20/2013)

    finally, you add the linked server on server A, and make sure you use the mapping idea fromt eh screenshot: login not defined = not be made, and map your ReportUser to [CLARKKENT], and he will use those credentials when accessing the linked server.

    Thanks again for your patience.

    He is a member of a database role that only grants SELECT on the view.

    I've gone into New Linked Server and under Security selected his local login MyServer\jobloggs.

    The 'not be made' radio button is selected, but I don't have anything for Remote User and Remote Password. Does he have to supply me with those? (Sorry if it's a stupid question but since I don't have access and can't troubleshoot it myself, I don't quite follow what is happening. I have set up loads of linked servers before but never to a database I don't have full permissions on!)

    Thanks

    great questions.

    so he's not using a SQL login, but rather a windows login [MyServer\jobloggs]( or is it really [MyDomain\jobloggs]?)

    does he already have permissions established on that remote server then? [ServerB]

    if he already has permissions, AND those permissions are not overreaching, then you skip the whole panel for "Local server login to remote server login mappings."

    and then select the radio button "Be made using the logins current security context"

    if he has NO permissions at all on the Remote server,the steps we described before are good; you just map his windows login to the remote login you created(or need to create?)

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 20, 2013 at 8:43 am

    #1667832

    so he's not using a SQL login, but rather a windows login [MyServer\jobloggs]( or is it really [MyDomain\jobloggs]?)

    No- see what I said above:

    He logs into my server (A) with a local account on that server. (This is because he's not an employee here, so he doesn't have a domain account.) He logs into SSMS with Windows Authentication, and his login gives him access to just one view on one database.

    does he already have permissions established on that remote server then? [ServerB]

    I don't know. I don't have access to that server and the guy is not an employee here so I can't find out easily. But surely his permissions on his server should not dictate his permissions on my server anyway?

    If he worked here we could have sat together and figured this out in no time, but unfortunately he's really abrasive and demanding by email, and he thinks he should be a sysadmin on everything, so I'm struggling a bit with him!

  • November 21, 2013 at 4:05 am

    #1668064

    Hello again. Ok, I've made quite a lot of progress on this now, but please could somebody tell me this if they know?

    If I test the connection (right-click on linked server, Test Connection) from server A to server B and it works, but not the other way round, what might that be indicating? Does a setting need to be changed on Server B, or could it be a firewall issue?

    Thanks.

  • November 27, 2013 at 5:52 am

    #1669433

    In case anybody stumbles across this one day and also needs the answer, it turned out to be a firewall issue. Opening port 1433 resolved the problem.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply