Linked server security

[laker42]

I need some help with setting up our linked servers. We have a couple of jobs that run on a weekly basis that need sysadmin access to our linked servers. The rest of the time we only want only the report users that have a login on the linked server to have select access to them. So here is what I tried:

1. I changed the SQL Server Agent to run under a sysadmin login.
2. Under the linked server's properties, I set the connection to be made using the login's current security context.
3. I then tried running the job and it failed saying that it could not login using the NULL username.

I thought this would run under the sysadmin login that SQL Server agent is running under. I have tried several other things. I thought the option of the connection being made using the login's current security context would make it so when a normal user logs in, they would only have public access to the linked servers they have a login on, and when a sysadmin user logs in, they would have full access to the linked servers. I am obviously mistaken and I can't find any good sources on how to set this up. I tried experimenting with the impersonate but I don't have a good understanding of it.

Do I need to setup a remote login for every user that will need access to the linked servers? Eventually all of the windows nt logins will be in windows groups so do I have to create a remote login for each windows group?

Do I need to use Security Account Delegation to accomplish what I want?

I would definitely appreciate any help you can offer.

Thanks!
John

 

[mrdenny]

If you only have a few accounts that will need specific access, I would recommend trying the following.

On the security tab of the linked server, in the "Local server login to remote server login mapping" setup mappings for the high end accounts that need access.

Then for everyone else setup a standard linked server account in the "Be made using this security context" section. See if that works for you.

Getting the "Be made using the login's current security context" to work correctly is fairly tricky.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[laker42]

Thanks for the reply! If I setup remote logins for the highend access, the remote logins are SQL Server correct? I would like to stay away from having to administer remote sql server logins.

I have started setting security account delegation up and am not having too much success. I am following what is in BOL. Here is what I have done:

1. The Account is sensitive and cannot be delegated check box must be selected for the user requesting delegation - check!
2. The Account is trusted for delegation check box must be selected for the service account of SQL Server - check!
3. The Computer is trusted for delegation check box must be selected for the server running an instance of MS SQL Server - check!
4. Run the following code on our domain administrator server:
setspn -A MSSQLSvr/server1.rbc.org:1433 serviceaccountlogin
setspn -A MSSQLSvr/server2.rbc.org:1433 serviceaccountlogin
5. I then setup a linked server on server1 to server2. The security setting I used was Be made using the login's current security context. Is this right or should I use another setting for delegation?
6. I then logged onto server1 and tried to run a query against server2 and got the following error:
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'Rbcdba' does not contain table '"rbc_datawarehouse"."dbo"."cmsbatch"'. The table either does not exist or the current user does not have permissions on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='Rbcdba', TableName='"rbc_datawarehouse"."dbo"."cmsbatch"'].
The table does exist so I assume that I need to do something else to get the account delegation to work? Any ideas what else I need to do? I believe I followed all of the steps in BOL.

Thanks!
John

 

[mrdenny]

Sounds like the pass through is working correctly. Mabey it's a permissions issue?

While using a sysadmin account try running select * from server2.master.dbo.sysdatabases. If that works, then everything is working correctly.

It sounds like everything is setup correctly.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[laker42]

Denny,
I can run the select statement that you mentioned and it returns results. If I try against the MSDB or any user databases, it doesn't return anything. The account I am signing into QA with is a sysadmin account on both servers.

John

 

[mrdenny]

ok, now that's just weird. The query is deffinetly getting to the other server, but it doesn't think that it's got access to anything else. Check your linked server mappings mabey. If you can only access the basic systems tables in master then your probably comming over with the guest account for some reason.

I'm not sure what would cause that to happen.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[laker42]

Yeah, I don't know what is going on. I can schedule a job that issues the same query and the job will run successfully. SQL Agent is running under the same login that i used to login to QA. So I don't know what is happening. I am about ready to call tech support.

Thanks,
John