Developing Matt

Setting up a linked server and accessing that server within a certain security context is easy.  Just add the server to the linked server collection (under server objects) and set your user/pass in the security section of the friendly GUI.  (Here’s the t-sql version:)

sp_addlinkedserver [ @server = ] ’server’
    [ , [ @srvproduct = ] ‘product_name’ ]
    [ , [ @provider = ] ‘provider_name’ ]
    [ , [ @datasrc = ] ‘data_source’ ]
    [ , [ @location = ] ‘location’ ]
    [ , [ @provstr = ] ‘provider_string’ ]
    [ , [ @catalog = ] ‘catalog’ ]

For the sake of our discussion I set the security on this linked server to MY_REMOTEUSER_NAME.

That works.  I can now execute select top 188 * from SERVERNAME.DATABASE.DBO.TABLE all day long (full qualification of server is necessary).  I can even run it from within a stored procedure.  The problem I had was when I wanted to run the procedure from a job.  When I did I received the error:

The job failed.  Unable to determine if the owner (DOMAIN\MCLINGAN) of job JOBNAME has server access (reason: Could not obtain information about Windows NT group/user ‘DOMAIN\MCLINGAN’, error code 0×5. [SQLSTATE 42000] (Error 15404)).

This is obviously a security problem.  I tried Execute AS in my stored proc…..didn’t work.  Recognizing that jobs run under the server agent login I tried adding it to my linked server, but my linked server security already stated that for a login not defined in the list connections will be made using MY_REMOTEUSER_NAME.  Adding any of the mssql defined logins to my linked server proved pointless. 

The following also didn’t work.  It seemed like a reasonable solution to me…enabling you to run a query specifying a linked server

select * from OPENQUERY(LINKEDSERVERNAME, ’select top 188 * SERVERNAME.DATABASE.DBO.TABLE’ )

The solution seemed strange to me:

DID YOU KNOW that the owner of the package was more than just informational?  it has direct security implications to the steps therein.  My solution:

1.  Add MY_REMOTEUSER_NAME to the database server and the database with the correct permissions of course.

2.  Modify the job owner to MY_REMOTEUSER_NAME that I added in step one.

BONUS:  When you use a linked server and operate under your database to connect to it you can use all the bells and whistles of sql serer 2008 on a 2000 database.  I have a linked server to a 2000 box and I can use my 2008 instance to query it with merge, rank, etc.