Post reply

Select query across linked server using security context

  • August 22, 2005 at 9:13 am

    #164866

    Hi,

    I have set up a linked server using a security context, say test and with the password test, which is a valid login on the linked server. I want script a job to make a selection from the linked server using the security context. The job on the base server runs under dbo and not the the test login and gives a 18452 error.

    How does one script the select query using the test login security context?

    The select query is of the type:

    EXEC SQL SELECT * INTO ServerATable FROM ServerB.DatabaseName.dbo.TableName

    I have tried GRANTing permissions but can't seem to get it right.

    I don't want to change the link to dbo as I want to control access to the linked server databases.

    Hoping someone can help.

    Denis

  • August 22, 2005 at 7:40 pm

    #584126

    The query looks ok.

    In your linked server you can specify which accounts can be mapped to the remote server. If you choose the Connections: Not be Made option, the only connections that can access the remote server are the ones in the list.

    As an Agent job it is likely the account that will run the job will be the one that starts SQL Agent.

    I have the SQL Agent connection (NOT Startup account) set to sa. I did this specifically to work with my linked servers.

    So, look at the account mapping in the linked Server, the Account and connection properties of SQL Agent, and the Run As option in the Scheduled job.

    Hope that helps,

    DB


    The systems fine with no users loggged in. Can we keep it that way ?br>

  • August 24, 2005 at 1:50 am

    #584492

    Thanks DB,

    To clarify things further I administer the linked server and am wanting the central DBA to enable this job to run. The message I get from the centre is that all the jobs run as sa, which I assume is what SQL Agent starts up with. The problem being that when the job is set to Run As anything but the sa account the job fails.

    I will try it again with them and see if it does run under this context. My problem is that I have no administration rights on the central server and can't even see any of the properties and configuration. I have to send my requests into the black hole and hope the red tape does not strangle it. I am wanting to put the information on the central database so it is available across the company intranet.

    My other posibility is to make the linked connection with the Not be Made option but then that exposes all my local databases.

    Thanks for the clarification.

Viewing 3 posts - 1 through 2 (of 2 total)

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