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
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