Linked server security
-
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
-
I'm thinking the error at step 3 was a result of the following snippet I pulled from the SQL Server Books Online for sp_addlinkedsrvlogin:
true for useself is invalid for a Windows NT authenticated login unless the Microsoft Windows NT® environment supports security account delegation and the provider supports Windows Authentication (in which case creating a mapping with a value of true is no longer required but still valid).
..........
Rather than having to use sp_addlinkedsrvlogin to create a predetermined login mapping, SQL Server can automatically use the Windows NT security credentials (Windows NT username and password) of a user issuing the query to connect to a linked server when all these conditions exist:
- A user is connected to SQL Server using Windows Authentication Mode.
- Security account delegation is available on the client and sending server.
- The provider supports Windows Authentication Mode (for example, SQL Server running on Windows NT).
After the authentication has been performed by the linked server using the mappings defined by executing sp_addlinkedsrvlogin on the local SQL Server, the permissions on individual objects in the remote database are determined by the linked server, not the local server.
So apparently the answer is yes for you since you don't appear to be running SQL Server authenticated logins.
-
Thanks for the reply! 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
-
We use SQL Server authentication here so I can't really help you out anymore. Could another reader please feel free to jump in?
The only suggestion I would have would be to set up your linked server using an explicite username/password briefly just to verify that your test query is functional and that the user really does have the required authority. If it does work that way then the problem truely is what you expect it is - authentication isn't being passed through for some reason.
-
Here's a strange thing. If I put the query that I tested into a job and run the job, it finishes successfully! SQL Server agent is running under the same account that i logged into QA with. Not sure why it works there and not in QA. When I set up the job step, it gives me the error below initially but then the job runs successfully and gives me the results of the query statement.
Any ideas?
John
-
I'm affraid I can't see your hard drive. Mind fixing your link to point to a public site or posting a text version of it? Rename "Error.jpg" on your desktop (and possibly then clear your browser cache) to see what I'm talking about.
-
Sorry about that. Here is what the error message says:
The server detected the following syntax errors in the command: Error 7314: OLE DB provider 'server2' does not contain table 'Rbc_datawarehouse.dbo.cmsbatch'. The table either does not exist or the current user does not have permissions on the table. OLE DB error trace [Non-interface error: OLE DB provider des not contain the table: ProviderName='server2',Tablename='Rbc_Datawarehouse.dbo.cmsBatch' Are you sure this is what you want?
John
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply